find
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.
Parameters
- :conditions - An SQL fragment like "administrator = 1", [ "user_name = ?", username ], or ["user_name = :user_name", { :user_name => user_name }]. 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.
- :having - Combined with :group this can be used to filter the records that a GROUP BY returns. Uses the HAVING 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), named associations in the same form used for the :include option, which will perform an INNER JOIN on the associated table(s), or an array containing a mixture of both strings and named associations. 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. Takes a string with the SELECT SQL fragment (e.g. "id, name").
- :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, :conditions => [ "user_name = :u", { :u => 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
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.
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.).
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')
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],
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' )
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.
:conditions examples
:conditions => {:login => login, :password => password}
:conditions => [‘subject LIKE :foo OR body LIKE :foo’, {:foo => ‘woah’}]
(from the book “The Rails Way”)
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
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.
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.
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.
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)
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.
"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
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
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.
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
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'")
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
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.