How to use Arel to query across a polymorphic has_one relationship

user_674989 0
Viewed 258

I am currently working on a project that has a jquery datatables frontend and am using the ajax-datatables-rails gem to handle the backend. After reviewing the examples they link to and am having a challenge.

I have a datatable and it displays data from a table, lets call it Foo, that has a has_one polymorphic relationship with 3 different tables. My search needs to search on a column in that polymorphic relationship. 

def my_filter
    ->(column,value) { Arel.sql("Foo.bar.description").matches("%#{value}%")}
end

One thing I tried was to convert the Arel::Table (a is the arel table)

Foo.where(a[:id].not_eq(nil)).find(
     :all,
     :joins => "bar",
     :conditions => ["bar.description LIKE %asd%", true]
 ).arel_table

However in this case I get a message stating it can't find the id on Foo.

arel datatables activerecord ruby-on-rails
2 Answers

There's two approaches I can think of (and depending on your DBMS, the thing that happens under the covers could be almost the same):

  • create a view for all relevant fields of all the different polymorphic bar types
  • join a dynamic table that gets created on the fly

In both cases, the underlying logic is a UNION command that will look somewhat like the following:

SELECT foo_id, description FROM bars1
UNION
SELECT foo_id, description FROM bars2
UNION
SELECT foo_id, description FROM bars3

In the case of the view, this UNION will make up the content of the view itself.

In the case of a dynamic table, it will look something like this:

SELECT foos.*, bars.*
FROM foos
LEFT JOIN (
  SELECT foo_id, description FROM bars1
  UNION
  SELECT foo_id, description FROM bars2
  UNION
  SELECT foo_id, description FROM bars3
) AS bars ON bars.foo_id = foos.id
WHERE bars.description LIKE 'whatever%'

With a view, the use of Arel is basically the same as usual, only you have to make the JOIN explicit:

Foo.joins("LEFT JOIN bars ON bars.foo_id = foos.id").where("bars.description LIKE ?", description)

With a dynamic table, I'm sure there's way to express it in pure Arel, but personally I'd simply go with SQL instead:

joins_sql = <~SQL.strip_heredoc
  LEFT JOIN (
    SELECT foo_id, description FROM bars1
    UNION
    SELECT foo_id, description FROM bars2
    UNION
    SELECT foo_id, description FROM bars3
  ) AS bars ON bars.foo_id = foos.id
SQL

Foo.select("foos.*, bars.*").joins(joins_sql).where("bars.description LIKE ?", description)

Hope that helps.

user_82719390

Try this way

class Foo < ActiveRecord::Base
  has_one :bar, polymorphic: true

  def view_columns
    @view_columns ||= {
      description: { source: "City.id", cond: by_description },
    }
  end

  def by_description
    ->(column) { joins(:bar).where("description LIKE ?", "%#{column.search.value}%")}
  end
end
user_11178510
Related