0

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?

1 Answer 1

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' ). =)

6
  • The query is way faster using regexp. It's almost there, but not quite. the order isn't quite there. Here are some of the actual position titles: Engineering Manager; Transmission & substation systems supervisor; contracts coordinator; sr. power systems project coordinator. This order puts them in alphabetical order. Additionally, do you know how I might get the rest of the users in this department that don't match this positional criteria? Would I do another query and exclude these users somehow? Thanks for the insight!
    – NW Tech
    Sep 21, 2023 at 13:45
  • To be honest, I think I misread/misinterpreted your question.. 😅 Therefore I completely revised my answer. I might add another update soon, but for now, I hope the current update helped you?
    – Sally CJ
    Sep 22, 2023 at 9:57
  • This new variation worked out great. Thank you!
    – NW Tech
    Sep 22, 2023 at 13:37
  • Thanks for accepting my answer, and I'm happy that the previous variation worked for you, but I suggest you to use the new code in Option 1 in my revised answer (and let me know how it goes) :) I also added Option 2, just in case you'd like to give it a try..
    – Sally CJ
    Sep 22, 2023 at 17:45
  • I've updated my template with the revised option 1. i will say, it seems to perform better -- the one I tired earlier worked but it took a little longer for the page to load.
    – NW Tech
    Sep 22, 2023 at 18:52

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.