Querying sanatized data with meta_query in WordPress

2 Views

After Googeling this for a while, I’ve found that this should work ref., but I don’t seem to get it running.

On the backend I have a custom field on commments for setting a list of userIDs of who liked it. It is stored sanatized in the DB this way:

Read More
a:2:{i:0;s:1:"1";i:1;s:1:"2";}

which translates into 

Array (
  [0] => 1
  [1] => 2
)

A pretty straight forward flat array where the values 1 and 2 are the user IDs of two likes. Now, I want to query comments liked by userID 1 

$args = array(
    'status' => 'approve',
    'post_status' => 'publish',
    'meta_query' => array(
        array(
            'key' => 'liked_by',
            'value' => array(1),
            'compare' => 'IN',
            'type' => 'numeric'
        )
    )
);
// The Query
$comments_query = new WP_Comment_Query;
$comments = $comments_query->query( $args );

And I get no comments in return, why is that?

Here is the query generated: 

SELECT * FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID INNER JOIN wp_commentmeta ON ( wp_comments.comment_ID = wp_commentmeta.comment_id ) WHERE ( comment_approved = '1' ) AND  wp_posts.post_status = 'publish' AND ( 
  ( wp_commentmeta.meta_key = 'liked_by' AND CAST(wp_commentmeta.meta_value AS SIGNED) IN ('1') )
) GROUP BY wp_comments.comment_ID ORDER BY comment_date_gmt DESC
Post Views: 2

Related posts

Leave a Reply

1 comment

  1. I wanted to do this over serialized data even though I cited an URL that said

    don’t store array of ids in one row instead loop through ids array and
    normalize your likes data manually Don’t serialize data into a
    database field.

    This is because I used ACF and added an Users field, which used serialized data. I solved my problem by avoiding ACF and just using the comment meta API.

    // UserID 1 likes commentID 9
add_comment_meta(9, 'like', 1, false);

// Get comments liked by userID 1
$args = array(
    'status' => 'approve',
    'post_status' => 'publish',
    'meta_query' => array(
        array(
            'key' => 'like',
            'value' => 1,
            'compare' => '=',
            'type' => 'numeric'
        )
    )
);

// The Query
$comments_query = new WP_Comment_Query;
$comments = $comments_query->query( $args );
// Surprise, it's commentID 9

    Notice how I use add_comment_meta with unique=false for every like instead of serializing an array into the database, making it unavailable for queries.