1

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

Drew Baker
  • 14,154
  • 15
  • 58
  • 97
  • are the likes adding up in the value? (i.e. as a new like comes in it adds it up) – Moishy Aug 11 '13 at 01:35
  • 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 Aug 11 '13 at 01:54
  • I would probably get a like count from facebook (see [this question](http://stackoverflow.com/questions/9255949/getting-facebook-like-count)) then `orderby` the `meta_value_num` – Moishy Aug 11 '13 at 03:53
  • My app has nothing to do with Facebook. Good idea though. – Drew Baker Aug 11 '13 at 05:40

1 Answers1

0

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?

Community
  • 1
  • 1
Sean H
  • 1,045
  • 1
  • 14
  • 32