---
title: ActiveRecord Eager Loading with Query Objects and Decorators
teaser: How can you plug complex, multi-model SQL queries into ActiveRecord models
  so that you can use them in your views?
tags: web,rails,sql,performance
author: Joe Ferris
published_on: 2014-09-19
---

We recently came across an interesting problem, which was discussed in a
previous post, [Postgres Window Functions]:

> We want to get each post's three most recent comments.

As discussed, you can't use simple eager loading:

```ruby
Post.order(created_at: :desc).limit(5).includes(:comments)
```

This will load every comment for each post. When there are many comments per
post, this quickly becomes unacceptable.

## Starting Slow

It's frequently easiest to start with a slow implementation and make it faster
when necessary. In many cases, the slower (and possibly simpler) implementation
will work just fine, and it's best to deploy it as-is. Let's look at a naive
implementation of our list of posts and comments:

```ruby
class PostsController < ApplicationController
  def index
    @posts = Post.order(created_at: :desc).limit(5)
  end
end
```

```ruby
class Post < ActiveRecord::Base
  has_many :comments, dependent: :destroy

  def latest_comments
    comments.order(created_at: :desc).limit(3)
  end
end
```

This will frequently do fine, but causes N+1 queries, which will look like this
in your log:

```log
    Started GET "/" for 127.0.0.1 at 2014-09-18 11:36:18 -0400
    Processing by PostsController#index as HTML
      Post Load (0.4ms)  SELECT "posts".* FROM "posts"
        ORDER BY "posts"."created_at" DESC LIMIT 5
      Comment Load (0.2ms)  SELECT "comments".* FROM "comments"
        WHERE "comments"."post_id" = $1
        ORDER BY "comments"."created_at" DESC
        LIMIT 3  [["post_id", 27]]
      Comment Load (0.3ms)  SELECT "comments".* FROM "comments"
        WHERE "comments"."post_id" = $1
        ORDER BY "comments"."created_at" DESC
        LIMIT 3  [["post_id", 28]]
      Comment Load (0.2ms)  SELECT "comments".* FROM "comments"
        WHERE "comments"."post_id" = $1
        ORDER BY "comments"."created_at" DESC
        LIMIT 3  [["post_id", 29]]
      ...
```

If you're using New Relic like we do, you'll know a slow transaction has an N+1
problem when it shows many queries to the same model or table in the transaction
log. Once performance starts to suffer, you'll want to consolidate those
queries.

In the previous post, we described how you could use a Postgres Window Function
to find the comments you want in one query:

```sql
SELECT * FROM (
  SELECT comments.*, dense_rank() OVER (
    PARTITION BY comments.post_id
    ORDER BY comments.created_at DESC
  ) AS comment_rank
) AS ranked_comments
WHERE comment_rank < 4;
```

However, how can we plug this query into ActiveRecord such that we can use the
data in our views?

It's actually fairly easy. You need two new objects: a [Query Object] and a
[Decorator]. Let's refactor to introduce these objects, and then we'll plug in
our query.

## The Query Object

We can perform the [Extract Class] refactoring and create a `Feed` model:

```ruby
class Feed
  def initialize(posts:)
    @posts = posts.order(created_at: :desc).limit(5)
  end

  def posts
    @posts
  end
end
```

```ruby
class PostsController < ApplicationController
  def index
    @feed = Feed.new(posts: Post.all)
  end
end
```

## The Decorator

We can use `SimpleDelegator` to create a quick decorator class for `Post`:

```ruby
class PostWithLatestComments < SimpleDelegator
  def latest_comments
    comments.order(created_at: :desc).limit(3)
  end
end
```

We can apply this decorator to each `Post` in the `Feed`:

```ruby
class Feed
  def posts
    @posts.map { |post| PostWithLatestComments.new(post) }
  end
end
```

## The SQL Query

At this point, we've done nothing except to introduce two new classes to our
system. However, we've provided ourselves an opportunity.

We frequently use Query Objects to wrap up complex <abbr title="Structured Query
Language">SQL</abbr> without polluting models.  In addition to encapsulating
<abbr title="Structured Query Language">SQL</abbr>, though, they can also hold
context, empowering objects to remember the query from whence they came. We'll
use this property of query objects to plug our <abbr title="Structured Query
Language">SQL</abbr> into our application.

