Skip to content

Instantly share code, notes, and snippets.

@eddiemoya
Created September 7, 2012 21:16
Show Gist options
  • Save eddiemoya/3669722 to your computer and use it in GitHub Desktop.
Save eddiemoya/3669722 to your computer and use it in GitHub Desktop.
Get users by role with taxonomy sql
SELECT DISTINCT
u.ID,
u.user_login,
u.user_nicename,
u.user_email,
u.display_name,
m2.meta_value as role,
GROUP_CONCAT(DISTINCT m.meta_value) AS terms
FROM wp_users as u
LEFT JOIN wp_usermeta AS m
ON u.ID = m.user_id
AND m.meta_key = "um-taxonomy-category"
JOIN wp_usermeta AS m2
ON u.ID = m2.user_id
AND m2.meta_key = 'wp_capabilities'
AND m2.meta_value REGEXP 'expert|author|editor|administrator'
GROUP BY u.ID
ORDER BY m.meta_key
@eddiemoya
Copy link
Author

To exclude users who do no have any taxonomy assigned to them, change the LEFT JOIN on line 10 to a normal JOIN

@eddiemoya
Copy link
Author

To filter users by a specific term or set of terms, add the follwing immediately below line #12:

AND m.meta_value IN (1, 2, 3)

1, 2, 3 being the term_id's for the terms to filter by.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment