Examples to learn the difference between preload, includes or eager_load

February 4, 2021

Do you know “in theory” the difference between “includes”, “preload”, “eager_load” and “joins”, but you still think that you need more examples to really understand how to use them?

Here you will find a set of examples to help you understand how you can use this 4 methods.

For each example you will find:

The description of a task
The query written with ActiveRecord
The produced SQL query
The result with the fetched records

For each example/excercise try read the description the task and try to think a little before reading the answers.

The model of the example

All the examples are based on the next model…

ActiveRecord::Schema.define(version: 1) do
  create_table :accomodations, if_not_exists: true do |t|
    t.column :name, :string
    t.column :bathrooms_count, :decimal
    t.column :guests_count, :integer
  end

  create_table :rooms, if_not_exists: true do |t|
    t.column :accomodation_id, :integer
    t.column :name, :string
    t.column :beds_count, :integer
  end

  create_table :reviews, if_not_exists: true do |t|
    t.column :accomodation_id, :integer
    t.column :body, :text
    t.column :stars, :integer
  end
end

class Accomodation < ActiveRecord::Base
  has_many :rooms
  has_many :reviews
end

class Room < ActiveRecord::Base
end

class Review < ActiveRecord::Base
end

Examples

1. Fetch all acomodations preloading rooms (with includes)

ActiveRecord query

Accomodation.includes(:rooms)

SQL query

SELECT "accomodations".* FROM "accomodations"
SELECT "rooms".* FROM "rooms" WHERE "rooms"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4]]

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Rooms
#<Room:0x00007f7fae488a20 id: 1, accomodation_id: 1, name: "R1", beds_count: 2>
#<Room:0x00007f7fae398408 id: 2, accomodation_id: 1, name: "R2", beds_count: 3>

#<Accomodation id: 2, name: "Home2", bathroms_count: nil, guests_count: 4>
Rooms
#<Room:0x00007f7fae392fd0 id: 3, accomodation_id: 2, name: "R1", beds_count: 4>
#<Room:0x00007f7fae392968 id: 4, accomodation_id: 2, name: "R2", beds_count: 3>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Rooms
#<Room:0x00007f7fae3926c0 id: 5, accomodation_id: 3, name: "R1", beds_count: 6>
#<Room:0x00007f7fae392238 id: 6, accomodation_id: 3, name: "R2", beds_count: 3>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Rooms
#<Room:0x00007f7fae3920f8 id: 7, accomodation_id: 4, name: "R1", beds_count: 6>
#<Room:0x00007f7fae391b08 id: 8, accomodation_id: 4, name: "R2", beds_count: 3>

2. Fetch all acomodations preloading rooms (with preload)

ActiveRecord query

Accomodation.preload(:rooms)

SQL query

SELECT "accomodations".* FROM "accomodations"
SELECT "rooms".* FROM "rooms" WHERE "rooms"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4]]

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Rooms
#<Room:0x00007f7fb28541a0 id: 1, accomodation_id: 1, name: "R1", beds_count: 2>
#<Room:0x00007f7fb284fec0 id: 2, accomodation_id: 1, name: "R2", beds_count: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Rooms
#<Room:0x00007f7fb284fda8 id: 3, accomodation_id: 2, name: "R1", beds_count: 4>
#<Room:0x00007f7fb284fbc8 id: 4, accomodation_id: 2, name: "R2", beds_count: 3>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Rooms
#<Room:0x00007f7fb284fab0 id: 5, accomodation_id: 3, name: "R1", beds_count: 6>
#<Room:0x00007f7fb284f9c0 id: 6, accomodation_id: 3, name: "R2", beds_count: 3>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Rooms
#<Room:0x00007f7fb284f790 id: 7, accomodation_id: 4, name: "R1", beds_count: 6>
#<Room:0x00007f7fb284f538 id: 8, accomodation_id: 4, name: "R2", beds_count: 3>

3. Fetch all acomodations preloading rooms (with eager load)

ActiveRecord query

Accomodation.eager_load(:rooms)

SQL query

