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
On 6/22/06, JD <jamesd@pirg.org> wrote:>
> 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!
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060622/471873e7/attachment-0001.html