How to order by an average value derived from two fields...

brandonr

Ok this is bound to be a long question but it seems pretty simple at heart
so please stick through this long explanation....

I have a file script, and each file is rate-a-ble by the users. I have two
mySQL tables, one for the file listings, and one for the ratings. They look
something like this:

Files
id, file, etc.

Ratings
id, fileid, rating, comment, etc.

Obviously the ratings "fileid" field correlates to the file's id... Anyways,
the ratings field contains a numeric rating unless they are not actually
rating the file - if they are only commenting on the file, the rating field
is a 0. Simple enough...

So now I want to print a list of the files out in a table, and on the table
have a rating column that shows the file's ratings... To do this, I did this
query:

============
SELECT
COUNT(games_ratings.id) AS FIELD_1,
SUM(games_ratings.rating) AS FIELD_2
FROM
games_ratings
WHERE

(`games_ratings`.`rating` <> 0)

And then I take field_2 / field_1, round, and voila there's the rating. Now
for the question. This is fine and dandy if I'm sorting the files by id or
whatever, but is it possible to sort the files by their rating, even though
the rating is not a field anywhere in the tables (rather a derived value)?
What would that possible query be? If any more elaboration is needed, please
ask for it.... Thanks a ton!

drawmack

============
SELECT
files.id,
files.name,
COUNT(games_ratings.id) AS FIELD_1,
SUM(games_ratings.rating) AS FIELD_2
ROUND((FIELD_1/FIELD2),2) AS FIELD_3
FROM
files,games_ratings
WHERE
(games_ratings.rating <> 0)
game_ratings.fileid = files.id
GROUP BY
game_ratings.fileid
ORDER BY

FIELD_3

That should do it, but this code is untested.

brandonr

Ahh that does seem like it will work once I get the little bugs worked out... Here's the query I have when I edit in my table names and stuff:

SELECT games_files.id,
games_files.trackname,
COUNT( games_ratings.id ) AS FIELD1,
SUM( games_ratings.rating ) AS FIELD2,
ROUND( ( FIELD1 / FIELD2 ), 2) AS FIELD3
FROM games_files, games_ratings
WHERE ( games_ratings.rating <>0 ) AND game_ratings.trackid = games_files.id
GROUP BY game_ratings.trackid
ORDER BY FIELD3

mySQL gives me this error:
#1054 - Unknown column 'FIELD1' in 'field list'

Odd since field1 is declared right at the start eh?

drawmack

it's bad form to make the server do the same math twice but it seems it may be necessary try this

SELECT 
    a.id, a.trackname, 
    COUNT( b.id ) AS FIELD1, 
   SUM( b.rating ) AS FIELD2, 
   ROUND( ((COUNT(b.id)/SUM(b.rating) ), 2) AS FIELD3
FROM games_files as a, games_ratings as b
WHERE ( b.rating <>0 ) AND b.trackid = a.id
GROUP  BY b.trackid
ORDER  BY FIELD3

Sxooter

Actually, drawmack, most query planners can figure out the repetition and only run the aggregate or math once (I know PostgreSQL has done this since something like version 7.2 or so).

Plus, since the where clause fires BEFORE the group by or select list, it HAS to have the same math all over again, because it hasn't been done yet. So, the optimization required for the planner is pretty simple, since a simple replacement tells it that the value from the where clause is exactly what it already needs.

brandonr

So close, yet so far away... 🙂 After some major reworking I ALMOST got some decent code, as of now, a little recap:

Two tables: games_files - houses the file information for each file in the database.

games_ratings - houses ratings for files (has a "rating" column, which is either a numeric value, or a 0 if the rating is just a comment with no numeric rating)

I came up with this query to get all the information about a file, and then averages the games_ratings.rating column (where the ratings column isn't equal to 0, don't want to factor the simple comments into the average):

SELECT games_files.id, games_files.trackname, AVG(games_ratings.rating) AS file_average
FROM games_files, games_ratings
WHERE games_files.id=games_ratings.trackid AND games_ratings.rating<>0 
GROUP BY games_files.id
ORDER BY games_files.id DESC LIMIT 10

Which would show the last 10 files submitted to the database... It works great EXCEPT for one major problem - files with no ratings do not show up at all. Any ideas how to alter the query to let files without any ratings show up as well?

I know it's the games_files.id=games_ratings.trackid argument that's preventing the non-rated files from showing up, but without it it just gives the average value for all the ratings in the database.... Help? :queasy:

Roger_Ramjet

Look up JOINS in the manual for the syntax and explanation. http://dev.mysql.com/doc/mysql/en/JOIN.html What you need is a LEFT JOIN.

brandonr

Thanks for that information, I got the following query to work:

SELECT games_files.id, games_files.trackname, games_files.screenshot, games_files.user, games_files.time, AVG(games_ratings.rating) AS average_rating
FROM games_files
LEFT JOIN games_ratings ON games_files.id=games_ratings.trackid AND games_ratings.rating<>'0' AND games_files.type = '4' AND games_files.contestid=''
GROUP BY games_files.id
ORDER BY games_files.id DESC LIMIT 10

But it seems to make the page load extremely slow... Is there a way to tell how efficient it is (or could using the microtime to time the php script execution tell that?)?

Roger_Ramjet

Read this http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html and particularly this http://dev.mysql.com/doc/mysql/en/Query_Speed.html to learn about optimising your queries.

Use EXPLAIN to find out what your query is actually doing http://dev.mysql.com/doc/mysql/en/EXPLAIN.html.

Take all the AND conditions out of the JOIN statement and put them in a WHERE clause where they belong. They do not belong in the JOIN clause because they have nothing to do with the join.