Best way to generate sums of groups

Rails
1

I’m a rails newby, so I’m trying to wrap my head around how to tackle
this, since my first few attempts failed.

Let’s say your database looks like this:

Users
id, name, created_at

Points
id, user_id, value, created_at

Users complete different tasks and are awarded varying levels of points,
which are recorded in the Points table. What if you wanted to display a
“high score list”, showing the users in order by who had the most
points? It might look like:

Username A - 20 points
Username B - 14 points
Username C - 13 points
and so on…

You’d need to sum the values for each user_id, then display them in
order. And, based on the user_ids, you’d need to pull in and display
the users’ names from the Users table.

I’ve been attempting to do this using various incarnations of
Point.sum(:values, :group => ‘user_id’), but without luck. Using that
approach, I can display the point totals for each user_id, but the thing
breaks down when I try to bring in the names from the Users table.

Any help would be hugely appreciated!

2

Hey,
The problem you are running in to is more of a SQL problem than
it
is rails. Meaning, once you figure out the query to get the result you
want,
its a simple matter of using rails to run it.

In you case, the mysql query will look something like this:

SELECT user_id, SUM(value) AS total FROM points GROUP BY user_id ORDER
BY
total DESC

that query will generate something like:
user_id total
1 100
2 50
3 45

where the second column is the total points associated with each
user_id.
Now if you want to get the user details not just the id of the user, you
will have to do a join.

SELECT u.*, SUM(p.value) AS total FROM users u, points p WHERE
u.id=p.user_id GROUP BY p.user_id ORDER BY total DESC

If I am understanding you correctly, that is the query you want. It will
produce something like
id name created_at total
1 john mar-3-06 100
2 sera jun-6-96 50
3 etc.

Now that you know what the query looks like, you can make it work in
rails
in various ways. The easiest one probably is to use find_by_sql.

User.find_by_sql "SELECT u.*, SUM(p.value) AS total " +
"FROM users u, points p " +
"WHERE u.id=p.user_id " +
"GROUP BY p.user_id " +
“ORDER BY total DESC”

You could always go another route and choose to break down that big
query in
to parts by utilizing the :join, :order options of the find method.

I hope that helped. If you were confused by how to construct the query,
I
highly recommend reading up on SQL. I guarentee that you will find it
useful.

~Rohith

3

You could always go another route and choose to break down that big
query in
to parts by utilizing the :join, :order options of the find method.

That is extremely helpful, thanks!

I understand the SQL query, I guess I was trying to avoid using
find_by_sql because people keep telling me that there are easier and/or
better ways to do this in rails. But it looks here like find_by_sql is
pretty easy too.

Could someone show me what this query would look like if done in rails,
using the find method and :join and :order?

4

User.find_by_sql "SELECT u.*, SUM(p.value) AS total " +
highly recommend reading up on SQL. I guarentee that you will find it useful

i agree
also see:
http://rubyonrails.org/api/classes/ActiveRecord/Calculations/ClassMethods.html

for stuff you can do without needing to dive into SQL…