[LRUG] association finder query problem

[Xin Zheng]

Thanks for all the help.

Pratik, I have had success with your solution. I had to use:

Monitor.find :all, :joins => [:features, :features], :conditions =>
["features.name = ? AND features_monitors.name
<http://features_items.name/>= ?", 'white', 'big']

Using your original line, mysql could not find features_items.name of the
second join.

It seems strange how Rails renames the second join to 'features_monitors'.
Here's the MySQL output:

SELECT `monitors`.* FROM `monitors` INNER JOIN `monitor_features` ON
monitor_features.piuid = monitors.piuid INNER JOIN `monitor_features`
features_monitors ON features_monitors.piuid = monitors.piuid WHERE
(monitor_features.feature_code = 'white' AND features_monitors.feature_code
= 'big')

In my application, it will need to match multiple features. For 3 joins, the
MySQL join fragment looks like:
INNER JOIN `monitor_features` features_monitors_2

I get it now!

It's to do with this in Monitor:
 has_many :features, :class_name => "MonitorFeatures"

Thanks for your inputs Ben, Mathew and Pratik.

Xin






2008/5/25 Pratik Naik <pratik at thinkwares.com>:

> Try :
>
> Monitor.find :all, :joins => [:features, :features], :conditions =>
> ["features.name = ? AND features_items.name = ?", 'white', 'big']
>
> On Sat, May 24, 2008 at 8:26 PM, Xin Zheng <xin at zenpow.com> wrote:
> > Hi Guys,
> >
> > For the life of me I can't figure this out. I think it requires a MySQL
> > solution than Rails. Anyway, here's my problem illustrated
> >
> > Models:
> >
> > Monitor (computer monitors)
> > has_many :features
> >
> > Feature:
> > name = [big, small, black, white]
> >
> > 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.
> >
> > Can anyone help at all?
> >
> > Xin
> > _______________________________________________
> > Chat mailing list
> > Chat at lists.lrug.org
> > http://lists.lrug.org/listinfo.cgi/chat-lrug.org
> >
> >
>
>
>
> --
> http://m.onkey.org
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20080525/932d02f2/attachment-0003.html>