Multiple joins to the same model using multiple belongs_to associations

Question

I have a class Agreement that has two belongs_to associations to a Member class - referenced as primary and secondary:

class Agreement < ActiveRecord::Base
  belongs_to :primary, class_name: 'Member'
  belongs_to :secondary, class_name: 'Member'
  ...

  def self.by_member(member_attribute_hash)
    # returns any agreements that has a primary OR secondary member that matches any of the values 
    # in the member_attribute_hash
    ...
  end
end

The Member class has no knowledge of the association with the Agreement class - it does not need to:

class Member < ActiveRecord::Base
  # contains surname, given_names, member_number
  ...

  def self.by_any(member_attribute_hash)
    # returns any member where the member matches on surname OR given_names OR member_number
    ...
  end
end

What I would like to do is search for all agreements where the primary or secondary member matches a set of criteria.

From previous work (see question #14139609), I've sorted out how to build the conditional where clause for Member.by_any().

Trying to reuse that method while search for Agreements led me to try this:

class Agreement < ActiveRecord::Base
  ...

  def self.by_member(member_attribute_hash)
    Agreement.joins{primary.outer}.merge(Member.by_any(member_attribute_hash)).joins{secondary.outer}.merge(Member.by_any(member_attribute_hash))
  end
end

On running this in the console, with a member_attribute_hash = {surname: 'Freud'}, the generated SQL fails to honour the alias generated for the second join to member:

SELECT "agreements".* 
FROM "agreements" 
  LEFT OUTER JOIN "members" 
        ON "members"."id" = "agreements"."primary_id" 
  LEFT OUTER JOIN "members" "secondarys_agreements" 
        ON "secondarys_agreements"."id" = "agreements"."secondary_id" 
WHERE "members"."surname" ILIKE 'Freud%' 
  AND "members"."surname" ILIKE 'Freud%'

Notice the duplicate conditions in the WHERE clause. This will return Agreements where the primary Member has a surname like 'Freud', but ignores the secondary Member condition because the alias is not flowing through the merge.

Any ideas?

erroric · Accepted Answer · 2013-03-07T15:59:43.930

After struggling to understand this, I ended up replacing the Member.by_any scope with a Squeel sifter:

class Member < ActiveRecord::Base
  # contains surname, given_names, member_number
  ...

  def self.by_any(member_attribute_hash)
    # returns any member where the member matches on surname OR given_names OR member_number
    squeel do
      [
        (surname =~ "#{member[:surname]}%" if member[:surname].present?),
        (given_names =~ "#{member[:given_names]}%" if member[:given_names].present?),
        (member_number == member[:member_number] if member[:member_number].present?)
      ].compact.reduce(:|)
      # compact to remove the nils, reduce to combine the cases with |
    end
  end
end

The only difference (code-wise), bewteen the sifter and the scope is the replacement of the where in the scope with squeel in the sifter.

So, instead of using a merge to access the Member.by_any scope from the Agreement model, I was now able to reference the Member :by_any sifter from the Agreement model. It looked like:

class Agreement < ActiveRecord::Base
  ...

  def self.by_member(member_attribute_hash)
    Agreement.joins{primary.outer}.where{primary.sift :by_any, member_attribute_hash}.joins{secondary.outer}.where{secondary.sift :by_any, member_attribute_hash}
  end
end

This fixed the aliasing issue - begin celebrating!:

SELECT "agreements".* 
FROM "agreements" 
  LEFT OUTER JOIN "members" 
        ON "members"."id" = "agreements"."primary_id" 
  LEFT OUTER JOIN "members" "secondarys_agreements" 
        ON "secondarys_agreements"."id" = "agreements"."secondary_id" 
WHERE "members"."surname" ILIKE 'Freud%' 
  AND "secondarys_agreements"."surname" ILIKE 'Freud%'

but I still wasn't getting the results I expected - celebration put on hold. What was wrong? The AND in the where clause was wrong. After a bit more digging (and a night away from the computer), a refreshed mind decided to try this:

class Agreement < ActiveRecord::Base
  ...

  def self.by_member(member_attribute_hash)
    Agreement.joins{primary.outer}.joins{secondary.outer}.where{(primary.sift :by_any, member_attribute_hash) | (secondary.sift :by_any, member_attribute_hash)}
  end
end

Producing this:

SELECT "agreements".* 
FROM "agreements" 
  LEFT OUTER JOIN "members" 
        ON "members"."id" = "agreements"."primary_id" 
  LEFT OUTER JOIN "members" "secondarys_agreements" 
        ON "secondarys_agreements"."id" = "agreements"."secondary_id" 
WHERE ((("members"."surname" ILIKE 'Freud%') 
   OR ("secondarys_agreements"."surname" ILIKE 'Freud%')))

Ah sweet... restart celebration... now I get Agreements that have a primary or secondary Member that matches according to the rules defined in a single sifter.

And for all the work he's done on Squeel, a big shout-out goes to Ernie Miller.

Multiple joins to the same model using multiple belongs_to associations

1 Answers1

Linked