在PostgreSQL中使用.select，.uniq和.order

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

我在模特Organizer和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:

上面的代码在SQLite3中运行良好,但在PostgreSQL中抛出了Heroku的以下错误:

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?

如何使查询在PG中工作,例如使用错误消息中建议的方法?

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

我希望有一个尽可能多的ActiveRecord解决方案,如果可能的话,不是纯SQL。

1 个解决方案

#1

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.

您按updated_at排序,但仅选择organizer_id。 Postgres要求ORDER子句中使用的字段在SELECT子句中。您需要将updated_at添加到选择列表中。

In SQL, right now you have:

在SQL中,您现在拥有:

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;

#1