I have 2 tables:
id int(11) unsigned NOT NULL AUTO_INCREMENT,
media_id int(11) unsigned DEFAULT NULL,
user_id int(11) unsigned DEFAULT NULL,//user who commented
title varchar(300) DEFAULT NULL,
url varchar(300) DEFAULT NULL,
c_date datetime,
comment longtext DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `media_id` (`media_id`)
id | comment | user_id |
---|---|---|
1 | foo | 1 |
2 | bla | 2 |
3 | something | 7 |
comment_id int(11) unsigned DEFAULT NULL,
user_id int(11) unsigned DEFAULT NULL,//user who voted
vote tinyint(1) DEFAULT 0,
INDEX `comment_id` (`comment_id`)
comment_id | user_id | vote |
---|---|---|
1 | 2 | 1 |
2 | 4 | -1 |
3 | 7 | 0 |
3 | 1 | 1 |
2 | 1 | 1 |
I have 2 queries (get all comments):
$comments = $wpdb->get_results($wpdb->prepare("SELECT ct.id, ct.comment, ct.user_id, ct.user_display_name, ct.avatar, ct.c_date, SUM(DISTINCT vt.vote) AS vote
FROM $comments_table as ct
LEFT JOIN $votes_table vt on ct.id = vt.comment_id
WHERE ct.media_id=%d
GROUP BY ct.id
ORDER BY ct.c_date DESC", $media_id), ARRAY_A);
Get comments where user has voted:
$user_votes = $wpdb->get_results($wpdb->prepare("SELECT vote, comment_id
FROM $votes_table
WHERE user_id=%d", $user_id), ARRAY_A);
This both works as expected. Is there to do this in one query? (for performance reasons)
For example add to results user_vote = votes_table.vote to a comment_table.id votes_table.user_id voted.
Use MAX(vt.user_id = %d)
to determine if any of the votes_table
rows are from the specified user. The value of a comparison is either 1 for TRUE or 0 for FALSE, so MAX()
will determine this existence.
$comments = $wpdb->get_results($wpdb->prepare("
SELECT ct.id, ct.comment, ct.user_id, ct.user_display_name, ct.avatar, ct.c_date,
SUM(vt.vote) AS vote, MAX(vt.user_id = %d) AS user_voted,
MAX(CASE WHEN vt.user_id = %d THEN vote END) AS user_vote
FROM $comments_table as ct
LEFT JOIN $votes_table vt on ct.id = vt.comment_id
WHERE ct.media_id=%d
GROUP BY ct.id
ORDER BY ct.c_date DESC", $user_id, $user_id, $media_id), ARRAY_A);