21202

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 (<a href="https://stackoverflow.com/questions/14139609/can-you-add-clauses-in-a-where-block-conditionally-when-using-squeel" rel="nofollow">see question #14139609</a>), 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?

Answer1:

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 - <em>begin celebrating!</em>:

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 - <em>celebration put on hold</em>. 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... <em>restart celebration</em>... 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.

Recommend

  • Unable to create new Application in Itunes Connect
  • Build Android on Jenkins failed due to licenses
  • How to Calculate current Sales Price of item from Code (after evaluating all trade agreements and di
  • Admob add within multiple activities
  • How to prevent direct access to properties when inheriting from a base provider?
  • Swift #available keyword vs respondsToSelector
  • Deprecation warning for capybara-webkit requiring Qt version 5
  • How Can I Prevent Recurring Automatic Connections to Oracle Database?
  • Get Distinct rows from a result of JOIN in SQL Server
  • How do I conditionally select a field from one of two tables?
  • Find record, that has ALL associated records
  • Sql indexes vs full table scan
  • How can Delete be both a DDL and a DML statement
  • Basic many-to-many left join query
  • Calculate time difference in hh:mm:ss with simple javascript/jquery
  • How can I display the parent menu item's description using Wordpress walkers?
  • Selenium to click on a javascript button corresponding to a text
  • calculate gradient output for Theta update rule
  • Authentication in Play! and RestEasy
  • How do I configure context broker accept post requests from my remote sensor?
  • What's the purpose of QString?
  • Assign variable to the value in HTML
  • Sort List of Strings By Version
  • How to write order and limit within cakephp joins array
  • Blackberry - Custom EditField Cursor
  • How to suppress a dialog
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • Content-Length header not returned from Pylons response
  • Django: Count of Group Elements
  • Ajax Loaded meta Tags
  • Xamarin Forms - UWP Fonts
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • ActionScript 2 vs ActionScript 3 performance
  • How can I estimate amount of memory left with calling System.gc()?
  • Apache 2.4 - remove | delete | uninstall
  • Arrays break string types in Julia
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • Angular 2 constructor injection vs direct access
  • Is there any way to bind data to data.frame by some index?
  • Programmatically clearing map cache