CODEWITHSUNDEEP
ruby-on-railspostgresql
Jareish
Jareish

Reputation: 782

Get the latest records of a has_many relation

I'm trying to create a query in postgres using (rails) activerecord, but can't seem to solve it.

A Company has many pages, and I'm trying to get the latest page of all companies.

I tried

Page.select('DISTINCT company_id')

which gives me 1 page of all companies. But this isn't the latest (created_at ASC)

So I tried

Page.select('DISTINCT company_id').order('created_at ASC')

which gives an error since created at isn't in the select. But if I add it, I get all records returned.

So I tried

Page.group('company_id').select('company_id').order('created_at ASC')

But this gives an error, that created_at should be in group by or aggregator function.

adding created_at to the group by, gives unwanted result (multiple pages, of the same company)

Any help?

edit:

I like it to be a scope, I need to chain the scopes later on.

Upvotes: 4

Views: 1364

Answers (2)

Mischa
Mischa

Reputation: 43298

You can use pluck and uniq:

Page.order('created_at ASC').pluck(:company_id).uniq

You could try this to get an ActiveRecord::Relation:

Page.select('max(id) as id, company_id').group(:company_id)

I would use id to determine the "recentness" of a page instead of created_at. The higher the id the more recent the page is. You can simply get the highest page id per company_id with the code above.

Upvotes: 3

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

Order by created_at (descending) and limit 1.

Upvotes: -4

Related Questions