ActiveRecord find starts with

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?

Solution 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.

In Rails 3.0+ this becomes:

MyModel.where("field LIKE :prefix", prefix: "#{prefix}%")

Solution 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.

Solution 3:

I don't want to write a scope every time I want to see if a specific column starts_with a prefix.

# initializers/active_record_initializers.rb
class ActiveRecord::Base
  # do not accept a column_name from the outside without sanitizing it
  # as this can be prone to sql injection
  def self.starts_with(column_name, prefix)
    where("lower(#{column_name}) like ?", "#{prefix.downcase}%")
  end
end

Call it like this:

User.starts_with('name', 'ab').limit(1)

Solution 4:

Disclaimer: I am new to Ruby and Rails, and I am still trying to learn the Ruby Way to do things, but I have been coding for more than half of my life, and professionally for a decade. DRY is a concept with which I am very familiar. Here is how I implemented it. It is very similar to narsk's answer, which I think is also good.

# name_searchable.rb
# mix this into your class using
#   extend NameSearchable
module NameSearchable
  def search_by_prefix (prefix)
    self.where("lower(name) LIKE '#{prefix.downcase}%'")
  end
end

And then in your model:

class User < ActiveRecord::Base
  extend NameSearchable
  ...
end

And then when you want to use it:

User.search_by_prefix('John')   #or
User.search_by_prefix("#{name_str}")

One thing to call out:

Traditional relational databases aren't extremely good at satisfying these kinds of queries. If you are looking for a highly responsive implementation that will not kill your databases under load, you should probably use a solution that is tailored to the purpose instead. Popular examples include Solr or Sphinx, and there are many others as well. With this implementation, since you DRY, you could substitute the implementation with a separate indexer in just one place and you would be ready to rock.

Solution 5:

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".