[LRUG] association finder query problem

[Matthew Rudy Jacobs]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20080524/1264748e/attachment-0003.html>