How can I get FOUND_ROW()s from an active record object in rails?

Question

When querying the database with:

@robots = Robot.all(:condition => [:a => 'b'], :limit => 50, :offset => 0)

What is the best way to get the total number of rows without the :limit?

In raw MySQL you could do something like this:

SELECT SQL_CALC_FOUND_ROWS * FROM robots WHERE a=b LIMIT 0, 50
SELECT FOUND_ROWS();

Is there an active record way of doing this?

Answer 1

Try find_by_sql may that help.

Answer 2

Is @robots.size what you're looking for? Or Robot.count?

Otherwise, please clarify.

Answer 3

This works for me:

ps = Post.all(:limit => 10, :select => "SQL_CALC_FOUND_ROWS *")
Post.connection.execute("select found_rows()").fetch_hash
=> {"found_rows()"=>"2447"}

This will probably not work for joins or anything complex, but it works for the simple case.

Answer 4

I think hakunin is right.

You can get no of row return by query by just chekcing the size of resulting array of query.

@robots = Robot.find_by_sql("Your sql")
or 
@robots = Robot.find(:all , :conditions=>["your condiitons"] )

@robots.size or @robots.count

Answer 5

search = Robot.all(:condition => ["a=b"], :offset => 0)
@robots = search[0..49]
@count = search.count

That should get what you want, gets all the Robots for counting and then sets @robots to the first 50. Might be a bit expensive on the resource front if the Robots table is huge.

You can of course do:

@count=Robot.all(:condition => ["a=b"], :offset => 0).count
@robots=Robot.all(:condition => ["a=b"], :limit => 50, :offset => 0)

but that will hit the database twice on each request (although rails does have query caching).

Both solutions only use active record so are database independent.

What do you need the total returned by the query for? if its pagination look into Will_paginate (Railscast) which can be extended with AJAX etc...

Answer 6

Robot.count actually is the solution you want.

Reading one of the comments above, it looks like you may have a misunderstanding of how .count works. It returns a count of all the rows in the table only if there's no parameters.

but if you pass in the same conditions that you pass to all/find eg:

Robot.count(:conditions => {:a => 'b'})

.count() will return the number of rows that match the given conditions. Just to be obvious - you can even save the condition-hash as a variable to pass into both - to reduce duplication, so:

conds = {:a => 'b'}
@robots = Robot.all(:conditions => conds, :limit => 50)
@num_robots = Robot.count(:conditions => conds)

That being said - you can't do an after-the-fact count on the result-set (like in your example). ie you can't just run your query then ask it how many rows would have been found. You do actually have to call .count on purpose.