I have two tables for storing comments
CREATE TABLE `comments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `text` text NOT NULL, `author` int(10) unsigned NOT NULL, `time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
CREATE TABLE `comments_ratings` ( `comment` int(10) unsigned NOT NULL, `user` int(10) unsigned NOT NULL, `vote` tinyint(4) NOT NULL, PRIMARY KEY (`comment`,`user`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
The goal is to select comments along with rating and information about the author as well as whether the logged in user has voted or not and if yes their vote's value.
SELECT c.id, c.text, c.time, c.author, u.email AS author_name, SUM(cr.vote) AS rating, MAX(if(cr.user = ?, cr.vote, NULL)) AS voted FROM comments c LEFT JOIN users u ON u.id = c.author LEFT JOIN comments_ratings cr ON c.id = cr.comment GROUP BY c.id ORDER BY c.id DESC
I used to do a second join on
comments_ratings in order to get the vote of the logged in user but I recently switched to this thing that I'm not fully sure on to how it works, I just have my guesses. I wrote it with the help of another question I asked and some luck.
Ultimately what I want to ask is, are there any possible issues I can run into with
MAX(if(cr.user = ?, cr.vote, NULL)) AS voted
And I'm asking performance-wise. I know it may not be very easy to read but all I care about now is performance. Is this alright or I should definitely switch back to an additional join on
LEFT JOIN comments_ratings cr2 ON c.id = cr2.comment AND c.author = cr2.user to get the value of