This project is archived and is in readonly mode.
ActiveRecord: :select is wiped when doing a join
Reported by Oleg | May 1st, 2008 @ 03:35 PM
First of all, a small annoying bug:
CompanyActivitiesSummary.find(:all, :select => 'company.id, sum(active_users_count)', :group => 'company_id', :include => :company)
produces this SQL: SELECT company.id, sum(active_users_count) FROM company_activities_summaries GROUP BYcompany_id
Notice the lack of space between 'GROUP BY' and 'company_id'
I'm very happy that :include parameter doesn't wipe :select any more, but sadly:
CompanyActivitiesSummary.find(:all, :select => 'company.id, sum(active_users_count)', :group => 'company_id', :include => :company, :order => 'companies.name')
As expected it does the join instead of 2 selects, but once again it throws away the :select producing all familiar junk like this:
SELECT `company_activities_summaries`.`id` AS t0_r0, `company_activities_summaries`.`company_id` AS t0_r1, `company_activities_summaries`.`on_date` AS t0_r2, ... LEFT OUTER JOIN `companies` ON `companies`.id = `company_activities_summaries`.company_id GROUP BY company_id ORDER BY companies.name ASC
Wish I could dig around to figure out where exactly this is happening, but I'm not that good with computers.
Thanks
Comments and changes to this ticket
-
Oleg May 6th, 2008 @ 03:09 PM
I'm bumping this again with a test
class AssociationsJoinModelTest < ActiveRecord::TestCase def test_my_little_test p = Post.find(:all, :select => 'author_id, count(comments_count) as my_comment_count', :group => 'author_id', :include => :comments) assert p.first.respond_to?(:my_comment_count) p = Post.find(:all, :select => 'author_id, count(comments_count) as my_comment_count', :group => 'author_id', :include => :comments, :order => 'comments.type') assert p.first.respond_to?(:my_comment_count), :message => 'joining kills my custom select' end end
And this is generated SQL.
First find call (Notice that eager loading is whacked too now, previous revisions worked fine though):
SELECT author_id, count(comments_count) as my_comment_count FROM `posts` GROUP BY author_id SELECT `comments`.* FROM `comments` WHERE (`comments`.post_id IN (NULL)) ORDER BY body
Second find call:
SELECT `posts`.`id` AS t0_r0, `posts`.`author_id` AS t0_r1, `posts`.`title` AS t0_r2, `posts`.`body` AS t0_r3, `posts`.`type` AS t0_r4, `posts`.`comments_count` AS t0_r5, `posts`.`taggings_count` AS t0_r6, `comments`.`id` AS t1_r0, `comments`.`post_id` AS t1_r1, `comments`.`body` AS t1_r2, `comments`.`type` AS t1_r3 FROM `posts` LEFT OUTER JOIN `comments` ON comments.post_id = posts.id GROUP BY author_id ORDER BY comments.type
-
Oleg May 6th, 2008 @ 03:20 PM
Ignore my comment about eager loading being borked, I forgot I needed to do :select => 'id' as well. Issue still stands, however.
-
Frederick Cheung May 6th, 2008 @ 05:36 PM
That's the way it's always been as far as I know. Sounds like you just want :joins instead of :include.
-
Oleg May 6th, 2008 @ 06:14 PM
Well, damn... You are correct. :joins actually works in this case. No reason why :include shouldn't work as well though. Rather misleading thinking that they can be used somewhat interchangeably.
No idea why I didn't try :joins right away.
Thanks :)
-
Frederick Cheung May 6th, 2008 @ 09:21 PM
I fear you've rather got the wrong end of the stick. :joins just write out all those joins for you, nothing else (prior to 2.0 you'd just write out the joins yourself). :include does that and then does a lot of wiring behind the scenes so that all the associations are loaded in one go. It's that fancy wiring that requires messing with :select. It's a waste to use :include when all you needed was :joins
-
James Kebinger June 6th, 2008 @ 02:38 AM
just noticed this - this bug actually is the cause of a bug I just added to #306 where eager loading relies on a column aliased as _parent_id in the :select that get clobbered because there's an :include specified
-
kos August 15th, 2008 @ 10:28 PM
- Tag set to activerecord, edge, joins
In case you still looking for solution (though this post is 3 month old) you can consider using select_with_include plugin (http://assertbuggy.blogspot.com/... or take a look at the following patch (http://dev.rubyonrails.org/attac...>
I faced the same problem not long ago. Unfortunately solutions mentioned above didn't work for me... Still searching :(
-
Andy Waite September 9th, 2008 @ 01:39 PM
It would be helpful if ActiveRecord generated an exception if you attempted to use :select in combination with :include.
-
Pratik December 20th, 2008 @ 03:49 PM
- Assigned user set to Frederick Cheung
-
Frederick Cheung December 20th, 2008 @ 10:59 PM
- State changed from new to wontfix
-
Ryan Bigg October 9th, 2010 @ 09:57 PM
- Tag cleared.
- Importance changed from to Low
Automatic cleanup of spam.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
People watching this ticket
Referenced by
- 306 Eager fetching a HABTM fails when the HABTM uses the "include" option I suspect this problem is linked with #77 http://rails.l...
- 306 Eager fetching a HABTM fails when the HABTM uses the "include" option I suspect this problem is linked with #77 http://rails.l...