First, we'll use the above <abbr title="Structured Query Language">SQL</abbr>
query to find the comments relevant to our posts:

```ruby
# feed.rb

def comments
  Comment.
    select("*").
    from(Arel.sql("(#{ranked_comments_query}) AS ranked_comments")).
    where("comment_rank <= 3")
end

def ranked_comments_query
  Comment.where(post_id: @posts.map(&:id)).select(<<-SQL).to_sql
    comments.*,
    dense_rank() OVER (
      PARTITION BY comments.post_id
      ORDER BY comments.created_at DESC
    ) AS comment_rank
  SQL
end
```

Then, we'll group those comments by `post_id` into a `Hash`:

```ruby
class Feed
  def initialize(posts:)
    @posts = posts.order(created_at: :desc).limit(5)
    @comment_cache = build_comment_cache
  end

  # ...

  private

  def build_comment_cache
    comments.group_by(&:post_id)
  end

  # ...
end
```

Now, we pass that `Hash` to our decorator:

```ruby
# feed.rb

def posts
  @posts.map { |post| PostWithLatestComments.new(post, @comment_cache) }
end
```

```ruby
class PostWithLatestComments < SimpleDelegator
  def initialize(post, comments_by_post_id)
    super(post)
    @comments_by_post_id = comments_by_post_id
  end

  def latest_comments
    @comments_by_post_id[id] || []
  end
end
```

## The Result

Our `Feed` class is now smart enough to perform two <abbr title="Structured
Query Language">SQL</abbr> queries:

* One query to `posts` to find the posts we care about.
* One query to `comments` (using Postgres Window Functions) to find the latest
  three comments for each post.

It then decorates each post, providing the preloaded `Hash` of comments to the
decorator. This allows the decorated posts to find their latest three comments
without performing an additional query.

The finished `Feed` class looks like this:

```ruby
class Feed
  def initialize(posts:)
    @posts = posts.order(created_at: :desc).limit(5)
    @comment_cache = build_comment_cache
  end

  def posts
    @posts.map { |post| PostWithLatestComments.new(post, @comment_cache) }
  end

  private

  def build_comment_cache
    comments.group_by(&:post_id)
  end

  def comments
    Comment.
      select("*").
      from(Arel.sql("(#{ranked_comments_query}) AS ranked_comments")).
      where("comment_rank <= 3")
  end

  def ranked_comments_query
    Comment.where(post_id: @posts.map(&:id)).select(<<-SQL).to_sql
      comments.*,
      dense_rank() OVER (
        PARTITION BY comments.post_id
        ORDER BY comments.created_at DESC
      ) AS comment_rank
    SQL
  end
end
```

As you can see, most of the logic is concerned with generating that <abbr title="Structured Query Language">SQL</abbr> query,
and the machinery for plugging the results into our ActiveRecord models is very
lightweight.

At this point, requests in our log look something like this:

```log
Started GET "/" for 127.0.0.1 at 2014-09-18 13:53:39 -0400
Processing by PostsController#index as HTML
  Post Load (0.4ms)  SELECT "posts".* FROM "posts"
    ORDER BY "posts"."created_at" DESC
    LIMIT 5
  Comment Load (0.5ms)  SELECT * FROM (
    SELECT comments.*,
      dense_rank() OVER (
        PARTITION BY comments.post_id
        ORDER BY comments.created_at DESC
      ) AS comment_rank
    FROM "comments"
    WHERE "comments"."post_id" IN (154, 153)
    ) AS ranked_comments WHERE (comment_rank <= 3)
```

You can use this approach for many situations where it's difficult to use simple
eager loading.

## What's next

If you're looking for more ActiveRecord magic, be sure to check out our
[Advanced ActiveRecord Querying course on Upcase][aarq], or learn how the
query in this post works by reading about [Postgres Window Functions].

[Postgres Window Functions]: https://thoughtbot.com/blog/postgres-window-functions
[Query Object]: http://blog.codeclimate.com/blog/2012/10/17/7-ways-to-decompose-fat-activerecord-models/#query-objects
[Decorator]: https://thoughtbot.com/blog/decorators-compared-to-strategies-composites-and
[Extract Class]: https://thoughtbot.com/upcase/videos/extract-class
[aarq]: https://thoughtbot.com/upcase/advanced-activerecord-querying