How to order by, sum, and join using ActiveRecord

Question

I have two tables:

   USERS    

id     name 
------------
1.    bill  
2.    dave  
3.    kate  
4.    sara

and

GAMES
score    user_id
------------------
10       1
 0       1
 0       2
10       3
 0       2
10       3
 0       3
 0       4

This query:

SELECT users.name, SUM(games.score) AS total FROM users JOIN games 
ON users.id = games.user_id ORDER BY total LIMIT 2;

produces output like this:

name    total
-------------
kate     20
bill     10

What is the correct Rails convention here for ActiveRecord to perform an ORDER BY with SUM and JOIN?

What do your models look like? Have you tried anything? You should start here: [Rails `ActiveRecord` Basics](https://guides.rubyonrails.org/active_record_basics.html) and [`ActiveRecord` Querying](https://guides.rubyonrails.org/active_record_querying.html). — lurker, May 07 '20 at 00:31
Welcome to SO. Please see "[ask]", "[Stack Overflow question checklist](https://meta.stackoverflow.com/questions/260648)" and all their linked pages. Your question isn't asked well. Formatting and grammar do matter on SO, because this site is more like an online reference book of programming Q&A than a forum or message list. On SO it really is important that you put in the effort if you want help in return because we're writing a book as a community. — the Tin Man, May 07 '20 at 04:11

score 0 · Answer 1 · answered May 07 '20 at 03:23

You're wanting to select the sum of scores of total games from each user...

You don't always have to copy the sql syntax over..just think about how to do it in rails with their query methods..

# Creates an array of all of the records that exist under the user_id of the
# current user being looped and adds all of those records together, you can
# handle whatever you want to do with that data here.  Say put it in an associate # table, or run some logic on it.  This is the total amount

Users.all.each do |user|
 total = Games.where(user_id: user.id).inject(0, :+)
 logger.info "User: #{user.id}"
 logger.info "Total: #{total}"
end

How to order by, sum, and join using ActiveRecord

1 Answers1