Recipe 13.8. Finding the Number of Rows Returned by a Query
Problem
Writing a DBI program, you want an efficient way to see how many rows were returned by a query.
Solution
A do command returns the number of rows affected by the command, so that one's easy. To demonstrate, I'll create a database table that keeps track of my prized collection of lowercase letters:
require 'cookbook_dbconnect'
with_db do |c|
c.do %{drop table if exists letters}
c.do %{create table letters(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
letter CHAR(1) NOT NULL)}
letter_sql = ('a'..'z').collect.join('"),("')
c.do %{insert into letters(letter) values ("#{letter_sql}")}
end
# => 26
When you execute a query, you get back a StatementHandle object representing the request. If you're using a MySQL database, you can call rows on this object to get the number of rows in the result set:
vowel_query = %{select id from letters where letter in ("a","e","i","o","u")}
with_db do |c|
h = c.execute vowel_query
"My collection contains #{h.rows} vowels."
end
# => "My collection contains 5 vowels."
If you're not using MySQL, things are a bit trickier. The simplest thing to do is simply retrieve all the rows as an array, then use the array's size as the number of rows:
with_db do |c|
vowels = c.select_all(vowel_query)
"My collection still contains #{vowels.size} vowels."
end
# => "My collection still contains 5 vowels."
But this can be disastrously inefficient; see below for details.
Discussion
When you select some items out of a Ruby array, say with
Array#grep, Ruby gives you the results in a brand new array. Once the array has been created, there's no cost to checking its size by calling Array#size.
A database query acts differently. Your query might have matched millions
of rows, and each result might contain kilobytes of data. This is why normally you iterate over a result set instead of using select_all to get it as an array. Getting the whole result set at once might use a huge amount of memory, which is why using select_all can be disastrous.
You've got two other options. If you're going to be iterating over the entire dataset anyway, and you don't need the count until you're all done, you can count the rows as you go. This will save memory over the fetch_all approach:
with_db do |c|
rows = 0
c.execute(vowel_query).each do |row|
rows += 1
# Process the row…
end
"Yup, all #{rows} vowels are still there."
end
# => "Yup, all 5 vowels are still there."
Otherwise, your only choice is to run two queries: the actual query, and a slightly modified version of the query that uses SELECT COUNT instead of SELECT. A method like this will work for simple cases (cases that don't contain GROUP BY statements). It uses a regular expression to turn a SELECT query into a SELECT COUNT query, runs both queries, and returns both the count and the query handle.
module DBI
class DatabaseHandle
def execute_with_count(query, *args)
re = /^\s*select .* from/i
count_query = query.sub(re, 'select count(*) from')
count = select_one(count_query)
[count, execute(query)]
end
end
end
with_db do |c|
count, handle = c.execute_with_count(vowel_query)
puts "I can't believe none of the #{count} vowels " +
"have been stolen from my collection!"
puts 'Here they are in the database:'
handle.each do |r|
puts "Row #{r['id']}"
end
end
# I can't believe none
of the 5 vowels have been stolen from my collection!
# Here they are in the database:
# Row 1
# Row 5
# Row 9
# Row 15
# Row 21
See Also
 | 
