具有连接和顺序的不同记录 [英] Distinct Records with joins and order
问题描述
我在User
和Donations
之间具有简单的关系,因为用户有很多捐赠,而捐赠属于用户. 我要做的是获取一个用户列表,按最近的捐款顺序排序.
I have a simple relationship between User
and Donations
in that a user has many donations, and a donation belongs to a user. What I'd like to do is get a list of users, ordered by the most recent donations.
这就是我要尝试的:
首先,我要获取uniq用户总数,该数量正在按预期运行:
First I want to get the total number of uniq users, which is working as expected:
> User.joins(:donations).order('donations.created_at').uniq.count
(3.2ms) SELECT DISTINCT COUNT(DISTINCT "users"."id") FROM "users" INNER JOIN "donations" ON "donations"."user_id" = "users"."id"
=> 384
接下来,当我删除count
方法时,出现错误消息"ORDER BY表达式必须出现在选择列表中":
Next, when I remove the count
method, I get an error that "ORDER BY expressions must appear in select list":
> User.joins(:donations).order('donations.created_at').uniq
User Load (0.9ms) SELECT DISTINCT "users".* FROM "users" INNER JOIN "donations" ON "donations"."user_id" = "users"."id" ORDER BY donations.created_at
PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...ON "donations"."user_id" = "users"."id" ORDER BY donations....
然后我尝试通过显式设置SELECT子句来修复Postgres错误,该子句乍看起来似乎是可行的:
Then I tried fixing the Postgres error by explicitly setting the SELECT clause which at first glance appears to work:
> User.select('DISTINCT "users".id, "users".*, "donations".created_at').joins(:donations).order('donations.created_at')
User Load (17.6ms) SELECT DISTINCT "users".id, "users".*, "donations".created_at FROM "users" INNER JOIN "donations" ON "donations"."user_id" = "users"."id" ORDER BY donations.created_at
但是,返回的记录数未考虑DISTINCT语句,而是返回692条记录:
However, the number of records returned does not take into account the DISTINCT statement and returns 692 records:
> _.size
=> 692
如何获得预期的结果数(384),同时还按捐赠的created_stamp时间戳排序?
推荐答案
尝试一下:
User.select('users.*,MAX(donations.created_at) as most_recent_donation').
joins(:donations).order('most_recent_donation desc').group('users.id')
我想一个用户有很多捐赠,这将选择最近创建的捐赠,并选择按其ID过滤的不同用户.
I suppose an user has many donations, this would select the most recent created donation and would select distinct users filtering by their id.
我还没有测试过.
这篇关于具有连接和顺序的不同记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!