Querying WordPress by array size

Question

I have a website which allows users to 'like' posts. A post's likes are stored as an array of user IDs in a postmeta field called "likers".

I'm having trouble displaying posts in order of most liked. Ideally I could query the size of the likers array and order by that value, but I can't find a way to do it using WP_Query.

At the moment I'm querying for every post's ID and likers field, then in a foreach loop I'm counting the size of the likers array, sorting by that value and using a custom loop to display each post. Except with this method I can't use the standard WP pagination function.

Has anyone got a better solution?

Here's the current code:

global $wpdb;
$posts = $wpdb->get_results("SELECT post_id, meta_value FROM $wpdb->postmeta WHERE meta_key = 'likers'", 'ARRAY_A');
if (!$posts) {
    return "No posts found.";
} else {

    // add them to an array with the size of their 'likers' field
    foreach($posts as &$post) {
        $post['likers'] = count(unserialize($post['meta_value']));
    }

    // sort array by likes
    uasort($posts, function ($i, $j) {
        $a = $i['likers'];
        $b = $j['likers'];
        if ($a == $b) return 0;
        elseif ($a < $b) return 1;
        else return -1;
    });

    // now display the posts...
    foreach($posts as $post) {

Answer 1

I'm not really sure how you store the number of likes in your database. If you store the data as an comma separated string <userid>,<userid>,<userid> you could write a query like this to order your posts by the number of likes:

global $wpdb;
$posts = $wpdb->get_results("SELECT ID, 
                             post_title, 
                             LENGTH(m.meta_value) -  
                             LENGTH(REPLACE(m.meta_value, ',', '')) + 1 likes 
                             FROM {$wpdb->posts} p
                             LEFT JOIN {$wpdb->postmeta} m ON m.post_ID = p.ID &&
                                                         m.meta_key = 'likes' 
                             ORDER BY likes DESC", OBJECT);

The above calculates the number of likes by subtracting the length of the meta_value with the length of the meta_value without commas and then adding 1.

You should be able to use the same technique in cases where your meta value contains a serialized array. Notice that each value need to be stored as a string for this to work:

// serialized array of user ids
a:4:{i:0;s:4:"1714";i:1;s:4:"1333";i:2;s:4:"1332";i:3;s:2:"38";}

$posts = $wpdb->get_results("SELECT ID, 
                             post_title, 
                             (LENGTH(m.meta_value) -  
                             LENGTH(REPLACE(m.meta_value, '\"', '')) - 2) * 0.5 likes 
                             FROM {$wpdb->posts} p
                             LEFT JOIN {$wpdb->postmeta} m ON m.post_ID = p.ID &&
                                                         m.meta_key = 'likes' 
                             ORDER BY likes DESC", OBJECT);