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 ones easy. To demonstrate, Ill 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 e 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 e 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 arrays 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, theres 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.

Youve got two other options. If you e going to be iterating over the entire dataset anyway, and you don need the count until you e 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 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	 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	 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

• The Ruby DBI tutorial describes the MySQL rows trick but says not to depend on it; we figure as long as you know about the alternatives, you e not dependent on the database-specific shortcut (http://www.kitebird.com/articles/ruby-dbi.html)