method

find

Importance_5
v2.1.0 - Show latest stable - 21 notes - Class: ActiveRecord::Base
find(*args) public

Find operates with four different retrieval approaches:

  • Find by id - This can either be a specific id (1), a list of ids (1, 5, 6), or an array of ids ([5, 6, 10]). If no record can be found for all of the listed ids, then RecordNotFound will be raised.
  • Find first - This will return the first record matched by the options used. These options can either be specific conditions or merely an order. If no record can be matched, nil is returned. Use Model.find(:first, *args) or its shortcut Model.first(*args).
  • Find last - This will return the last record matched by the options used. These options can either be specific conditions or merely an order. If no record can be matched, nil is returned. Use Model.find(:last, *args) or its shortcut Model.last(*args).
  • Find all - This will return all the records matched by the options used. If no records are found, an empty array is returned. Use Model.find(:all, *args) or its shortcut Model.all(*args).

All approaches accept an options hash as their last parameter.

Attributes

  • :conditions - An SQL fragment like "administrator = 1" or [ "user_name = ?", username ]. See conditions in the intro.
  • :order - An SQL fragment like "created_at DESC, name".
  • :group - An attribute name by which the result should be grouped. Uses the GROUP BY SQL-clause.
  • :limit - An integer determining the limit on the number of rows that should be returned.
  • :offset - An integer determining the offset from where the rows should be fetched. So at 5, it would skip rows 0 through 4.
  • :joins - Either an SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id" (rarely needed) or named associations in the same form used for the :include option, which will perform an INNER JOIN on the associated table(s). If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table’s columns. Pass :readonly => false to override.
  • :include - Names associations that should be loaded alongside. The symbols named refer to already defined associations. See eager loading under Associations.
  • :select - By default, this is "*" as in "SELECT * FROM", but can be changed if you, for example, want to do a join but not include the joined columns.
  • :from - By default, this is the table name of the class, but can be changed to an alternate table name (or even the name of a database view).
  • :readonly - Mark the returned records read-only so they cannot be saved or updated.
  • :lock - An SQL fragment like "FOR UPDATE" or "LOCK IN SHARE MODE". :lock => true gives connection’s default exclusive lock, usually "FOR UPDATE".

Examples

  # find by id
  Person.find(1)       # returns the object for ID = 1
  Person.find(1, 2, 6) # returns an array for objects with IDs in (1, 2, 6)
  Person.find([7, 17]) # returns an array for objects with IDs in (7, 17)
  Person.find([1])     # returns an array for the object with ID = 1
  Person.find(1, :conditions => "administrator = 1", :order => "created_on DESC")

Note that returned records may not be in the same order as the ids you provide since database rows are unordered. Give an explicit :order to ensure the results are sorted.

