WordPress order query posts on popularity of a custom field

Question

I have a WordPress site that allows a user to "like" a post. This adds a custom field to a post with the user ID in it, like so:

add_post_meta($post_id, 'like', $user_id, false);

So a post can end up with a something like a 100 different 'like' keys with a user ID.

So I want to return a leader board of most active users based on likes. So I'd want to query that looked at meta_key 'like' for the $user_id's that appear the most. It would look something like this. Obviously this doesn't work currently.

$args = array(
   'post_type' => 'post',
   'meta_key' => 'likes',
   'post_status'       => 'publish',
   'posts_per_page'    => 20,
   'orderby'           => 'popularity', // This doesn't exist yet, but I wants it!      
   'order' => 'ASC',
   'meta_query' => array(
       array(
           'key' => 'likes',
           'value' => $user_id,
           'compare' => '=',
       )
   )
 );
 $query = new WP_Query($args);

Is this even possible? Or would I be better of getting all posts periodically, adding up the likes and caching the results?

I know I could make a custom field that incremented as a person like it, but I'd rather come up with a query to get results in real time (more robust when deleting a user etc).

Thanks, Drew

are the likes adding up in the value? (i.e. as a new like comes in it adds it up) — Mark
No likes arnt adding up. Each like is just the user-id of the person who clicked the like. You have to be logged in to see the page. — Drew Baker
I would probably get a like count from facebook (see this question) then orderby the meta_value_num — Mark

Sean H Sean H · Accepted Answer · 2015-08-03T13:35:55

Here's how to do it using SQL:

SELECT post_id, COUNT(*) AS likes
FROM wp_postmeta
WHERE meta_key='like'
GROUP BY post_id
ORDER BY likes DESC

Let me know if you've managed to figure it out using WP_Query, which is what I'm asking for here: WordPress Query: Orderby number of matched rows?

WordPress order query posts on popularity of a custom field

1 Answers