How to order users by a date in the meta_value array

by

No, it’s not possible to do this using the get_users function the way that you’ve outlined it, because get_users doesn’t accept a meta_query argument the same way that get_posts does.

I see two approaches to get your desired result: either 1) get the users, then sort them afterwards, or 2) use $wpdb to write a direct database call. If you went that route, you’d want to be sure to cache the results using the transients API.

Option 1 route could be something like this:


<?php
// Assume that this is the data that we get back from get_users or a similar function
$users = array(
    0 => array(
        'name' => 'Tom',
        'payments' => array(
            0 => array(
                'date' => '2020-04-15',
                'amount' => '750',
            ),
            1 => array(
                'date' => '2020-04-20',
                'amount' => '900',
            ),
        ),
    ),
    1 => array(
        'name' => 'Dick',
        'payments' => array(
            0 => array(
                'date' => '2020-04-10',
                'amount' => '750',
            ),
            1 => array(
                'date' => '2020-04-15',
                'amount' => '900',
            ),
            2 => array(
                'date' => '2020-04-25',
                'amount' => '1250',
            ),
        ),
    ),
    2 => array(
        'name' => 'Harry',
        'payments' => array(
            0 => array(
                'date' => '2020-04-24',
                'amount' => '750',
            ),
        ),
    ),
);

function sort_users_by_date( $user1, $user2 ) {
    $date1 = end($user1['payments'])['date'];
    $date2 = end($user2['payments'])['date'];

    return strtotime($date1) - strtotime($date2);
}

usort($users, 'sort_users_by_date');

var_dump($users);

Demo

Using $wpdb is tricker. It’d go along these lines:

global $wpdb;
$users = $wpdb->get_results( 'SELECT * FROM {$wpdb->prefix}users INNER JOIN {$wpdb->prefix}usermeta ON {$wpdb->prefix}users.ID = {$wpdb->prefix}usermeta.user_id WHERE {$wpdb->prefix}usermeta.meta_key = "last_payment_date" ORDER BY {$wpdb->prefix}usermeta.meta_value DESC' );

This example assumes a couple of important things:

  1. You need to add another user meta key called “last_payment_date.” Update this value when you add another payment to the user payments meta item. This way, you can query and order by a singular value, rather than going through the process of parsing an array inside an SQL statement.
  2. You should probably use $wpdb->prepare along with this. You can find documentation on that here.