How to Get Last N Records with Activerecord

How to get last N records with activerecord?

Updated Answer (2020)

You can get last N records simply by using last method:

Record.last(N)

Example:

User.last(5)

Returns 5 users in descending order by their id.

Deprecated (Old Answer)

An active record query like this I think would get you what you want ('Something' is the model name):

Something.find(:all, :order => "id desc", :limit => 5).reverse

edit: As noted in the comments, another way:

result = Something.find(:all, :order => "id desc", :limit => 5)

while !result.empty?
        puts result.pop
end

ActiveRecord - Get the last n records and delete them in one command?

To do it in one SQL query use delete_all:

Model.order(created_at: :desc).limit(n).delete_all

But delete_all won't execute any model callbacks or validations

To run callbacks and validations use destroy_all:

Model.order(created_at: :desc).limit(n).destroy_all

Unfortunately destroy_all will execute n + 1 SQL queries: 1 query to retrieve records and n queries to delete each record.

ActiveRecord - get the last 20 records of a where

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)

SQL query to get last record based on table relation

you can do this query (Result here)

with x as (
select row_number() over (partition by p.id order by b.created_at desc) as rn,b.id as id_box,p.id as id_paper
from boxes b join stones s on b.stone_id = s.id
join papers p on p.id = s.paper_id)
select x.id_box from x where rn = 1

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

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.

get last 5 records

Order received_documents by the creation date and take 5 last:

@business_partner.received_documents.order(:created_at).limit(5)

To get 5 newest ones you'd do:

@business_partner.received_documents.order(created_at: :desc).limit(5)

EDIT

The problem with this

@business_partner.received_documents.last(5) do |document|

is that you actually do not iterate over the collection, thus no output is shown.

Use each:

@business_partner.received_documents.last(5).each do |document|

Rails 3- Retrieve last N records

with the Rails 2.x query interface the answer is:

Quarter.find(:all, :order => :year, :limit => 4).reverse

with the Rails 3.x query interface the answer is:

Quarter.order(:by => :year).limit(4).reverse

btw. I think you should not store your data in a model called "Quarter" , but rather in a model called "FinancialData" and have that model have an attribute "quarter" which would contain 2011/1, 2011/2, or havt two attributes :year and :quarter which need to be indexed together

See also:

http://guides.rubyonrails.org/active_record_querying.html

http://m.onkey.org/active-record-query-interface

How can I get last ActiveRecord query data?

You can't do this directly because

1. The request that applies the filters and the request that triggers the search are 2 different requests.
2. Rails initializes a new controller instance for each request, so the controller for filtering and the controller for searching are 2 different objects. They don't share any instance variables.

You can however store the IDs returned by the filter phase in session, and use those IDs in search phase.

def filter
  @prices = Price.where(date: Date.today).order(price: :asc)
  if params[:filter1] != nil 
    @prices = @prices.where('filter1_field IN (?)', params[:filter1])
  end
  if params[:filter2] != nil 
    @prices = @prices.where('filter2_field IN (?)', params[:filter2])
  end      

  session[:price_ids] = @prices.pluck(:id)

  respond_to do |format|
    format.js
  end
end

def search

  @prices = session[:price_ids].present? ? 
            Price.where(id: session[:price_ids]) :
            Price.all

  @prices = @price.where(date: Date.today).order(price: :asc)
  if params[:search] != nil
     @prices = @prices.where('name LIKE ?', "%#{params[:search]}%")
  end
end

This approach has a drawback that you have to remove price IDs from sessions at the proper time, which can be hard do define.

Another approach is to ensure the browser sending the filter every time it requests a search (unless your Rails app is an API for single page application). This approach requires a redirect whenever filter is changed.

def filter
  redirect_to search_path(params.slice(:filter1, :filter2))
end

def search
  @prices = Price.where(date: Date.today).order(price: :asc)
  if params[:filter1] != nil 
    @prices = @prices.where('filter1_field IN (?)', params[:filter1])
  end
  if params[:filter2] != nil 
    @prices = @prices.where('filter2_field IN (?)', params[:filter2])
  end
  if params[:search] != nil
     @prices = @prices.where('name LIKE ?', "%#{params[:search]}%")
  end
end

And your search form should contain 2 hidden fields for the filters

<%= form_tag '/prices/search', method: :get do %>
  <%= hidden_field_tag :filter1, params[:filter1] %>
  <%= hidden_field_tag :filter2, params[:filter2] %>
  <!-- other form stuff ... -->
<% end %>

Rails: how to find record before last?

Table.where(:dogovor_id => p.id).order("id DESC").first           # last
Table.where(:dogovor_id => p.id).order("id DESC").offset(1).first # last - 1
Table.where(:dogovor_id => p.id).order("id DESC").offset(2).first # last - 2

How to get the latest record from each group in ActiveRecord?

Postgres

In Postgres, this can be achieved with the following query.

SELECT DISTINCT ON ("group") * FROM projects
ORDER BY "group", date DESC, id DESC

Because the date column might not be unique here, I have added an additional ORDER BY clause on id DESC to break ties in favor of the record with the higher ID, in case two records in a group have the same date. You might instead want to use another column like the date/time of the last update or so, that depends on your use case.

Moving on, ActiveRecord unfortunately has no API for DISTINCT ON, but we can still use plain SQL with select:

Project.select('DISTINCT ON ("group") *').order(:group, date: :desc, id: :desc)

or if you prefer using ARel instead of having raw SQL:

p = Project.arel_table
Project.find_by_sql(
  p.project(p[Arel.star])
   .distinct_on(p[:group])
   .order(p[:group], p[:date].desc, p[:id].desc)
)

MySQL

For other databases like MySQL this is unfortunately not as convenient. There are a variety of solutions available, see for example this answer.

---

Related Topics