Rails Ruby on Rails → 2.x

Comments and changes to this ticket

• You flagged this item as spam.
  

  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

• You flagged this item as spam.
  

  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

• You flagged this item as spam.
  

  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

• You flagged this item as spam.
  

  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.

• You flagged this item as spam.
  

  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

• You flagged this item as spam.
  

  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.

• You flagged this item as spam.
  

  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 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 August 6th, 2009 @ 02:08 PM

  • Tag set to “bugmash”
• 

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

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

• You flagged this item as spam.
  

  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 ?

• You flagged this item as spam.
  

  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 October 7th, 2010 @ 05:21 AM

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

  [bulk edit]

• You flagged this item as spam.
  

  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.

• You flagged this item as spam.
  

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

  Confirmed that this problem exists in 2.3.5 as well.