SELECT "accomodations"."id" AS t0_r0, "accomodations"."name" AS t0_r1, "accomodations"."bathrooms_count" AS t0_r2, "accomodations"."guests_count" AS t0_r3, "rooms"."id" AS t1_r0, "rooms"."accomodation_id" AS t1_r1, "rooms"."name" AS t1_r2, "rooms"."beds_count" AS t1_r3 FROM "accomodations" LEFT OUTER JOIN "rooms" ON "rooms"."accomodation_id" = "accomodations"."id"

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Rooms
#<Room:0x00007f7fae7b0e00 id: 1, accomodation_id: 1, name: "R1", beds_count: 2>
#<Room:0x00007f7fae7b0b80 id: 2, accomodation_id: 1, name: "R2", beds_count: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Rooms
#<Room:0x00007f7fae7b0108 id: 3, accomodation_id: 2, name: "R1", beds_count: 4>
#<Room:0x00007f7fae7abd88 id: 4, accomodation_id: 2, name: "R2", beds_count: 3>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Rooms
#<Room:0x00007f7fae7ab5e0 id: 5, accomodation_id: 3, name: "R1", beds_count: 6>
#<Room:0x00007f7fae7ab270 id: 6, accomodation_id: 3, name: "R2", beds_count: 3>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Rooms
#<Room:0x00007f7fae7aaaf0 id: 7, accomodation_id: 4, name: "R1", beds_count: 6>
#<Room:0x00007f7fae7aa7f8 id: 8, accomodation_id: 4, name: "R2", beds_count: 3>

4. Fetch all acomodations preloading reviews (with includes)

ActiveRecord query

Accomodation.includes(:reviews)

SQL query

SELECT "accomodations".* FROM "accomodations"
SELECT "reviews".* FROM "reviews" WHERE "reviews"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4]]

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Reviews
#<Review:0x00007f7fae75b018 id: 1, accomodation_id: 1, body: "B1", stars: 3>
#<Review:0x00007f7fae721160 id: 2, accomodation_id: 1, body: "B2", stars: 3>
#<Review:0x00007f7fae721070 id: 3, accomodation_id: 1, body: "B3", stars: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae720cd8 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae720be8 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae720af8 id: 6, accomodation_id: 2, body: "B3", stars: 4>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Reviews
#<Review:0x00007f7fae720a08 id: 7, accomodation_id: 3, body: "B1", stars: 5>
#<Review:0x00007f7fae720918 id: 8, accomodation_id: 3, body: "B2", stars: 5>
#<Review:0x00007f7fae7207d8 id: 9, accomodation_id: 3, body: "B3", stars: 5>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fae7206e8 id: 10, accomodation_id: 4, body: "B1", stars: 5>
#<Review:0x00007f7fae7205f8 id: 11, accomodation_id: 4, body: "B2", stars: 4>
#<Review:0x00007f7fae720508 id: 12, accomodation_id: 4, body: "B3", stars: 4>

5. Fetch all acomodations preloading reviews (with preload)

ActiveRecord query

Accomodation.preload(:reviews)

SQL query

SELECT "accomodations".* FROM "accomodations"
SELECT "reviews".* FROM "reviews" WHERE "reviews"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4]]

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Reviews
#<Review:0x00007f7fae6e8e00 id: 1, accomodation_id: 1, body: "B1", stars: 3>
#<Review:0x00007f7fae6e8d10 id: 2, accomodation_id: 1, body: "B2", stars: 3>
#<Review:0x00007f7fae6e8ba8 id: 3, accomodation_id: 1, body: "B3", stars: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae6e8a18 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae6e88b0 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae6e86a8 id: 6, accomodation_id: 2, body: "B3", stars: 4>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Reviews
#<Review:0x00007f7fae6e84c8 id: 7, accomodation_id: 3, body: "B1", stars: 5>
#<Review:0x00007f7fae6e82e8 id: 8, accomodation_id: 3, body: "B2", stars: 5>
#<Review:0x00007f7fae6e80b8 id: 9, accomodation_id: 3, body: "B3", stars: 5>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fae6e3f90 id: 10, accomodation_id: 4, body: "B1", stars: 5>
#<Review:0x00007f7fae6e3ea0 id: 11, accomodation_id: 4, body: "B2", stars: 4>
#<Review:0x00007f7fae6e3d88 id: 12, accomodation_id: 4, body: "B3", stars: 4>

