diff options
| -rw-r--r-- | sql/new.sql | 18 | ||||
| -rw-r--r-- | src/database.rs | 6 |
2 files changed, 21 insertions, 3 deletions
diff --git a/sql/new.sql b/sql/new.sql new file mode 100644 index 0000000..57d918b --- /dev/null +++ b/sql/new.sql @@ -0,0 +1,18 @@ +WITH users AS ( + SELECT * + FROM ( VALUES ('dennis'), ('caitriao') ) AS _ (user_id) + ) + SELECT track_id, name, artist + FROM track + JOIN ( + SELECT track_id + FROM user_track + JOIN suser USING (user_id) + JOIN track USING (track_id) + JOIN lobby USING (lobby_id) + WHERE suser.user_name IN (SELECT user_name FROM users) AND token = 'shnPcxQ7REwF3QL1T65mcqPtf3k=' + GROUP BY track_id + HAVING COUNT(track_id) = (SELECT COUNT(*) FROM users) + ORDER BY SUM(score) DESC + ) AS _ USING (track_id) + ; diff --git a/src/database.rs b/src/database.rs index a5204a1..7b548de 100644 --- a/src/database.rs +++ b/src/database.rs @@ -156,9 +156,9 @@ pub async fn match_users(lobby: String, names: &[&str]) -> Result<String, Error> JOIN suser USING (user_id) JOIN track USING (track_id) JOIN lobby USING (lobby_id) - WHERE suser.user_name IN (SELECT user_name FROM users) AND token = $1 + WHERE suser.user_name IN (SELECT * FROM users) AND token = $1 GROUP BY track_id - ---HAVING COUNT(track_id) = (SELECT COUNT(*) FROM users) + HAVING COUNT(track_id) = (SELECT COUNT(*) FROM users) ORDER BY SUM(score) DESC ) AS _ USING (track_id) ; @@ -172,7 +172,7 @@ pub async fn match_users(lobby: String, names: &[&str]) -> Result<String, Error> use futures::{pin_mut, TryStreamExt}; let mut it = client.query_raw(query.as_str(), values).await?; pin_mut!(it); - for row in it.try_next().await? { + while let Some(row) = it.try_next().await? { let name: String = row.get(1); let artist: String = row.get(2); songs = format!("{}{} by {}\n", songs, name, artist); |