Separate entries with joins and order

Question

I have a simple connection between User and Donations that the user has a lot of donations, and the donation belongs to the user. What I would like to do is get a list of users ordered with the latest donations.

Here is what I am trying:

First, I want to get the total number of uniq users, which works 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 

Then, when I delete the count method, I get an error message that should look like “ORDER BY expressions in the 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.... 

Then I tried to fix the Postgres error by explicitly setting the SELECT clause, which, at first glance, works:

  > 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 

However, the number of records returned does not account for the DISTINCT statement and returns 692 records:

 > _.size => 692 

How to get the expected number of results (384), and also sort by created_at timestamp creation date?

Answer 1

Try the following:

 User.select('users.*,MAX(donations.created_at) as most_recent_donation'). joins(:donations).order('most_recent_donation desc').group('users.id') 

I believe that the user has a lot of donations, this will allow you to select the most recently created donation and select a separate filtering of users by their identifier.

I have not tested this though.