Whats the best way to get the nth to last record?

Question

I saw here: How to get last N records with activerecord? that the best way to get the last 5 records is SomeModel.last(5). Is the best way to get the 5th last record only SomeModel.last(5).first or is it something else?

Thanks in advance!

Answer 1

What you're looking for is a combination of LIMIT, OFFSET, and ensuring that the query is using ORDER BY as a constraint. This is described on the PostgreSQL - Queries: Limit and Offset documentation page.

An example is:

irb> SomeModel.count
=> 35

irb> SomeModel.order(:id).reverse_order.limit(1).offset(4)
# SomeModel Load (0.7ms)  SELECT "some_models".* FROM "some_models" ORDER BY "some_models".id DESC LIMIT 1 OFFSET 4
=> #<ActiveRecord::Relation [#<SomeModel id: 31, name: "hello world", created_at: "2014-03-24 21:52:46", updated_at: "2014-03-24 21:52:46">]>

Which yields the same result (albeit as an AR::Relation) as:

irb> SomeModels.last(5).first
# SELECT "some_models".* FROM "some_models" ORDER BY "some_models"."id" DESC LIMIT 5
=> #<SomeModel id: 31, name: "hello world", created_at: "2014-03-24 21:52:46", updated_at: "2014-03-24 21:52:46">

The difference is that in the former query, PostgreSQL will treat the .reverse_order.limit.offset query in memory, and restrict the values appropriately before it returns the result. In the latter, PostgreSQL will return 5 results, and you restrict the values in Ruby by using #first.

Answer 2

You can use offset with a query in the reverse order. For example, the fifth last created record can be retrieved with:

SomeModel.order("created_at DESC").offset(4).first