WordPress Meta Query Arrays

Question

I'm building a way form my users to "like" a post.

I'll have an array of users that have liked posts:

$user_ids = array(60, 61, 62, 63);

And then a post will have a post_meta that saves the user ID when the "like" button is clicked on a post, like so:

// Build array of user IDs that have liked this post
$likes = array(61, 62);

// Save array
update_post_meta($post->ID, likes, $likes);

So how can I return all posts liked by the users. This is how I'm doing it currently, which doesn't work.

$args = array(
    'post_type'         => 'post',
    'post_status'       => 'publish',
    'posts_per_page'    => -1,
    'orderby'           => 'rand',
    'meta_query'        => array(
        'relation'  => 'AND',
        array(
            'key'       => 'likes',
            'value'     => $user_ids,
            'compare'   => 'IN'
        )
    )
);
$posts = get_posts($args);

This should return all posts that have '60, 61, 62, 63' saved in the posts 'likes' meta. But it returns 0 results.

Now, I think this is because WordPress serializes arrays when they are saved in a post_meta.

So, how do I a do a query like this? Is it even possible? Maybe I have to use the $wpdb class.

Thanks!

Have you tried to pass your values as string in args like : $user_ids = array('60', '61', '62', '63'); ? — user1409909
– user1409909, Commented Aug 6, 2013 at 9:23
why you used 'relation' => 'AND', ? Because you not used twor or more than two metakey — Datta Parad
– Datta Parad, Commented Aug 6, 2013 at 15:45

M Khalid Junaid · Accepted Answer · 2013-08-07 20:39:11Z

Try this one with WP_Query no need of relation just use the compare part with IN

 $user_ids = array(60, 61, 62, 63);
 $args = array(
   'post_type' => 'post',
   'meta_key' => 'likes',
   'post_status'       => 'publish',
   'posts_per_page'    => -1,
   'orderby'           => 'rand',       
   'order' => 'ASC',
   'meta_query' => array(
       array(
           'key' => 'likes',
           'value' => $user_ids, //array
           'compare' => 'IN',
       )
   )
 );
 $query = new WP_Query($args);

See the given example above "Time Parameters" Heading WP_Query

OR by get_posts try this

$args = array(
    'post_type'         => 'post',
    'post_status'       => 'publish',
    'posts_per_page'    => -1,
    'orderby'           => 'rand',
    'meta_query'        => array(

        array(
            'key'       => 'likes',
            'value'     => $user_ids,
            'compare'   => 'IN'
        )
    )
);
$posts = get_posts($args);

OR by a custom query you can do as follows

global $wpdb;

