Using named_scope to get row count

Question

Rails gurus: I've just discovered named_scope thanks to another SO user. :)

I'd like to get the count of a set of rows - i.e. a SELECT COUNT(*). Additionally, I want to still be able to chain named scopes in the call.

Is this a legitimate (albeit weird) usage of named scope?

named_scope :count, :select => "COUNT(*) as count_all"

So then I can do (for example):

@foobar = Foobar.count.scope.scope.scope

The count is accessed via @foobar.first.count_all.

(Edited to address Allan's comments)

You could do:

@foobar = Foobar.scope.scope.scope.size

But this would cause a result query and not the faster SELECT COUNT(*) query. I have a large amount of rows in the database I am querying.

Is there a better way to do this?

Answer 1

I don't think this is correct at all. Scopes are used to refine find statements and the count query won't play well with these.

Answer 2

There is a better way to do this, rails already gives you the means.

Using your example, you could just do this:

@foobar_size = Foobar.all.size #returns integer equal to total rows of Foobar

and even scope it like so:

@banned_foobars = Foobar.scope_to_find_banned.size #returns integer equal to total rows for "scope_to_find_banned"

Answer 3

The functionality you're looking for is built in.

Foobar.count # SELECT count(*) AS count_all FROM "foobars"
Foobar.named_scope.count # SELECT count(*) AS count_all FROM "foobars" WHERE ....

If you run script/server in dev mode, you'll see the queries as they get executed.