6. Fetch all acomodations preloading reviews (with eager_load)

ActiveRecord query

Accomodation.eager_load(:reviews)

SQL query

SELECT "accomodations"."id" AS t0_r0, "accomodations"."name" AS t0_r1, "accomodations"."bathrooms_count" AS t0_r2, "accomodations"."guests_count" AS t0_r3, "reviews"."id" AS t1_r0, "reviews"."accomodation_id" AS t1_r1, "reviews"."body" AS t1_r2, "reviews"."stars" AS t1_r3 FROM "accomodations" LEFT OUTER JOIN "reviews" ON "reviews"."accomodation_id" = "accomodations"."id"

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Reviews
#<Review:0x00007f7fae6a9e80 id: 1, accomodation_id: 1, body: "B1", stars: 3>
#<Review:0x00007f7fae6a9cf0 id: 2, accomodation_id: 1, body: "B2", stars: 3>
#<Review:0x00007f7fae6a9a98 id: 3, accomodation_id: 1, body: "B3", stars: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae6a9638 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae6a9408 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae6a9250 id: 6, accomodation_id: 2, body: "B3", stars: 4>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Reviews
#<Review:0x00007f7fae6a8d00 id: 7, accomodation_id: 3, body: "B1", stars: 5>
#<Review:0x00007f7fae6a8b98 id: 8, accomodation_id: 3, body: "B2", stars: 5>
#<Review:0x00007f7fae6a8940 id: 9, accomodation_id: 3, body: "B3", stars: 5>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fae6a8580 id: 10, accomodation_id: 4, body: "B1", stars: 5>
#<Review:0x00007f7fae6a8328 id: 11, accomodation_id: 4, body: "B2", stars: 4>
#<Review:0x00007f7fae6a8170 id: 12, accomodation_id: 4, body: "B3", stars: 4>

7. Fetch all acomodations preloading reviews and rooms (with includes)

ActiveRecord query

Accomodation.includes(:reviews, :rooms)

SQL query

SELECT "accomodations".* FROM "accomodations"
SELECT "reviews".* FROM "reviews" WHERE "reviews"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4]]
SELECT "rooms".* FROM "rooms" WHERE "rooms"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4]]

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Reviews
#<Review:0x00007f7fae66acd0 id: 1, accomodation_id: 1, body: "B1", stars: 3>
#<Review:0x00007f7fae66ab40 id: 2, accomodation_id: 1, body: "B2", stars: 3>
#<Review:0x00007f7fae66aa50 id: 3, accomodation_id: 1, body: "B3", stars: 3>
Rooms
#<Room:0x00007f7fae662d78 id: 1, accomodation_id: 1, name: "R1", beds_count: 2>
#<Room:0x00007f7fae662c88 id: 2, accomodation_id: 1, name: "R2", beds_count: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae66a938 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae66a848 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae66a6b8 id: 6, accomodation_id: 2, body: "B3", stars: 4>
Rooms
#<Room:0x00007f7fae662b70 id: 3, accomodation_id: 2, name: "R1", beds_count: 4>
#<Room:0x00007f7fae662a80 id: 4, accomodation_id: 2, name: "R2", beds_count: 3>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Reviews
#<Review:0x00007f7fae66a438 id: 7, accomodation_id: 3, body: "B1", stars: 5>
#<Review:0x00007f7fae66a348 id: 8, accomodation_id: 3, body: "B2", stars: 5>
#<Review:0x00007f7fae66a258 id: 9, accomodation_id: 3, body: "B3", stars: 5>
Rooms
#<Room:0x00007f7fae662990 id: 5, accomodation_id: 3, name: "R1", beds_count: 6>
#<Room:0x00007f7fae662788 id: 6, accomodation_id: 3, name: "R2", beds_count: 3>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fae66a168 id: 10, accomodation_id: 4, body: "B1", stars: 5>
#<Review:0x00007f7fae66a078 id: 11, accomodation_id: 4, body: "B2", stars: 4>
#<Review:0x00007f7fae669f88 id: 12, accomodation_id: 4, body: "B3", stars: 4>
Rooms
#<Room:0x00007f7fae662648 id: 7, accomodation_id: 4, name: "R1", beds_count: 6>
#<Room:0x00007f7fae662440 id: 8, accomodation_id: 4, name: "R2", beds_count: 3>

