We have a lot of custom member groups. I need to find all members who are not in Group1, Group2, or Group3 (those are just example names). I have spent some time in the database and I can query for members and then use the cmsMember2MemberGroup table to find the ID of a member's groups based on the member ID and then sort of deduce what the ID of a group is, but that is getting laborious. Where in the database are the group names and their IDs stored? Also, is there a better way to do this? It's basically for reporting to someone, so I'm not trying to write code that will be used on a web page: just a query that will spit out some data, like "tell me what members are in Group1".
These are kind of the tables you are joining together and need to play around with, this query will give you all the Member Groups and their Members:
SELECT mmg.MemberGroup as 'MemberGroupId', un.[text] AS 'MemberGroupName', mmg.Member as 'MemberId', m.LoginName AS 'Login Name', unm.[text] AS 'Member Name'
FROM cmsMember2MemberGroup mmg
INNER JOIN umbracoNode un ON un.id = mmg.MemberGroup
INNER JOIN cmsMember m ON m.nodeId = mmg.Member
INNER JOIN umbracoNode unm ON unm.id = m.nodeId
So to filter this list to only show people who aren't in Group1, Group2 or Group3 then something like this might work:
SELECT mmg.MemberGroup as 'MemberGroupId', un.[text] as 'MemberGroupName', mmg.Member as 'MemberId', m.LoginName as 'Login Name', unm.[text] as 'Member Name'
FROM cmsMember2MemberGroup mmg
INNER JOIN umbracoNode un ON un.id = mmg.MemberGroup
INNER JOIN cmsMember m ON m.nodeId = mmg.Member
INNER JOIN umbracoNode unm ON unm.id = m.nodeId
WHERE mmg.MemberGroup NOT IN (SELECT id FROM umbracoNode WHERE [text] in ('GROUP1','GROUP2','GROUP3') AND nodeObjectType ='366E63B9-880F-4E13-A61C-98069B029728')
ORDER BY un.[text]
You can put the names of your groups to exclude - in that 'inner Select statement, where I have GROUP1, GROUP2 etc
But hopefully basically all you really needed to know is the umbracoNode table is the one that contains the names of things that display in Umbraco and can jiggle this around for your needs
How can I query for member group types?
Hello,
We have a lot of custom member groups. I need to find all members who are not in Group1, Group2, or Group3 (those are just example names). I have spent some time in the database and I can query for members and then use the cmsMember2MemberGroup table to find the ID of a member's groups based on the member ID and then sort of deduce what the ID of a group is, but that is getting laborious. Where in the database are the group names and their IDs stored? Also, is there a better way to do this? It's basically for reporting to someone, so I'm not trying to write code that will be used on a web page: just a query that will spit out some data, like "tell me what members are in Group1".
Thanks!
Jeremy
EDIT:
I did see the SO thread at https://stackoverflow.com/questions/14344364/where-are-the-member-properties-stored-in-umbraco/14612781#:~:text=2%20Answers&text=The%20member%20properties%20are%20stored,the%20id%20of%20the%20member., but that query doesn't return group names.
Hi Jeremy
These are kind of the tables you are joining together and need to play around with, this query will give you all the Member Groups and their Members:
So to filter this list to only show people who aren't in Group1, Group2 or Group3 then something like this might work:
You can put the names of your groups to exclude - in that 'inner Select statement, where I have GROUP1, GROUP2 etc
But hopefully basically all you really needed to know is the umbracoNode table is the one that contains the names of things that display in Umbraco and can jiggle this around for your needs
regards
Marc
This looks great! I can't wait to give it a shot. I'll let you know what happens. Thanks!
is working on a reply...