query users by role

Question

I have a custom plugin which queries users and usermeta, but I now need to filter admins out of the results. A very simplified version of my sql query is:

SELECT * FROM usermeta LEFT JOIN users ON users.ID = user_id WHERE meta_key = 'last_name' AND user_role != 'admin' ORDER BY meta_value ASC LIMIT 0, 25 user_role is not a field, and i saw how it is stored as a config string, but i don't see how to make an equivalent query to this. am i missing something?

thanks.

Answer 1

You can use this function:

// get users with specified roles
function getUsersWithRole( $roles ) {
    global $wpdb;
    if ( ! is_array( $roles ) )
        $roles = array_walk( explode( ",", $roles ), 'trim' );
    $sql = '
        SELECT  ID, display_name
        FROM        ' . $wpdb->users . ' INNER JOIN ' . $wpdb->usermeta . '
        ON          ' . $wpdb->users . '.ID             =       ' . $wpdb->usermeta . '.user_id
        WHERE       ' . $wpdb->usermeta . '.meta_key        =       \'' . $wpdb->prefix . 'capabilities\'
        AND     (
    ';
    $i = 1;
    foreach ( $roles as $role ) {
        $sql .= ' ' . $wpdb->usermeta . '.meta_value    LIKE    \'%"' . $role . '"%\' ';
        if ( $i < count( $roles ) ) $sql .= ' OR ';
        $i++;
    }
    $sql .= ' ) ';
    $sql .= ' ORDER BY display_name ';
    $userIDs = $wpdb->get_col( $sql );
    return $userIDs;
}