I’m building a private comments plugin, whereby comments can only be viewed by the comment author and the commenter. I’m doing this by making the comment_type = private, recording the sender and recipient as metadata.
I’m limiting the displayed results using code found
here
The code below let’s me limit results by one metakey => metavalue, but not two. Can anyone help me adapt it?
I.e. I want to retrieve all comments WHERE ( (metakey = ‘sender’ AND metavalue = ‘$current_user’) OR (metakey = ‘recipient’ AND metavalue = ‘$current_user’) )
/**
* Limits displayed comments on front and backend to just those with the
* specified meta key/value pair.
*
* @param array $pieces
* @param WP_Comment_Query $query
* @return array
*/
add_action('comments_clauses', function(array $pieces, WP_Comment_Query $query) {
global $wpdb;
$meta_query = new WP_Meta_Query();
$meta_query->parse_query_vars([
'meta_key' => 'recipient',
'meta_value' => $current_user,
]);
if ( !empty($meta_query->queries) )
{
$meta_query_clauses = $meta_query->get_sql( 'comment', $wpdb->comments, 'comment_ID', $query );
if ( !empty($meta_query_clauses) )
{
$pieces['join'] .= $meta_query_clauses['join'];
if ( $pieces['where'] )
$pieces['where'] .= ' AND ';
// Strip leading 'AND'.
$pieces['where'] .= preg_replace( '/^\s*AND\s*/', '', $meta_query_clauses['where'] );
if ( !$query->query_vars['count'] )
{
$pieces['groupby'] = "{$wpdb->comments}.comment_ID";
}
}
}
return $pieces;
}, 10, 2);
The topic ‘Help with SQL to limit comments by meta’ is closed to new replies.
(@granrojo)
10 years, 6 months ago
I’m building a private comments plugin, whereby comments can only be viewed by the comment author and the commenter. I’m doing this by making the comment_type = private, recording the sender and recipient as metadata.
I’m limiting the displayed results using code found
here
The code below let’s me limit results by one metakey => metavalue, but not two. Can anyone help me adapt it?
I.e. I want to retrieve all comments WHERE ( (metakey = ‘sender’ AND metavalue = ‘$current_user’) OR (metakey = ‘recipient’ AND metavalue = ‘$current_user’) )