ActiveRecord find starts with

Question

Really simple question - how do I do a search to find all records where the name starts with a certain string in ActiveRecord. I've seen all sorts of bits all over the internet where verbatim LIKE SQL clauses are used - but from what I've heard that isn't the 'correct' way of doing it.

Is there a 'proper' Rails way?

Answer 1

If you're looking to do the search in the database then you'll need to use SQL.

And, of course, you'll need to do the search in the database otherwise you need to load all the objects into Ruby (which isn't a good thing).

So, you will need something like

MyModel.find(:all, :conditions => ["field LIKE ?", "#{prefix}%"])

where prefix is a variable with the string you're looking for.

Answer 2

I would highly recommend the Searchlogic plugin.

Then it's as easy as:

@search = Model.new_search(params[:search])
@search.condition.field_starts_with = "prefix"
@models = @search.all

Searchlogic is smart enough, like ActiveRecord, to pick up on the field name in the starts_with condition. It will also handle all pagination.

This method will help prevent SQL injection and also will be database agnostic. Searchlogic ends up handling the search differently depending on the database adapter you're using. You also don't have to write any SQL!

Searchlogic has great documentation and is easy to use (I'm new to Ruby and Rails myself). When I got stuck, the author of the plugin even answered a direct email within a few hours helping me fix my problem. I can't recommend Searchlogic enough...as you can tell.

Answer 3

Very much like the above answer, but if you're using ActiveRecord...2.1 or greater, you could use a named scope which would allow you to use this "finder" on records retrieved thru associations:

class User
  named_scope :with_name_like, lambda {|str|
    :conditions => ['lower(name) like ?', %(%#{str.downcase}%)]
  }
end

Used like:

User.with_name_like("Samson")

(returns all users in your database with a name like "Samson".

Or:

some_association.users.with_name_like("Samson")

Users off that association only with a name like "Samson".