Chris
2009-Feb-15 05:45 UTC
conditions on association include, hacky but more or less solved
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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Mark Reginald James
2009-Feb-19 13:07 UTC
Re: conditions on association include, hacky but more or less solved
Chris wrote:> 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. > ... > :joins => " AND > key_factors.entity_id="+@entity.id.to_s, > > 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.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.> 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.Agree. Or simply an option that forces single-query mode. -- Rails Wheels - Find Plugins, List & Sell Plugins - http://railswheels.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Chris Warren
2009-Feb-19 14:23 UTC
Re: conditions on association include, hacky but more or less solved
Mark Reginald James wrote:> Chris wrote: > >> 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. >> ... >> :joins => " AND >> key_factors.entity_id="+@entity.id.to_s, >> >> 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. >> > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Mark Reginald James
2009-Feb-19 14:47 UTC
Re: conditions on association include, hacky but more or less solved
Chris Warren 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 -- Rails Wheels - Find Plugins, List & Sell Plugins - http://railswheels.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---