Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Jeremy Coulson 61 posts 143 karma points
    Dec 16, 2020 @ 14:47
    Jeremy Coulson
    0

    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.

  • Marc Goodson 1733 posts 11490 karma points MVP 7x c-trib
    Dec 16, 2020 @ 17:17
    Marc Goodson
    0

    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:

    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

    regards

    Marc

  • Jeremy Coulson 61 posts 143 karma points
    Dec 16, 2020 @ 21:54
    Jeremy Coulson
    0

    This looks great! I can't wait to give it a shot. I'll let you know what happens. Thanks!

Please Sign in or register to post replies

Write your reply to:

Draft