php - 按用户角色过滤 WordPress 评论

Question

尝试按特定用户角色过滤我的所有评论。在本例中，用户角色称为 group1。仅当我删除 WHERE m.meta_key = 'wp_capability' AND m.meta_value = 'group1' 时，才会返回所有评论来自下面的查询。

$query = $wpdb->prepare( "
SELECT *
  FROM $wpdb->comments c
  LEFT 
  JOIN $wpdb->commentmeta cm 
    ON c.comment_ID = cm.comment_id
  LEFT 
  JOIN $wpdb->users u 
    ON c.user_id = u.ID
  LEFT 
  JOIN $wpdb->usermeta m 
    ON m.user_id = u.ID
 WHERE c.comment_post_ID = %s
   AND c.comment_approved = 1
 WHERE m.meta_key = 'wp_capabilities'
   AND m.meta_value = 'group1'
 ORDER 
    BY c.comment_ID $comment_order
                    ",
                    $post->ID);

Answer 1

您有两个 WHERE 子句，但即使修复了语法错误，它也会作为 inner join 作为 where 子句中外部表的过滤条件将外连接转变为内连接。

在 LEFT JOIN 的 ON 子句本身中添加外部表的过滤器:

select *
from $wpdb - > comments c
left join $wpdb - > commentmeta cm on c.comment_ID = cm.comment_id
left join $wpdb - > users u on c.user_id = u.ID
left join $wpdb - > usermeta m on m.user_id = u.ID
    and m.meta_key = 'wp_capabilities'
    and m.meta_value = 'group1'
where c.comment_post_ID = % s
    and c.comment_approved = 1
order by c.comment_ID $comment_order