Get multiple users with meta value in one query and populate WP_User class

Question

Hopefully someone can help me with this.

When you load multiple users with get_users() (let's say 100+) WP triggers the user meta query for every single user. This triggers 101 queries (main user query + meta query for each user).

The thing is, I only need 1 meta value from the database (capabilities for the current blog) and I see that the WP_User class doesn't use any of the other data for role and capability management.

I already have the SQL ready, no problemo. But does anyone know how to populate the WP_User class while bypassing the default meta query?

(Off course this will need to be compatible with any stuff that happens afterwards by other plugins)

See wp-includes/class-wp-user.php

Default callstack for WP_User (when passing a $wpdb->get_results() user object):

WP_User->_construct
WP_User->init()
WP_User->for_blog()
WP_User->_init_caps()
    - get_user_meta()
        - update_meta_cach()
            - query :-(

For completeness, my current SQL:

SELECT {$wpdb->users}.*, {$wpdb->usermeta}.meta_value FROM {$wpdb->users} 
LEFT JOIN {$wpdb->usermeta} ON {$wpdb->users}.ID = {$wpdb->usermeta}.user_id
WHERE {$wpdb->usermeta}.meta_key = '{$wpdb->get_blog_prefix()}capabilities'
ORDER BY {$wpdb->users}.display_name

Any help greatly appreciated!

Answer 1

For anyone interested.

Got it solved using the get_user_metadata filter to short circuit the metaquery and cache loading.

Steps:

• Run query
• Store result data in the class property
• Add get_user_metadata filter
• Start foreach loop on results
• Trigger new WP_User( $result_value ) for each result
• Within the filter function, validate meta key on $wpdb->get_blog_prefix() . 'capabilities'.
• Return the unserialized value from the capabilities meta data
• After the loop, remove the get_user_metadata filter again
• Done!

Full query (slightly modified)

SELECT {$wpdb->users}.*, {$wpdb->usermeta}.meta_value as roles FROM {$wpdb->users} 
LEFT JOIN {$wpdb->usermeta} ON {$wpdb->users}.ID = {$wpdb->usermeta}.user_id
WHERE {$wpdb->usermeta}.meta_key = '{$wpdb->get_blog_prefix()}capabilities'
ORDER BY {$wpdb->users}.display_name

This will return all the values from the users table + an extra column named roles with the meta_value for TABLEPREFIX_capabilities

There, one single query to load a bulk of users and their capabilities/roles.