This project is archived and is in readonly mode.

#77 ✓wontfix
Oleg

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

    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

    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

    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

    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

    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

    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

    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

    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

    Pratik December 20th, 2008 @ 03:49 PM

    • Assigned user set to “Frederick Cheung”
  • Frederick Cheung

    Frederick Cheung December 20th, 2008 @ 10:59 PM

    • State changed from “new” to “wontfix”

    Not convinced of the real world usefulness. #1060 is a similar ticket, the attached patch has been pluginised here

  • Ryan Bigg

    Ryan Bigg October 9th, 2010 @ 09:57 PM

    • Tag cleared.
    • Importance changed from “” to “Low”

    Automatic cleanup of spam.

  • klkk

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>

Referenced by

Pages