How to use Arel to query across a polymorphic has_one relationship

Question

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.

Answer 1

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.