Posted 2 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
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:
https://core.trac.wordpress.org/ticket/10201
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/
Posted 2 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).
This topic has been closed to new replies.
