ActiveRecord Query Interface All Versions

Examples

• 4

  .where

  edit move example delete share

  4

  The where method is available on any ActiveRecord model and allows querying the database for a set of records matching the given criteria.

  The where method accepts a hash where the keys correspond to the column names on the table that the model represents.

  As a simple example, we will use the following model:

  class Person < ActiveRecord::Base
    #attribute :first_name, :string
    #attribute :last_name, :string
  end
  

  To find all people with the first name of Sven:

  people = Person.where(first_name: 'Sven')
  people.to_sql # "SELECT * FROM people WHERE first_name='Sven'"
  

  To find all people with the first name of Sven and last name of Schrodinger:

  people = Person.where(first_name: 'Sven', last_name: 'Schrodinger')
  people.to_sql # "SELECT * FROM people WHERE first_name='Sven' AND last_name='Schrodinger'"
  

  In the above example, the sql output shows that records will only be returned if both the first_name and the last_name match.

  query with OR condition

  To find records with first_name == 'Bruce' OR last_name == 'Wayne'

  User.where('first_name = ? or last_name = ?', 'Bruce', 'Wayne')
  # SELECT "users".* FROM "users" WHERE (first_name = 'Bruce' or last_name = 'Wayne')
  

  edited Nov 8 at 10:06

  

  

  

  
• 4

  .where with an array

  edit move example delete share

  4

  The where method on on any ActiveRecord model can be used to generate SQL of the form WHERE column_name IN (a, b, c, ...). This is achieved by passing an array as argument.

  As a simple example, we will use the following model:

  class Person < ActiveRecord::Base
    #attribute :first_name, :string
    #attribute :last_name, :string
  end
  
  people = Person.where(first_name: ['Mark', 'Mary'])
  people.to_sql # "SELECT * FROM people WHERE first_name IN ('Mark', 'Mary')"
  

  edited Jul 22 at 19:27

  

  

  
• 3

  Get first and last record

  edit move example delete share

  3

  Rails have very easy way to get first and last record from database.

  To get the first record from users table we need to type following command:

  User.first
  

  It will generate following sql query:

  SELECT  `users`.* FROM `users`  ORDER BY `users`.`id` ASC LIMIT 1
  

  And will return following record:

  #<User:0x007f8a6db09920 id: 1, first_name: foo, created_at: Thu, 16 Jun 2016 21:43:03 UTC +00:00, updated_at: Thu, 16 Jun 2016 21:43:03 UTC +00:00 >
  

  To get the last record from users table we need to type following command:

  User.last
  

  It will generate following sql query:

  SELECT  `users`.* FROM `users`  ORDER BY `users`.`id` DESC LIMIT 1
  

  And will return following record:

  #<User:0x007f8a6db09920 id: 10, first_name: bar, created_at: Thu, 16 Jun 2016 21:43:03 UTC +00:00, updated_at: Thu, 16 Jun 2016 21:43:03 UTC +00:00 >
  

  Passing an integer to first and last method creates a LIMIT query and returns array of objects.

  User.first(5)
  

  It will generate following sql query.

  SELECT  "users".* FROM "users"  ORDER BY "users"."id" ASC LIMIT 5
  

  And

  User.last(5)
  

  It will generate following sql query.

  SELECT  "users".* FROM "users"  ORDER BY "users"."id" DESC LIMIT 5
  

  edited Oct 10 at 8:35

  

  
• 3

  Scopes

  edit move example delete share

  3

  Scopes act as predefined filters on ActiveRecord models.

  A scope is defined using the scope class method.

  As a simple example, we will use the following model:

  class Person < ActiveRecord::Base
    #attribute :first_name, :string
    #attribute :last_name, :string
    #attribute :age, :integer
  
    # define a scope to get all people under 17
    scope :minors, -> { where(age: 0..17) }
  
    # define a scope to search a person by last name
    scope :with_last_name, ->(name) { where(last_name: name) }
  
  end
  

  Scopes can be called directly off the model class:

  minors = Person.minors
  

  Scopes can be chained:

  peters_children = Person.minors.with_last_name('Peters')
  

  The where method and other query type methods can also be chained:

  mary_smith = Person.with_last_name('Smith').where(first_name: 'Mary')
  

  Behind the scenes, scopes are simply syntactic sugar for a standard class method. For example, these methods are functionally identical:

  scope :with_last_name, ->(name) { where(name: name) }
  
  # This ^ is the same as this:
  
  def self.with_last_name(name)
    where(name: name)
  end
  

  edited Jul 22 at 9:45

  

  

  
