CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3arel
phaedryx
phaedryx

Reputation: 2054

How would I do this query with Arel?

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.

Upvotes: 0

Views: 221

Answers (1)

MrTheWalrus
MrTheWalrus

Reputation: 9700

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?

Upvotes: 1

Related Questions