Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

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

Avatar

Level 1

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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"

View solution in original post

4 Replies

Avatar

Community Advisor

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

Avatar

Level 1

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

Avatar

Correct answer by
Community Advisor

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"