In this article, I am going to tell you about how decorators cause a N+1 query and how I solve it.
Scenario
I developed an API to support a web site that allows their user sends messages to other users typing their emails. This case is about the endpoint which lists all the messages of the logged user.
I had three models: Message, Recipient, and User. A Message has many Recipients and these recipients have a User that could exist or not because the user could be registered on the web site or not.
# app/models/message.rb
class Message < ApplicationRecord
belongs_to :user
has_many :recipients, dependent: :destroy
validates :title, :description, presence: true
end
# app/models/recipient.rb
class Recipient < ApplicationRecord
belongs_to :user, optional: true
validates :email, presence: true, uniqueness: { scope: :message_id },
end
# app/models/user.rb
class User < ApplicationRecord
has_many :messages, dependent: :destroy
has_many :recipients, inverse_of: :user
validates :name, presence: true
end
Also, I have an endpoint that returns all the messages to the current user.
# app/controllers/api/v1/messages_controller.rb
module Api
module V1
class MessageController < ApplicationController
before_action :authenticate_user!
def index
@messages = current_user.messages.includes(recipients: :user).order(created_at: :desc).decorate
end
end
end
end
At this point, I just did the recommended code to avoid N+1 queries. Finally, I render the view using jbuilder to return the object as a JSON. The decorators are implemented with the gem draper to allow some presentation values keeping clean models.
# app/views/api/v1/messages/index.json.jbuilder
json.messages @messages, partial: 'info', as: :message
# app/views/api/v1/messages/_info.json.jbuilder
json.extract! message, :id, :title, :description, :created_at
json.recipients message.recipients.decorate, partial: 'api/v1/recipients/info', as: :recipient
# app/views/api/v1/recipients/_info.json.jbuilder
json.extract! recipient, :email, :user_name
# app/decorators/recipients_decorator.rb
class RecipientDecorator < Draper::Decorator
delegate_all
def user_name
return user.name if user.present?
email.split('@').first
end
end
My surprise was when I run the code and got that output in the console:
Started GET "/api/v1/messages" for ::1 at 2020-02-22 13:08:16 -0300
Processing by Api::V1::MessagesController#index as JSON
Parameters: {"message"=>{}}
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."uid" = $1 LIMIT $2 [["uid", "user@mail.com"], ["LIMIT", 1]]
Rendering api/v1/messages/index.json.jbuilder
Message Load (4.5ms) SELECT DISTINCT "messages".* FROM "messages" INNER JOIN "recipients" ON "recipients"."message_id" = "messages"."id" WHERE "messages"."user_id" = $1 AND "messages"."id" NOT IN (SELECT DISTINCT "messages"."id" FROM "messages" INNER JOIN "recipients" ON "recipients"."message_id" = "messages"."id" WHERE "messages"."user_id" = $2 AND "recipients"."state" = $3) AND "recipients"."state" = $4 AND (opening_date >= '2020-01-23 00:00:00') ORDER BY "messages"."created_at" DESC LIMIT $5 [["user_id", 1], ["user_id", 1], ["state", 1], ["state", 0], ["LIMIT", 2]]
Recipient Load (0.4ms) SELECT "recipients".* FROM "recipients" WHERE "recipients"."message_id" IN ($1, $2) [["message_id", 49], ["message_id", 45]]
User Load (0.4ms) SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2, $3) [["id", 6], ["id", 2], ["id", 1]]
Recipient Load (0.3ms) SELECT "recipients".* FROM "recipients" WHERE "recipients"."message_id" = $1 [["message_id", 49]]
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 6], ["LIMIT", 1]]
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 1.8ms | Allocations: 730)
User Load (0.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]]
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 1.3ms | Allocations: 727)
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 1.3ms | Allocations: 726)
Rendered api/v1/messages/_info.json.jbuilder (Duration: 7.1ms | Allocations: 3130)
Recipient Load (0.2ms) SELECT "recipients".* FROM "recipients" WHERE "recipients"."message_id" = $1 [["message_id", 45]]
User Load (0.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 6], ["LIMIT", 1]]
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 1.3ms | Allocations: 726)
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]]
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 1.3ms | Allocations: 727)
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 1.7ms | Allocations: 726)
Rendered api/v1/messages/_info.json.jbuilder (Duration: 6.4ms | Allocations: 3123)
Rendered api/v1/messages/index.json.jbuilder (Duration: 24.4ms | Allocations: 9626)
User Load (1.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
Completed 200 OK in 33ms (Views: 17.9ms | ActiveRecord: 8.7ms | Allocations: 12313)
If you see, the first query is right, it calls all the necessary data in a single query. But then, when starts to render the partial views, it queries again by recipients and users 🤯.
Finding the trouble
After many hours of surfing by different web sites and apply many workarounds. Just try deleting the decorate statement from message.recipients.decorate at app/views/api/v1/messages/_info.json.jbuilder... Suddenly, the rendering works as it suppose to work, making only the first query. But, I also have the issue that I need the decorator to render the user_name at app/views/api/v1/recipients/_info.json.jbuilder view.
Solution
So, I have to call the decorator at Messages view to be able to use the user_name decorate inner the Recipients view. According to the draper documentation the <collection>.decorate apply to collections which are an ActiveRecord query, that means that it raises a query to the database ignoring the preload data. Also, there is a way to decorate any collection <Decorator class>.decorate_collection(<collection>).
Finally, I refactor the code to use this new method. The result was the following.
# app/views/api/v1/messages/_info.json.jbuilder
json.extract! message, :id, :title, :description, :created_at
json.recipients RecipientDecorator.decorate_collection(message.recipients),
partial: 'api/v1/recipients/info',
as: :recipient
And now, when I call the endpoint the log was these:
Started GET "/api/v1/messages" for ::1 at 2020-02-22 13:37:04 -0300
Processing by Api::V1::MessagesController#index as JSON
Parameters: {"message"=>{}}
User Load (0.8ms) SELECT "users".* FROM "users" WHERE "users"."uid" = $1 LIMIT $2 [["uid", "user@mail.com"], ["LIMIT", 1]]
Rendering api/v1/messages/index.json.jbuilder
Message Load (2.0ms) SELECT DISTINCT "messages".* FROM "messages" INNER JOIN "recipients" ON "recipients"."message_id" = "messages"."id" WHERE "messages"."user_id" = $1 AND "messages"."id" NOT IN (SELECT DISTINCT "messages"."id" FROM "messages" INNER JOIN "recipients" ON "recipients"."message_id" = "messages"."id" WHERE "messages"."user_id" = $2 AND "recipients"."state" = $3) AND "recipients"."state" = $4 AND (opening_date >= '2020-01-23 00:00:00') ORDER BY "messages"."created_at" DESC LIMIT $5 [["user_id", 1], ["user_id", 1], ["state", 1], ["state", 0], ["LIMIT", 2]]
Recipient Load (0.4ms) SELECT "recipients".* FROM "recipients" WHERE "recipients"."message_id" IN ($1, $2) [["message_id", 49], ["message_id", 45]]
User Load (0.4ms) SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2, $3) [["id", 6], ["id", 2], ["id", 1]]
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 0.4ms | Allocations: 379)
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 0.3ms | Allocations: 340)
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 0.4ms | Allocations: 339)
Rendered api/v1/messages/_info.json.jbuilder (Duration: 3.2ms | Allocations: 1753)
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 0.5ms | Allocations: 337)
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 0.7ms | Allocations: 338)
Rendered api/v1/recipients/_info.json.jbuilder (Duration: 0.4ms | Allocations: 337)
Rendered api/v1/messages/_info.json.jbuilder (Duration: 2.8ms | Allocations: 1597)
Rendered api/v1/messages/index.json.jbuilder (Duration: 26.6ms | Allocations: 16194)
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
Completed 200 OK in 129ms (Views: 23.4ms | ActiveRecord: 21.5ms | Allocations: 35428)
Summary
The moral of the story is: If you need to decorate a has many collection inner a view, choose <Decorator class>.decorate_collection(<collection>) instead of <collection>.decorate.
Top comments (2)
Nice post.
If you’re curious, there are now built in alternatives to Draper, which we recently implemented... just in case you’re curious about moving off the external dependency.
Not that Draper hasn’t been a fabulous gem.
What type of PR is this? (check all applicable)
Description
This PR does the following things:
Added tests?
Added to documentation?
oh, I haven't heard about that before. I'll look at that. Thank you!