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

Question

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 criteria1) Find user by group name2) 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();

giuseppebaglio · Accepted Answer

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. * @90521 resourceResolver The AEM ResourceResolver instance. * @90521 groupName The name of the user group (e.g., "administrators"). * @2007960 A list of user IDs in the group. */public List findUsersInGroup(ResourceResolver resourceResolver, String groupName) { List 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 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;}

giuseppebaglio · Answer

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):

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded