ActiveRecord: Top 10 Ways To Query Single Record

Explaining 10 must known query methods to fetch single records

Feb 06, 2024

Introduction

In this article, we are covering the top 10 query methods to fetch a single record from the database table. This article includes fetching single records with additional ordering and filter criteria along with examples.

Model

For example purpose we will take the Product as our sample model:

irb(main):018:0> Product
=> Product(id: integer, name: string, description: text, created_at: datetime, updated_at: datetime)


irb(main):032:0> Product.all.to_a
  Product Load (0.8ms)  SELECT "products".* FROM "products"
=>                                       
[#<Product:0x00000001142926c8 id: 1, name: "iphone1", description: "desc", created_at: Sat, 20 May 2023 00:49:44.987968000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:20:47.842524000 UTC +00:00>,
 #<Product:0x0000000114292588 id: 2, name: "iphone2", description: "desc", created_at: Sat, 20 May 2023 00:49:51.637848000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:04.847528000 UTC +00:00>,
 #<Product:0x0000000114292448 id: 3, name: "iphone3", description: "desc", created_at: Sat, 20 May 2023 00:49:53.202945000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:12.453226000 UTC +00:00>,
 #<Product:0x0000000114292308 id: 4, name: "iphone4", description: "desc", created_at: Sat, 20 May 2023 00:49:54.235186000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:42.264448000 UTC +00:00>,
 #<Product:0x00000001142921c8 id: 5, name: "iphone5", description: "desc", created_at: Sun, 21 May 2023 01:45:09.418790000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:49.172319000 UTC +00:00>,
 #<Product:0x0000000114292088 id: 6, name: "iphone6", description: "desc", created_at: Sun, 21 May 2023 01:45:15.100317000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:57.794298000 UTC +00:00>,
 #<Product:0x0000000114291f48 id: 7, name: "iphone7", description: "desc", created_at: Sun, 21 May 2023 01:45:19.568327000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:22:31.555078000 UTC +00:00>]

**1. find(*args)**

If you know the primary key of the record, then we can use find(*args) method to retrieve that specific record.

irb(main):041:0> Product.find(1)

Product Load (0.4ms)  SELECT "products".* FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]

=> #<Product:0x00000001146dd840 id: 1, name: "iphone1", description: "desc", created_at: Sat, 20 May 2023 00:49:44.987968000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:20:47.842524000 UTC +00:00>

we can also use it for querying multiple records by passing an array of primary keys.

irb(main):042:0> Product.find([1,2,3])
  
Product Load (3.4ms)  SELECT "products".* FROM "products" WHERE "products"."id" IN ($1, $2, $3)  [["id", 1], ["id", 2], ["id", 3]]

