Forum: Ruby on Rails conditions on association include, hacky but more or less solved

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
D74cc7f3b588c1933e5417b99bb1ed59?d=identicon&s=25 Chris (Guest)
on 2009-02-15 06:46
(Received via mailing list)
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.
C64e63b70be7dfed8b0742540b8b27e5?d=identicon&s=25 Mark Reginald James (Guest)
on 2009-02-19 14:07
(Received via mailing list)
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
D74cc7f3b588c1933e5417b99bb1ed59?d=identicon&s=25 Chris Warren (Guest)
on 2009-02-19 15:24
(Received via mailing list)
Mark Reginald James 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
C64e63b70be7dfed8b0742540b8b27e5?d=identicon&s=25 Mark Reginald James (Guest)
on 2009-02-19 15:48
(Received via mailing list)
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/br...

--
Rails Wheels - Find Plugins, List & Sell Plugins -
http://railswheels.com
This topic is locked and can not be replied to.