CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Tintin81
Tintin81

Reputation: 10207

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

In my Ruby on Rails application I have a database structure like this:

Project.create(:group => "1", :date => "2014-01-01")
Project.create(:group => "1", :date => "2014-01-02")
Project.create(:group => "1", :date => "2014-01-03")

Project.create(:group => "2", :date => "2014-01-01")
Project.create(:group => "2", :date => "2014-01-02")
Project.create(:group => "2", :date => "2014-01-03")

# and so forth...

How can I get the latest record from each group using ActiveRecord?

The solution is probably simple but I can't get my head around this.

Thanks for any help.

Upvotes: 46

Views: 33987

Answers (6)

jing
jing

Reputation: 2021

Following solution based on this link works for MySQL and it's extensible to all fields in group table.

Project.select(:group, 'MAX(date) AS date').group(:group)

Upvotes: 6

jstafford
jstafford

Reputation: 418

I spent some time battling this and thought I'd share what I found to be the cleanest and stunningly easy solution (assuming date or other sorting field contains unique values):

Project.group(:group).maximum(:date)

Hat tip to qarol for posting this in this comment.

Upvotes: 13

Patrick Oscity
Patrick Oscity

Reputation: 54684

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.

Upvotes: 77

Navid Farjad
Navid Farjad

Reputation: 455

This works for me

ids = Message.select("MAX(id) AS id").group(:column_name).collect(&:id)
@result = Message.order("created_at DESC").where(:id => ids)

Upvotes: 7

AdamT
AdamT

Reputation: 6485

Something like this?

Project.select(:group).map(&:group).uniq.each do |grp|
  puts Project.where(group: grp).order("date DESC").last
end

This will go through all your groups and identify the unique ones. In your example it should return ["1", "2"]. Then it iterates over that array and selects the last Project with a group id of 1 and the last Project with a group id of 2.

** Update **

Just realized you said "latest" and not "last" which required adding an order to ensure latest works. Last still pulls just one.

Upvotes: -6

nzajt
nzajt

Reputation: 3

Project.where(:group => "1", :date => "2014-01-01").last

.last is what you are looking for.

Upvotes: -11

Related Questions