User Query Multiple Orderby Clause

Question

I'm having trouble getting consistent results from a WP_User_Query when ordering by multiple meta values.

There is a meta field that contains a serialized list of IDs, and I need to display users with a certain ID (72) first. These users need to be alphabetized by last name. The following users without this ID need to be alphabetized by last name as well.

The query works fine if I only look for users with the ID 72 and sort by last name. When I use the query below however, I get inconsistent last name sorting results. The ID 72 users always show up first, but certain users are inexplicably out of order.

Here's the gist of my query:

$users = new WP_User_Query( array(
    'meta_query' => array(
        'relation' => 'AND',
        'lastname' => array(
            'key' => 'last_name',
            'compare' => 'EXISTS',
            'type' => 'CHAR',
        ),
        'list' => array(
            'distribution_list' => array(
                'key' => 'distribution_list',
                'value' => '"72"',
                'compare' => 'LIKE'
            ),
            'distribution_list2' => array(
                'key' => 'distribution_list',
                'value' => '"72"',
                'compare' => 'NOT LIKE'
            ),
            'relation' => 'OR',
        ),
    )
    'orderby' => array(
        'distribution_list' => 'ASC',
        'last_name' => 'ASC',
    ),
) );

Answer 1

Try using:

'orderby'=>'distribution_list last_name',
'order'=>'ASC'

Instead of:

'orderby' => array(
    'distribution_list' => 'ASC',
    'last_name' => 'ASC',
),

So:

$users = new WP_User_Query( array(
    'meta_query' => array(
        'relation' => 'AND',
        'lastname' => array(
            'key' => 'last_name',
            'compare' => 'EXISTS',
            'type' => 'CHAR',
        ),
        'list' => array(
            'distribution_list' => array(
                'key' => 'distribution_list',
                'value' => '"72"',
                'compare' => 'LIKE'
            ),
            'distribution_list2' => array(
                'key' => 'distribution_list',
                'value' => '"72"',
                'compare' => 'NOT LIKE'
            ),
            'relation' => 'OR',
        ),
    ),
    'orderby'=>'distribution_list last_name',
    'order'=>'ASC'
) );

Answer 2

I ended up writing some manual SQL to solve this issue.

I kept the LIKE/NOT LIKE meta queries and last_name meta_value in the arguments:

$args['meta_query'][] = array(
    'relation' => 'OR',
    array(
        'key' => 'distribution_list',
        'value' => '"72"',
        'compare' => 'LIKE',
    ),
    array(
        'key' => 'distribution_list',
        'value' => '"72"',
        'compare' => 'NOT LIKE'
    )
);

$args['meta_key'] = 'last_name';

Then added this hook to add a SQL CASE statement:

add_filter('pre_user_query', 'pre_user_query');
function pre_user_query($user_query) {
    if ( !is_admin() && str_contains($user_query->query_where, 'distribution_list')) {
        global $wpdb;
        $user_query->query_orderby =
            "ORDER BY CASE
                WHEN mt1.meta_key = 'distribution_list' AND mt1.meta_value LIKE '" . esc_sql(  '%"72"%' )  . "' THEN 1
                WHEN mt1.meta_key = 'distribution_list' AND mt1.meta_value NOT LIKE '" . esc_sql(  '%"72"%' )  . "' THEN 0
            END DESC, wp_usermeta.meta_value ASC";
    }
}

Not sure if the is_admin() is necessary. I also kept "mt1" hardcoded and ensured that the meta_query above is listed first.

Let me know if anyone has any better ways to do this, but this is working for now.