[LRUG] association finder query problem

[Matthew Rudy Jacobs]

That's fine
(although I have a feeling it'll break in 2.1, with the changes to
eager-loading, if Freddy is right)

But, I just feel like we're cheating using :include, when our goal is not
actually to :include.
And, it'll have us ending up with a "Monitor" with a random set of
"features" eager loaded
(all those that are "big", I think?)

Perhaps the best way, if we don't mind doing multiple queries is;

big_ids    = Feature.find(:all, :select => "DISTINCT monitor_id",
:conditions => {:name => "big"}).map(&:monitor_id)
white_ids = Feature.find(:all, :select => "DISTINCT monitor_id", :conditions
=> {:name => "white"}).map(&:monitor_id)

Monitor.find(big_ids & white_ids, :include => :features)

Although,
I don't really understand why the models are set up like this.

Feels like,

Monitor
  MonitorFeature
Feature

2008/5/24 Ben Griffiths <bengriffiths at gmail.com>:

> 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
> >
> _______________________________________________
> 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/b3a1000d/attachment-0003.html>