This project is archived and is in readonly mode.

#1349 ✓stale
JensC

named scope with group by bug?

Reported by JensC | November 10th, 2008 @ 10:35 AM | in 2.x

Hi all,

I have a many to many relation that uses named_scopes with a group option. Executing the named scope returns the correct results. Using the named_scope with count however ignores the group by part of the query.

Attached is an example that shows the effect. Hopefully this is a bug and not a feature I should know about.

Thanks again for the great work. Cheers Jens

Comments and changes to this ticket

  • Hubert Łępicki

    Hubert Łępicki November 12th, 2008 @ 11:11 AM

    Yes, I see this is happening also in 2.2 branch.

    Problem is that "count" method doesn't work properly with named scopes when "group by" clause is specified.

    Another example easy to reproduce:

    class Person < ActiveRecord::Base has_many :properties

    named_scope :with_2_props, :include => [:properties], :conditions => ["properties.name in ('admin', 'user')"], :group => "people.id having COUNT(properties.id) = 2"
    
    

    end

    class Property < ActiveRecord::Base belongs_to :person end

    people = Person.with_2_props

    SELECT people.id AS t0_r0, people.name AS t0_r1, people.created_at AS t0_r2, people.updated_at AS t0_r3, properties.id AS t1_r0, properties.person_id AS t1_r1, properties.name AS t1_r2, properties.created_at AS t1_r3,properties.updated_at AS t1_r4 FROM people LEFT OUTER JOIN properties ON properties.person_id = people.id WHERE (properties.name in ('admin', 'user')) GROUP BY people.id having COUNT(properties.id) = 2

    people.size # 2, proper answer

    Person.with_2_props.count # 3, wrong answer

    SELECT count(DISTINCT people.id) AS count_all FROM people LEFT OUTER JOIN properties ON properties.person_id = people.id WHERE (properties.name in ('admin', 'user'))

    As you see, generated SQL query has lost it's GROUP BY clause.

    I am thinking about solution, and I think that one of possible is to change AR count() method so that it wraps records around another select in case named scope was specified inside. Such result query would be for above example:

    Person.with_2_props.count

    select count(count_all) from (SELECT count(DISTINCT people.id) AS count_all FROM peopleLEFT OUTER JOIN properties ON properties.person_id = people.id WHERE (properties.name in ('admin', 'user')) group by people.id having count(properties.id) = 2 ) as virtual_table;

    But I don't have an idea if this is acceptable and meets ActiveRecord standards.

    I would be thankful if any of core devs could advise me here.

    Best, Hubert

  • Hubert Łępicki

    Hubert Łępicki November 12th, 2008 @ 11:20 AM

    Yes, I see this is happening also in 2.2 branch.

    Problem is that "count" method doesn't work properly with named scopes when "group by" clause is specified.

    Another example easy to reproduce:

    class Person < ActiveRecord::Base has_many :properties

    named_scope :with_2_props, :include => [:properties], :conditions => ["properties.name in ('admin', 'user')"], :group => "people.id having COUNT(properties.id) = 2"
    
    

    end

    class Property < ActiveRecord::Base belongs_to :person end

    people = Person.with_2_props

    SELECT people.id AS t0_r0, people.name AS t0_r1, people.created_at AS t0_r2, people.updated_at AS t0_r3, properties.id AS t1_r0, properties.person_id AS t1_r1, properties.name AS t1_r2, properties.created_at AS t1_r3,properties.updated_at AS t1_r4 FROM people LEFT OUTER JOIN properties ON properties.person_id = people.id WHERE (properties.name in ('admin', 'user')) GROUP BY people.id having COUNT(properties.id) = 2

    people.size # 2, proper answer

    Person.with_2_props.count # 3, wrong answer

    SELECT count(DISTINCT people.id) AS count_all FROM people LEFT OUTER JOIN properties ON properties.person_id = people.id WHERE (properties.name in ('admin', 'user'))

    As you see, generated SQL query has lost it's GROUP BY clause.

    I am thinking about solution, and I think that one of possible is to change AR count() method so that it wraps records around another select in case named scope was specified inside. Such result query would be for above example:

    Person.with_2_props.count

    select count(count_all) from (SELECT count(DISTINCT people.id) AS count_all FROM peopleLEFT OUTER JOIN properties ON properties.person_id = people.id WHERE (properties.name in ('admin', 'user')) group by people.id having count(properties.id) = 2 ) as virtual_table;

    But I don't have an idea if this is acceptable and meets ActiveRecord standards.

    I would be thankful if any of core devs could advise me here.

    UPDATE: this issue appears to be related with possible fix (didn't test it) http://rails.lighthouseapp.com/p...

    Best, Hubert

  • JensC

    JensC November 17th, 2008 @ 02:22 PM

    Hi Hubert,

    Yep, thats exactly the bug I have mentioned in my example and it exists in 2.1.2 too. It would be of course great to see this fixed in the 2.2 release. However, just in case, is there an easy way to ask the named scope what sql statement it produces? That would give me a handle to find a wrok-around.

    Again thanx a lot Jens

  • Hubert Łępicki

    Hubert Łępicki November 17th, 2008 @ 02:41 PM

    Hi Jens,

    You can easily see SQL queries generated in log files. I also think that if you operate from Rails console, and have server started in other window (like mongrel), you can see detailed output there too.

    I have figured out that a bug is related to that AR's calculations.rb (whare for example count() is defined) file processes queries much differend than calling find() method - it has even additional params related to grouping, like :having => "something".

    I don't feel like I know AR internals enough to change these, so I gave up for now.

    Best, H.

  • JensC

    JensC November 17th, 2008 @ 03:10 PM

    Hi Hubert,

    Thanks for your very fast answer. True, I know that I can get the sql statement from the logs, but that's not what I meant (sorry not to clear before). I have a pretty complex search form with many options to choose. Dependent what the user has chosen, different named scopes are chained. With the buggy count the used will_paginate (plugin to show the search results page by page), shows the wrong number of of pages. My idea of a workaround would be to ask AR what sql statement it created and use the sql/virtual_table (as you described above) to get the correct count. With the correct count I can convince will_paginate to show the correct number of pages.

    Any ideas how to do this?

    Thanks Jens

  • Hubert Łępicki

    Hubert Łępicki November 17th, 2008 @ 03:50 PM

    Oh I don't know if such functionality exist. I guess you could hack on ActiveRecord and build it yourself quite easily, but it's got obvious disadvantages like need to update your hacks with newer versions of Rails...

    Try asking some forums and newsgroups...

    Good luck, H.

  • Stephen Augenstein

    Stephen Augenstein February 13th, 2009 @ 10:21 PM

    I just ran into this "bug" today and basically here's the issue. If you do a simple COUNT(*) when there is a "GROUP BY" portion to the SQL query, it will do a count for the number of rows in that group (or it does in MySQL). What you are looking for is something like a "SELECT COUNT(*) AS count_all FROM (scope-chained-sql-query) AS sub-query", which returns the number of rows that would be returned by the SQL query. One way to do this is to just call Model.scope1.scope2.length, which should return what you are looking for. The other option is to call Model.scope1.scope2.count(:group => 'GROUP').length.

    I would make a patch that uses the sub-query method when there is a group on one of the named scopes, but I'm pretty new to rails, so I'm not sure if it would have any negative effects on other plugins or existing code. In addition, I'm not sure how well supported sub-queries are across the databases that rails supports. If anyone can confirm that the patch as described would have a chance of being accepted into rails, I'll be happy to write it, but otherwise calling length instead of count is probably the best option.

  • CancelProfileIsBroken

    CancelProfileIsBroken April 22nd, 2009 @ 10:28 PM

    • State changed from “new” to “incomplete”

    Check the patches in #2189, see if they address this case as well. Otherwise we need an actual patch.

  • CancelProfileIsBroken
  • Rizwan Reza

    Rizwan Reza January 20th, 2010 @ 10:51 AM

    • Tag cleared.
    • State changed from “incomplete” to “stale”
  • Smeevil

    Smeevil August 8th, 2010 @ 03:23 PM

    • Tag set to activerecord associations, group, has_many_through_association
    • Importance changed from “” to “”

    I just ran into a similar bug :

    Using :

    has_many :followers, :class_name=>"User", :through=>:received_investments, :source=>:wallet, :group=>"users.id"

    Model.followers runs a query :

    SELECT "users".* FROM "users" INNER JOIN "wallet_transactions" ON "users".id = "wallet_transactions".wallet_id WHERE (("wallet_transactions".investable_item_id = 1) AND (("wallet_transactions"."kind" = 'investment'))) GROUP BY users.id ;

    Model.followers.count runs :
    Model.believers.count : SELECT count(*) AS count_all FROM "users" INNER JOIN "wallet_transactions" ON "users".id = "wallet_transactions".wallet_id WHERE (("wallet_transactions".investable_item_id = 1) AND (("wallet_transactions"."kind" = 'investment')))

    I noticed the ticket has gone Stale, Is there already a patch or a work around ?

  • Smeevil

    Smeevil August 8th, 2010 @ 03:45 PM

    My current "workaround" :

    has_many :followers, :class_name=>"User", :through=>:received_investments, :source=>:wallet, :group=>"users.id"
    def followers 
      return self.followers_with_group_bug.to_a
    end
    

    This prevents followers.count to run an AR query because the result is an array so the count will be on the array items which is correct
    The array items are still AR Proxy objects so you can still use for example : followers.first.wallet etc...

    According to my specs everything still behaves as it should.

  • Rohit Arondekar

    Rohit Arondekar October 7th, 2010 @ 05:21 AM

    • Tag changed from activerecord associations, group, has_many_through_association to activerecord, associations, group, has_many_through_association
  • Jeff Keen

    Jeff Keen December 7th, 2010 @ 11:20 PM

    The easiest workaround I've figured out is doing something like this:

      named_scope :grouped_scope, {:group => "column1, column2"} do
        def count
          self.to_a.size
        end
      end
    

    Chaining scopes and using .count works as desired, using the grouped result.

  • Chris Hapgood

    Chris Hapgood April 13th, 2011 @ 03:09 PM

    Confirmed that this problem exists in 2.3.5 as well.

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>

Attachments

Pages