# Conditions on association include, hacky but more or less solved **URL:** \ **Category:** Rails\ **Created:** [February 15, 2009, 6:46am UTC](https://www.ruby-forum.com/t/conditions-on-association-include-hacky-but-more-or-less-solved/160354 "2009-02-15T06:46:43Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [February 15, 2009, 6:46am UTC](https://www.ruby-forum.com/t/conditions-on-association-include-hacky-but-more-or-less-solved/160354/1 "2009-02-15T06:46:43Z")
Hi all, I just finally figured out how to get 2.2.2 to do this, and thought I’d share in case others run into the same thing. The situation is a find with associations, but the tricky part is that the association shouldn’t always be loaded. This is similar to putting a condition on a has\_many association in a model, but the condition is dynamic rather than predefined. The situation I had is ``` @entity = Entity.find(params[:id]) @traits = Trait.find :all, :include => [:trait_values => [:key_factors]] ``` but I only want the key\_factors that connect to the given entity to be loaded. E.g. I have entities Jane and Marco, and traits Height (with values Short, Medium, and Tall) and HairColor (with values Red, Brown, and Black). Key factors connect an entity with a trait value and an additional descriptor; if Jane is very tall and has reddish-brown hair there would be three key factors Jane-Tall(Height)-Very, Jane-Red (HairColor)-Somewhat, and Jane-Brown(HairColor)-Somewhat. I want an admin to be able to specify / alter the key factors, so I need to show all the trait values and also the current key factors if they exist. For entity Jane I would want Height.Short – key factor not loaded here Height.Medium – key factor not loaded here Height.Tall.Very – key factor IS loaded here HairColor.Red.Somewhat – key factor IS loaded here HairColor.Brown.Somewhat – key factor IS loaded here HairColor.Black – key factor not loaded here and for Marco I’d want Height.Short Height.Medium Height.Tall HairColor.Red HairColor.Brown HairColor.Black In SQL this would be represented by a condition in the join clause: … FROM `traits` LEFT OUTER JOIN `trait_values` ON trait\_values.trait\_id = traits.id LEFT OUTER JOIN `key_factors` ON key\_factors.trait\_value\_id = trait\_values.id AND key\_factors.entity\_id=@entity.id There are two steps to getting this effect. First, you have to get the associations to load in a single query. Second, you have to get the condition into the join clause. By default in 2.2.2 associations are loaded in follow up queries. That is, for @traits = Trait.find :all, :include =\> [:trait\_values =\> [:key\_factors]] you’d get three separate queries (with different where clauses - I have a small, testing data set loaded), one to load the traits, one to load the associated trait values, and one to load the key factors associated with those values: SELECT `traits`.\* FROM `traits` SELECT `trait_values`.\* FROM `trait_values` WHERE (`trait_values`.trait\_id = 1) SELECT `key_factors`.\* FROM `key_factors` WHERE (`key_factors`.trait\_value\_id IN (1,2)) To get that as a single query you need to add dummy conditions to your find that mention the join tables: @traits = Trait.find :all, :include =\> [:trait\_values =\> [:key\_factors]], :conditions =\> “trait\_values.trait\_id = traits.id AND key\_factors.id = key\_factors.id” which then produces SQL like: FROM `traits` LEFT OUTER JOIN `trait_values` ON trait\_values.trait\_id = traits.id LEFT OUTER JOIN `key_factors` ON key\_factors.trait\_value\_id = trait\_values.id WHERE (trait\_values.trait\_id = traits.id AND key\_factors.id = key\_factors.id) Now that final join clause needs to be specified slightly differently (it needs another condition) - this is where it get’s hacky. Various experimentation in trying to solve this problem showed that the association based includes are specified before anything in a joins field, AND the contents of the joins field is directly appended. So, the relevant condition can be specified there, and it will be appended to the auto-generated join. @traits = Trait.find :all, :include =\> [:trait\_values =\> [:key\_factors]], :joins =\> " AND key\_factors.entity\_id="removed\_email\_address@domain.invalid\_s, :conditions =\> “trait\_values.trait\_id = traits.id AND key\_factors.id = key\_factors.id” This generates FROM `traits` LEFT OUTER JOIN `trait_values` ON trait\_values.trait\_id = traits.id LEFT OUTER JOIN `key_factors` ON key\_factors.trait\_value\_id = trait\_values.id AND key\_factors.entity\_id=2 WHERE (trait\_values.trait\_id = traits.id AND key\_factors.id = key\_factors.id) which almost works. The last thing to do is adjust the where conditions to account for the fact that the key\_factors may not be there. @traits = Trait.find :all, :include =\> [:trait\_values =\> [:key\_factors]], :joins =\> " AND key\_factors.entity\_id="removed\_email\_address@domain.invalid\_s, :conditions =\> “trait\_values.trait\_id = traits.id AND IFNULL (key\_factors.id,0) = IFNULL(key\_factors.id,0)”, Which finally produces FROM `traits` LEFT OUTER JOIN `trait_values` ON trait\_values.trait\_id = traits.id LEFT OUTER JOIN `key_factors` ON key\_factors.trait\_value\_id = trait\_values.id AND key\_factors.entity\_id=2 WHERE (trait\_values.trait\_id = traits.id AND IFNULL(key\_factors.id,0) = IFNULL(key\_factors.id,0)) This find specification gets the job done, but it’s a pretty nasty hack. It won’t work if you need this kind of trick on more than the last association, and it won’t work if the SQL builder changes. Ideally You’d be able just to specify the entire join clause in :joins, or else specify it for each :include-ed thing, or else have parametrized associations in the model. I think a fully specified :joins is probably the proper way to do this. Currently having both the includes and the joins causes a “Not unique table/alias” error. I suspect that’s a bug / oversight in the SQL generator. It does a good job of figuring out that JOINs are needed when a table is mentioned in :conditions, but it skips the step of checking to see if that join is already specified in :joins. Even better, since the :conditions are only there to get the associations to join in a single query, the builder could check :joins as well as :conditions to see if it should switch to that method (from the multi-query method), then the dummy conditions could be left out entirely. ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [February 19, 2009, 2:07pm UTC](https://www.ruby-forum.com/t/conditions-on-association-include-hacky-but-more-or-less-solved/160354/2 "2009-02-19T14:07:46Z")
Chris wrote: > in :joins, or else specify it for each :include-ed thing, or else have > parametrized associations in the model. That’s a useful trick to know. An alternative I’ve been using is to dynamically change the :conditions option of association reflections. The advantage is that it works for all includes rather than just the last include, but the disadvantage is that it’s not thread safe, unless you put a lock around the query. I’d like to see explicit support for dynamic conditions, by storing any dynamic change in thread variables. > entirely. > Agree. Or simply an option that forces single-query mode. – Rails Wheels - Find Plugins, List & Sell Plugins - [http://railswheels.com](http://railswheels.com) ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [February 19, 2009, 3:24pm UTC](https://www.ruby-forum.com/t/conditions-on-association-include-hacky-but-more-or-less-solved/160354/3 "2009-02-19T15:24:32Z")
Mark Reginald J. wrote: > > last association, and it won’t work if the SQL builder changes. > > Ideally You’d be able just to specify the entire join clause > > in :joins, or else specify it for each :include-ed thing, or else have > > parametrized associations in the model. > > An alternative I’ve been using is to dynamically change the > :conditions option of association reflections. The advantage > is that it works for all includes rather than just the last include, > but the disadvantage is that it’s not thread safe, unless you > put a lock around the query. How do you dynamically set the association conditions? I’ve done a fair amount of programming, but am new to RoR - I abandoned that approach because I thought the conditions were fixed when the object was defined. Thanks ``` -Chris W ``` ---
**Author:** ![chris](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/958977/32.png) [@chris](https://www.ruby-forum.com/u/chris)\ **Post date:** [February 19, 2009, 3:48pm UTC](https://www.ruby-forum.com/t/conditions-on-association-include-hacky-but-more-or-less-solved/160354/4 "2009-02-19T15:48:48Z")
Chris W. wrote: > How do you dynamically set the association conditions? I’ve done a fair > amount of programming, but am new to RoR - I abandoned that approach > because I thought the conditions were fixed when the object was defined. [http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/16e51993be4f52f6](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/16e51993be4f52f6) – Rails Wheels - Find Plugins, List & Sell Plugins - [http://railswheels.com](http://railswheels.com)