• 2

  Includes

  edit move example delete share

  2

  ActiveRecord with includes ensures that all of the specified associations are loaded using the minimum possible number of queries. So when querying a table for data with an associated table, both tables are loaded into memory.

  @authors = Author.includes(:books).where(books: { bestseller: true } )
  
  # this will print  results without additional db hitting
  @authors.each do |author| 
    author.books.each do |book|
      puts book.title
    end
  end
  

  Author.joins(:books).where(books: { bestseller: true } ) will load only authors with conditions into memory without loading books. Use joins when additional information about nested associations isn't required.

  @authors = Author.joins(:books).where(books: { bestseller: true } )
  
  # this will print results without additional queries
  @authors.each { |author| puts author.name }
  
  # this will print results with additional db queries
  @authors.each do |author| 
    author.books.each do |book|
      puts book.title
    end
  end
  

  created Jul 27 at 18:14

  
• 2

  Ordering

  edit move example delete share

  2

  You can order ActiveRecord query results by using .order:

  User.order(:created_at)
  #=> => [#<User id: 2, created_at: "2015-08-12 21:36:23">, #<User id: 11, created_at: "2015-08-15 10:21:48">]
  

  If not specified, ordering will be performed in ascending order. You can specify it by doing:

  User.order(created_at: :asc)
  #=> => [#<User id: 2, created_at: "2015-08-12 21:36:23">, #<User id: 11, created_at: "2015-08-15 10:21:48">]
  
  User.order(created_at: :desc)
  #=> [#<User id: 7585, created_at: "2016-07-13 17:15:27">, #<User id: 7583, created_at: "2016-07-13 16:51:18">]
  

  .order also accepts a string, so you could also do

  User.order("created_at DESC")
  #=> [#<User id: 7585, created_at: "2016-07-13 17:15:27">, #<User id: 7583, created_at: "2016-07-13 16:51:18">]
  

  As the string is raw SQL, you can also specify a table and not only an attribute. Assuming you want to order users according to their role name, you can do this:

  Class User < ActiveRecord::Base
      belongs_to :role
  end
  
  Class Role < ActiveRecord::Base
    has_many :users
  end
  
  User.includes(:role).order("roles.name ASC")
  

  The order scope can also accept an Arel node:

  User.includes(:role).order(User.arel_table[:name].asc)
  

  edited Jul 28 at 15:16

  

  
• 2

  where.not

  edit move example delete share

  2

  where clauses can be negated using the where.not syntax:

  class Person < ApplicationRecord
    #attribute :first_name, :string
  end
  
  people = Person.where.not(first_name: ['Mark', 'Mary'])
  # => SELECT "people".* FROM "people" WHERE "people"."first_name" NOT IN ('Mark', 'Mary')
  

  Supported by ActiveRecord 4.0 and later.

  edited Jul 22 at 9:45

  

  
