SQL2 query to list all users in a group

[KhalidMomin1]

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"

[arunpatidar]

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();
  }
}

[KhalidMomin1]

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

Thanks

[Jagadeesh_Prakash · Accepted solution]

@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

[Himanshu_Singhal]

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[]. 



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