# Size of array returned by named\_scope with :group/:select distinct

**URL:** <https://www.ruby-forum.com/t/size-of-array-returned-by-named-scope-with-group-select-distinct/177401>\
**Category:** Rails\
**Created:** [October 16, 2009, 10:35am UTC](https://www.ruby-forum.com/t/size-of-array-returned-by-named-scope-with-group-select-distinct/177401 "2009-10-16T10:35:19Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![asetzler](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ac91a4/32.png) [@asetzler](https://www.ruby-forum.com/u/asetzler)\
**Post date:** [October 16, 2009, 10:35am UTC](https://www.ruby-forum.com/t/size-of-array-returned-by-named-scope-with-group-select-distinct/177401/1 "2009-10-16T10:35:19Z")

</div>

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:

sqlite\> 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?

---

<div class="post-metadata">

**Author:** ![asetzler](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ac91a4/32.png) [@asetzler](https://www.ruby-forum.com/u/asetzler)\
**Post date:** [October 16, 2009, 6:56pm UTC](https://www.ruby-forum.com/t/size-of-array-returned-by-named-scope-with-group-select-distinct/177401/2 "2009-10-16T18:56:25Z")

</div>

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:

±----------+  
| count\_all |  
±----------+  
| 2 | # \<= a’s  
| 1 | # \<= b  
±----------+

What you really want to know is the number of rows in that resultset,  
since that’s the number of groups in your query. I’m not sure if  
there’s a good way of doing that without running a subquery. Any  
ideas?

Also, the reason why Thing.distinct.all.size works is because .all  
converts the association proxy returned by the named scope into an  
array. Rather than running an SQL query to determine the size of the  
dataset, after calling .all it just returns the number of items in the  
actual array of data. Since the GROUP is added properly to the actual  
data fetch query, .all returns the proper number because that array  
has the proper data in it.

This seems like a bug to me… Any AR hackers out there know why this  
might be intended behavior, or should I work on a patch?

- Brandon

---

<div class="post-metadata">

**Author:** ![asetzler](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ac91a4/32.png) [@asetzler](https://www.ruby-forum.com/u/asetzler)\
**Post date:** [October 16, 2009, 10:57pm UTC](https://www.ruby-forum.com/t/size-of-array-returned-by-named-scope-with-group-select-distinct/177401/3 "2009-10-16T22:57:01Z")

</div>

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.

On Oct 16, 10:08 am, Brandon D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![asetzler](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/ac91a4/32.png) [@asetzler](https://www.ruby-forum.com/u/asetzler)\
**Post date:** [August 5, 2010, 7:28pm UTC](https://www.ruby-forum.com/t/size-of-array-returned-by-named-scope-with-group-select-distinct/177401/4 "2010-08-05T19:28:43Z")

</div>

I found that calling named\_scope.all.size resolves this issue. It’s not  
perfect, but it does the job.
