Forum: Ruby on Rails I can't figure out how to solve this one

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Bill P. (Guest)
on 2006-01-19 20:09
(Received via mailing list)
I am creating an application to run a fishing tournament (see http://
www.ruby-forum.com/topic/51209 for a little background)

Now I am running into an issue trying to calculate a leader board.
each participate can enter multiple fish but only the largest fish
per a given species counts towards the overall score. My entries
table looks like this:

+-------------+--------------+------+-----+---------------------
+----------------+
| Field       | Type         | Null | Key | Default             |
Extra          |
+-------------+--------------+------+-----+---------------------
+----------------+
| id          | int(11)      |      | PRI | NULL                |
auto_increment |
| species_id  | int(11)      |      |     | 0
|                |
| location_id | int(11)      |      |     | 0
|                |
| caught_on   | datetime     |      |     | 0000-00-00 00:00:00
|                |
| user_id     | int(11)      |      |     | 0
|                |
| length      | int(3)       |      |     | 0
|                |
| image       | varchar(100) | YES  |     | NULL
|                |
| created_at  | datetime     | YES  |     | NULL
|                |
| updated_on  | datetime     | YES  |     | NULL
|                |
+-------------+--------------+------+-----+---------------------
+----------------+

The species table holds the multiplier for a given species so when I
want to see the Top 10 for a given species I can do this:

select entries.id, species.name, length,species.multiplier*length as
score,caught_on, (select login from users where id = entries.user_id)
as Angler, image  from entries join species on species_id =
species.id where species_id = ? order by score DESC LIMIT 10

If users are only allowed to enter one fish per species then I can
just go count all there fish and it makes it simple. This is an
option FYI but not one I would like to use. However if users are
allowed to enter more than one fish per species then things get hard
(for me at least). I would like to use the MySQL MAX() function but
it appears to be a little to broad for this purpose. I can only get
the single biggest fish per species or per user not per user AND
species. I am pretty sure I could do this with a temp table but since
this is going to be hit all the time it seems silly to do that.

After typing all that I think I am realizing I should just limit them
to one entry per fish :-)

TIA!


- Bill
John D. (Guest)
on 2006-01-19 20:34
(Received via mailing list)
Bill P. wrote:
> | Field       | Type         | Null | Key | Default             |
> |                |
> +-------------+--------------+------+-----+---------------------+----------------+
> If users are only allowed to enter one fish per species then I can just
> one entry per fish :-)
SQL 'group by' is your friend.  Aggregate functions (min, max, sum, etc)
work hand-in-hand with group by.

http://dev.mysql.com/doc/refman/5.0/en/group-by-fu...

How about:

select max(length) as length from entries group by species, user_id
order by species, length desc

John
John D. (Guest)
on 2006-01-19 20:43
(Received via mailing list)
John D. wrote:

> SQL 'group by' is your friend.  Aggregate functions (min, max, sum, etc)
> work hand-in-hand with group by.
>
> http://dev.mysql.com/doc/refman/5.0/en/group-by-fu...
>
> How about:
>
> select max(length) as length from entries group by species, user_id
> order by species, length desc

Well, I can't type...still untested (forgot columns in select):

select species, user_id, max(length) as length from entries group by
species, user_id order by species, length desc

John
Bill P. (Guest)
on 2006-01-19 21:43
(Received via mailing list)
Thanks John I tweaked the query a bit to this:

select species.name, user_id, max(length*species.multiplier) as score
from entries join species on species_id = species.id group by
species_id, user_id order by species_id, score desc;

Which returns:

+---------+---------+-------+
| name    | user_id | score |
+---------+---------+-------+
| Cabezon |       3 |   182 |
| Salmon  |       2 |   225 |
| Salmon  |       3 |   140 |
| Salmon  |       4 |   135 |
| Striper |       4 |   165 |
| Striper |       3 |   120 |
| Striper |       2 |   115 |
+---------+---------+-------+

This is closer but I need to aggregate the users score now. I tried
to SUM(max(length*species.multiplier)) but I get a grouping error.

ERROR 1111 (HY000): Invalid use of group function



On Jan 19, 2006, at 10:02 AM, John D. wrote:

>> select max(length) as length from entries group by species, user_id
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>



