Best way to generate sums of groups

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!

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

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?

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…

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs