0

I have a User model and an Item model. I want to rank users according to the value of the items they have. I want to do the equivalent of this query:

SELECT rank() OVER (ORDER BY grand_total DESC), u.*, grand_total
FROM users AS u
JOIN
(SELECT user_id, SUM(amount) AS grand_total FROM items WHERE EXTRACT(YEAR FROM sold_at)='2012' GROUP BY user_id) AS i
ON u.id = i.user_id;

Specifically, I don't know how to join on my select.

phaedryx
  • 2,024
  • 3
  • 16
  • 24

1 Answers1

1

Given the problem as you describe it, I would write the query thus:

select users.*, sum(items.amount) as rank
  from users
  join items on items.user_id = users.id
  group by users.id
  order by rank desc;

Which would translate into AREL as:

User.select('users.*, sum(items.amount) as rank').joins('join items on items.user_id = users.id').group('users.id').order('rank desc')

This has the handy side-effect that you can call .rank on the resulting User objects and get the value of the rank column from the query, in case you need to display it.

Is there something about your situation I'm not grasping here, or would this work?

MrTheWalrus
  • 9,670
  • 2
  • 42
  • 66
  • I need both a numerical ranking and a total for my purposes, so I can't just combine them into one column as you've done. Also, I realized I left off the part where I have to restrict it to a certain year, which I added after your answer. However, I really appreciate your answer, I got some good information about how I was doing my arel join clauses incorrectly. – phaedryx Sep 21 '12 at 20:48