具有连接和顺序的不同记录 [英] 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屋！