Searching a Database in Rails

For searching your database, ActiveRecord (AR) provides many methods – some generated automatically based on your tables. The most basic way to find an object is with calling the class method ‘find’ on your model. find, by default, takes the id(s) of the record you’re looking for as an argument.

User.find(1)
User.find(1, 7, 14)

You can find the first, last, and all records by passing a symbol as the first argument. There are also shortcut methods for accessing these records.

User.find(:all)         User.all
User.find(:first)       User.first
User.find(:last)        User.last

Rails automatically generates a number of dynamic find methods based on your table’s design. This makes it easy to find records quickly.

User.find_by_email('jared@modsognir.com')

You can find first, last, and all by adding the word after ‘find’ in the chain.

Article.find_all_by_user_id(1)
Article.find_first_by_email('j@modsognir.com')

And it is easy to find based on multiple columns too.

User.find_first_by_user_name_and_password('Jared', password)

This covers simple cases. As you delve further into your project, you’ll likely require more complicated searches. Passing a hash as the last argument allows you to pass more refined information into the query.

The ‘:conditions’ option has two modes. The first, by passing a hash, uses AR to form your database query. This abstracts the SQL to Rails, and ensures database compatability as well as protecting from SQL injection attacks.

User.find(:first, :conditions => { :username => 'Jared', :password => password })

The second is accessed either by passing an Array or a String. You should use this only if you are familiar with SQL syntax and need more specialised cases. . These two queries are identical.

User.find(:first, :conditions => "username LIKE #{user.username}")
User.find(:first, :conditions => ["username LIKE ?", user.username])

The Array form will also protect you from SQL injection attacks (while the string wont). It works by having a string as the first element containing SQL, then any question mark in that string will be replaced one by one by the remaining elements.

User.find(:first, :conditions => ["username LIKE ? AND email LIKE ?", user.username, user.email])

When you need to have the returned records ordered in a particular way, use the ‘:order’ option. This takes a string as basic SQL.

User.find(:all,  rder => "created_at DESC")
User.find(:all, :conditions =>; { :role => "admin" },  rder => "username ASC")