[wp-hackers] select all users with role X

[Owen Winkler]

Ryan Boren wrote:
> Someone could propose a schema and implement a replacement WP_User class
> for everyone to argue over.  We could abstract these queries with
> get_users_with_role() and get_users_with_cap() while we're at it.

Rather than creating a whole new table mess (this wasn't actually 
suggested, was it?), just store the capabilities in multiple rows 
instead of a serialized array.

SELECT * FROM wp_usermeta WHERE meta_key = 'wp_caps_granted';
SELECT * FROM wp_usermeta WHERE meta_key = 'wp_caps_denied';

Any plugin that implements caching/selecting users based on role could 
use this schema.  Hopefully it could be tested and adopted, since it's a 
bit more flexible.

> In the meantime, plugins can check what levels are in a particular role
> and query the user's level key for those levels.

Use a regex query on the usermeta value field to search for the 
capability required.  Like this:

SELECT user_id FROM wp_usermeta WHERE meta_key = 'wp_capabilities' AND 
meta_value RLIKE '[[:<:]]administrator[[:>:]]';

Be sure to verify that the users returned actually DO have the role 
requested.  Note that there are a bunch of "wp_"s in the query that need 
to be replaced with the database prefix.

Alternatively, the code that creates the user list page in the WordPress 
admin could also be used to sift out the users having specific roles, 
although it is not very efficient, since it's designed to get all users 
and their roles.

Owen