Merge :joins instead of clobbering them
Reported by Andrew White | June 27th, 2008 @ 05:35 PM | in 2.2
The attached patch enhances with_scope, etc. by enabling the merging of :joins specified either in the new style or old style sql fragments.
1. Where both are new style they are merged in the same manner as :include.
2. Where both are strings they are concatenated together.
3. Where one is a new style and the other is a string then the new style :joins is converted to an sql fragment and then concatenated with the other sql fragment.
4. construct_finder_sql adds the DISTINCT sql keyword to limit the number of rows returned as joins generally multiply the number of rows returned - this is an enhancement to the change in ticket #46.
5. Tests have been added and all tests pass
6. Documentation has been updated to reflect that :joins as well as :include and :conditions are merged.
Comments and changes to this ticket
-
Jeremy Kemper June 28th, 2008 @ 02:06 AM
- → Milestone changed from “” to “2.2”
- → State changed from “new” to “open”
- → Assigned user changed from “” to “Jeremy Kemper”
Hey Andrew, nice patch. Could you explain adding DISTINCT to the finder sql?
-
Andrew White June 28th, 2008 @ 06:22 AM
The DISTINCT is for when you're joining a has_many or habtm association - e.g. a post joined with comments:
Post.find(:all, :joins => :comments, :conditions => 'comments.approved = 1')
Ticket #46 scopes the select to the posts table (i.e. 'posts.*') but if a post has more than one comment approved you'll get back duplicate post instances. Adding 'DISTINCT posts.*' eliminates these duplicate records.
There may by some circumstances where you don't want the DISTINCT in which case you can override it with :select, but the majority of cases would need it so that's why I've added it by default.
-
Pratik July 12th, 2008 @ 03:06 AM
I agree with Andrew that DISTINCT would make sense in a lot of cases. However, using DISTINCT can easily become a performance problem if the query has LIMIT, ORDER BY etc. Apart from that, it'll also change the existing behavior, which can possibly break some stuff. So I'm not sure if it'll be a sensible default or not.
How about adding :distinct option to find ?
Interested in hearing more thoughts on this.
-
Andrew White July 16th, 2008 @ 11:31 AM
We could do a :distinct option and then set it true by default if we detect a has_many or habtm join being specified. This would only be when using the new style join syntax limiting any backward compatibility problems.
-
-
Andrew White July 16th, 2008 @ 12:13 PM
If we don't set it as the default when the destination join has more than one matching row then we'll get duplicate instances of the parent. I can't think of a use case where this would correct or useful.
However looking at count it sets :distinct => true for :include and leaves it out when using :joins, so I guess the precedent is to make the developer specify it.
-
Andrew White July 29th, 2008 @ 03:14 PM
I've modified the patch to only merge :joins and not bother about making the select statement distinct. I'll create a separate ticket and patch for adding a distinct option to find.
One question regarding the distinct option for find - which of the following would be better:
Post.find :all, :distinct => true, :joins => :comments
or
Post.find :distinct, :joins => :comments
-
Pratik July 29th, 2008 @ 03:18 PM
"Post.find :all, :distinct => true, :joins => :comments"
We can always do "Post.all, :distinct => true, :joins => :comments" once that's done.
-
-
-
Repository August 28th, 2008 @ 08:09 PM
- → State changed from “open” to “resolved”
(from [db22c89543f45d7f27847003af949afa21cb6fa1]) Merge scoped :joins together instead of overwriting them. May expose scoping bugs in your code!
[#501 state:resolved]
Signed-off-by: Jeremy Kemper jeremy@bitsweat.net http://github.com/rails/rails/co...
-
David Stevenson September 18th, 2008 @ 07:31 PM
- → Tag changed from “activerecord enhancement joins patch” to “activerecord enhancement joins patch”
Wait!
Is there a reason merge_joins isn't used inside of with_scope to merge joins there too?
Without it in that location, it's not possible to chain multiple named_scopes that have joins.
-
Andrew White September 19th, 2008 @ 06:01 AM
It is used in with_scope - chaining multiple named scopes works fine for me. Do you have an example that doesn't work?
Please Login or create a free account to add a new comment.
You can update this ticket by sending an email to from your email client. (help)
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Source available from github
The Git repository resides at http://github.com/rails
Check out the current development trunk (Edge Rails) with:
git clone git://github.com/rails/rails.git
Creating or reviewing a patch
See the contributor guide.
Creating a feature request
Please don't. If you want a new feature in Rails, you'll have to pull up your sleeves and get busy yourself. Or convince someone else to do it. See the contributor guide on how to get going. But posting them here is just going to lead to ticket root.
Creating a bug report
When creating a bug report, be sure to include as much relevant information as possible. Post the code sample that causes the problem. Preferably, alter the unit tests and show through either changed or added tests how the expected behavior is not occuring.
Security vulnerabilities should be reported via an email to security@rubyonrails.org, do not use trac for reporting security vulnerabilities. All content in trac is publicly available as soon as it is posted.
Then don't get your hopes up. Unless you have a "Code Red, Mission Critical, The World is Coming to an End" kinda bug, you're creating this ticket in the hope that others with the same problem will be able to collaborate with you on solving it. Do not expect that the ticket automatically will see any activity or that others will jump to fix it. Creating a ticket like this is mostly to help yourself start on the path of fixing the problem and for others to sign on to with a "I'm having this problem too".
Tarmo Tänav
Dave Rothlisberger
Steve Richert