Sorting Find Results by Accuracy

Rails
1

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

2

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.

3

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

4

On Dec 26, 9:27 am, Mozmonkey [email protected] wrote:

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

5

On 12/24/07, Mozmonkey [email protected] wrote:

I want the exact matches (i.e. “bob”) to appear at the top of the
list. How do I do this?

Use a database that supports fulltext searching. Your query results
will return a score or provide a means to rank the results.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

–
Greg D.
http://destiney.com/

6

You could also use soundex to return similar results.

On Dec 27, 2007 9:28 AM, Greg D. [email protected] wrote:

jobob, bobby, bobdobbs, bob
PostgreSQL: Documentation: 8.3: Controlling Text Search

–
Greg D.
http://destiney.com/

–
Ryan B.

Feel free to add me to MSN and/or GTalk as this email.

7

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

8

That’s a great solution. Now how do I ensure the ORDER BY statement
is sanitized from the user input?

9

On Dec 26, 10:07 pm, Mozmonkey [email protected] wrote:

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

10

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.

From my tests the following causes a SQL error:

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!

11

On Dec 27, 7:23 pm, Mozmonkey [email protected] wrote:

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! :slight_smile:

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

12

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