Search users by roles and/or capabilities (4 posts)

  1. Lucas Martins
    Posted 7 years ago #

    I'm looking for a way to search users with a certain role or capability, but this information is stored serialized in the database, and build a query using "like" would take too much time in a large database.

    I dont want to retrieve the entire user table and then check each one. Any ideas?

    thank you

  2. MichaelH
    Posted 7 years ago #

    Not what you wanted but I'm not sure there is something to meet your criteria:

    Return users of the editor role
    SELECT wpusers.*
    FROM wp_users wpusers, wp_usermeta wpusermeta
    WHERE wpusers.ID = wpusermeta.user_id
    AND wpusermeta.meta_key = 'wp_capabilities'
    AND wpusermeta.meta_value RLIKE '[[:<:]]editor[[:>:]]'
    ORDER BY wpusers.user_nicename ASC

    Also note changes in the works:

  3. anmari
    Posted 7 years ago #

    My plugin recently launched at the http://weblogtoolscollection.com/pluginblog/2009/07/31/amr-users-plugin-for-user-lists-and-reporting/
    ia aimed at exactly that sort of problem.

    It is a fairly "fresh" plugin, tested, but not yet on very large volumes, so I'd like to know how it goes - I had some ideas for cacheing if there was a problem.

    It is very configurable and I'd be intereseted to see what you think. If it is useful to you, please rate it at the weblogtoolscollection.com site and at wordpress http://wordpress.org/extend/plugins/amr-users/

  4. semperos
    Posted 6 years ago #

    Can anyone provide a good explanation as to why roles/capabilities are stored as a serialized string in the DB? Or point me to a link where that discussion has occurred already?

    I see the trac link above, but it's not quite satisfactory as to why such things were ever serialized in the DB instead of keeping a clear, query-able numeric id (like user_level).

Topic Closed

This topic has been closed to new replies.

About this Topic