0

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',
    ),
) );
2
  • 1
    orderby should be outside meta_query. Dec 15, 2022 at 7:34
  • @JacobPeattie my bad, that was a typo, i've edited the question Dec 15, 2022 at 14:50

2 Answers 2

0

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'
) );
1
  • This appears to do the same thing as the array, still getting the same inconsistent last_name sorting Dec 15, 2022 at 21:32
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.