=>                                                                 
[#<Product:0x00000001146bfa20 id: 1, name: "iphone1", description: "desc", created_at: Sat, 20 May 2023 00:49:44.987968000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:20:47.842524000 UTC +00:00>,
 #<Product:0x00000001146bf8e0 id: 2, name: "iphone2", description: "desc", created_at: Sat, 20 May 2023 00:49:51.637848000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:04.847528000 UTC +00:00>,
 #<Product:0x00000001146bf7a0 id: 3, name: "iphone3", description: "desc", created_at: Sat, 20 May 2023 00:49:53.202945000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:12.453226000 UTC +00:00>]

if the record doesn’t exist in the table, then it raises (ActiveRecord::RecordNotFound)

**2. find_by(arg, args*)**

We can use find_by() to find the first matching record with the specified condition.
For example, we can query products by id and name property.

irb(main):048:0> Product.find_by(id: 1, name: "iphone1")

Product Load (5.4ms)  SELECT "products".* FROM "products" WHERE "products"."id" = $1 AND "products"."name" = $2 LIMIT $3  [["id", 1], ["name", "iphone1"], ["LIMIT", 1]]

=> #<Product:0x000000011429a788 id: 1, name: "iphone1", description: "desc", created_at: Sat, 20 May 2023 00:49:44.987968000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:20:47.842524000 UTC +00:00>

If the record is not found then it will return nil. If we want to raise the error then we can use find_by!().

irb(main):063:0> Product.find_by!(name: "iphone99")

Product Load (1.1ms)  SELECT "products".* FROM "products" WHERE "products"."name" = $1 LIMIT $2  [["name", "iphone99"], ["LIMIT", 1]]
`raise_record_not_found_exception!': 

Couldn't find Product with [WHERE "products"."name" = $1] 
(ActiveRecord::RecordNotFound)

3. find_sole_by(arg, *args)

If we need to ensure that there is only one record should be returned and no duplicates exist, then we can use find_sole_by which will return only one record.

irb(main):076:0> Product.find_sole_by(name: "iphone2")

Product Load (18.6ms)  SELECT "products".* FROM "products" WHERE "products"."name" = $1 ORDER BY "products"."id" ASC LIMIT $2  [["name", "iphone2"], ["LIMIT", 2]]

=> #<Product:0x0000000114e99250 id: 2, name: "iphone2", description: "desc", created_at: Sat, 20 May 2023 00:49:51.637848000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:04.847528000 UTC +00:00>
irb(main):077:0>

If there is more than one record then it will raise (ActiveRecord::SoleRecordExceeded). For example, we have two products with the name “iphone1”, so when we query for it we receive the error.

irb(main):077:0> Product.where(name: "iphone1")

Product Load (0.3ms)  SELECT "products".* FROM "products" WHERE "products"."name" = $1  [["name", "iphone1"]]
=>                                                                 
[#<Product:0x0000000114e91690 id: 1, name: "iphone1", description: "desc", created_at: Sat, 20 May 2023 00:49:44.987968000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:20:47.842524000 UTC +00:00>,
 #<Product:0x0000000114e91550 id: 8, name: "iphone1", description: nil, created_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00>]


irb(main):075:0> Product.find_sole_by(name: "iphone1")
  Product Load (0.6ms)  SELECT "products".* FROM "products" WHERE "products"."name" = $1 ORDER BY "products"."id" ASC LIMIT $2  [["name", "iphone1"], ["LIMIT", 2]]

/usr/local/lib/ruby/gems/3.2.0/gems/activerecord-7.0.4.2/lib/active_record/relation/finder_methods.rb:118:in 
`sole': Wanted only one Product (ActiveRecord::SoleRecordExceeded)

4. first(limit = nil)

the first method returns the first n records from the table. if we don’t specify the number then it returns the first 1 record.
It uses natural ordering by primary key when limiting the record.

irb(main):078:0> Product.first
Product Load (0.8ms)  SELECT "products".* FROM "products" ORDER BY "products"."id" ASC LIMIT $1  [["LIMIT", 1]]

=> #<Product:0x000000011497d848 id: 1, name: "iphone1", description: "desc", created_at: Sat, 20 May 2023 00:49:44.987968000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:20:47.842524000 UTC +00:00>

If we need a custom order, then we need to do so before applying the first() method.
As we can see below, when we add an order by created_at desc, we get the last record “iphone8”.

irb(main):086:0> Product.order(created_at: :desc).first
Product Load (0.3ms)  SELECT "products".* FROM "products" ORDER BY "products"."created_at" DESC LIMIT $1  [["LIMIT", 1]]

=> #<Product:0x0000000114e9a010 id: 8, name: "iphone1", description: nil, created_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00>

5. where.first

If we have some condition before selecting our record from the table we can filter those records with where clause.
For example, if we need all the products with IDs greater than 1 and then select the first record from it we can add first() to the filter records.

irb(main):080:0> Product.where("id > ?", 1).first
Product Load (1.6ms)  SELECT "products".* FROM "products" WHERE (id > 1) ORDER BY "products"."id" ASC LIMIT $1  [["LIMIT", 1]]

=> #<Product:0x0000000114e1e4d8 id: 2, name: "iphone2", description: "desc", created_at: Sat, 20 May 2023 00:49:51.637848000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:04.847528000 UTC +00:00>

6. take(limit=nil)

We can also get a single record using take() but there is no implied ordering of the record. This makes it different from than first() method that has default ordering by primary key.
If we don’t define a limit then the default is a single record.

irb(main):081:0> Product.where("id > ?", 1).take
Product Load (0.5ms)  SELECT "products".* FROM "products" WHERE (id > 1) LIMIT $1  [["LIMIT", 1]]

=> #<Product:0x000000011497fc88 id: 2, name: "iphone2", description: "desc", created_at: Sat, 20 May 2023 00:49:51.637848000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:21:04.847528000 UTC +00:00>\

If we need ordering we can then user order() method and then apply take to fetch a single record.

irb(main):106:0> Product.order(created_at: :desc).take
Product Load (0.6ms)  SELECT "products".* FROM "products" ORDER BY "products"."created_at" DESC LIMIT $1  [["LIMIT", 1]]

=> #<Product:0x0000000114978c08 id: 8, name: "iphone1", description: nil, created_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00>
irb(main):107:0>

7. where.take

If we need to add filtering based on certain conditions then we can add a where clause and take the single record out of it.

irb(main):108:0> Product.where("created_at > ?", Time.now-1.day)
                        .order(created_at: :desc)
                        .take
  Product Load (0.5ms)  SELECT "products".* FROM "products" WHERE (created_at > '2024-02-04 00:52:08.238179') ORDER BY "products"."created_at" DESC LIMIT $1  [["LIMIT", 1]]

=> #<Product:0x0000000114d91920 id: 8, name: "iphone1", description: nil, created_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00>
irb(main):109:0>

8. where.sole

If we need to guarantee that there are only single records and throw errors otherwise, the sole is the best method we can use with the where clause condition.

irb(main):083:0> Product.where("id = ?", 1).sole
Product Load (12.0ms)  SELECT "products".* FROM "products" WHERE (id = 1) ORDER BY "products"."id" ASC LIMIT $1  [["LIMIT", 2]]

=> #<Product:0x0000000114e5eb00 id: 1, name: "iphone1", description: "desc", created_at: Sat, 20 May 2023 00:49:44.987968000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:20:47.842524000 UTC +00:00>

Now if our result query returns more than 1 record then the sole will throw an error, this may be a good way to capture if there are duplicates in your table where you don’t expect them to be.

irb(main):084:0> Product.where("name= ?", "iphone1").sole
Product Load (0.9ms)  SELECT "products".* FROM "products" WHERE (name= 'iphone1') ORDER BY "products"."id" ASC LIMIT $1  [["LIMIT", 2]]

`sole': Wanted only one Product (ActiveRecord::SoleRecordExceeded)

9. include?(record)

If we don’t need a record but rather want to check if the table contains a record and take a decision based on that, then we can use the include method to return the existence of it in the table.
For example, below we are checking if the last_product is part of the product table’s records. It returns true.

irb(main):121:0> last_product = Product.where("created_at > ?", Time.now-1.day).order(created_at: :desc).take
  Product Load (0.6ms)  SELECT "products".* FROM "products" WHERE (created_at > '2024-02-04 01:05:34.473705') ORDER BY "products"."created_at" DESC LIMIT $1  [["LIMIT", 1]]
=> #<Product:0x0000000114d7ba58 id: 8, name: "iphone1", description: nil, created_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00, updated_at: Sun, 04 Feb 2024 15:42:05.172176000 UTC +00:00>

irb(main):122:0> Product.all.include?(last_product)
  Product Exists? (0.5ms)  SELECT 1 AS one FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 8], ["LIMIT", 1]]
=> true

If we check for products that have not yet made it to the table, then include? will return false.

irb(main):123:0> new_product = Product.new
=> #<Product:0x0000000114e7e248 id: nil, name: nil, description: nil, created_at: nil, updated_at: nil>
irb(main):124:0> Product.all.include?(new_product)
=> false

10. exists?(condition=:none)

If we want to check if the particular record exists but by a certain column and not with the complete record, then we can do so using exists? method.
As we can see below we are finding by name of the product, and passing condition of id which returns true or false based on existence.

irb(main):125:0> Product.exists?(name: "iphone")
  Product Exists? (5.9ms)  SELECT 1 AS one FROM "products" WHERE "products"."name" = $1 LIMIT $2  [["name", "iphone"], ["LIMIT", 1]]
=> false                                                                                    

irb(main):126:0> Product.exists?(name: "iphone1")
  Product Exists? (0.4ms)  SELECT 1 AS one FROM "products" WHERE "products"."name" = $1 LIMIT $2  [["name", "iphone1"], ["LIMIT", 1]]
=> true   


irb(main):130:0> Product.exists?(["id > ?", 4])
  Product Exists? (0.4ms)  SELECT 1 AS one FROM "products" WHERE (id > 4) LIMIT $1  [["LIMIT", 1]]
=> true

Conclusion

In this article, we explored 10 ways to query a single record from the table. We also take into account things like filter conditions and ordering of the records before fetching them. I have almost used or seen all of the above methods getting used in production depending on the need for API.

Before You Leave

Checkout below resources for becoming a better dev:

Ruby on Rails: Learn 25+ gems and build a Startup MVP
Upgrade SQL skills by practicing it.(Use discount code: ABNEW20OFF for 20% off )

monoRails - a Ruby on Rails newsletter

Discussion about this post