8. Fetch all acomodations preloading reviews and rooms (with preload)

ActiveRecord query

Accomodation.preload(:reviews, :rooms)

SQL query

SELECT "accomodations".* FROM "accomodations"
SELECT "reviews".* FROM "reviews" WHERE "reviews"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4]]
SELECT "rooms".* FROM "rooms" WHERE "rooms"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 1], [nil, 2], [nil, 3], [nil, 4]]

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Reviews
#<Review:0x00007f7fae6035f8 id: 1, accomodation_id: 1, body: "B1", stars: 3>
#<Review:0x00007f7fae603508 id: 2, accomodation_id: 1, body: "B2", stars: 3>
#<Review:0x00007f7fae603418 id: 3, accomodation_id: 1, body: "B3", stars: 3>
Rooms
#<Room:0x00007f7fae5f9c60 id: 1, accomodation_id: 1, name: "R1", beds_count: 2>
#<Room:0x00007f7fae5f9af8 id: 2, accomodation_id: 1, name: "R2", beds_count: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae603328 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae6031c0 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae6030d0 id: 6, accomodation_id: 2, body: "B3", stars: 4>
Rooms
#<Room:0x00007f7fae5f99b8 id: 3, accomodation_id: 2, name: "R1", beds_count: 4>
#<Room:0x00007f7fae5f98a0 id: 4, accomodation_id: 2, name: "R2", beds_count: 3>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Reviews
#<Review:0x00007f7fae602fe0 id: 7, accomodation_id: 3, body: "B1", stars: 5>
#<Review:0x00007f7fae602ef0 id: 8, accomodation_id: 3, body: "B2", stars: 5>
#<Review:0x00007f7fae602e00 id: 9, accomodation_id: 3, body: "B3", stars: 5>
Rooms
#<Room:0x00007f7fae5f97b0 id: 5, accomodation_id: 3, name: "R1", beds_count: 6>
#<Room:0x00007f7fae5f9698 id: 6, accomodation_id: 3, name: "R2", beds_count: 3>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fae602d10 id: 10, accomodation_id: 4, body: "B1", stars: 5>
#<Review:0x00007f7fae602b58 id: 11, accomodation_id: 4, body: "B2", stars: 4>
#<Review:0x00007f7fae602950 id: 12, accomodation_id: 4, body: "B3", stars: 4>
Rooms
#<Room:0x00007f7fae5f9580 id: 7, accomodation_id: 4, name: "R1", beds_count: 6>
#<Room:0x00007f7fae5f9468 id: 8, accomodation_id: 4, name: "R2", beds_count: 3>

9. Fetch all acomodations preloading reviews and rooms (with eager_load)

ActiveRecord query

Accomodation.eager_load(:reviews, :rooms)

SQL query

SELECT "accomodations"."id" AS t0_r0, "accomodations"."name" AS t0_r1, "accomodations"."bathrooms_count" AS t0_r2, "accomodations"."guests_count" AS t0_r3, "reviews"."id" AS t1_r0, "reviews"."accomodation_id" AS t1_r1, "reviews"."body" AS t1_r2, "reviews"."stars" AS t1_r3, "rooms"."id" AS t2_r0, "rooms"."accomodation_id" AS t2_r1, "rooms"."name" AS t2_r2, "rooms"."beds_count" AS t2_r3 FROM "accomodations" LEFT OUTER JOIN "reviews" ON "reviews"."accomodation_id" = "accomodations"."id" LEFT OUTER JOIN "rooms" ON "rooms"."accomodation_id" = "accomodations"."id"

