Problem
After creating a user with a certain role I need to do a custom sql query where it only selected users of a specific role. This failed to find anything:
select u.ID, u.display_name, u.user_login
from $wpdb->users as u
inner join $wpdb->usermeta as umr
on ( umr.user_id = u.ID )
and ( umr.meta_key = 'wp_capabilities' )
$user = get_userinfo( $user_id );
var_dump( $user->roles );
get_userinfo got the right role, so saving was correct.
$caps = get_user_meta( $user_id, 'wp_capabilities' );
var_dump( $caps );
$caps was null. weird.
Solution
You learn something new everyday. After looking through the WordPress core code I found that the meta key is actually composed of the table prefix and ‘capabilities’. So the correct query would be:
select u.ID, u.display_name, u.user_login
from $wpdb->users as u
inner join $wpdb->usermeta as umr
on ( umr.user_id = u.ID )
and ( umr.meta_key = '" . $wpdb->prefix . "capabilities' )