CODEWITHSUNDEEP
ruby-on-railspostgresql
Tomm P
Tomm P

Reputation: 825

ActiveRecord - get the last 20 records of a where

I have an ActiveRecord query:

@results = Instrument.where("id < ?", this_id).limit(20)

This returns the first 20 records that match the criteria. I am trying to find a method of efficiently getting the last 20 records of the query.

To be more specific, if I have 100 records total with id 1 through 100 and I specify 

this_id = 51
@results = Instrument.where("id < ?", this_id).limit(20)

I get records 1 thru 20, whereas I want to get records 31 to 50. Is there an efficient way to do this? I am trying to avoid reading the entire database up to the search criteria just to take the last 20.

By the way, this SQL does exactly what I am looking for:

SELECT * FROM (SELECT * FROM instruments WHERE id < 151000 ORDER BY id DESC LIMIT 10 ) sub ORDER BY id ASC;

Upvotes: 2

Views: 687

Answers (2)

Sebasti&#225;n Palma
Sebasti&#225;n Palma

Reputation: 33420

As per the SQL query in the update of your question, you can use from to pass a subquery and build something like that:

Instrument
  .from(Instrument.where('id < ?', 151_000).order(id: :desc).limit(10),
        :instruments)
  .order(:id)

Upvotes: 3

Roman Alekseiev
Roman Alekseiev

Reputation: 1920

What about:

@results = Instrument.where("id < ?", this_id).last(20)

Upvotes: 0

Related Questions