[wp-hackers] select all users with role X

[Roy Schestowitz]

_____/ On Tue 27 Dec 2005 03:22:20 GMT, [Aaron Brazell] wrote : \_____

> On 12/26/05, Scott Merrill <skippy at skippy.net> wrote:
>>
>> I'd like to send an email to all the users with the Administrator role.
>>
>> Since the roles are stored as a serialized array in the usermeta table,
>> I need to iterate over the entire user table to find all the
>> administrators.  I _could_ cheat for the time being and use the
>> deprecated wp_user_level value, but that's just avoiding the issue.
>>
>> In many circumstances, iterating over the user table won't be a big
>> deal, because the number of registered users is so small.  This is,
>> historically, because there is very little value for readers to become
>> registered users.  The newest version of my subscribe2 plugin is likely
>> to change that, and I suspect that some of my users are going to see
>> some reasonably large user lists.  Iterating over these to parse each
>> array of roles seems like a real waste of an RDBMS.
>>
>> Short of re-working the roles and capabilities system, does anyone have
>> any tricks up their sleeves that might help?
>>
>> Thanks,
>> Scott
>>
>
> Hmmmm... without iterating over the user table? Maybe create an item in
> the options table with an array of user ids updated whenever a user is
> added to the admin group?  Would probably require a one time user table
> scan when the plugin is installed to get all current admins and could be
> added to after that.
>
> I almost think iterating over the entire table would be more worthwhile,
> but I don't know.

As Robert pointed out, hanging bits off the core is rarely the 
desirable option.
I don't know particular database systems, but the correct thing  is to do a
table join of all users and then apply a mask. In this case, it can even be
bitwise. I don't know how/if you can do this in MySQL. Here is a very
simplified example:


|user-id|role|email|
|0000000|0011|10000|
|0000001|0011|01011|
|0000010|0010|01101|
|0000011|0100|01010|
|0000100|0011|01000|
|0000101|0010|01111|

Assuming administrator corresponds to level 8, apply XOR mask to role:

0100

If 0, you found an administrator, so get fetch from the table.

Roy