Distinct Records with joins and order
join, postgresql, ruby-on-rails, ruby-on-rails-4, sql-order-by
Solution
Try this:
User.select('users.*,MAX(donations.created_at) as most_recent_donation').
joins(:donations).order('most_recent_donation desc').group('users.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.
I have not tested this though.
Problem
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. Here's what I'm trying: 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 ``` 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.... ``` 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 ``` However, the number of records returned does not take into account the DISTINCT statement and returns 692 records: ``` > _.size => 692 ``` How do I get the expected number of results (384) while also sorting by the donation's created_at timestamp?