ActiveRecord Query Interface in Rails 5

We can count the number of products.

Product.count
   (0.3ms)  SELECT COUNT(*) FROM "products"
 => 4

If you provide an id of a product that does not exist in the database, you will get ActiveRecord::RecordNotFound exception.

product = Product.find 100
  Product Load (0.4ms)  SELECT  "products".* FROM "products" WHERE "products"."id" = ? LIMIT ?  [["id", 100], ["LIMIT", 1]]
ActiveRecord::RecordNotFound: Couldn't find Product with 'id'=100

If the product exists, the find method returns that record.

product = Product.find 1
  Product Load (0.1ms)  SELECT  "products".* FROM "products" WHERE "products"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
 => #<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fcc31c8,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">

The take method returns one product from the database. You can see that this generates SQL query where limit is used.

first = Product.take
  Product Load (0.2ms)  SELECT  "products".* FROM "products" LIMIT ?  [["LIMIT", 1]]
 => #<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fcc3888,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">

You can also specify the number of records you want to take.

products = Product.take 2
  Product Load (0.3ms)  SELECT  "products".* FROM "products" LIMIT ?  [["LIMIT", 2]]
 => [#<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fcc380,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">, #<Product id: 2, name: "pillow", price: #<BigDecimal:7fc17b0,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">]

You can retrieve the first product in the products table.

p = Product.first
  Product Load (0.3ms)  SELECT  "products".* FROM "products" ORDER BY "products"."id" ASC LIMIT ?  [["LIMIT", 1]]
 => #<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fcc778,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">

You can also retrieve the first three products in the products table.

products = Product.first 3
  Product Load (0.1ms)  SELECT  "products".* FROM "products" ORDER BY "products"."id" ASC LIMIT ?  [["LIMIT", 3]]
 => [#<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fcc380f0,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">, #<Product id: 2, name: "pillow", price: #<BigDecimal:7fc07b8,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 3, name: "light", price: #<BigDecimal:7fcc88,'0.1095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">]

You can retrieve the last product from the products table.

Product.last
  Product Load (0.2ms)  SELECT  "products".* FROM "products" ORDER BY "products"."id" DESC LIMIT ?  [["LIMIT", 1]]
 => #<Product id: 4, name: "rug", price: #<BigDecimal:7fcc38,'0.1E3',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">

You can also retrieve the last three products in the products table.

Product.last 3
  Product Load (0.1ms)  SELECT  "products".* FROM "products" ORDER BY "products"."id" DESC LIMIT ?  [["LIMIT", 3]]
 => [#<Product id: 2, name: "pillow", price: #<BigDecimal:7fcc380,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 3, name: "light", price: #<BigDecimal:7fbbf0,'0.1095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 4, name: "rug", price: #<BigDecimal:7fc710,'0.1E3',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">]

The find_by method returns one product that matches the column value specified.

Product.find_by(name: 'pillow')
  Product Load (0.2ms)  SELECT  "products".* FROM "products" WHERE "products"."name" = ? LIMIT ?  [["name", "pillow"], ["LIMIT", 1]]
 => #<Product id: 2, name: "pillow", price: #<BigDecimal:7fcc380,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">

The find_by method returns nil where there is no record that satisfies the criteria.

Product.find_by(name: 'spoon')
  Product Load (0.1ms)  SELECT  "products".* FROM "products" WHERE "products"."name" = ? LIMIT ?  [["name", "spoon"], ["LIMIT", 1]]
 => nil

If we use where to search for pillow, we get ActiveRecord::Relation as the result, whereas the find_by returns the product.

Product.where(name: 'pillow')
  Product Load (0.2ms)  SELECT "products".* FROM "products" WHERE "products"."name" = ?  [["name", "pillow"]]
 => #<ActiveRecord::Relation [#<Product id: 2, name: "pillow", price: #<BigDecimal:7fcc568,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">]>

We can check the class.

 p.class
 => Product::ActiveRecord_Relation

When you chain the take method with where, we get a product instance as the result.

Product.where(name: 'pillow').take.class
  Product Load (0.1ms)  SELECT  "products".* FROM "products" WHERE "products"."name" = ? LIMIT ?  [["name", "pillow"], ["LIMIT", 1]]
 => Product(id: integer, name: string, price: decimal, created_at: datetime, updated_at: datetime)

The bang version of find_by method will throw ActiveRecord::RecordNotFound exception when the record is not found.

Product.find_by!(name: 'spoon')
  Product Load (0.1ms)  SELECT  "products".* FROM "products" WHERE "products"."name" = ? LIMIT ?  [["name", "spoon"], ["LIMIT", 1]]
ActiveRecord::RecordNotFound: Couldn't find Product

The take also has a bang version that throws an exception when a record is not found.

Product.where(name: 'spoon').take!
  Product Load (0.1ms)  SELECT  "products".* FROM "products" WHERE "products"."name" = ? LIMIT ?  [["name", "spoon"], ["LIMIT", 1]]
ActiveRecord::RecordNotFound: Couldn't find Product with [WHERE "products"."name" = ?]

We can retrieve all products ordered by created_at field.

Product.order(:created_at)
  Product Load (0.3ms)  SELECT "products".* FROM "products" ORDER BY "products"."created_at" ASC
 => #<ActiveRecord::Relation [#<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fcc3826,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">, #<Product id: 2, name: "pillow", price: #<BigDecimal:7fcc388,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 3, name: "light", price: #<BigDecimal:7fcc3826,'0.1095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 4, name: "rug", price: #<BigDecimal:78980,'0.1E3',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">]>

We can provide a string instead of symbol for the column.

Product.order('created_at')
  Product Load (0.2ms)  SELECT "products".* FROM "products" ORDER BY created_at
 => #<ActiveRecord::Relation [#<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fc10,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">, #<Product id: 2, name: "pillow", price: #<BigDecimal:7fcc382,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 3, name: "light", price: #<BigDecimal:7fcc348,'0.1095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 4, name: "rug", price: #<BigDecimal:7fcc00,'0.1E3',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">]>

The order method can also take a hash that can specify the order. We can list all products by descending created_at column.

Product.order(created_at: :desc)
  Product Load (0.2ms)  SELECT "products".* FROM "products" ORDER BY "products"."created_at" DESC
 => #<ActiveRecord::Relation [#<Product id: 4, name: "rug", price: #<BigDecimal:7fcc348,'0.1E3',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 3, name: "light", price: #<BigDecimal:7fcc38,'0.1095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 2, name: "pillow", price: #<BigDecimal:7f2bb0,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fccb0,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">]>

You can get all the products and retrieve only the specified columns id and name by using the select method.

Product.select('id, name')
  Product Load (0.1ms)  SELECT id, name FROM "products"
 => #<ActiveRecord::Relation [#<Product id: 1, name: "SCISSORS">, #<Product id: 2, name: "pillow">, #<Product id: 3, name: "light">, #<Product id: 4, name: "rug">]>

You can select records that has unique values for a given column by using distinct.

Product.select(:name).distinct
  Product Load (0.3ms)  SELECT DISTINCT "products"."name" FROM "products"
 => #<ActiveRecord::Relation [#<Product id: nil, name: "SCISSORS">, #<Product id: nil, name: "pillow">, #<Product id: nil, name: "light">, #<Product id: nil, name: "rug">]>

We can retrieve two records by using limit. This generates the same SQL as the take we saw earlier.

Product.limit(2)
  Product Load (0.2ms)  SELECT  "products".* FROM "products" LIMIT ?  [["LIMIT", 2]]
 => #<ActiveRecord::Relation [#<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fcc30,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">, #<Product id: 2, name: "pillow", price: #<BigDecimal:7fcc30,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">]>

You can provide an offset to retrieve records by using the offset.

Product.limit(2).offset(1)
  Product Load (0.2ms)  SELECT  "products".* FROM "products" LIMIT ? OFFSET ?  [["LIMIT", 2], ["OFFSET", 1]]
 => #<ActiveRecord::Relation [#<Product id: 2, name: "pillow", price: #<BigDecimal:7fcc32f8,'0.9E2',9(18)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">, #<Product id: 3, name: "light", price: #<BigDecimal:7f5b80,'0.1095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-05 00:57:35">]>

You can get an empty ActiveRecord::Relation by using none.

Product.none
 => #<ActiveRecord::Relation []>

You can retrieve a record for read only purpose by using readonly.

p = Product.readonly.first
  Product Load (0.2ms)  SELECT  "products".* FROM "products" ORDER BY "products"."id" ASC LIMIT ?  [["LIMIT", 1]]
 => #<Product id: 1, name: "SCISSORS", price: #<BigDecimal:7fcc370,'0.2095E2',18(27)>, created_at: "2016-04-05 00:57:35", updated_at: "2016-04-12 23:20:46">

This product's name is SCISSORS.

p.name
 => "SCISSORS"

If we change the value of the name and save it, we get ActiveRecord::ReadOnlyRecord exception.

p.name = 'scissors'
 => "scissors"
p.save
   (0.1ms)  begin transaction
   (0.1ms)  rollback transaction
ActiveRecord::ReadOnlyRecord: Product is marked as readonly

Summary

In this article, you learned some of the ActiveRecord query interface in Rails 5.


Related Articles


Create your own user feedback survey