query - Sort users in admin screen by meta value whether it exists or not

admin管理员组

文章数量:1124548

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 ) )

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 ) )

• query
• user-meta
• wp-user-query

Share Improve this question asked Feb 24, 2024 at 10:58 BadgerBadger 19311 gold badge33 silver badges1010 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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.

本文标签： querySort users in admin screen by meta value whether it exists or not