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
[email protected]
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="[email protected]_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="[email protected]_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.