- Bill
Bill P. (Guest)
on 2006-01-19 21:58
(Received via mailing list)
Sorry just to follow up this query produces what I what but assumes
that there is only one of each type of species.

select (select users.login from users where users.id =
entries.user_id) as Angler, SUM(length*species.multiplier) as Score
from entries join species on entries.species_id = species.id group by
user_id order by Score DESC LIMIT 10;

+--------+-------+
| Angler | Score |
+--------+-------+
| Mooch  |   442 |
| bill   |   340 |
| Stuart |   300 |
+--------+-------+


On Jan 19, 2006, at 11:04 AM, Bill P. wrote:

> +---------+---------+-------+
> to SUM(max(length*species.multiplier)) but I get a grouping error.
>>> sum, etc)
>>
>
>
> - Bill
>
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>



- Bill
John D. (Guest)
on 2006-01-19 22:04
(Received via mailing list)
Bill P. wrote:
> +---------+---------+-------+
> SUM(max(length*species.multiplier)) but I get a grouping error.
>
> ERROR 1111 (HY000): Invalid use of group function

Ok...you really don't want to use max at all. With the group by, the sum
will only sum the rows for the group by criteria (user and species).
So, this should give you what you want:

select
  species.name,
  user_id,
  sum(length*species.multiplier) as score
from entries
  join species on species_id = species.id
group by
  species_id, user_id
order by
  species_id, score desc;
Bill P. (Guest)
on 2006-01-19 22:52
(Received via mailing list)
Thanks for all your help John, I really appreciate it. That new query
gives me the same results as the old query. I think I have to use MAX
() or something like it so I only retrieve the highest scored fish
per species. This query gives me exactly what I want but again
assumes the user is only able to enter one fish per species:

select (select users.login from users where users.id =
entries.user_id) as Angler, SUM(length*species.multiplier) as Score
from entries join species on entries.species_id = species.id group by
user_id order by Score DESC LIMIT 10;

+--------+-------+
| Angler | Score |
+--------+-------+
| Mooch  |   442 |
| bill   |   340 |
| Stuart |   300 |
+--------+-------+

It is looking like overwriting (or just selecting the latest entry
per species) is the easiest way to go.



On Jan 19, 2006, at 11:24 AM, John D. wrote:

>> | name    | user_id | score |
>> This is closer but I need to aggregate the users score now. I
> select
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>



- Bill
John D. (Guest)
on 2006-01-20 01:00
(Received via mailing list)
Bill P. wrote:
> Thanks for all your help John, I really appreciate it. That new query

Sure, although it sounds like this isn't solved yet :-)

> gives me the same results as the old query. I think I have to use MAX()
> or something like it so I only retrieve the highest scored fish per
> species. This query gives me exactly what I want but again assumes the
> user is only able to enter one fish per species:

Hmm...If you want the leaderboard for the case where only 1 fish per
species is allowed, do the max.  If you want the leaderboard for the
case where more than 1 fish per species is allowed do the sum.

So, lets say you add a flag column in the species table that says
one_fish_only = TRUE, then you can create an if statement in your select
that says if one_fish_only = 1, use the max, else use the sum.

select
  species.name,
  user_id,
  IF(species.one_fish_only=1, max(length*species.multiplier),
                              sum(length*species.multiplier)) as score,
from entries
  join species on species_id = species.id
group by
  species_id, user_id
order by
  species_id, score desc;

I didn't try this so there is probably a syntax error, but I _think_
that would give you what you want :-)

John
Bill P. (Guest)
on 2006-01-20 01:39
(Received via mailing list)
Ahh! of course I could just let the user choose what fish they want
to enter and just default it to the last one they add or write a
helper to check to see if it is bigger than the previous and flag it
accordingly. Then I can just add "is_entered = `1`" to my where clause.

I think I see a little light...

Let me go try this, gives me a good excuse to write my first
migration as well :-)


On Jan 19, 2006, at 2:22 PM, John D. wrote:

>> user is only able to enter one fish per species:
> select
>   species_id, score desc;
>
> I didn't try this so there is probably a syntax error, but I _think_
> that would give you what you want :-)
>
> John
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>



- Bill
This topic is locked and can not be replied to.