How to find paginated list of all users who belongs to the given group

[SharifHa]

We have a requirement of building functionality to show paginated list of users by the given filter criteria. Some of the filter criteria we have already achived by directly querying users from /home/users.

We are facing difficulty in two filter criteria

1) Find user by group name

2) Find user by directory read access.

We are unable to create query or anything that can support this requirement and returns paginated list of users. Following is our code so far.

StringBuilder queryBuilder = new StringBuilder("SELECT * FROM [rep:User] AS u WHERE ISDESCENDANTNODE(u, [/home/users])");

if (StringUtils.isNotBlank(criteria.getUserId())) {
    queryBuilder.append(" AND (u.[rep:principalName] LIKE \"%").append(criteria.getUserId().trim()).append("%\")");
}
if (StringUtils.isNotBlank(criteria.getUserMail())) {
    queryBuilder.append(" AND (u.[profile/email] LIKE \"%").append(criteria.getUserMail().trim()).append("%\")");
}
if (StringUtils.isNotBlank(criteria.getCompanyName())) {
    queryBuilder.append(" AND (u.[profile/companyName] LIKE \"%").append(criteria.getCompanyName().trim()).append("%\")");
}
if(StringUtils.isNotBlank(criteria.getGroup())){
    //TODO: filter by group
}


String query = queryBuilder.toString();
ResourceResolver resolver = resourceResolverService.getWritableResourceResolver();
Session readingSession = resolver.adaptTo(Session.class);
QueryManager qm = readingSession.getWorkspace().getQueryManager();
Query q = qm.createQuery(query, Query.JCR_SQL2);
q.setLimit(10);
q.setOffset(0);
QueryResult result = q.execute();

[giuseppebag]

hi @SharifHa, I tested your query, and I was able to get some results by using single quotes instead of double quotes to enclose the path and values. 

SELECT * FROM [rep:User] AS u WHERE ISDESCENDANTNODE(u, '/home/users')
AND u.[rep:principalName] LIKE '%admin%'
AND u.[profile/email] LIKE '%example.com%'
AND u.[profile/companyName] LIKE '%ACME%'

You can see the results in the image below. (I changed "AND" to "OR," though):

[SharifHa]

I need to write a query based on the given group name. I’m not sure how your query applies the filter by group—could you kindly recheck it?

[giuseppebag · Accepted solution]

I think there is no direct JCR SQL2 query that can fetch all users of a group in one query because the membership is stored as references on the group. But you can use the User Management API to retrieve a user given the group name, here's a Java code snippet (to be verified thoroughly) that you can tweak according to your need:

import java.util.Iterator;
import javax.jcr.RepositoryException;
import org.apache.jackrabbit.api.security.user.Authorizable;
import org.apache.jackrabbit.api.security.user.Group;
import org.apache.jackrabbit.api.security.user.User;
import org.apache.jackrabbit.api.security.user.UserManager;
import org.apache.sling.api.resource.ResourceResolver;



/**
 * Finds and returns a list of users in a given group.
 * @Param resourceResolver The AEM ResourceResolver instance.
 * @Param groupName The name of the user group (e.g., "administrators").
 * @Return A list of user IDs in the group.
 */
public List<String> findUsersInGroup(ResourceResolver resourceResolver, String groupName) {
    List<String> userIds = new ArrayList<>();
    
    try {
        // Get the UserManager from the ResourceResolver
        UserManager userManager = resourceResolver.adaptTo(UserManager.class);
        
        // Get the Authorizable for the specified group name
        Authorizable authorizable = userManager.getAuthorizable(groupName);

        // Check if the authorizable is a group
        if (authorizable != null && authorizable.isGroup()) {
            Group group = (Group) authorizable;
            
            // Iterate through all members of the group
            Iterator<Authorizable> members = group.getMembers();
            while (members.hasNext()) {
                Authorizable member = members.next();
                
                // Check if the member is a user (and not another group)
                if (!member.isGroup()) {
                    userIds.add(member.getID());
                }
            }
        }
    } catch (RepositoryException e) {
        // Log or handle the exception appropriately
        // e.g., LOG.error("Error finding users in group: {}", e.getMessage(), e);
    }
    return userIds;
}

[SharifHa]

Thank you for your prompt response. We have considered your suggestion and decided to proceed with filtering in Java for now. We will continue our research to explore whether we can identify a more efficient solution.