My site lets people search for other users by part of their username. The SQL snippet looks like this:
WHERE username LIKE "%bob%"
This will return the users:
jobob, bobby, bobdobbs, bob
I want the exact matches (i.e. "bob") to appear at the top of the list. How do I do this?
Thanks, Jeremy
While playing around with SQL queries I came up with what seems to me to be a hacky way to solve this:
SELECT u1.name, u2.name FROM users AS u1 LEFT OUTER JOIN users AS u2 ON u1.id = u2.id AND u2.name LIKE 'bob' WHERE u1.name LIKE '%bob%' ORDER BY u2.name DESC;
I'm hoping there's a cleaner way, because this is ugly.
jobob, bobby, bobdobbs, bob
I want the exact matches (i.e. "bob") to appear at the top of the list. How do I do this?
select name from users where name like '%bob%' order by name = 'bob' desc
///ark
That's a great solution. Now how do I ensure the ORDER BY statement is sanitized from the user input?
Thanks! I think you would use :order => ['name = ? desc', user_input] in your query. You'd do the same thing with :condition => ['name like ?', '%' + user_input + '%'] .
///ark
select name from users where name like '%bob%' order by name = 'bob' desc
You might even want to try
order by name like 'bob%' desc
which adds a little order to the matches following the exact match.
///ark
Use a database that supports fulltext searching. Your query results will return a score or provide a means to rank the results.
Use a database that supports fulltext searching. Your query results will return a score or provide a means to rank the results.
I would, but I don't believe Rails natively supports fulltext searching, and it seems like more than I need for this situation (correct me if I'm wrong -- I've never used fulltext search before and might not properly understand it). My solution was to use the sanitize_sql function, which is part of the ActiveRecord::Base object, so I had to put the find code within the Model. Here's the snippet:
def search(params)
if params.has_key?("username") && !params[:username].nil? conditions = "name LIKE :name" values[:name] = "%#{params[:username]}%" options[:order] = sanitize_sql ["name LIKE ? DESC", params[:username]] end
# ...Other stuff... #
options[:conditions] = [conditions, values] find(:all, options) end
I would, but I don't believe Rails natively supports fulltext searching, and it seems like more than I need for this situation (correct me if I'm wrong -- I've never used fulltext search before and might not properly understand it).
Just be aware that any time you use a condition like LIKE '%foo%' the entire table (or index) will be scanned. If the table is large (thousands of names), it might make better sense to use fulltext searching. A hybrid way that I've used is to generate my own indexes.
My solution was to use the sanitize_sql function, which is part of the ActiveRecord::Base object,
sanitize_sql is not necessary when you use parameter substitution, as you're doing.
so I had to put the find code within the Model.
The code definitely belongs in the model, so that's OK.
def search(params)
if params.has_key?("username") && !params[:username].nil? conditions = "name LIKE :name"
Does that work? I would've thought you needed %'s in there. And I don't understand :name.
values\[:name\] = "%\#\{params\[:username\]\}%" options\[:order\] = sanitize\_sql \["name LIKE ? DESC",params[:username]]
No, the :order part has to be an exact or prefix match to put the exact match at the top. Plus I think there's some %'s needed there, too.
You might want to have another look at the code I posted, and let me/ us know if you have any questions about it.
///ark
Just be aware that any time you use a condition like LIKE '%foo%' the entire table (or index) will be scanned. If the table is large (thousands of names), it might make better sense to use fulltext searching. A hybrid way that I've used is to generate my own indexes.
Great point. I'll consider converting it over. Does Rails have support for fulltext queries?
sanitize_sql is not necessary when you use parameter substitution, as you're doing.
options[:order] = ["name = ? DESC", params[:username]]
Because the resulting SQL is:
ORDER BY name = ? DESCbob
And entering the value into the :order string doesn't sanitize it:
options[:order] = "name = '#{params[:username]}' DESC"
So the only solution I could see is using the sanitize_sql function. If there's a simpler way, I'd love to know.
> if params.has_key?("username") && !params[:username].nil? > conditions = "name LIKE :name"
Does that work? I would've thought you needed %'s in there. And I don't understand :name.
It seems to add the '%' characters for me. The resulting SQL is: name LIKE '%bob%'
The :name is for sanitized replacement, similar to the '?'. Here's how it goes together in a find statement:
User.find(:all, :conditions => ["name LIKE :name AND foo = :bar", {:name => "bob", :foo => "baz"}]
No, the :order part has to be an exact or prefix match to put the exact match at the top. Plus I think there's some %'s needed there, too.
Good catch, thanks!
Thanks for all the help!
Does Rails have support for fulltext queries?
I think the acts_as_ferret plugin supports fulltext queries.
From my tests the following causes a SQL error:
options[:order] = ["name = ? DESC", params[:username]]
Because the resulting SQL is:
ORDER BY name = ? DESCbob
Ah, interesting. I looked carefully at the documention before posting, and it did seem to me that :order allowed substitution. I didn't actually try it, however. That's annoying. But yes, then, you're right - sanitize_sql is the ticket.
> > if params.has_key?("username") && !params[:username].nil? > > conditions = "name LIKE :name"
> Does that work? I would've thought you needed %'s in there. And I > don't understand :name.
It seems to add the '%' characters for me. The resulting SQL is: name LIKE '%bob%'
Sounds good to me, then! 
The :name is for sanitized replacement, similar to the '?'. Here's how it goes together in a find statement:
User.find(:all, :conditions => ["name LIKE :name AND foo = :bar", {:name => "bob", :foo => "baz"}]
Cool - thanks for the info.
///ark