#3 ✓resolved
Georg Ledermann

InnerJoin is wrong for "any == true"

Reported by Georg Ledermann | September 24th, 2008 @ 03:12 AM

Using "INNER JOIN" is not the right choice if conditions.any == true.

Example:

search = User.new_search :conditions => { :name_contains => 'Ben',
                                          :orders => { :total_gt => 100 } }
search.conditions.any = true

Using INNER JOIN requires both criteria to be fulfilled. But with "any==true" you have to do a LEFT JOIN.

Comments and changes to this ticket

  • Georg Ledermann

    Georg Ledermann September 24th, 2008 @ 03:16 AM

    Because if a user named "Ben" does not have any orders, he will not be included in the result set.

  • Ben Johnson

    Ben Johnson September 24th, 2008 @ 11:48 AM

    What sucks about this is that AR provides 2 methods for auto joining tables: :joins and :include. :include performs a left outer join, which would solve your problem. :joins performs an inner join, which is causing the problem. :joins is perferred because it doesn't auto load those associations and is MUCH faster. My only solution is to write my own SQL.

    What do you think about using left outer joins on everything?

  • Georg Ledermann

    Georg Ledermann September 24th, 2008 @ 12:51 PM

    What about using :include just if any==true and using :joins if any==false ? If this is not possible: I prefer using left joins on everything, because correct results are better then fast (but wrong) results.

  • Ben Johnson

    Ben Johnson September 24th, 2008 @ 12:54 PM

    I agree. I think I can pull that off. I just don't want to use :include at all. Because behind the scenes it runs additional queries and instantiates a lot of additional objects. It is much slower.

    What I think would be best is to use left outer joins for any == true and inner joins for everything else.

    I'm working on it now and should have something up shortly.

  • Ben Johnson

    Ben Johnson September 24th, 2008 @ 05:32 PM

    • State changed from “new” to “resolved”

    I released v1.2.0 that should fix this problem. I ONLY use left outer joins when searching by any conditions. Otherwise I stick with inner joins. To my knowledge inner joins are faster so we should use those if we can.

    Let me know if you have any other problems.

  • Georg Ledermann

    Georg Ledermann September 25th, 2008 @ 04:28 AM

    It works fine, thank you very much!

    I agree with you that INNER JOINS are faster and should be used whenever possible. Switching to LEFT JOINS only for any conditions seems to be the perfect way.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.

People watching this ticket

Pages