Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

SQL2 query to list all users in a group

Avatar

Level 2

How to get the list of all users in a group using SQL2 query?

I tried this:

select * from [rep:User] as user inner join [rep:Group] as group on user.[jcr:uuid] = group.[rep:members] where group.[rep:principalName]="myGroup"

 

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor
4 Replies

Avatar

Community Advisor

Hi,

You can do it this without a query.

 

Session session = getSubserviceSession();
JackrabbitSession jcrSession = (JackrabbitSession) session;
UserManager uM = jcrSession.getUserManager();
Group group = (Group) uM.getAuthorizable("my-group");
terator<Authorizable> itr = group.getMembers();
while (itr.hasNext()) {
  Object obj = itr.next();
  if (obj instanceof User) {
    User user = (User) obj;
    String uid = user.getID();
  }
}


Arun Patidar

Avatar

Level 2

I am looking for SQL2 query to generate user and group reports.

Thanks

Avatar

Correct answer by
Community Advisor

@KhalidMomin1  I recommend to try the "Users to CSV Exporter" of ACS AEM Commons instead

 

https://adobe-consulting-services.github.io/acs-aem-commons/features/exporters/users/index.html

 

Avatar

Community Advisor

Hi @KhalidMomin1 
Getting group members via SQL2 query won't be possible because it doesn't store the actual path of members. Instead it does store as WeakReference[]. 

Screenshot 2023-01-10 at 14.56.47.png

As you can see, property holds the members references and not actual path and there's no way using SQL2 query to basically iterate and get each reference absolute path even using inner join IMO. So, the possible way is what described by @arunpatidar 

Best regards,
Himanshu Singhal