$liked_posts=$wpdb->get_results("SELECT * FROM `wp_posts` WHERE 
post_type='post' AND post_status ='publish' AND ID
 IN(
SELECT post_id FROM `wp_postmeta` WHERE meta_key='likes' 
AND meta_value IN (".join(',',$user_ids).")
) ORDER BY RAND()");

Also 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. That's what Database_normalization is for and insert each id in new row because

Wordpress stores the meta values as strings. When you pass update_post_meta an array, it automatically converts it to a string. What you'll need to do is unserialize it when you attempt to read the data but in mysql you can't unserialize column data in the query because mysql doesn't about the serialization of php

$likes = array(61, 62);

foerach($likes as $l){

update_post_meta($post->ID, "likes", $l);

}

And below is the query you have asked for in bounty comments

If a $wpdb answer is given, then please elaborate on how to factor in category (include and exclude), and ignore posts by an array of IDs

$liked_posts=$wpdb->get_results("
SELECT * FROM `wp_posts` wp
INNER JOIN `wp_term_relationships` wtr ON (wp.`ID`=wtr.`object_id`)
INNER JOIN  `wp_term_taxonomy` wtt ON (wtr.`term_taxonomy_id` =wtt.`term_taxonomy_id`)
WHERE  wp.post_type='post' AND wp.post_status ='publish' AND wp.ID
 IN(
SELECT post_id FROM `wp_postmeta` WHERE meta_key='likes' 
AND meta_value IN (".join(',',$user_ids).")
)  AND wp.ID NOT IN (100,101,102)
AND wtt.`term_id` IN(1,2,3) AND wtt.`term_id` NOT IN (4,5,6,)    
ORDER BY RAND() ");

I have used the INNER JOINs on wp_term_relationships and wp_term_taxonomy the table wp_term_relationshipsstores the relation of posts and the taxonomy of category and the table wp_term_taxonomy have the taxonomy of the category and also the category id

Here is the part which covers the

1. category (include and exclude)

AND wtt.`term_id` IN(1,2,3) AND wtt.`term_id` NOT IN (4,5,6,)

2. ignore posts by an array of IDs

AND wp.ID NOT IN (100,101,102) 
or $postids =array(100,101,102);
AND wp.ID NOT IN (".join(',',$postids).")

wunderdojo · Accepted Answer · 2013-08-06 19:56:36Z

3

You're going to run into problems with your current approach because, as you note, the ids are saved as serialized arrays in a single field. An easier approach would be to save each like as a single postmeta entry. So meta_key = "like" and meta_value = user ID.

So for storing the likes switch from update_post_meta to add_post_meta:

/** The fourth parameter, false, tells WP that there can be multiple
  * post_meta entries with the same meta_key. It's false by default, just here
  * here for reference
  */
add_post_meta($post->ID, 'like', $user_id, false);

Then to get posts liked by a specific user you would just do:

$liked_posts = $wpdb->get_results("SELECT post_id FROM {$wpdb->prefix}postmeta 
              WHERE meta_key = 'like' AND meta_value = $user_id");

To get results for a number of users at once, like user ID 4, 23, 39, etc.

$liked_posts_by_user = $wpdb->get_results("SELECT meta_value as user_id, 
         GROUP_CONCAT(post_id) as posts FROM {$wpdb->prefix}wp_postmeta 
         WHERE meta_value IN('22','23','24') GROUP BY user_id

That will give you results like:

  user_id    posts
   23         1
   25         2,40,3

So just replace the IN portion of the query with your array of user ids.

answered Aug 6, 2013 at 19:56

wunderdojo

1,0278 silver badges7 bronze badges

1 Comment

Drew Baker Over a year ago

This is a very good answer too, just not as in depth as the accepted one. Thanks for the help though!

yunieski dieguez garcia · Accepted Answer · 2015-07-15 13:48:24Z

3

I had a similar problem and i solved it using the serialize(), you can use ir in case that the vale you are working with is an integer here´s my example:

    $args = array(
    'meta_query' => array(
        array(
            'key' => 'my_meta_key',
            'value' => serialize(strval($my_id)),
            'compare' => 'LIKE'
        )
    )
);
$posts = get_posts( $args );

and that´s all, you hace your meta query ready.

answered Jul 15, 2015 at 13:48

yunieski dieguez garcia

812 bronze badges

2 Comments

weaveoftheride Over a year ago

YES MATE, serialize is the one !

huykon225 Over a year ago

If my meta_value like this:

a:3:{i:0;a:3:{s:12:"bundle_order";i:1;s:10:"product_id";s:4:"2187";s:11:"bp_quantity";i:1;}i:1;a:3:{s:12:"bundle_order";i:2;s:10:"product_id";s:4:"2189";s:11:"bp_quantity";i:1;}i:2;a:3:{s:12:"bundle_order";i:3;s:10:"product_id";s:4:"2186";s:11:"bp_quantity";i:1;}}

How I can get product_id = 2186 ?

Mangesh Parte · Accepted Answer · 2013-08-07 12:08:40Z

0

Your query is right. But as you are storing user ids as a string your query wont work. Save user id a single meta value for post.

add_post_meta($post->ID, 'like', $user_id, false);

and replace 'compare' => 'IN' with 'compare' => 'LIKE'

answered Aug 7, 2013 at 12:08

Mangesh Parte

2,1771 gold badge13 silver badges16 bronze badges

Collectives™ on Stack Overflow

WordPress Meta Query Arrays

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related