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, ctment, 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 = vtment_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.

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, ctment, 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 = vtment_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.

• mysql
• variables
• select

• There is, though it mostly depends on the data you want as a result, because you need vote and comments you can just join both tables, get the data and process it in code, because the same user can have more than one comment per vote as your data sample shows. The join is pretty straightforward. I always refer this site for mysql tutorials, it is pretty great: mysqltutorial./mysql-basics/mysql-join – Je Campos Commented 9 hours ago
• What are ct.user_display_name, ct.avatar? They're in your query, but not shown in the table schema. Why are these even in the comments table at all, they should be in the users table. Can a user have different display names and avatars on different comments? – Barmar Commented 8 hours ago
• SUM(DISTINCT vt.vote) doesn't seem right. If 10 people vote 1, and 2 people vote 2, this sum will be 3, not 14. Why do you have DISTINCT there? – Barmar Commented 8 hours ago

1 Answer 1

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, ctment, 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
    FROM $comments_table as ct 
    LEFT JOIN $votes_table vt on ct.id = vtment_id 
    WHERE ct.media_id=%d 
    GROUP BY ct.id
    ORDER BY ct.c_date DESC", $user_id, $media_id), ARRAY_A);