The other day, I had an interview for the first time in a long time. The other party asked me how to deal with the N + 1 problem in ActiveRecord. I was nervous and answered only includes, and then the other party asked me if I knew preload, eager_load, etc. I hurriedly answered, "I forgot." Here, I decided to re-examine and summarize includes, preload, and eager_load.
It's a joint issue if you use other ORMs as well as ActiveRecord.
Simply put, preload gets the specified association in a separate query and caches it, and eager_load gets the specified association in a left join and caches it. includes depends on how you use it.
preload
Order.preload(:cards).where(sex: "male")
Query issued
SELECT `orders`.* FROM `orders` WHERE `orders`.`sex` = 'male'
SELECT `cards`.* FROM `cards` WHERE `cards`.`oid` IN (1, 2)
Therefore, if you use a column of a table other than the orders table as a filtering condition, an error will occur.
eager load Use LEFT OUTER JOIN to get and cache the specified association in a single query.
Order.eager_load(:cards).where(sex:"male")
Query issued
SELECT `orders`.`id` AS t0_r0, `orders`.`oid` AS t0_r1, `orders`.`name` AS t0_r2, `orders`.`email` AS t0_r3, `orders`.`phone` AS t0_r4, `orders`.`option` AS t0_r5, `orders`.`order_status` AS t0_r6, `orders`.`order_type` AS t0_r7, `orders`.`start_at` AS t0_r8, `orders`.`end_at` AS t0_r9, `orders`.`birth_year` AS t0_r10, `orders`.`birth_month` AS t0_r11, `orders`.`birth_day` AS t0_r12, `orders`.`sex` AS t0_r13, `orders`.`amount` AS t0_r14, `orders`.`price` AS t0_r15, `orders`.`created_at` AS t0_r16, `orders`.`updated_at` AS t0_r17, `cards`.`id` AS t1_r0, `cards`.`oid` AS t1_r1, `cards`.`card_no` AS t1_r2, `cards`.`card_rank` AS t1_r3, `cards`.`ordered_at` AS t1_r4, `cards`.`linked_at` AS t1_r5, `cards`.`delivered_at` AS t1_r6, `cards`.`used_at` AS t1_r7, `cards`.`card_status` AS t1_r8, `cards`.`created_at` AS t1_r9, `cards`.`updated_at` AS t1_r10 FROM `orders` LEFT OUTER JOIN `cards` ON `cards`.`oid` = `orders`.`id` WHERE `orders`.`sex` = 'male'
includes
Order.includes(:cards).where(sex:"male")
Issued query
SELECT `orders`.* FROM `orders` WHERE `orders`.`sex` = 'male'
SELECT `cards`.* FROM `cards` WHERE `cards`.`oid` IN (1, 2)
#Is
Order.preload(:cards).where(sex:"male")
#Same behavior as
Order.includes(:cards).where(cards:{card_rank: "gold"})
Issued query
SELECT `orders`.`id` AS t0_r0, `orders`.`oid` AS t0_r1, `orders`.`name` AS t0_r2, `orders`.`email` AS t0_r3, `orders`.`phone` AS t0_r4, `orders`.`option` AS t0_r5, `orders`.`order_status` AS t0_r6, `orders`.`order_type` AS t0_r7, `orders`.`start_at` AS t0_r8, `orders`.`end_at` AS t0_r9, `orders`.`birth_year` AS t0_r10, `orders`.`birth_month` AS t0_r11, `orders`.`birth_day` AS t0_r12, `orders`.`sex` AS t0_r13, `orders`.`amount` AS t0_r14, `orders`.`price` AS t0_r15, `orders`.`created_at` AS t0_r16, `orders`.`updated_at` AS t0_r17, `cards`.`id` AS t1_r0, `cards`.`oid` AS t1_r1, `cards`.`card_no` AS t1_r2, `cards`.`card_rank` AS t1_r3, `cards`.`ordered_at` AS t1_r4, `cards`.`linked_at` AS t1_r5, `cards`.`delivered_at` AS t1_r6, `cards`.`used_at` AS t1_r7, `cards`.`card_status` AS t1_r8, `cards`.`created_at` AS t1_r9, `cards`.`updated_at` AS t1_r10 FROM `orders` LEFT OUTER JOIN `cards` ON `cards`.`oid` = `orders`.`id` WHERE `cards`.`card_rank` = 'gold'
#Is
Order.eager_load(:cards).where('cards.card_rank = "gold"')
#Same behavior as
Order.includes(:cards).where('cards.card_rank="gold"').references(:cards)
#Same behavior as
last
Order.includes(:cards).where(sex:"male").references(:cards)
#Is
Order.eager_load(:cards).where(sex:"male")
#Same behavior as
| Method | cache | Query |
|---|---|---|
| eager_load | To do | Singular |
| preload | To do | Multiple |
| includes | To do | Depending on the case |