• 1

  .delete_all

  edit move example delete share

  1

  If you need to delete a lot of records quickly, ActiveRecord gives .delete_all method. to be called directly on a model, to delete all records in that table, or a collection. Beware though, as .delete_all does not instantiate any object hence does not provide any callback (before_* and after_destroy don't get triggered).

  User.delete_all
  #=> 39  <-- .delete_all return the number of rows deleted
  
  User.where(name: "John").delete_all 
  

  created Jul 22 at 21:39

  
• 1

  .distinct (or .uniq)

  edit move example delete share

  1

  If you want to remove duplicates from a result, you can use .distinct():

  Customers.select(:country).distinct
  

  This queries the database as follows:

  SELECT DISTINCT "customers"."country" FROM "customers"
  

  .uniq() has the same effect. With Rails 5.0 it got deprecated and it will be removed from Rails with version 5.1. The reason is, that the word unique doesn't have the same meaning as distinct and it can be misleading. Furthermore distinct is closer to the SQL syntax.

  created Jul 24 at 16:10

  
• 1

  .find_by

  edit move example delete share

  1

  You can find records by any field in your table using find_by.

  So, if you have a User model with a first_name attribute you can do:

  User.find_by(first_name: "John")
  #=> #<User id: 2005, first_name: "John", last_name: "Smith">
  

  Mind that find_by doesn't throw any exception by default. If the result is an empty set, it returns nil instead of find.

  If the exception is needed may use find_by! that raises an ActiveRecord::RecordNotFound error like find.

  edited Aug 22 at 17:15

  

  

  
• 1

  .group and .count

  edit move example delete share

  1

  We have a Product model and we want to group them by their category.

  Product.select(:category).group(:category)
  

  This will query the database as follows:

  SELECT "product"."category" FROM "product" GROUP BY "product"."category"
  

  Make sure that the grouped field is also selected. Grouping is especially useful for counting the occurrence - in this case - of categories.

  Product.select(:category).group(:category).count
  

  As the query shows, it will use the database for counting, which is much more efficient, than retrieving all record first and do the counting in the code:

  SELECT COUNT("products"."category") AS count_categories, "products"."category" AS products_category FROM "products" GROUP BY "products"."category"
  

  created Jul 24 at 16:10

  
• 1

  ActiveRecord case insensitive search

  edit move example delete share

  1

  If you need to search an ActiveRecord model for similar values, you might be tempted to use LIKE or ILIKE but this isn't portable between database engines. Similarly, resorting to always downcasing or upcasing can create performance issues.

  You can use ActiveRecord's underlying Arel matches method to do this in a safe way:

  addresses = Address.arel_table
  Address.where(addresses[:address].matches("%street%"))
  

  Arel will apply the appropriate LIKE or ILIKE construct for the database engine configured.

  created Jul 23 at 1:17

  
• 1

  Joins

  edit move example delete share

  1

  joins() allows you to join tables to your current model. For ex.

  User.joins(:posts)
  

  will produce the following SQL query:

  "SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id""
  

  Having table joined, you will have access to it:

  User.joins(:posts).where(posts: { title: "Hello world" })
  

  Pay attention on plural form. If your relation is :has_many, then the joins() argument should be pluralized. Otherwise, use singular.

  Nested joins:

  User.joins(posts: :images).where(images: { caption: 'First post' })
  

  which will produce:

  "SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" INNER JOIN "images" ON "images"."post_id" = "images"."id""
  

  edited Nov 10 at 12:08

  

  
• 0

  Limit and Offset

  edit move example delete share

  0

  You can use limit to tell the number of records to be fetched, and use offset to tell the number of records to skip before starting to return the records.

  For Example

  User.limit(3) #returns first three records
  

  It will generate following sql query.

  "SELECT  `users`.* FROM `users` LIMIT 3"
  

  As offset is not mentioned in above query so it will return first three records.

  User.limit(5).offset(30) #returns 5 records starting from 31th i.e from 31 to 35
  

  It will generate following sql query.

  "SELECT  `users`.* FROM `users` LIMIT 5 OFFSET 30"
  

  edited Nov 10 at 13:59

  

  
• -1

  ActiveRecord Bang (!) methods

  edit move example delete share

  -1

  If you need an ActiveRecord method to raise an exception instead of a false value in case of failure, you can add ! to them. This is very important. As some exceptions/failures are hard to catch if you don't use ! on them. I recommended doing this in your development cycle to write all your ActiveRecord code this way to save you time and trouble.

  Class User < ActiveRecord::Base
    validates :last_name, presence: true
  end
  
  User.create!(first_name: "John")
  #=> ActiveRecord::RecordInvalid: Validation failed: Last name can't be blank
  

  The ActiveRecord methods which accept a bang (!) are:

  • .create!
  • .take!
  • .first!
  • .last!
  • .find_by!
  • .find_or_create_by!
  • #save!
  • #update!
  • all AR dynamic finders

  edited Jul 23 at 1:19