How to get assigned groups for a site and list of members in a group from Database query

[abojja]

We need to provide list of assigned groups for a particular site and list of members of a group, Can you please provide query to get that information from Database?

[angelborroy]

Why to use a DB query?

It should be easier to use the REST API.

For instance, for site "swsdp"...

curl -X GET "http://localhost:8080/alfresco/api/-default-/public/alfresco/versions/1/sites/swsdp/members?skipCount=0&maxItems=100"

... that includes all the information in the response:

{
  "list": {
    "pagination": {
      "count": 3,
      "hasMoreItems": false,
      "totalItems": 3,
      "skipCount": 0,
      "maxItems": 100
    },
    "entries": [
      {
        "entry": {
          "role": "SiteManager",
          "person": {
            "firstName": "Administrator",
            "capabilities": {
              "isGuest": false,
              "isAdmin": true,
              "isMutable": true
            },
            "displayName": "Administrator",
            "emailNotificationsEnabled": true,
            "company": {},
            "id": "admin",
            "enabled": true,
            "email": "admin@alfresco.com"
          },
          "id": "admin",
          "isMemberOfGroup": false
        }
      },
      {
        "entry": {
          "role": "SiteCollaborator",
          "person": {
            "lastName": "Beecher",
            "userStatus": "Helping to design the look and feel of the new web site",
            "capabilities": {
              "isGuest": false,
              "isAdmin": false,
              "isMutable": true
            },
            "displayName": "Alice Beecher",
            "jobTitle": "Graphic Designer",
            "statusUpdatedAt": "2011-02-15T20:20:13.432+0000",
            "mobile": "0112211001100",
            "emailNotificationsEnabled": true,
            "description": "Alice is a demo user for the sample Alfresco Team site.",
            "telephone": "0112211001100",
            "enabled": false,
            "firstName": "Alice",
            "skypeId": "abeecher",
            "avatarId": "198500fc-1e99-4f5f-8926-248cea433366",
            "location": "Tilbury, UK",
            "company": {
              "organization": "Moresby, Garland and Wedge",
              "address1": "200 Butterwick Street",
              "address2": "Tilbury",
              "address3": "UK",
              "postcode": "ALF1 SAM1"
            },
            "id": "abeecher",
            "email": "abeecher@example.com"
          },
          "id": "abeecher",
          "isMemberOfGroup": false
        }
      },
      {
        "entry": {
          "role": "SiteManager",
          "person": {
            "lastName": "Jackson",
            "userStatus": "Working on a new web design for the corporate site",
            "capabilities": {
              "isGuest": false,
              "isAdmin": false,
              "isMutable": true
            },
            "displayName": "Mike Jackson",
            "jobTitle": "Web Site Manager",
            "statusUpdatedAt": "2011-02-15T20:13:09.649+0000",
            "mobile": "012211331100",
            "emailNotificationsEnabled": true,
            "description": "Mike is a demo user for the sample Alfresco Team site.",
            "telephone": "012211331100",
            "enabled": false,
            "firstName": "Mike",
            "skypeId": "mjackson",
            "avatarId": "3fbde500-298b-4e80-ae50-e65a5cbc2c4d",
            "location": "Threepwood, UK",
            "company": {
              "organization": "Green Energy",
              "address1": "100 Cavendish Street",
              "address2": "Threepwood",
              "address3": "UK",
              "postcode": "ALF1 SAM1"
            },
            "id": "mjackson",
            "email": "mjackson@example.com"
          },
          "id": "mjackson",
          "isMemberOfGroup": false
        }
      }
    ]
  }
}