Order a WP_Query by meta value where the value is an array

Question

I have a WordPress custom query:

$args = array(
    'posts_per_page' => 2,
    'meta_key' => 'pworks_popular_posts',
    'paged' => $page_nr
);

$query = new WP_Query($args);

if($query->have_posts()) {
    while($query->have_posts()) {
        $query->the_post();
        $pworks_post_meta = get_post_meta(get_the_ID(), 'pworks_popular_posts', true);
        $pworks_post_list[get_the_ID()] = $pworks_post_meta['Post visited'];
    }
}

I am using this method to sort an array of post ids and post visits which then I sort using arsort and display a list of posts by the number of visits they got:

arsort($pworks_post_list);

However, I realized that pagination will be a nightmare if I continue to do it this way, as there is no way to go to a specific page of the sorted results, only the unsorted ones.

To accomplish this I have to order my posts by meta_value and ditch the arsort() method.

The problem is - the meta value is an array that looks like this (I did it this way because I will most likely be adding more fields to that array later):

array(1) {
  ["Post visited"]=>
  int(0)
}

Is there a way to order the query by this "Post visited" array field in WP_Query?

I would appreciate your help greatly.

Answer 1

At query level? Not possible.

{db_prefix}_postmeta has only one field for value. WP serializes arrays before inserting them and deserializes after they're out. So you have three choices here:

1. If you really need this sorting at query level (pagination, maybe?) you have to rethink your information architecture and start using a separate custom_post_meta field for each field that you want to sort by.

2. Forget sorting.

3. If your number of items is limited and it's going to remain limited, you could pull them all out with (posts_per_page=-1) and sort them with array_multisort(). As you might expect, this will slow the page down a bit (depending on the total number of posts that have that particular custom_post_meta).

As a variant to option 1, for performance reasons, depending on number of items and complexity of data, you might consider not using custom_post_meta at all, but store it in a separate custom table.