Store aggregation results from reduce index to Documents

[giannik]

Currently in orchard there is a UserByRoleNameIndex that stores the number of users per role.
I would like to show in the roles list in the admin a badge with the number of users per role.
But I have to change the entire query from using the document to using the reduce index query.

Is there some way or hook or event to store the aggregation result inside the role document so i can just grab it from the document and show it in the UI. Since it inherits from an IEntity which is a JObject I can just add anything I want in there.
But it needs to be updated anytime the reduce index is changed.
Is there any way to acheive this ?

Originally post on the yesSQL repo but asking here since I wish to use it within orchardcore
sebastienros/yessql#446

[Skrypt]

You need to create a method "if it doesnt already exist" that will retrieve the data from this UserByRoleNameIndex and that will cache it on server side. Then you call a second method GetUserCountByRole(string roleName).

[giannik]

I dont want to cache something on the server . I want to store in the document the aggregation results per role .
The question is when and where in the lifecycle of the reduce index do i perform the retreival and storage.
Isnt the whole purpose of the document to store whatever information you want per document to avoid making excessive queries?

[giannik]

thanks @Skrypt Im just looking for something simple so that I dont have to change my entire code base just to show the count of users per role or count of blog posts per blog.

[Skrypt]

Simple would be to use a SQL Query from the Admin UI.

[Skrypt]

Here there is a method that already exists :

OrchardCore/src/OrchardCore/OrchardCore.Users.Core/Services/UserStore.cs

Lines 453 to 462 in 9c8f94f

	public async Task<IList<IUser>> GetUsersInRoleAsync(string normalizedRoleName, CancellationToken cancellationToken)
	{
	if (string.IsNullOrEmpty(normalizedRoleName))
	{
	throw new ArgumentNullException(nameof(normalizedRoleName));
	}
	var users = await _session.Query<User, UserByRoleNameIndex>(u => u.RoleName == normalizedRoleName).ListAsync();
	return users == null ? new List<IUser>() : users.ToList<IUser>();
	}

Though calling this method on a list of Users may not be optimal if the results are not cached.
Let's say you have 5 different roles. Then, you call this method 5 times in a loop that will cache the count results per role. Though, again, nothing tells you when to invalidate that cache. So, this cache needs to be invalidated every time a user is added, modified, or removed. Nothing simple if you want to make it performant. Adding the data on the Role document metadata may also require refreshing the role cache every time a user is modified ...

I say, let's add an issue about it and find a proper implementation.

[giannik]

The best cache is the document its self.

[Skrypt]

The Role document comes from cached data that is retrieved from the RoleStore.