[LRUG] association finder query problem

[Ben Griffiths]

I don't know your application - particularly whether that function is
frequently called, nor the size and cardinality of your database, -
but it might be perfectly reasonable to just use the array
intersection operator (&). Something along the lines of:

monitors = Monitor.find(:all, :include=>:features :conditions => "name
= 'big'") & Monitor.find(:all, :include=>:features :conditions =>
"name = 'white'")

The two queries and unnecessary marshaling, and so on may not be an
issue (only you will know) - and it's much more readable and
intentional than jumping through sql loops.

Actually, having said all that, I don't know if this works with rails
object arrays, should do though? Maybe you could try it and let me
know!

Ben

On 5/24/08, Matthew Rudy Jacobs <matthewrudyjacobs at gmail.com> wrote:
> 2008/5/24 Xin Zheng <xin at zenpow.com>:
>> I want to find a Monitor that is BOTH white and big.
>>
>>My first shot at this:
>>find(:all, :include => :features, :conditions => ["name IN ('white',
>>'big')" )
>>
>>But this query will find Monitors that's EITHER white or big.
>
>
> Yeah,
> the short answer is;
> "that's exactly what you've asked it to do"
>
> ===========
> Your db
> ===========
> - Monitor#1
> -- Feature#1 = big
> -- Feature#2 = white
> -- Feature#3 = small
>
> - Monitor#2
> -- Feature#4 = big
>
> - Monitor#3
> -- Feature#5 = white
>
> - Monitor#4
> -- *no features*
> ===========
>
> Doing an include gets you a left outer join on "features"
> which creates a new line for each feature.
> ===========
> - Monitor#1 & Feature#1(big)
> - Monitor#1 & Feature#2(white)
> - Monitor#1 & Feature#3(small)
> - Monitor#2 & Feature#4(big)
> - Monitor#3 & Feature#5(white)
> - Monitor#4 & *no feature*
> ===========
>
> Then you say
> "name IN (white, big)"
> ===========
> this grabs all those lines which satisfy your requirement
> ===========
> - Monitor#1 & Feature#1(big)
> - Monitor#1 & Feature#2(white)
> - Monitor#2 & Feature#4(big)
> - Monitor#3 & Feature#5(white)
> ===========
>
> Hence,
> you don't get what you want.
>
> And,
> you don't actually intend to ":include" these features anyway, (if you do,
> then my solution is wrong)
> you're just using the :include, because it does the join automatically.
>
> The right solution is debatable;
> can do it with subqueries, or a double "inner join"
>
> but I think subqueries are the right way,
> they look dirty, but it's exactly what you're asking for.
>
> Hopefully MySQL will optimise it correctly.
>
> condition_bit = "EXISTS(SELECT * FROM features WHERE monitor_id =
> monitors.id & features.name = ?)"
> condition_string = ([condition_bit]*2).join(" AND ")
> conditions = [condition_string, "big", "white"]
> Monitor.find(:all, :conditions => conditions)
>
> my friend Freddy Cheung wrote a nice blog post about similar stuff:
> http://www.spacevatican.org/2008/4/29/include-and-conditions
>