ActiveRecord : LEFT OUTER self-JOIN with conditions on joined table

Question

I have an ActiveRecord class with a reflexive association (note : class names are redacted for non-disclosure reasons) :

 class Thing < ActiveRecord::Base belongs_to :parent, class_name: 'Thing' has_many :children, class_name: 'Thing', foreign_key: :parent_id scope :children, ->{ where(arel_table[:parent_id].not_eq(nil)) } end

I want to get all children, left outer joined to their parent, with an additional condition on the parent if it's present, while still eager-loading the parent.

Usually in this case i do something like :

 Thing.includes(:other_thing).merge(OtherThing.some_scope)

But it's not possible in this case : as this is a self-join, if I merge a scope from Thing the condition will apply to the children things, not the parents.

I tried to do this in raw SQL :

 scope :some_scope, ->{ children.joins(<<-SQL).references(:parent) LEFT OUTER JOIN things AS parents ON things.parent_id = parents.id AND parents.some_field IN ('foo', 'bar') SQL }

The query is OK, but it seems that the parent does not get eager-loaded (n+1 queries when trying to map(&:parent) on this scope).

For now, my more-or-less working solution is to use a sub query (plucking ids that match the parent condition, then selecting children with parent_id IN this set), but I'd like to know if there is a more appropriate / elegant one.

PinnyM · Accepted Answer · 2013-11-21 16:27:08Z

I haven't found a way to do this using merge (which seems to insist on using the model table_name), but you can do this using includes and building the condition around the expected alias - you'll need to modify this alias according to your own naming:

User.includes(:parent). where('parents_users.some_field IN (?)', ['foo', 'bar']). references(:parents_users)

However, you may need to watch out for NULL parent id's since the condition will be added to the WHERE clause and not the LEFT JOIN itself:

User.includes(:parent). where('parents_users.some_field IN (?) OR parents_users.id IS NULL', ['foo', 'bar']). references(:parents_users)

This should perform all eager loading properly.

+1, thanks for looking into this. I did not mention this solution, but I tested it, too (I was just missing the references part). I guess that's an improvement over what I have now, but I'm not very fond of "guessing" what kind of alias AR will pull out of its magician hat...

Collectives™ on Stack Overflow

ActiveRecord : LEFT OUTER self-JOIN with conditions on joined table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related