DEV Community

Agustin Martinez
Agustin Martinez

Posted on • Updated on

Rails N+1 query and Draper decorators

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)

Collapse
 
ben profile image
Ben Halpern

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.

Switch decorators from Draper to Rails builtin decorations #6040

rhymes avatar
rhymes commented on Feb 12, 2020

What type of PR is this? (check all applicable)

  • [x] Refactor
  • [ ] Feature
  • [ ] Bug Fix
  • [x] Optimization
  • [ ] Documentation Update

Description

This PR does the following things:

  • uses delegate_missing_to that was added in Rails 5.1 as a simpler decoration mechanism
  • fixes some bugs in the existing decorators
  • adds specs for all decorators methods that were missing them
  • says bye bye to Draper 🗡 as we use none of its advanced features and draper has too much magic 👀

Added tests?

  • [x] yes
  • [ ] no, because they aren't needed
  • [ ] no, because I need help

Added to documentation?

  • [ ] docs.dev.to
  • [ ] readme
  • [x] no documentation needed
Collapse
 
agustincchato profile image
Agustin Martinez

oh, I haven't heard about that before. I'll look at that. Thank you!