Esta consulta é válida e retorna os resultados esperados:
$arr = [7,1];//dynamic
$vids = explode(',',$arr);
$in = implode(',', array_fill(0, count($ids), '%d'));
$stmt = $wpdb->prepare("
SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count
FROM $media_table as mt
LEFT JOIN $comments_table ct ON ct.media_id = mt.id
LEFT JOIN $like_table lt ON lt.media_id = mt.id
WHERE mt.id IN ($in)
GROUP BY mt.id
ORDER BY $sortOrder $sortDirection", $vids);
Se eu print_r($stmt)obtiver:
"SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count FROM wp_mvp_reel_media as mt LEFT JOIN wp_mvp_reel_comments ct ON ct.media_id = mt.id LEFT JOIN wp_mvp_reel_like lt ON lt.media_id = mt.id WHERE mt.id IN (7,1) GROUP BY mt.id ORDER BY order_id ASC"
Quando adiciono MAX(lt.user_id = %d) AS user_liked)- a consulta falha.
Se I print_r($stmt), uma string vazia será retornada "":
$stmt = $wpdb->prepare("
SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count, MAX(lt.user_id = %d) AS user_liked
FROM $media_table as mt
LEFT JOIN $comments_table ct ON ct.media_id = mt.id
LEFT JOIN $like_table lt ON lt.media_id = mt.id
WHERE mt.id IN ($in)
GROUP BY mt.id
ORDER BY $sortOrder $sortDirection", $user_id, $vids);
