Home > Net >  ActiveRecord - left join on filtered list
ActiveRecord - left join on filtered list

Time:02-17

I have the following classes:

Parent
  has_many :children

Child
  belongs_to :parent
  belongs_to :other

I want to be able to left join parents and children and also filter the children by the other association. I achieved this by writing a custom join clause:

joins("LEFT JOIN children ON children.parent_id=parents.id AND children.other_id IN (#{ids.join(",")})")

It seems like there should be a more Rails-y way to achieve this that doesn't involve a custom join clause. Any ideas?

CodePudding user response:

You could use Arel for that join, because the ActiveRecord joins doesn't allow bind parameters:

children = Children.arel_table
parents = Parent.arel_table
join = children
  .join(parents, Arel::Nodes::OuterJoin)
  .on(
    children[:parent_id]
      .eq(parents[:id])
      .and(children[:other_id].in(ids))
  )
  
Children.joins(join.join_sources)

The idea is to create the LEFT (OUTER) JOIN as it normally is, and then add the and(children[:other_id].in(ids)) which makes the AND ... IN (...).

  • Related