Rails N+1 query and Draper decorators
Tags: rails, decorator, activerecord, jbuilder
Published: 2020-02-22 16:59:11



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.

```ruby
# 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.

```ruby
# 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](https://github.com/rails/jbuilder) to return the object as a JSON. The decorators are implemented with the gem [draper](https://github.com/drapergem/draper) to allow some presentation values keeping clean models.

```ruby
# 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
```

```ruby
# 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:

```bash
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](https://github.com/drapergem/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.

```ruby
# 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:

```bash
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`.