Complicated `orderby` based on text in custom field

Question

I'm doing a user query and looking to query users that are in a certain department (custom field) -- this works. Now I'm trying to manipulate the order....I'd like to use the meta_key for position and list users in this order: "Managers", "Supervisors", "Foreman", "Coordinator", everyone else

The way I accomplished this query in the past, outside of Wordpress, was:

SELECT * FROM employees WHERE emp_department = 'Engineering' AND emp_employed = 'Y' AND emp_position LIKE '%Manager%' UNION SELECT * FROM employees WHERE emp_department = 'Engineering' AND emp_employed = 'Y' AND emp_position LIKE '%Sup%' UNION SELECT * FROM employees WHERE emp_department = 'Engineering' AND emp_employed = 'Y' AND emp_position LIKE '%Foreman%' UNION SELECT * FROM employees WHERE emp_department = 'Engineering' AND emp_employed = 'Y' AND emp_position LIKE '%Coordinator%' UNION SELECT * FROM employees WHERE emp_department = 'Engineering' AND emp_employed = 'Y' ORDER BY field(emp_position,'%MANAGER%', '%Sup%', '%Foreman%', '%Coordinator%', emp_id) ASC

I've tried a couple different things with varied results. The one that seemed most promising was this:

$title = get_the_title();
$args = array(  
    'meta_query' => array(
        'relation' => 'OR',
        array(
            array(
                'relation' => 'AND',
                'key' => 'emp_dept',
                'value' => $title,
                'compare' => 'LIKE',
            ),
            'manager_clause' => array(
                'key' => 'emp_position',
                'value' => 'Manager',
                'compare' => 'LIKE',
            ),
        ),
        array(
            array(
                'relation' => 'AND',
                'key' => 'emp_dept',
                'value' => $title,
                'compare' => 'LIKE',
            ),              
            'supervisor_clause' => array(
                'key' => 'emp_position',
                'value' => 'Sup',
                'compare' => 'LIKE',
            ), 
        ),
        array(
            array(
                'relation' => 'AND',
                'key' => 'emp_dept',
                'value' => $title,
                'compare' => 'LIKE',
            ),              
            'foreman_clause' => array(
                'key' => 'emp_position',
                'value' => 'Foreman',
                'compare' => 'LIKE',
            ), 
        ),
        array(
            array(
                'relation' => 'AND',
                'key' => 'emp_dept',
                'value' => $title,
                'compare' => 'LIKE',
            ),              
            'coordinator_clause' => array(
                'key' => 'emp_position',
                'value' => 'Coordinator',
                'compare' => 'LIKE',
            ), 
        ),
    ),
    'orderby' => array( 
        'manager_clause' => 'DESC',
        'supervisor_clause' => 'DESC',
        'foreman_clause' => 'DESC',
        'coordinator_clause' => 'DESC',
    ),
);
$users = get_users($args); 

This ends up just bogging down the server memory and crashes it. If I reduce the query to just checking for "managers" and "supervisors", it works....it only shows those positions and slow to load, but it works.

Another variation I tried was:

$title = get_the_title();
$args = array(  
    'meta_query' => array(
            array(
                'relation' => 'and',
                'key' => 'emp_dept',
                'value' => $title,
                'compare' => 'LIKE',
            ),
            'position_clause' => array(
                'relation' => 'or',
                array (
                    'key' => 'emp_position',
                    'value' => 'Manager',
                    'compare' => 'LIKE',
                ),
                array(
                'key' => 'emp_position',
                'value' => 'Sup',
                'compare' => 'LIKE',
                ), 
                array(
                'key' => 'emp_position',
                'value' => 'Foreman',
                'compare' => 'LIKE',
                ), 
                array(
                'key' => 'emp_position',
                'value' => 'Coordinator',
                'compare' => 'LIKE',
                ), 
            ),
    ),
    
    'orderby' => array( 
        'position_clause' => 'DESC',
    ),
);
$users = get_users($args);

This gave me only the users with he requested positions, however, they were ordered by username, not by the hierarchical order of the position I was hoping for.

Is there a better way to accomplish this?

Answer 1

You should really just forget about the first code in your post/question, but anyway, what you're trying to achieve can be accomplished either by sorting at the database level or PHP level, and you would probably want to use the latter if pagination is not needed. But that is up to you to decide.