Results

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Reviews
#<Review:0x00007f7fae52b5e0 id: 3, accomodation_id: 1, body: "B3", stars: 3>
#<Review:0x00007f7fae52b108 id: 2, accomodation_id: 1, body: "B2", stars: 3>
#<Review:0x00007f7fae52ae60 id: 1, accomodation_id: 1, body: "B1", stars: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae52a488 id: 6, accomodation_id: 2, body: "B3", stars: 4>
#<Review:0x00007f7fae529d58 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae529ad8 id: 4, accomodation_id: 2, body: "B1", stars: 4>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Reviews
#<Review:0x00007f7fae529218 id: 9, accomodation_id: 3, body: "B3", stars: 5>
#<Review:0x00007f7fae528b38 id: 8, accomodation_id: 3, body: "B2", stars: 5>
#<Review:0x00007f7fae5289a8 id: 7, accomodation_id: 3, body: "B1", stars: 5>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fae5280e8 id: 12, accomodation_id: 4, body: "B3", stars: 4>
#<Review:0x00007f7fae523a20 id: 11, accomodation_id: 4, body: "B2", stars: 4>
#<Review:0x00007f7fae5235e8 id: 10, accomodation_id: 4, body: "B1", stars: 5>

10. Fetch all acomodations for exactly 4 guests, preloading reviews (with includes)

ActiveRecord query

Accomodation.where(guests_count: 4).includes(:reviews)

SQL query

SELECT "accomodations".* FROM "accomodations" WHERE "accomodations"."guests_count" = $1  [["guests_count", 4]]
SELECT "reviews".* FROM "reviews" WHERE "reviews"."accomodation_id" = $1  [["accomodation_id", 2]]

Results

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae4a3640 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae4a2f38 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae4a2830 id: 6, accomodation_id: 2, body: "B3", stars: 4>

11. Fetch all acomodations for exactly 4 guests, preloading reviews (with preload)

ActiveRecord query

Accomodation.where(guests_count: 4).preload(:reviews)

SQL query

SELECT "accomodations".* FROM "accomodations" WHERE "accomodations"."guests_count" = $1  [["guests_count", 4]]
SELECT "reviews".* FROM "reviews" WHERE "reviews"."accomodation_id" = $1  [["accomodation_id", 2]]

Results

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae439678 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae4391c8 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae438f70 id: 6, accomodation_id: 2, body: "B3", stars: 4>

12. Fetch all acomodations for exactly 4 guests, preloading reviews (with eager_load)

ActiveRecord query

Accomodation.where(guests_count: 4).eager_load(:reviews)

SQL query

SELECT "accomodations"."id" AS t0_r0, "accomodations"."name" AS t0_r1, "accomodations"."bathrooms_count" AS t0_r2, "accomodations"."guests_count" AS t0_r3, "reviews"."id" AS t1_r0, "reviews"."accomodation_id" AS t1_r1, "reviews"."body" AS t1_r2, "reviews"."stars" AS t1_r3 FROM "accomodations" LEFT OUTER JOIN "reviews" ON "reviews"."accomodation_id" = "accomodations"."id" WHERE "accomodations"."guests_count" = $1  [["guests_count", 4]]

Results

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae3d2d88 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae3d25b8 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae3d1dc0 id: 6, accomodation_id: 2, body: "B3", stars: 4>

13. Fetch all acomodations with reviews of 4 stars (not in average), preloading reviews (with includes)

ActiveRecord query

Accomodation.includes(:reviews).where(reviews: {stars: 4})

SQL query

SELECT "accomodations"."id" AS t0_r0, "accomodations"."name" AS t0_r1, "accomodations"."bathrooms_count" AS t0_r2, "accomodations"."guests_count" AS t0_r3, "reviews"."id" AS t1_r0, "reviews"."accomodation_id" AS t1_r1, "reviews"."body" AS t1_r2, "reviews"."stars" AS t1_r3 FROM "accomodations" LEFT OUTER JOIN "reviews" ON "reviews"."accomodation_id" = "accomodations"."id" WHERE "reviews"."stars" = $1  [["stars", 4]]

Results

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae359a50 id: 6, accomodation_id: 2, body: "B3", stars: 4>
#<Review:0x00007f7fae359708 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae359280 id: 4, accomodation_id: 2, body: "B1", stars: 4>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fae353808 id: 12, accomodation_id: 4, body: "B3", stars: 4>
#<Review:0x00007f7fae3534c0 id: 11, accomodation_id: 4, body: "B2", stars: 4>

