A Rails HOWTO: Query By Example
So I was hanging out in #rubyonrails yesterday, and someone asked a great question:
(10:40:53) jbwiv: guys, does Rails have the equivalent of hibernate's query by example?
(10:41:39) jbwiv: By QBE, I mean I can create an object, set fields on that object, and then query the database by providing that object as an example. Does rails (active record) have an equivalent?
The answer is interesting: no, but adding that’s a snap.
Wanna see how?
Update 2/19/2006: find_by_example now takes arrays as parameters.
Update 2/6/2006: One of the folks in #rubyonrails pointed out a possible security problem with this, if the attributes list is taken directly from the user. Sanitization has been added.
First, we know we’re going to be extending one of the classes in ActiveRecord, so here’s the game plan:
- Write our extension of ActiveRecord.
- Put it in a file,
ar_query_by_example.rb, in the/libdirectory of our Rails app. requirethat file fromconfig/environment.rbso that the extensions are loaded by the time ActiveRecord does anything interesting.
Query By Example
Let’s add #find_all_by_example as a class method of ActiveRecord::Base. It’ll take a single record and a list of symbols which reference the attributes you want to use as part of the example. #find_all_by_example will take that record, create a query based on the provided attributes, and return all records which match that query.
Behold:
class ActiveRecord::Base
def self.find_all_by_example(record, *attributes)
conditions = Array.new
query = Array.new
for attribute in attributes.flatten.uniq
query << "#{self.connection.quote_column_name(attribute.to_s)} = ?"
conditions << record[attribute]
end
self.find(:all, :conditions => [query.join(' AND ')] + conditions)
end
end
A weird example
So you know Bob’s a cat freak, and you know what about him (the number of cats he owns, the fact that he calls them “his babies,” and the fact that he has allergies) makes him a cat freak. You want to find all the cat freaks, but you’re feeling lazy.
Pop quiz, hot shot. What do you do? What do you do?
If you answered “query by example,” then you’re 10% smarter than the person next to you.
Witness the fitness:
@bob = Person.find_by_name('Bob Person')
@cat_freaks = Person.find_all_by_example(@bob, :cats_owned, :calls_cats_babies, :has_allergies)
# @cat_freaks = [Bob, Marcy, Hank, T'challa]
Obviously this is a weird example, but you there are better applications. For example, you could create a dummy Search model (i.e., not store in the database) and then use Rails’ easy model helpers to make a scaffold for searches. Or not. And that’s part of the beauty of it all.
Have fun!
February 15th, 2006 at 12:28pm
That is sweet, and totally useful. You should submit that as a RoR patch.
February 19th, 2006 at 3:36pm
I’m totally new to ruby. I have this working, but have a question. Can I dynamically add the parameters I want to query on? Say I have a search form, with 5 text fields each bound to a different member variable of the same 1 class. I want to search on each field only if some text is entered into that field…How would I do it?
February 19th, 2006 at 3:52pm
Ben–It’s Ruby! You can do everything dynamically! ;-)
If what you’re talking about is, say, five fields (first name, last name, birthday, address, blood type) and you want your users to be able search for people based on those parameters, find_by_example will work.
Just name your text fields following the Rails conventions (e.g., model[field]), and then inside the controller, try something like this:
This won’t save the example to the database, it’ll just stick around in memory as a data object.
Also, be sure to update your find_by_example source. I just changed it to accept arrays as a parameter.
Have fun!
February 20th, 2006 at 2:45am
Coda- I have written a plugin that simplifies database queries like this and other where clause building situation. Just though you might like to check it out: http://brainspl.at/articles/2006/01/30/i-have-been-busy
July 4th, 2006 at 9:24am
Coda - I’ve played around with your examples, and can’t seem to get them to work. By that, I mean that the pages display wonderfully, but nothing gets returned from the DB.
in person_controller.rb:
[code]
def search
person = Person.new
end # renders a stripped-down version of the new.rhtml form,
# with the most popular searchable fields
def searchresult
example = Person.new(params[:person])
@results = Person.find_all_by_example(example, params[:person].keys.uniq)
end # renders a page similar to list.rhtml
[/code]
The AR mod file is copied verbatim. Everything renders fine, but the search is bringing back nothing.
Any suggestions on what I’ve done wrong? Thanks.
July 4th, 2006 at 12:13pm
Hmm, you may need to change the
conditions << record[attribute]line in the QBE code to this:I’m not sure offhand if AR stores unsaved attributes in the attribute hash, or just in the accessor methods.
August 11th, 2006 at 10:42am
Great stuff. Just what I was loooking for: simple and easy way to add QBE from a user-entered form.
How about this change to allow for substring matches:
replaces
This will allow the user to enter any part of the saved value in a particular field.