Option 1: Sorting at the database level.

This can be done using the CASE operator in MySQL/MariaDB, along with a custom orderby value, and the pre_user_query hook to set/modify the ORDER BY clause for your user query.

1. Define the query arguments:

   $args = array(
       'meta_query' => array(
           // Include users from the selected department (named $title) only, which
           // means only those who have the meta emp_dept.
           array(
               'key'     => 'emp_dept',
               'value'   => $title,
               'compare' => 'LIKE',
           ),
   
           // Include both the users who have the emp_position meta, and those who do
           // not have that meta.
           array(
               'relation'        => 'OR',
               'emp_position'    => array(
                   'key'     => 'emp_position',
                   'compare' => 'EXISTS',
               ),
               'no_emp_position' => array(
                   'key'     => 'emp_position',
                   'compare' => 'NOT EXISTS',
               ),
           ),
       ),
       'orderby'    => 'emp_position',
   );
   

2. Set/modify the ORDER BY clause via the pre_user_query hook:

   add_action( 'pre_user_query', 'my_pre_user_query' );
   function my_pre_user_query( \WP_User_Query $query ) {
       if ( 'emp_position' === $query->get( 'orderby' ) ) {
           $clauses = $query->meta_query->get_clauses();
   
           // Get the table aliases used with the current query. If you changed the
           // array keys in your meta query, then change the ones below accordingly.
           $mt2 = $clauses['emp_position']['alias'];
           $mt3 = $clauses['no_emp_position']['alias'];
   
           // The WP_User_Query class does not (yet) have a FILTER hook for changing
           // the ORDER BY clause, but we can change it by direct modification on the
           // $query->query_orderby property.
           $query->query_orderby = <<<SQL
   ORDER BY
       # 1. Group the users.
       CASE
           WHEN ( $mt2.meta_value LIKE '%Manager%' ) THEN 1
           WHEN ( $mt2.meta_value LIKE '%Supervisor%' ) THEN 2
           WHEN ( $mt2.meta_value LIKE '%Foreman%' ) THEN 3
           WHEN ( $mt2.meta_value LIKE '%Coordinator%' ) THEN 4
           # Yes, these are intentionally 6 and then 5.
           WHEN ( $mt3.user_id IS NULL ) THEN 6
           ELSE 5
       END ASC,
   
       # 2. Sort the groups; A-Z.
       CAST( $mt2.meta_value AS CHAR ) ASC
   SQL;
       }
   }
   

Option 2: Sorting at the PHP level only.

This is just one of the many ways of doing it in PHP, but I hope this helps.

1. Define the query arguments – Just use the same $args as above, but omit the orderby or don't set it to emp_position.

2. Get and sort the users.

   $users  = get_users( $args );
   $groups = array(
       'Manager'     => array(),
       'Supervisor'  => array(),
       'Foreman'     => array(),
       'Coordinator' => array(),
       'Other'       => array(),
   );
   
   // 1. Group the users.
   foreach ( $users as $i => $user ) {
       $group = 'Other';
   
       if ( preg_match( '/(Manager|Supervisor|Foreman|Coordinator)/i', $user->emp_position, $matches ) ) {
           $group = ucfirst( $matches[1] );
       }
   
       // Note that I'm just referencing and not copying the actual user object
       // stored in the $users array.
       $groups[ $group ][] = &$users[ $i ];
   }
   
   $cmp = function ( $a, $b ) {
       // I used the ternary so that users having an empty emp_position are put
       // at the bottom.
       return $a->emp_position ? strcmp( $a->emp_position, $b->emp_position ) : 1;
   };
   

3. For completeness, this is how I displayed the results:

   foreach ( $groups as $name => $list ) {
       echo '<h3>' . esc_html( $name ) . '</h3>';
   
       // 2. Sort the group upon displaying; A-Z.
       usort( $list, $cmp );
   
       echo '<ol>';
       foreach ( $list as $user ) {
           printf(
               '<li>%s: <i>%s</i></li>',
               esc_html( $user->user_login ),
               esc_html( $user->emp_position )
           );
       }
       echo '</ol>';
   }
   

---

Additionally, you should know that the 'position_clause' => 'DESC' in your second code won't work because the array did not have a direct key item as in 'position_clause' => array( 'key' => 'emp_position' ). =)