14. Fetch all acomodations with reviews of 4 stars (not in average), preloading reviews (with preload)

ActiveRecord query

Accomodation.joins(:reviews).where(reviews: {stars: 4}).distinct(true).preload(:reviews)

SQL query

SELECT DISTINCT "accomodations".* FROM "accomodations" INNER JOIN "reviews" ON "reviews"."accomodation_id" = "accomodations"."id" WHERE "reviews"."stars" = $1  [["stars", 4]]
SELECT "reviews".* FROM "reviews" WHERE "reviews"."accomodation_id" IN ($1, $2)  [[nil, 2], [nil, 4]]

Results

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fae27a828 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fae279928 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fae2737f8 id: 6, accomodation_id: 2, body: "B3", stars: 4>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fae2721f0 id: 10, accomodation_id: 4, body: "B1", stars: 5>
#<Review:0x00007f7fae26a400 id: 11, accomodation_id: 4, body: "B2", stars: 4>
#<Review:0x00007f7fae269848 id: 12, accomodation_id: 4, body: "B3", stars: 4>

15. Fetch all acomodations with reviews of 4 stars (not in average), preloading reviews (with eager_load)

ActiveRecord query

Accomodation.eager_load(:reviews).where(reviews: {stars: 4})

SQL query

SQL (0.3ms)  SELECT "accomodations"."id" AS t0_r0, "accomodations"."name" AS t0_r1, "accomodations"."bathrooms_count" AS t0_r2, "accomodations"."guests_count" AS t0_r3, "reviews"."id" AS t1_r0, "reviews"."accomodation_id" AS t1_r1, "reviews"."body" AS t1_r2, "reviews"."stars" AS t1_r3 FROM "accomodations" LEFT OUTER JOIN "reviews" ON "reviews"."accomodation_id" = "accomodations"."id" WHERE "reviews"."stars" = $1  [["stars", 4]]

Results

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fb28acee0 id: 6, accomodation_id: 2, body: "B3", stars: 4>
#<Review:0x00007f7fb28accd8 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fb28acb48 id: 4, accomodation_id: 2, body: "B1", stars: 4>

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fb28ac760 id: 12, accomodation_id: 4, body: "B3", stars: 4>
#<Review:0x00007f7fb28ac580 id: 11, accomodation_id: 4, body: "B2", stars: 4>

16. List of accomodations with reviews of 4 stars (in average), preloading reviews

ActiveRecord query

Accomodation.joins(:reviews).group(:id).having("avg(reviews.stars) > 4").preload(:reviews)

SQL query

SELECT "accomodations".* FROM "accomodations" INNER JOIN "reviews" ON "reviews"."accomodation_id" = "accomodations"."id" GROUP BY "accomodations"."id" HAVING (avg(reviews.stars) >= 4)
SELECT "reviews".* FROM "reviews" WHERE "reviews"."accomodation_id" IN ($1, $2, $3)  [[nil, 4], [nil, 2], [nil, 3]]

Results

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Reviews
#<Review:0x00007f7fb28747e8 id: 10, accomodation_id: 4, body: "B1", stars: 5>
#<Review:0x00007f7fb28746f8 id: 11, accomodation_id: 4, body: "B2", stars: 4>
#<Review:0x00007f7fb2874608 id: 12, accomodation_id: 4, body: "B3", stars: 4>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Reviews
#<Review:0x00007f7fb2875a80 id: 4, accomodation_id: 2, body: "B1", stars: 4>
#<Review:0x00007f7fb2874d38 id: 5, accomodation_id: 2, body: "B2", stars: 4>
#<Review:0x00007f7fb2874c48 id: 6, accomodation_id: 2, body: "B3", stars: 4>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Reviews
#<Review:0x00007f7fb2874b58 id: 7, accomodation_id: 3, body: "B1", stars: 5>
#<Review:0x00007f7fb28749c8 id: 8, accomodation_id: 3, body: "B2", stars: 5>
#<Review:0x00007f7fb28748d8 id: 9, accomodation_id: 3, body: "B3", stars: 5>

