named scope with group by bug?

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

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

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

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.

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

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.

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.

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.

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.

Confirmed that this problem exists in 2.3.5 as well.