I want to list down the groups which does not associate with any USERS, Brand Folders directly or indirectly.

Question

Hi Team,I want to list down all the groups which does not associate with any USERS, Brand Folders directly or indirectly on my AEM 6.5, Can you Please suggest any sql 2 query. Thanks,User415

Kamal_Kishor · Accepted Answer

@tkhan415 : Yes, everything that we do in query builder can be converted to backend code.

    // create query description as hash map (simplest way, same as form post)
    Map<String, String> map = new HashMap<String, String>();

// create query description as hash map (simplest way, same as form post)
//I have done this with static values for demo, the values and property names can be dynamic as well.
    map.put("path", "/home/groups");
    map.put("1_property", "jcr:primaryType");
    map.put("1_property", "rep:Group");
    map.put("2_property", "rep:members");
    map.put("2_property.operation", "not");

    map.put("p.limit", "-1"); // -1, gives all the matching results, if you set a postive value, it will return that many results

    Query query = builder.createQuery(PredicateGroup.create(map), session);
    SearchResult result = query.getResult();

You can refer this example and will be able to get an idea on how to do it: https://experienceleague.adobe.com/docs/experience-manager-65/content/implementing/developing/platform/query-builder/querybuilder-api.html?lang=en#example-query-builder-api-usage

thanks.

Kamal_Kishor · Answer

@tkhan415 : Please try with below in (assuming you are doing this locally, else replace the localhost and port) http://localhost:4502/libs/cq/search/content/querydebug.html

List of groups who are not associated with any other user or group.

path=/home/groups
1_property=jcr:primaryType
1_property.value=rep:Group
2_property=rep:members
2_property.operation=not
p.limit=-1

List of groups who are associated with any specific user or group

path=/home/groups
1_property=jcr:primaryType
1_property.value=rep:Group
2_property=rep:members
2_property.value=<jcr:uuid of specific user or group>
p.limit=-1

Sign up

Login with SSO

Login to the community

Login with SSO