Users and Groups / Search groups and display number of users in each group

[mcrio]

Hi,

Scenario: Search groups by group name and display the number of users assigned to each group search result.
Seems trivial but for some reason, I'm stuck generating a proper index for this requirement.

A user can belong to one or more groups. As it’s expected that a user may belong just to a few groups, the User holds an array of group ids.

User:

public class User {
    public string Id {get;set;}
    public string Name {get;set;}
    public string[] Groups {get;set;}
    ...
}

public class Group {
     public string Id {get;set;}
     public string Name {get;set;}
     ….
}

Requirement: Search for groups by name (full-text) and display the number of users assigned when displaying the search result:

Search result:

Group name	Num. of users in group
Administrator	2
Editor	12

I’m not really sure how to craft the index to include the number of users assigned.

One approach is to make a Map-Reduce index on the User entity which would calculate the number of users assigned but there are some questions coming up:

• I need the Group document but the Map Reduce index will be on the User entity
• I need to retrieve the whole Group document and additionally the calculated number of users assigned

[aventus13]

Indeed, what you need is a map-reduce index. I recommend learning about this type of an index as it's a very powerful tool.

    public class Users : AbstractIndexCreationTask<User, Users.Result>
    {
        public class Result
        {
            public string GroupName { get; set; }
            public int Count { get; set; }
        }

        public Users()
        {
            Map = users => users.SelectMany(u => u.Groups
                .Select(g => new Result
                {
                    GroupName = LoadDocument<Group>(g).Name,
                    Count = 1
                }));

            Reduce = results => results.GroupBy(r => r.GroupName)
                .Select(g => new Result
                {
                    GroupName = g.Key,
                    Count = g.Sum(x => x.Count)
                });
        }
    }

Here's equivalent of the above if you prefer a query syntax

            Map = users => from user in users
                           from groupId in user.Groups
                           select new Result
                           {
                               GroupName = LoadDocument<Group>(groupId).Name,
                               Count = 1
                           };

            Reduce = results => from result in results
                                group result by result.GroupName into g
                                select new Result
                                {
                                    GroupName = g.Key,
                                    Count = g.Sum(x => x.Count)
                                };

Then you can query it like so

var usersInGroups = session.Query<Users.Result, Users>();

// or

var usersInTest = session.Query<Users.Result, Users>().Where(r => r.GroupName == "test");

Please note that this index loads related documents, which may incur performance overhead in some cases. Nevertheless it is probably the best way if you want to execute the query database-side in one request.

[mcrio]

Thanks for the example. Yeah, that's a good direction, and to get the Group documents as part of the same query, I should be able to use Include() which for some reason I forgot about :)

As we are searching for Groups and not users we actually need a multi-map reduce index in order to be able to index groups that have no users assigned.

(final question is below the implementation)

Requirments:

public class User {
    public string TenantId {get;set;}
    public string Id {get;set;}
    public string[] Groups {get;set;}
    ...
}

public class Group {
     public string TenantId {get;set;}
     public string Id {get;set;}
     public string Name {get;set;}
     public string Abbreviation { get; set; }
     public bool IsActive {get;set;}
     ….
}

• Search groups by by group name or abbreviation using full-text search
• Filter by group active state
• Order results by group Name
• In search result display basic group info with number of users assigned

Multi-Map Reduce index

public class Groups_Search : AbstractMultiMapIndexCreationTask<Groups_Search.Result>
{
    public Groups_Search()
    {
        AddMap<User>(
            users => from user in users
                let groups = user.Groups.Select(LoadDocument<Group>)
                from gr in groups
                select new Result
                {
                    TenantId = gr.TenantId,
                    GroupId = gr.Id,
                    GroupName = gr.Name,
                    GroupActive = gr.IsActive,
                    GroupQuery = new[]
                    {
                        gr.Abbreviation,
                        gr.Name,
                    },
                    AssigneesCount = 1,
                }
        );

        AddMap<Group>(
            groups => from gr in groups
                select new Result
                {
                    TenantId = gr.TenantId,
                    GroupId = gr.Id,
                    GroupName = gr.Name,
                    GroupActive = gr.IsActive,
                    GroupQuery = new[]
                    {
                        gr.Abbreviation,
                        gr.Name,
                    },
                    AssigneesCount = 0,
                }
        );

        Reduce = results => from result in results
            group result by new
                { result.TenantId, result.GroupId, result.GroupActive, result.GroupName, result.GroupQuery }
            into g
            select new Result
            {
                TenantId = g.Key.TenantId,
                GroupId = g.Key.GroupId,
                GroupName = g.Key.GroupName,
                GroupActive = g.Key.GroupActive,
                GroupQuery = g.Key.GroupQuery,
                AssigneesCount = g.Sum(x => x.AssigneesCount),
            };

        Index(result => result.GroupQuery, FieldIndexing.Search);
    }

    public class Result
    {
        public string TenantId { get; init; }

        public string GroupName { get; init; }

        public string GroupId { get; init; }

        public bool GroupActive { get; init; }

        public string[] GroupQuery { get; init; }

        public int AssigneesCount { get; init; }
    }
}

Simple query

var query = "editor";
var results = _documentSession
    .Query<Groups_Search.Result, Groups_Search>()
    .Include(result => result.GroupId)
    .Search(
        result => result.GroupQuery,
        $"{query}*",
        @operator: SearchOperator.And
    )
    .OrderBy(memento => memento.GroupName)
    .Take(...)
    .Skip(....)
    .Select(....)
    .ToListAsync();

[Question]
Do you see any problems with this kind of map-reduce where we group by many properties including a string array ?

[aventus13]

Yes I think this is fine, although someone from the Raven team may want to weigh in and suggest some performance improvements (if any). The only thing that I may change is your grouping- I don't think it's necessary to group by all those different fields, surely all you need is to group by the GroupId?

group result by new
                { result.TenantId, result.GroupId, result.GroupActive, result.GroupName, result.GroupQuery }

// vs

group result by result.GroupId

[mcrio]

You are right, I can just group by GroupId as all other properties except AssigneesCount are the same so they can be extracted from the group like:

select new Result
{
    TenantId = g.First().TenantId,
    GroupId = g.Key.GroupId,
    GroupName = g.First().GroupName,
    GroupActive = g.First().GroupActive,
    GroupQuery = g.First().GroupQuery,
    AssigneesCount = g.Sum(x => x.AssigneesCount),
};

... so the question about performance and all these groupings becomes obsolete. 👍