size of array returned by named_scope with :group/:select distinct

rubyonrails-talk
1

Here's the example: $ ruby script/generate scaffold thing name:string class Thing < ActiveRecord::Base   named_scope :distinct, :group => :name end

:select => "DISTINCT things.*" would work in the same way.

Start up script/console:

Thing.new(:name => "a").save!

=> true

Thing.new(:name => "a").save!

=> true

Thing.all

=> [#<Thing id: 1, name: "a", created_at: "2009-10-16 02:34:28", updated_at: "2009-10-16 02:34:28">, #<Thing id: 2, name: "a", created_at: "2009-10-16 02:34:28", updated_at: "2009-10-16 02:34:28">]

Thing.distinct

=> [#<Thing id: 2, name: "a", created_at: "2009-10-16 02:34:28", updated_at: "2009-10-16 02:34:28">]

Thing.distinct.size

=> 2

Thing.distinct.all.size

=> 1

As you can see, Thing.distinct.size is mis-reporting the size of the result. I can't figure out what's causing this, or why adding .all fixes it. Running the query straight into the database predictably returns just one record:

SELECT * FROM "things" GROUP BY name;

        id = 2       name = a created_at = 2009-10-16 02:34:28 updated_at = 2009-10-16 02:34:28

Am I missing something obvious?

2

I just stumbled upon the same problem... From what I can tell, ActiveRecord doesn't add the GROUP BY clause when it's doing a COUNT. Take a look in your log. You should see two nearly identical queries, but one will be a SELECT with the GROUP BY and one will be a COUNT without the GROUP BY.

Something like this:

SELECT things.* FROM things GROUP BY name

and then

SELECT COUNT(*) AS count_all FROM things; # NO GROUP BY!

The thing is, GROUP BY with an aggregate function will return the number of items in each group. So if you have 3 Things, "a", "a", and "b", adding a GROUP BY at the end of the COUNT query will give you something like this:

3

That makes sense - I was thinking of what named_scope returns as an Array, when really it's ActiveRecord::NamedScope::Scope. I see now in the source that it just delegates Array methods to find(:all), which is why you can do things like Thing.distinct.each and why Thing.distinct.to_s returns an Array's string notation. I'm not sure why it has to define a size method, that seems like something it could just hand off to find(:all) too.

I'll have to think about that subquery.

4

I found that calling named_scope.all.size resolves this issue. It's not perfect, but it does the job.