How can I get all the groups a user belongs to using SQL-2 | Community
Skip to main content
isrhaels5043423
isrhaels5043423
September 3, 2018
Solved

How can I get all the groups a user belongs to using SQL-2

  • September 3, 2018
  • 4 replies
  • 2221 views

I created the following query, but I can not make it work.

SELECT user.*, group.* FROM [rep:User] AS user 
INNER JOIN [rep:Group] AS group 
ON ISDESCENDANTNODE(user, [/home/users])
WHERE user.[jcr:uuid] IN (group.[rep:members])
and user.[rep:principalName] = 'usertest'

I get the following error: expected: static operand.

Regards,

Isrhael

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by arunpatidar

No worries, try below:

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

4 replies

arunpatidar
Community Advisor
arunpatidarCommunity Advisor
Community Advisor
September 3, 2018

Hi,

You can do it without SQL-2.

with ResourceResolver

request.getResourceResolver().adaptTo(User.class).memberOf();

aem63app-repo/SimpleGetGroup.java at master · arunpatidar02/aem63app-repo · GitHub

Arun Patidar
isrhaels5043423
isrhaels5043423Author
September 3, 2018

Thank you very much, but it is a requirement that is using SQL-2.

arunpatidar
Community Advisor
arunpatidarCommunity AdvisorAccepted solution
Community Advisor
September 3, 2018

No worries, try below:

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

Arun Patidar
isrhaels5043423
isrhaels5043423Author
September 3, 2018

This worked very well for me! Thank you!

Terms & ConditionsAccessibility statement

Loading footer...