Recently, we were working on a feature which required us to retrieve our most recent account records ordered from newest to oldest (i.e., by id descending). This is a simple enough use case–after all, displaying subsets of records chronological order is one of the most common tasks of a web app. Without pausing to think, we wrote Account.order(id: :desc).last(10). That should work, right? Give us the last ten accounts ordered by id descending. Imagine our momentary surprise when instead of the last ten accounts, we got the first ten:

Account.order(id: :desc).last(10)
  SELECT  "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT $1  [["LIMIT", 10]]
    [#<Account id: 1536>,
     #<Account id: 1280>,
     #<Account id: 1024>,
     #<Account id: 768>,
     #<Account id: 512>,
     #<Account id: 256>,
     #<Account id: 158>,
     #<Account id: 78>,
     #<Account id: 7>,
     #<Account id: 1>]

Fortunately, the surprise didn’t last very long when we stopped to think about how the query reads: order all the accounts from newest to oldest, then take the last 10. Nonetheless it was a good reminder that it is helpful to understand the basics of how ActiveRecord builds its queries. In this post we will walk through a few common use cases for retrieving collections with first and last, see how these methods work in each scenario, and explore some simple pitfalls.

Scenario 1: first ten records which are ordered from oldest to newest

Easy; let’s use first:

Account.first(10)
  SELECT  "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT $1  [["LIMIT", 10]]
    [#<Account id: 1>,
     #<Account id: 7>,
     #<Account id: 78>,
     #<Account id: 158>,
     #<Account id: 256>,
     #<Account id: 512>,
     #<Account id: 768>,
     #<Account id: 1024>,
     #<Account id: 1280>,
     #<Account id: 1536>]

Before continuing, let’s stop and take a look at the generated query. Notice that the accounts are ordered by id ASC and a limit is applied without providing those explicitly.

Scenario 2: first ten records which are ordered from newest to oldest

Basically, the same records as last time, but in the reverse order. Let’s try to override the default ordering used by first:

Account.order(id: :desc).first(10)
  SELECT  "accounts".* FROM "accounts" ORDER BY "accounts"."id" DESC LIMIT $1  [["LIMIT", 10]]
    [#<Account id: 14901685>,
     #<Account id: 14901504>,
     #<Account id: 14901248>,
     #<Account id: 14900992>,
     #<Account id: 14900736>,
     #<Account id: 14900480>,
     #<Account id: 14900224>,
     #<Account id: 14899968>,
     #<Account id: 14899712>,
     #<Account id: 14899456>]

That isn’t what we want, but it does make sense–it is the first 10 of all records when ordered by primary key descending. Remember that first builds its query using the stated order, then takes records from the top of that list using limit.

Seeing that first gives us the opposite of what we want, what happens if we use last?

Account.order(id: :desc).last(10)
  SELECT  "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT $1  [["LIMIT", 10]]
    [#<Account id: 1536>,
     #<Account id: 1280>,
     #<Account id: 1024>,
     #<Account id: 768>,
     #<Account id: 512>,
     #<Account id: 256>,
     #<Account id: 158>,
     #<Account id: 78>,
     #<Account id: 7>,
     #<Account id: 1>]

While this is the result we want, there is a curious mismatch between the query, the SQL, and the resulting collection. In our statement, we asked for id DESC but SQL seems to ignore this with id ASC. Yet, the collection itself is indeed ordered by id descending. This mismatch is the result of additional processing performed by ActiveRecord.

Let’s take a look at the source code for last:

def last(limit = nil)
  return find_last(limit) if loaded? || has_limit_or_offset?

  result = ordered_relation.limit(limit)
  result = result.reverse_order!

  limit ? result.reverse : result.first
end

In the line result = ordered_relation.limit(limit), ordered_relation represents a query that would return the accounts ordered as requested (id DESC) and the limit is applied on that. On the next line, the query is modified by calling reverse_order! which inverts each order directive in the query and flips id DESC to id ASC. The limit is still present, but the order statement has been reversed–this is what we see in the SQL. At this stage, result is an array containing the first 10 account records in ascending ID order. Finally, in the last line, the result array is reversed so the final collection is ordered by primary key descending in accordance with our initial directive.

In summary: last chains a limit to an ordered_relation query, inverts the order directives of that query, executes the query, and reverses the results in-memory. This is done so for performance reasons. Using SQL’s limit is efficient compared to gathering all records into memory and reversing them in-memory. ActiveRecord alters the order to maximize performance.

Scenario 3: last ten records ordered from oldest to newest

There are fewer possible surprises here. As we have already seen, ordered_relation introduces a default order of primary key ascending. This is the correct order for this case, so last functions as one might expect. Account.last(10) is functionally equivalent to both Account.order(id: :desc).first(10).reverse and Account.order(id: :desc).limit(10).reverse.

Scenario 4: last ten records ordered from newest to oldest

One obvious choice would be to use last and reverse:

Account.last(10).reverse
  SELECT  "accounts".* FROM "accounts" ORDER BY "accounts"."id" DESC LIMIT $1  [["LIMIT", 10]]
    [#<Account id: 14901685>,
     #<Account id: 14901504>,
     #<Account id: 14901248>,
     #<Account id: 14900992>,
     #<Account id: 14900736>,
     #<Account id: 14900480>,
     #<Account id: 14900224>,
     #<Account id: 14899968>,
     #<Account id: 14899712>,
     #<Account id: 14899456>]

This works, but recall that reverse is now being performed twice. Therefore, it is actually better to use what we saw earlier with first:

Account.order(id: :desc).first(10)
  SELECT  "accounts".* FROM "accounts" ORDER BY "accounts"."id" DESC LIMIT $1  [["LIMIT", 10]]
    [#<Account id: 14901685>,
     #<Account id: 14901504>,
     #<Account id: 14901248>,
     #<Account id: 14900992>,
     #<Account id: 14900736>,
     #<Account id: 14900480>,
     #<Account id: 14900224>,
     #<Account id: 14899968>,
     #<Account id: 14899712>,
     #<Account id: 14899456>]

Calling first in this way is functionally equivalent to Account.order(id: :desc).limit(10).

Conclusion

In summary, here are the key points:

  • first and last work by building an ordered query (called ordered_relation) and applying a limit to that query.
  • limit can only return the first records from a query, so last inverts the order before execution and reverses the results in ruby.
  • If you write .last(10).reverse, reverse runs twice. You can optimize your query by using .order(whatever: :desc).first(10) instead.
  • ordered_relation introduces a default order of primary key ascending, so calling first or last with order(id: :asc) is redundant and doesn’t change the results.
  • first and last use the ActiveRecord caching infrastructure to avoid extra queries. If you don’t need this, you can avoid extra overhead by writing explicit queries with limit, order, and reverse.

Hopefully this helps you optimize some simple queries and perhaps avoid a few mistakes.