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)


Strings

Numbers

Date and Time

Arrays

Hashes

Files and Directories

Code Blocks and Iteration

Objects and Classes8

Modules and Namespaces

Reflection and Metaprogramming

XML and HTML

Graphics and Other File Formats

Databases and Persistence

Internet Services

Web Development Ruby on Rails

Web Services and Distributed Programming

Testing, Debugging, Optimizing, and Documenting

Packaging and Distributing Software

Automating Tasks with Rake

Multitasking and Multithreading

User Interface

Extending Ruby with Other Languages

System Administration



Ruby Cookbook
Ruby Cookbook (Cookbooks (OReilly))
ISBN: 0596523696
EAN: 2147483647
Year: N/A
Pages: 399

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net