17. List of accomodations with exactly 2 rooms, preloading rooms

ActiveRecord query

Accomodation.joins(:rooms).group(:id).having("count(rooms.id) = 2").preload(:rooms)

SQL query

SELECT "accomodations".* FROM "accomodations" INNER JOIN "rooms" ON "rooms"."accomodation_id" = "accomodations"."id" GROUP BY "accomodations"."id" HAVING (count(rooms.id) = 2)
SELECT "rooms".* FROM "rooms" WHERE "rooms"."accomodation_id" IN ($1, $2, $3, $4)  [[nil, 4], [nil, 2], [nil, 3], [nil, 1]]

Results

#<Accomodation id: 4, name: "Home4", bathrooms_count: nil, guests_count: 7>
Rooms
#<Room:0x00007f7fb282dd48 id: 7, accomodation_id: 4, name: "R1", beds_count: 6>
#<Room:0x00007f7fb282dc08 id: 8, accomodation_id: 4, name: "R2", beds_count: 3>

#<Accomodation id: 2, name: "Home2", bathrooms_count: nil, guests_count: 4>
Rooms
#<Room:0x00007f7fb282e540 id: 3, accomodation_id: 2, name: "R1", beds_count: 4>
#<Room:0x00007f7fb282e338 id: 4, accomodation_id: 2, name: "R2", beds_count: 3>

#<Accomodation id: 3, name: "Home3", bathrooms_count: nil, guests_count: 5>
Rooms
#<Room:0x00007f7fb282e1d0 id: 5, accomodation_id: 3, name: "R1", beds_count: 6>
#<Room:0x00007f7fb282df28 id: 6, accomodation_id: 3, name: "R2", beds_count: 3>

#<Accomodation id: 1, name: "Home1", bathrooms_count: nil, guests_count: 3>
Rooms
#<Room:0x00007f7fb282e950 id: 1, accomodation_id: 1, name: "R1", beds_count: 2>
#<Room:0x00007f7fb282e680 id: 2, accomodation_id: 1, name: "R2", beds_count: 3>

Did you finish?

If you have already finished… Awesome!!

If you need more time… It’s ok!… Try to make some time in your schedule and try to finish it!

If you find this post helpful, it would be nice if you can share it with someone that you think it also could be helpful =).

Do you want the code?

You can find this examples in two ways.

A repo with all the examples with the answers - You will be able to run the code, and experiment with all the exercises.
A repo just the exercises, as a quiz - You will be able to test you knowledge and experiment a little more.

Download a free ebook to learn the basics of n+1 queries on Rails basics

Learn just enough fundamentals to be fluent preloading associations with ActiveRecord, and start helping your team to avoid n+1 queries on production.

Examples to learn the difference between preload, includes or eager_load

The model of the example

Examples

1. Fetch all acomodations preloading rooms (with includes)

2. Fetch all acomodations preloading rooms (with preload)

3. Fetch all acomodations preloading rooms (with eager load)

4. Fetch all acomodations preloading reviews (with includes)

5. Fetch all acomodations preloading reviews (with preload)

6. Fetch all acomodations preloading reviews (with eager_load)

7. Fetch all acomodations preloading reviews and rooms (with includes)

8. Fetch all acomodations preloading reviews and rooms (with preload)

9. Fetch all acomodations preloading reviews and rooms (with eager_load)

10. Fetch all acomodations for exactly 4 guests, preloading reviews (with includes)

11. Fetch all acomodations for exactly 4 guests, preloading reviews (with preload)

12. Fetch all acomodations for exactly 4 guests, preloading reviews (with eager_load)

13. Fetch all acomodations with reviews of 4 stars (not in average), preloading reviews (with includes)

14. Fetch all acomodations with reviews of 4 stars (not in average), preloading reviews (with preload)

15. Fetch all acomodations with reviews of 4 stars (not in average), preloading reviews (with eager_load)

16. List of accomodations with reviews of 4 stars (in average), preloading reviews

17. List of accomodations with exactly 2 rooms, preloading rooms

Did you finish?

Do you want the code?

Related articles

Download a free ebook to learn the basics of n+1 queries on Rails basics