Clear ORM
Search…
Eager Loading

Resolving the N+1 query problem

N+1 query is a common anti-pattern which happens when you call a relation inside a collection of model. Let's take this example:
1
Post.query.each do |post|
2
puts "Post category: #{post.category.name}"
3
end
4
5
# Output:
6
# SELECT * FROM posts;
7
# SELECT * FROM categories WHERE post_id = 1
8
# SELECT * FROM categories WHERE post_id = 2
9
# SELECT * FROM categories WHERE post_id = 3
10
# SELECT * FROM categories WHERE post_id = 4
11
# SELECT * FROM categories WHERE post_id = 5
12
# ....
Copied!
Since it's faster to query once 100 models than to query 100 times for each model, we could optimize it by calling two requests: one for the posts then one for the related categories.
Clear offers convenient methods called with_[relation] which build the query and cache the related model. Let's try it:
1
Post.query.with_category.each do |post|
2
puts "Post category: #{post.category.name}"
3
end
4
5
# Output:
6
# SELECT * FROM category WHERE post_id IN (SELECT id FROM posts);
7
# SELECT * FROM posts;
Copied!
We just resolved our problem, and we will execute only two requests.

Deep inclusion

with_[relation] helper allows you to pass a block, which can refine the related objects. Therefore, it's easy to include far-related model like in this example:
1
User.query.with_posts(&.with_category).each do |user|
2
puts "User #{user.id}'s posts:"
3
user.posts.each do |post|
4
puts "Post category: #{post.category.name}"
5
end
6
end
Copied!
Since the with_[relation] helper return a collection in the block, you can apply filtering over the query:
1
User.query.with_posts{ |p|
2
p.where(published: true).with_category{ |c|
3
c.select("name")
4
}
5
}.each do |user|
6
puts "User #{user.id}'s published posts:"
7
user.posts.each do |post|
8
puts "Post category: #{post.category.name}"
9
end
10
end
11
12
# Note: we encourage using &.xxx notation and scopes for the query above.
13
# It would then be rewritten like this:
14
User.query.with_posts(&.published.with_category(&.select("name"))).each do |user|
15
puts "User #{user.id}'s published posts:"
16
user.posts.each do |post|
17
puts "Post category: #{post.category.name}"
18
end
19
end
Copied!
Last modified 1yr ago