CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Fellow Stranger
Fellow Stranger

Reputation: 34013

Using .select, .uniq and .order in PostgreSQL

I have a one-to-many relationship between the models Organizer and Event.

I want to get the five organizers that most recently has updated an event.

    Event.order('updated_at asc').
    where(organizer_id: [1,2,3,4,5,6,7,8,9]).
    select(:organizer_id).uniq.limit(5)

The above code works good in SQLite3 but throws the follwoing error on Heroku in PostgreSQL:

for SELECT DISTINCT, ORDER BY expressions must appear in select list

How do I make the query work in PG, e.g. with the method suggested in the error message?

I would prefer to have a solution that is as much ActiveRecord as possible, not pure SQL, if possible.

Upvotes: 1

Views: 339

Answers (1)

Chris Heald
Chris Heald

Reputation: 62648

You are ordering by updated_at, but are selecting only organizer_id. Postgres requires that fields used in ORDER clauses be in the SELECT clause. You need to add updated_at to your select list.

In SQL, right now you have:

SELECT organizer_id FROM events WHERE ... ORDER BY updated_at asc;

You need:

SELECT organizer_id, updated_at FROM events WHERE ... ORDER BY updated_at asc;

Upvotes: 3

Related Questions