WP_User_Query capability support (WordPress 5.9)

[cbratschi]

Is your enhancement related to a problem? Please describe.

The following query introduced in WordPress 5.9 runs extremely slow on our system (4-8 seconds):

SELECT wp_users.ID,wp_users.user_login,wp_users.display_name
FROM wp_users
INNER JOIN wp_usermeta
ON ( wp_users.ID = wp_usermeta.user_id )
WHERE 1=1
AND ( ( ( ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"edit\\_posts\"%' )
OR ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"administrator\"%' )
OR ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"editor\"%' )
OR ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"author\"%' )
OR ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"contributor\"%' )
OR ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"wpseo\\_manager\"%' )
OR ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"wpseo\\_editor\"%' ) ) ) )
ORDER BY display_name ASC

This is executed by the quick edit code and runs while dispalys the posts or pages admin screens. Responsible is wp_dropdown_users().

Therefore we tried to use ElasticPress to speed up this call:

/**
 * Use ElasticPress for very slow user queries (takes 4 - 8 seconds on prod!).
 */
add_filter('wp_dropdown_users_args', function ($query_args, $r) {
    //enable ElasticPress
    $query_args['ep_integrate'] = true;

    return $query_args;
}, 10, 2);

However, this is not yet supported for the new features introduced in WordPress 5.9.

Describe the solution you'd like

Support the capability fields:

• capability
• capability__in
• capability__not_in

More details are listed here:

https://make.wordpress.org/core/2022/01/05/new-capability-queries-in-wordpress-5-9/

Designs

Describe alternatives you've considered

Additional context

[felipeelia]

Thanks @cbratschi, this would be a good addition to the plugin. For reference, we would need another block similar to what we have here. Would you be interested in crafting a PR with that?

[cbratschi]

Hi @felipeelia, I will have a look how to integrate this. A problem could be that the WordPress 5.9 implementation scans through serialized meta_value values. There is probably a better way for an ElasticSearch query.

[cbratschi]

As a first workaround we convert the capability items to roles. This is a partial workaround which covers our use cases but ignores user specific capabilities where both the role and capability have to be looked for.

/**
 * Use ElasticPress for very slow user queries (takes 4 - 8 seconds on prod!).
 */
add_filter('wp_dropdown_users_args', function ($query_args, $r) {
    //enable ElasticPress (Note: capability filed not yet supported by ElasticPress)
    $query_args['ep_integrate'] = true;

    //convert capability
    $capability = $query_args['capability'] ?? null;

    if (!empty($capability)) {
        //usually 'edit_posts'

        //get capabilities
        $capabilities = [];

        if (is_array($capability)) {
            $capabilities = $capability;
        } elseif (is_string($capability)) {
            $capabilities = array_map('trim', explode(',', $capability));
        }

        //get roles
        global $wp_roles;

        $blog_id = 0;

        if (isset($query_args['blog_id'])) {
            $blog_id = absint($query_args['blog_id']);
        }

        $wp_roles->for_site($blog_id);
        $available_roles = $wp_roles->roles;

        //convert to roles
        $roles = [];

        foreach ($available_roles as $role => $role_data) {
            $role_caps = array_keys(array_filter($role_data['capabilities']));

            foreach ($capabilities as $cap) {
                if (in_array( $cap, $role_caps, true)) {
                    $roles[] = $role;
                    break;
                }
            }
        }

        //Note: in our case filtering roles is enough and performs much better (we are not using per user capabilities)
        unset($query_args['capability']);
        $query_args['role__in'] = array_unique($roles);
    }

    //debug
    //tb_debug($query_args);

    return $query_args;
}, 10, 2);

This takes now just about 120 ms instead of many seconds.

The implementation in ElasticPress should be very similar to the roles queries.

[oscarssanchez]

Right now the major issue that I find is https://github.com/10up/ElasticPress/blob/develop/includes/classes/Indexable/User/User.php#L834

We are defining capabilities as a field in the user mapping, but we are actually storing user roles. We might need to rewrite the role__in support as well if we want to have two separate fields, one for capabilities and another one for roles. We should also have caution to test when on multisite and also document that this won't work for capabilities not stored in the database.

[felipeelia]

As that would mean a change in the mapping, it will probably be something for a 5.0 version (as changes in the mapping are breaking changes.)
As WordPress 6.0 will also introduce a change in the fields parameter (see here) we could make a release focused on this feature.

ps.: We also need to check if the changes outlined in this article will affect ElasticPress somehow.

[cbratschi]

@felipeelia my patch above is a workaround for those performance problems listed in the article. We didn't see any other affected admin screens and therefore recommend anyone affected to use this code. Good to see they add some workarounds to WordPress core too.