0

I want to list all users and order them by a meta value. Several users have it and some do not, but I want to be able to list them all and group them accordingly. I have the following code but it only selects the users with the meta value and ignores those without it:

    function handle_column_sorting( $query ) {
    
        if ( !is_admin() ) {
            return;
        }
    
        if ( isset( $query->query_vars['orderby'] ) && $query->query_vars['orderby'] == 'friend' ) {
    
            $meta_query = array(
                'relation' => 'OR',
                    array(
                        'key' => 'festival_friend',
                        'compare' => 'EXISTS',
                    ),
                    array(
                        'key' => 'festival_friend',
                        'compare' => 'NOT EXISTS'
                    ),
                );
            $query->set( 'orderby', 'meta_value' );
            $query->set( 'meta_key', 'festival_friend' );
            $query->set( 'meta_query', $meta_query );
    
            $query->prepare_query();
            
        }
    }
    add_action( 'pre_user_query', 'handle_column_sorting' );

A dump of the $query object is below. My SQL knowledge isn't strong enough to figure it out, but it looks wrong to me in that the last bit should read OR mt2.meta_key IS NULL. I don't know where I can change that in the query.

    [query_fields] => DISTINCT SQL_CALC_FOUND_ROWS dcw_users.ID
    [query_from] => FROM dcw_users LEFT JOIN dcw_usermeta ON ( dcw_users.ID = dcw_usermeta.user_id )  LEFT JOIN dcw_usermeta AS mt1 ON ( dcw_users.ID = mt1.user_id ) LEFT JOIN dcw_usermeta AS mt2 ON ( dcw_users.ID = mt2.user_id AND mt2.meta_key = 'festival_friend' )
    [query_where] => WHERE 1=1 AND ( dcw_usermeta.meta_key = 'festival_friend' AND ( mt1.meta_key = 'festival_friend' OR mt2.user_id IS NULL ) )

1 Answer 1

1

it only selects the users with the meta value and ignores those without it

That's because of this part: $query->set( 'meta_key', 'festival_friend' );, which adds an extra clause to the meta query clauses, i.e. the meta_query is basically appended with a array( 'key' => 'festival_friend' ) (yes, an array with just a key item). Try var_dump( $query->meta_query->queries ); and you'd understand what I mean.

So, to fix that, just remove the part I mentioned above. However, you should also set the orderby to meta_value_num, if the meta value is numeric.

Also, you should not re-prepare the query, i.e. $query->prepare_query() should not need to be called and instead, use the pre_get_users action to filter the query args. So,

  • Remove this line: $query->prepare_query();.

  • Replace the add_action( 'pre_user_query', 'handle_column_sorting' ) with add_action( 'pre_get_users', 'handle_column_sorting' ).


Your code should now work, however, if you want the users which have the meta festival_friend to always be on the top in the list, specifically when the sort order is ASC (i.e. ascending), then try this after making the above changes:

  1. Set a key for the clauses in your meta query array, e.g. has_friend and no_friend.

    $meta_query = array(
        'relation' => 'OR',
    
        // Clause 1: Find users having the meta.
        'has_friend' => array(
            'key' => 'festival_friend',
            'compare' => 'EXISTS',
        ),
    
        // Clause 2: Find users without the meta.
        'no_friend'  => array(
            'key' => 'festival_friend',
            'compare' => 'NOT EXISTS'
        ),
    );
    
  2. Add a custom arg named orderby_friend, whereby the value is the key of the first clause above.

    $query->set( 'orderby_friend', 'has_friend' );
    
  3. Use this to modify the ORDER BY clause of the user query.

    add_action( 'pre_user_query', 'admin_custom_orderby_friend' );
    function admin_custom_orderby_friend( $query ) {
        if ( ! is_admin() || ! $query->get( 'orderby_friend' ) ) {
            return;
        }
    
        $clause_key  = $query->get( 'orderby_friend' );
        $clauses_arr = $query->meta_query->get_clauses();
    
        if ( empty( $clauses_arr[ $clause_key ] ) ) {
            return;
        }
    
        global $wpdb;
    
        $order = strtoupper( $query->get( 'order' ) );
        $order = ( 'DESC' === $order ) ? 'DESC' : 'ASC';
    
        $alias = $clauses_arr[ $clause_key ]['alias'];
    
        $query->query_orderby = <<<SQL
    ORDER BY
        # 1. Group the users.
        (CASE
            WHEN ( $alias.meta_key = 'festival_friend' ) THEN 1
            ELSE 2
        END) ASC,
        # 2. Sort them by the meta value.
        $alias.meta_value $order
    SQL;
    }
    

Notes

  • Plugins and themes can also filter the query, hence, instead of hard-coding it, I used WP_Meta_Query::get_clauses() to get the table alias of the target meta query clause.

  • I used the CASE operator in MySQL / MariaDB to group the users into 2 groups — one for users having the meta, and the other group for users not having that meta. So the 1st group is at the top and will be sorted by the meta value.

  • If the meta value is numeric, you can use $alias.meta_value+0 instead of $alias.meta_value.

4
  • Thanks that's very helpful. I was wondering if I should have deleted the meta_key bit. I have done the first section of your answer and it now works in that it retrieves all the users. However, the 'Friends' column is now empty in all cases.
    – Badger
    Commented Feb 26 at 15:59
  • 1
    Ignore the above, they are there, they're just grouped in the middle pages for some reason. Might have to implement your second suggestion also.
    – Badger
    Commented Feb 26 at 16:13
  • I'm glad my answer helped, but I should add that if you're specifically targeting just the list table on the admin's users screens, then you should use the users_list_table_query_args filter instead of pre_get_users. Happy coding!
    – Sally CJ
    Commented Mar 1 at 10:30
  • Thanks for that, it works well. I only wish I could get them to appear in order of the meta value, they just appear in a clump in the middle whether I want them ascending or descending.
    – Badger
    Commented Mar 13 at 10:32

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.