Examples

  # find first
  Person.find(:first) # returns the first object fetched by SELECT * FROM people
  Person.find(:first, :conditions => [ "user_name = ?", user_name])
  Person.find(:first, :order => "created_on DESC", :offset => 5)

  # find last
  Person.find(:last) # returns the last object fetched by SELECT * FROM people
  Person.find(:last, :conditions => [ "user_name = ?", user_name])
  Person.find(:last, :order => "created_on DESC", :offset => 5)

  # find all
  Person.find(:all) # returns an array of objects for all the rows fetched by SELECT * FROM people
  Person.find(:all, :conditions => [ "category IN (?)", categories], :limit => 50)
  Person.find(:all, :conditions => { :friends => ["Bob", "Steve", "Fred"] }
  Person.find(:all, :offset => 10, :limit => 10)
  Person.find(:all, :include => [ :account, :friends ])
  Person.find(:all, :group => "category")

Example for find with a lock: Imagine two concurrent transactions: each will read person.visits == 2, add 1 to it, and save, resulting in two saves of person.visits = 3. By locking the row, the second transaction has to wait until the first is finished; we get the expected person.visits == 4. 

  Person.transaction do
    person = Person.find(1, :lock => true)
    person.visits += 1
    person.save!
  end
Show source
Register or log in to add new notes.
August 22, 2008 - (>= v2.1.0)
13 thanks

Specifying :include no longer necessarily joins the association

Before Rails 2.1, adding an :include=>[:association] in your find method caused ActiveRecord to generate SQL using a join. Since 2.1, it MAY NOT execute as a join.

The join executes a large query and returned potentially duplicate records for a one-to-many association. After 2.1, the query is broken down and eager-loaded using an additional query per association, passing the set of id’s to load, and avoiding the duplicate rows.

The new method eliminates duplicates, but can incur more database overhead. If you are loading a very large set of records (more than a “page”), you may need to “force” the join or use find_by_sql instead.

When you specify a “table.column” syntax within a

:conditions=>["child.name=?", name]

or

:order=>'child.name'

then ActiveRecord will build the older, full query with the join because you are referencing columns from another table to build. This will cause the duplicate rows to reappear.

Whenever you reference a column from another table in a condition or order clause, ALWAYS use the table name to prefix the column, even if it not ambiguous among the tables involved. Otherwise the query will not be executed as a join and you will receive an SQL error referencing the “missing” column.

You can “force” a join by adding a reference to the other tables in your :conditions or :options parameters, even if the test or sort is irrelevant.

February 24, 2009
9 thanks

Specialized versions of find with method_missing

Check ActiveRecord::Base.method_missing for documentation on the family of “magic” find methods (find_by_x, find_all_by_x, find_or_create_by_x, etc.).

August 8, 2008
7 thanks

Avoiding duplicate results when you do a join

When you use the :joins option you can get multiple instances of the same item. For example, say you want every User who owns one or more Lists. With the code below, if a user owns 5 lists, the User will show up five times in the results:

users = User.find(:all, 
  :conditions => ['users.id = lists.user_id'], 
  :joins => [:lists], 
  :order => 'users.username')

You can cause each found user to appear only once by using the :select option with “DISTINCT”:

users = User.find(:all, 
  :conditions => ['users.id = lists.user_id'], 
  :joins => [:lists], 
  :select => 'DISTINCT users.*'
  :order => 'users.username')
August 7, 2009
7 thanks

Join multiple tables

It’s easy to join multiple tables too. In this case we have:

class Article
  belongs_to :feed
end

class Feed
  has_many :articles
  belongs_to :source
end

class Source
  has_many :feeds
  # t.bool :visible
end

You can search articles and specify a condition on the sources table.

Article.find(:all, 
  :conditions => { :feeds => { :sources => { :visible => true }}}, 
  :joins => [:feed => :source],
August 4, 2008
7 thanks

Example: find by associated table

Say you have tables “authors” and “books” and they have a one-to-many association.

You want authors who have written books with “cooking” in the title…

cookbook_authors = Author.find(:all, 
  :conditions => ['books.title LIKE ?', '%cooking%'], 
  :joins => [:books], 
  :order => 'authors.last_name' )

For many-to-many associations, it’s a similar pattern. Say you have tables “people” and “organizations” with a many-to-many association through the join table “organization_memberships”.

Ski Club members whose first name starts with “a”…

ski_club_members = Person.find(:all, 
  :conditions => ['first_name LIKE ? AND organizations.name = ?', 
    'a%', 'Ski Club'], 
  :joins => [:organizations], 
  :order => 'people.last_name' )
December 11, 2008
6 thanks

Accessing aggregate methods with :group

You can access aggregate methods (such as SUM, COUNT, etc.) when using a JOIN and GROUP BY query by simply naming the aggregate columns and calling them as methods on the returned objects:

hits_by_page = WebpageHit.all({
  :select => "webpages.*, COUNT(webpage_hit.id) AS view_count",
  :joins => :webpage,
  :group => "webpages.id"
})
homepage_hits = hits_by_page[homepage.id].view_count

The view_count method is added to the Webpage model by this call. Note, however, that this method returns a string, and is not typecasted by Rails.

July 22, 2008
4 thanks

:conditions examples

:conditions => {:login => login, :password => password}

:conditions => [‘subject LIKE :foo OR body LIKE :foo’, {:foo => ‘woah’}]

(from the book “The Rails Way”)

August 23, 2008
3 thanks

Include two level has many model example

class Issue < ActiveRecord::Base

  has_many :journals
end

class Journal < ActiveRecord::Base
  belongs_to :issue
  has_many :details, :class_name => "JournalDetail", :dependent => :delete_all
end

class JournalDetail < ActiveRecord::Base
  belongs_to :journal
end

<hr/>

issue = Issue.find(:first, :include => {:journals => :details}

log record follow:

SELECT * FROM `issues` LIMIT 1
SELECT `journals`.* FROM `journals` WHERE (`journals`.`journalized_id` IN (1) and `journals`.`journalized_type` = 'Issue' AND (dustbin <> 1))
SELECT `journal_details`.* FROM `journal_details` WHERE (`journal_details`.journal_id IN (1,2,876177898,935815637))

when execute follow code, then not build sql sentent:

issue.journals
issue.journals[0].details
August 22, 2008
2 thanks

Response to created_at/created_on and find(:first).map

A couple of comments on the comments:

The created_at/created_on thing clearly relates to the columns that have been defined in your model – it’s got nothing to do with Rails 2.1 (although the Rails 2 “timestamp” method adds the created_at column).

And find(:first), find(:last) return model objects, rather than arrays/result sets, which is why you can’t do a map on them – you can’t do anything that you would do on an Enumerable, unless the model object itself is Enumerable.

May 2, 2009
2 thanks

Re: Find random record

Ordering by RAND() is not a wise idea when you have a large table with lots of rows. Your database will have to calculate a different random value for every row in your database – O(N) – then sort the entire table by those values – O(N log N).

There are a number of better ways to get a random record from your table. Some examples:

  • If your table is not sparse, choose a random ID and get that row (or the nearest row):

rand_id = rand(Model.count)
rand_record = Model.first(:conditions => [ "id >= ?", rand_id]) # don't use OFFSET on MySQL; it's very slow

  • If your table is sparse, or does not have a primary key, consider adding an indexed column of random numbers between 0 and N. You can then order by this column quickly and choose a value using a method similar to the above example.

February 5, 2010
2 thanks

the :order parameter is not sanitized

The :order parameter is not sanitized, so doing something like Person.find( :first , :order => params[:order] ) could get you in trouble.

November 16, 2008
1 thank

:select multiple fields

it might be obvious or not:

Task.find :all, :select => "name, members"
July 23, 2008
1 thank

Using .map(&:item)

You can only use .map(&:item) with find(:all, not find(:first. For example; the first works, but the second does not.

@person = Person.find(:all, :conditions => {
  :id => @person.id}, :select => "name").map(&:name)

@person = Person.find(:first, :conditions => {
  :id => @person.id}, :select => "name").map(&:name)
April 30, 2009
1 thank

Find random record

It’s as simple as:

Things.first(:order => 'RAND()')

Of course depending on your database it could be ‘RANDOM()’ or something similar.

August 4, 2008 - (>= v2.1.0)
1 thank

"created_at" instead "created_on"

In examples( at least for version 2.1) should be Person.find(:last, :order => “created_at DESC”, :offset => 5)

instead of: Person.find(:last, :order => “created_on DESC”, :offset => 5)

the same with 2nd auto-generated table: in my rails 2.1 it is updated_at not _on

October 18, 2011
0 thanks

Upgrading to 3.x

http://railscasts.com/episodes/202-active-record-queries-in-rails-3

Since this is deprecated, one can watch the Railcast for upgrading to 3.x

The equivalent is the ActiveRecord finder methods. http://apidock.com/rails/ActiveRecord/Fixture/find

July 29, 2009
0 thanks

Paginating grouped records

If you are grouping similar records and paginating you might need to use :group You’ll want to :select only the field you’re collapsing on probably.

Model.count(:select => :attribute, :group => :attribute)

This will return an OrderedHash of your attributes with a count for each.

{"Column Content" => 6, "Another Column's Content" => 8}

You’ll need a second query to pull all of your records out.

May 2, 2009
0 thanks

Re: Find random record

How about if you wanted to find a random set of records instead of a singular record, what would be the best way?

Thank you

April 1, 2010
0 thanks

Fetching records when column is set to nil or false

If you want to fetch all records when one column (boolean) is set to nil or false, try this:

Project.all(:conditions => "archived IS NULL OR archived = 'f'")
March 22, 2011
0 thanks

Find a random *set* of records (without killing the db)

If you want to find any number of records without sorting your entire table randomly every time, try the solution I posted here:

http://rubyglasses.blogspot.com/2010/05/activerecord-find-in-random-order.html

November 4, 2008
0 thanks

re: Specifying :include no longer necessarily joins the association

I have seen how :include does not nessisarily perform a join during that SQL query, if you need the join to occur then, rather then tricking AR (“forcing”), use :joins instead of :include to ensure the joins occur.