mysqlperformancevariablesselect

mysql select and set variable in one query


I have 2 tables:

comments_table

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

votes_table

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.


Solution

  • 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);