I have two tables, created thus (only pertinent info shown)
CREATE TABLE levels
id INT(6) UNSIGNED AUTO_INCREMENT,
CREATE TABLE votes
levelid INT(6) NOT NULL REFERENCES levels(id),
date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
Users get to vote on levels (either +1 or -1) and thus creating a votes record that points back to a level.
What I want to do with the data is this, in non-syntax:
SELECT * FROM levels ORDER BY
(select sum(vote) from votes where votes.levelid=levels.id and date is within the last week)
How could I turn that into a legitimate mySQL query? Everything I try gives me an extraordinarily cryptic error message ("Something's wrong! Check the manual!") and I'm only an advanced beginner at mySQL.
The goal is to return all my levels, sorted by whoever has the best votes sum in the last week... that way new levels will make their way on and older ones will fall off.
Thanks in advance!
So here's an example of expected results:
Here's my data (rendered in English for clarity):
+1 for LEVEL1 two days ago
+1 for LEVEL1 ten days ago
+1 for LEVEL1 twenty days ago
+1 for LEVEL2 one day ago
+1 for LEVEL2 three days ago
-1 for LEVEL2 thirty days ago
So I can do a query like this:
SELECT * FROM LEVELS ORDER BY (select sum(vote) from votes where levelid=levels.id)
and that gets me +3 for LEVEL1, and +1 for LEVEL2.
BUT, in the last seven days, I should see +1 for LEVEL1, and +2 for LEVEL2, thus returning LEVEL2 first.
Also, I get the general idea that "order by (select..." would be pretty slow.
One more edit:
This query works:
SELECT * FROM levels ORDER BY (SELECT SUM(vote) FROM votes WHERE levelid=levels.id AND votes.date>=( CURDATE() - INTERVAL 7 DAY )) DESC
...but is that the best way to do this sort of thing?
You can do this with a left join:
SELECT levels.*, coalesce(sum(votes.vote),0) as total_vote FROM levels LEFT JOIN votes ON votes.levelid = levels.id WHERE votes.date >=( CURDATE() - INTERVAL 7 DAY )) GROUP BY levels.id ORDER BY coalesce(sum(votes.vote),0) desc;
The coalesces here ensure that levels that haven't been voted on get a score (instead of null) and are sorted correctly.