I work on a project where there is STI Item with 5 subclasses (Item1, Item2 ... Item5). This STI (items table) is mapped over a join table item_parents to Parent record (parents table) record. The mapping is done via has_many trough:.

Each of the items has two fields: name, code both are strings. Parent has many fields, but for the sake of example let's say it has name, created_at. On the frontend, they are displayed in one table, like this:

Parent.name | Parent.created_at | Item1.name | Item1.code | Item2.name | Item2.code | ...

Users can configure filtering for each of the columns. It can be any combination or no filter at all. For example, they can choose the following combination:

Parent.created_at before 2020.02.22
Item1.name containing 'abc'
Item2.name containing 'xyz'
Item3.code equals 'Z12'

The filtering code implemented like this:

def search(filters)
  filters.reduce(Parent.all) { |query, (key, value)| apply_filter(query, key, value) }
end

def apply_filter(query, key, value)
  case filter_key
  when :parent_name_contains
    query.where(Parent.arel_table[:name].matches("%#{value}%"))
  when :parent_created_at_before
    query.where(Parent.arel_table[:created_at].lt(value))
  when :item1_name_contains
    query.joins(:item1s).where(Item1.arel_table[:name].matches("%#{value}%"))
  when :item2_name_contains
    query.joins(:item2s).where(Item2.arel_table[:name].matches("%#{value}%"))
  when :item1_code_equals
    query.joins(:item1s).where(Item1.arel_table[:name].eq(value))
  when :item2_code_equals
    query.joins(:item2s).where(Item2.arel_table[:name].eq(value))
  # ... and so on for all the filters
  else
    query
  end
end

The problem

When I query by fields of two or more different subclasses of Item, ActiveRecord fails to generate correct WHERE clause. It does not use the alias that it has assigned for the association in JOIN clause.

Let's say I want to filter by Item1.name = 'i1' and Item2.name = 'i2', then what rails generates is this:

SELECT "parents".*
FROM "parents"
         INNER JOIN "item_parents"
                    ON "item_parents"."parent_id" = "parents"."id"
         INNER JOIN "items"
                    ON "items"."id" = "item_parents"."item_id"
                        AND "items"."item_type" = 'Item::Item1'

         INNER JOIN "item_parents" "item_parents_parents_join"
                    ON "item_parents_parents_join"."parent_id" = "parents"."id"
         INNER JOIN "items" "item2s_parents" -- OK. join has an alias
                    ON "item2s_parents"."id" = "item_parents_parents_join"."item_id"
                        AND "item2s_parents"."item_type" = 'Item::Item2'

WHERE "items"."name" = 'i1' 
  AND "items"."name" = 'i2' -- Wrong! Must be "item2s_parents"."name" = 'i2'

As a result, I have zero rows returned, because it's impossible to have an item with name equal to 'i1' AND 'i2' at the same time.

What I tried

It seemed to be a good idea to write a custom joins_item method, that would dig the query and check whether it has other joins called on it before (AR stores such information in query.values[:joins] and query.values[:left_outer_joins]) and if there is, then it would return another Arel::Table instance having the correct alias. If there is nothing joined before, then I don't need alias and return the default Arel::Table.

But then I found out that AR resolves aliases at the moment of building SQL. So even though I could guess the correct alias (or no alias) at the moment of joining it can change in the end. And this is actually what happens when you do left_outer_joins first and then joins. AR always places INNER JOINs before LEFT OUTER JOINs in the resulting SQL.

So the question is...

Is there a way to force AR to alias everything when I do joins or left_outer_joins with Arel, or any other more or less maintainable workaround/fix/monkey patch for this issue?

0

There are 0 best solutions below