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!
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).")
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.
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.
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'
