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="+@entity.id.to_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="+@entity.id.to_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.