Sorting Find Results by Accuracy

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.

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

http://www.postgresql.org/docs/8.3/static/textsearch-controls.html#TEXTSEARCH-RANKING

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