Struggling With Ruby: The Model Part 5 - Find and other ActiveRecord methods

The find method
ActiveRecord provides a lot of functionality through the find method and its derivatives. Here are some examples:

Post.find 45
# Retrieves the post with id=45
Post.find 2, 4, 5
# Gets the posts with id equal to 2, 4 and 5 (as an array)
Post.find :first
# Retrieves the first post in the database
Post.find :last
# Retrieves the last post in the database
Post.find :all
# Retrieves all the posts (as an array)
Post.find_by_author 'F2Andy'
# Retrieves the first post in the database authored by F2Andy
Post.find_all_by_author 'F2Andy'
# Retrieves all the posts in the database authored by F2Andy
Post.find_all_by_author_and_title 'F2Andy', 'ActiveRecord find'
Post.find :all, conditions => { :title => 'ActiveRecord find',
                             :author => 'F2Andy'}
# Both these retrieves all the posts in the database with
# the given author and title

There are various options you can include in your search, as illustrated here, which gets the 21st through to 30th posts, ordered by the title:

Post.find_all_by_author 'F2Andy', :order => 'title',
                    :offset => 20, :limit => 10

You can determine whether the ordering goes up or down using ASC and DESC (defaults to ASC). The offset is ignored if limit is not present.

When you know what you are searching for, ActiveRecord has it covered. However, often we do not. Do a search of Google, and you are looking for one or more words within the text. To do that for ActiveRecord you need to delve into SQL a little.

Dipping a Toe into SQL
Rails helps you to some degree, and to see how, let us perform that last search again.

Post.find :all, :conditions => ['author = ? AND title = ?',
                     'F2Andy', 'ActiveRecord find']

Notice the :conditions key. This maps to an array. The first element is the base string, some SQL code including question marks. All the other elements are substituted for the question marks in order. The SQL command that is used will be something like:

SELECT * FROM Post WHERE author = "F2Andy" AND
                       title = "ActiveRecord find"

You can use symbols rather than question marks. It is a little more long-winded, but makes it clear what goes where, and you can go in any order, so would be preferable in all but the simplest of cases.

Post.find :all,
:conditions => ['author = :author AND title = :title?',
:author => 'F2Andy', :title => 'ActiveRecord find'}]

ActiveRecord does some kind of sanitising to protect against SQL injection attacks. While the following will work, it is a bad idea, as it will bypass that process:

Post.find :all,
 :conditions => 'author = "F2Andy" AND title = "ActiveRecord find"'
Post.find_by_sql 'SELECT * FROM Post WHERE author = "F2Andy" AND title = "ActiveRecord find"'

However, for complex searches, this might be your only option (an interesting page on that can be seen here).

Using Wildcards
So now we are ready to search for a fragment within a field. In SQL, you use the LIKE keyword, rather than the equals sign, and use % as a wildcard. Let us suppose I have a database of literature references, and I am searching for one author, Smith.

refs = Ref.find :all,
 :conditions => [ "authors LIKE ?", "%Smith%" ]

This will return any record where "Smith" appears in the authors field.

Case Insensitive
There is no standard for doing case sensitive/insensitive seaches in SQL (and no help from Rails either), nor any standard about which should be the default (indeed, that depends on how the database is set up).

On PostgreSQL, you can use ILIKE to do case insensitive searches:

refs = Ref.find :all,
 :conditions => [ "authors ILIKE ?", "%smith%" ]

On MySQL, you can change the collaton method (this seems to be the usual SQL strategy, however as each database system has its own collations, it still varies between databases; SQL Server uses SQL_Latin1_General_CP1_CI_AS I think).

refs = Ref.find :all,
 :conditions => [ "authors LIKE ? COLLATE utf8_general_ci",
                   "%smith%" ]

What this all means is that the only safe way is to convert to all lower (or upper) case. The only way I could get this to work was to change the search term before hand (possibly due to how Rails sanitises the SQL).

term = "Smith".downcase
refs = Ref.find :all,
 :conditions => [ "LOWER(authors) LIKE ?", "%#{term}%" ]

Complicated searches
So what if we want to search for muliple words in multiple fields? What you need to do is build a search string that each term with be put into, and an array of terms. Here is an addition to ActiveRecord::Base that will do just that.

class ActiveRecord::Base
# Retrieves all ActiveRecords that contain the
# user supplied keywords.
# The hash parameter should contain column name
# mappings to strings of keywords. For example:
#  refs = Ref.search :authors => 'smith jones',
#                   :body => 'ruby search'
# This will retrieve any record containing both
# "smith" and "jones" in the authors field, and
# "ruby" and "search" in the body field.
# The search is case insensitive.
# Note that simply sending the params hash from
# the controller will not work, as this includes
# values for :action and :controller.
def self.search params
 # This will becomes the base for the SQL string
 sql_fragments = []
 # This is the array of search terms. The first
 #entry is just a place holder; this will be
 # replaced by the SQL string at the end.
 search_terms = ['']
 params.each_pair do |k, v|
   v.split.each do |e|
     sql_fragments << "LOWER(#{k.to_s}) LIKE ?"
     search_terms << "%#{e.downcase}%"
   end
 end
 # Now assemble the SQL string and put it at
 # the start of the array of terms.
 search_terms[0] = sql_fragments.join(' AND ')
 find :all, :conditions => search_terms
end
end

Other methods

The delete and destroy methods
These take an id or an array of ids to delete a set of records, returning the number of records set. The destroy method actually creates a new instance, populates it with data from the table, then calls the destroy method on the object, so will be slower, but will ensure any custom code in the destroy method and any callbacks and filters is invoked.

Post.delete 19
Post.destroy [23, 67, 103]

The delete_all and destroy_all methods
These are similar to find :all, and will accept the same sort of conditions. As with delete and destroy, destroy_all is slower as the objects are instantiated first.

Post.delete_all :conditions => [ "user_name = ? AND category = ?",
    user_name, category ]

The exists? method
This method returns true if one or more records matching the condition exists. Note that it accepts either an id, or the condition itself (rather than :condition mapping to the condition)

Post.exists? 45
Post.exists? :user_name => user_name, :category => category
Post.exists? ["user_name = ? AND category = ?",
                   user_name, category]

Calculations
As well as retrieving records, you can perform calculations, using average, maximum, minimum and sum. Examples from the API:

Person.calculate(:count, :all) # The same as Person.count
Person.average(:age) # SELECT AVG(age) FROM people...
Person.minimum(:age,
 :conditions => ['last_name != ?', 'Drake'])
# Selects the minimum age for everyone with a last name other
# than 'Drake'
Person.minimum(:age, :having => 'min(age) > 17',
 :group => :last_name)
# Selects the minimum age for any family without any minors
Person.sum("2 * age")

You can also use count. Again, examples from the API:

Person.count
# returns the total count of all people
Person.count(:age)
# returns the total count of all people
# whose age is present in database
Person.count(:conditions => "age > 26")
Person.count(:conditions => "age > 26 AND job.salary > 60000",
 :include => :job)
# because of the named association, it finds the DISTINCT
# count using LEFT OUTER JOIN.
Person.count(:conditions => "age > 26 AND job.salary > 60000",
 :joins => "LEFT JOIN jobs on jobs.person_id = person.id")
# finds the number of rows matching the conditions and joins.
Person.count('id', :conditions => "age > 26")
# Performs a COUNT(id)
Person.count(:all, :conditions => "age > 26")
# Performs a COUNT(*) (:all is an alias for '*')

Struggling With Ruby

Monday, 23 March 2009

The Model Part 5 - Find and other ActiveRecord methods

No comments:

Blog Archive