Getting the total amount of results in a paginated query

Question

In my RoR app, I have a query that could return anywhere 0 to 1000000 results, that I'm limiting to 16 and providing pagination for:

find(:all, :conditions => conditions, :limit => limit, :offset => offset)

I'd like to be able to tell the user how many results they're looking at vs. the total amount of results. Something like "Showing 16 of 500". What is the best way to get the total amount of rows without doing an non-limited query, which would surely be slow in the case of a large result set? I'd also like to remain database agnostic.

Answer 1

The database has no way to know the number of rows match the query unless it actually executes the whole thing. For example, if you are selecting for MYCOLUMN>5 then it needs to read MYCOLUMN in every row in the table to know how many have values greater than 5. (Though you might be able to improve the efficiency with an index.)

Usually one uses COUNT(*) for the purpose that you've described.

The only other way I can think of to get the number of rows that would be returned for a query without doing much work is to put a trigger function on the table that updates the count every time a row is inserted, updated or deleted based on whether it matches the query. However, this is rigid as it must be customized for individual queries, and it certainly isn't a database-agnostic approach.

Answer 2

It would appear I should have utilized the google a bit more thoroughly. Rails has a built in class called ActiveRecord::Calculations (go figure) to do pragmatic database counting. I wound up using code like this:

results = find(:all, :conditions => conditions, :limit => limit, :offset => offset)
total_count = count(:conditions => conditions)
return {:results => results, :total_count => total_count}

Answer 3

Use will_paginate