Filtering/grouping on an aggregate column

Mar 7, 2012 at 9:08 PM
Edited Mar 8, 2012 at 3:15 PM

I'm not sure how to do this, but maybe someone has a neat solution.  I have some columns that gets aggregated from a collection when mapped from an Entity to ViewModel.  For example, my entity contains a collection of simple strings, but for displaying purposes I map the collection to a single comma separated string like this:

 

.ForMember(lvm => lvm.TagsInline, m => m.MapFrom(s => string.Join(", ", s.Tags.Select(x => x.ForTag.Name))))

so, I still want to be able to filter or group by tags, although sorting wouldn't make much sense.  Does anyone know of a solution?

Mar 8, 2012 at 3:11 PM
Edited Mar 8, 2012 at 3:26 PM

As I'm thinking about the solutions for this issue, I have come up with one possible (partial) solution.  I can create a view in the database that does the comma separated aggregation.  I'm using Oracle, for which LISTAGG can be used.  Then to filter I would just choose Contains "value".  The grouping wouldn't work on this column though, and filtering performance may be slow since LISTAGG would have to run for all the parent records, and then a LIKE "%VALUE%" on top of that.

I think, ideally, the solution would be to construct the nHibernate query in such a way that it filters on the child table's column - the TagTable.TagName column in my case.  The user would filter the Tags column with Equals instead of Contains, and grouping would work as well.  Sorting on that column, again, would be meaningless.

Mar 8, 2012 at 7:55 PM
Edited Mar 8, 2012 at 8:00 PM

So after messing around with this thing all day, I came up with a filtering solution.  Basically, I intercept the GridCommand before it gets passed to the Helper and map the Tag filters to the QueryOver query as needed, then remove the filter from the GridCommand object.  Call MapItemsToQuery method from the controller, and then the recursive MapTagToQuery method gets called to build the query.  Aliasing is only done if tag filters are found.  Here's the code (with some renaming for  privacy reasons):

 

private void MapItemsToQuery(GridCommand command, IQueryOver<SomeObject,SomeObject> query)
{
	var mapped = MapTagToQuery(command.FilterDescriptors, query);
	if(mapped > 0 ) //create the aliases only if tag filtering is done
	{
		SomeObjectTag someObjecttag = null;
		Tag thetag = null;
		query 
			.JoinAlias(x => x.Tags, () => someObjecttag, JoinType.InnerJoin)
			.JoinAlias(() => someObjecttag.ForTag, () => thetag, JoinType.InnerJoin);
	}
}

private int MapTagToQuery(IList<IFilterDescriptor> dlist, IQueryOver<SomeObject,SomeObject> query)
{
	if (dlist == null)
		return 0;

	var count = 0;

	//reverse loop and remove items from list that are mapped
	for (var i = dlist.Count - 1; i >= 0; i--)
	{
		var descriptor = dlist[i];
		var fd = descriptor as FilterDescriptor;
		if (fd != null)
		{
			if (fd.Member.Equals("TagsInline"))
			{
				Tag thetag = null;
				
				switch (fd.Operator)
				{
					case FilterOperator.IsEqualTo:
						query = query.AndRestrictionOn(() => thetag.Name).
						IsLike(fd.Value.ToString(), MatchMode.Exact);
						break;
					case FilterOperator.Contains:
						query = query.AndRestrictionOn(() => thetag.Name).
						IsLike(fd.Value.ToString(), MatchMode.Anywhere);
						break;
					case FilterOperator.StartsWith:
						query = query.AndRestrictionOn(() => thetag.Name).
						IsLike(fd.Value.ToString(), MatchMode.Start);
						break;
					case FilterOperator.EndsWith:
						query = query.AndRestrictionOn(() => thetag.Name).
						IsLike(fd.Value.ToString(), MatchMode.End);
						break;
					case FilterOperator.IsNotEqualTo:
						Tag exTag = null;
						SomeObjectTag ltAlias = null;
						var queryExclude = QueryOver.Of<SomeObjectTag>(() => ltAlias)
							.JoinAlias(x => x.ForTag, () => exTag)
								.Where(() => exTag.Name == fd.Value.ToString())
								.Select(lt => ltAlias.SomeObjectId);

						query = query.WithSubquery.WhereProperty(x => x.Id).NotIn(queryExclude);
						break;
				}
				dlist.Remove(fd); //we don't want this filter to be processed by grid helper
				count++;
			}
		}

		var cfd = descriptor as CompositeFilterDescriptor;
		if (cfd != null)
		{
			count += MapTagToQuery(cfd.FilterDescriptors, query); //recursive call
		}
	}
	return count;
}

 

Now, I don't know if this will yield better performance than the view approach I described before.  I have a feeling that the view should be done in any case, since a lot less records will be returned in the query due to aggregation.  It would also allow me to try out filtering with Contains, and if performance is good then I'd rather not bother with these intercepting mapping methods for custom filtering.  Even if I end up doing the custom filtering above, a view will still be able to support the custom filtering.

Coordinator
Mar 8, 2012 at 9:30 PM

Wow, I see you've been busy, thanks for sharing your story. Unfortunately the next days will be a little difficult for me and I think I shall have to stay away during this time, but pretty soon I'll be back and I'll work on it.

Mar 12, 2012 at 1:56 PM

Luis,

I think this functionality could be supported by the helper.  Basically, there needs to be a way to tell the helper on which column to filter/group/sort on for any given column that's displayed to the user.  Let's call them "Display Column" - the column that gets displayed to the user, and "Action Column" - the column that filter/group/sort gets applied to.  The Action Column doesn't have to be in the parent table, so there needs to be a mechanism to specify how to join the table with the Action Column.  Do you think such functionality is feasible?

Alex

Coordinator
Mar 13, 2012 at 1:09 AM

I think this is possiblebut I need some thing before and maybe you can help me.

First of all we need to create a fail test, it would be very nice if you could get me the following:

  • A minimal structure of the database (only tables and columns that will be affected by the test) in SQL format;
  • Entities and their respective NHibernate mappings;
  • ViewModels and their respective AutoMapper mappings;
  • And the Grid configuration in your view.

Again, I just need test data, ie stubs and unreal objects (It would be cool if we could do this on NorthWind).

** I tried it with the information you have posted here, but I could not get too far...

Mar 13, 2012 at 5:47 PM

Luis,

I've created an issue for this feature. There you'll find details and an attached sample project.  If you don't mind, can you please provide a workaround for the oracle paging bug first?  That's a critical issue for me at the moment.

Thanks.

Alex

Coordinator
Mar 13, 2012 at 6:06 PM

Thanks Alex, this will certainly be very useful for the whole community.

About the problem with paging in Oracle, I have already provided a solution and a new beta version will be released tonight.

Mar 13, 2012 at 7:21 PM
Edited Mar 14, 2012 at 12:43 PM

Thanks Luis.

While providing the sample project for this feature I noticed a filtering bug that returned duplicate records because of the joins in the many relationships.  The sample project that I attached to the issue has updated code, but I want to also paste it here as well for easy accessibility.  The new code works well, with no duplicate rows, but it does an "OR" instead of an "AND".  So if you filter for Tags begin with "s" AND Tags end with "t" the results will be for tags beginning with "s" OR ending with "t".  I don't think it's so bad, but just wanted to make people aware of what's going on so they can change the logic if they have to.  Here's the updated code:

 

private void MapItemsToQuery(GridCommand command, IQueryOver<SomeEntity,SomeEntity> query)
{
	MapTagToQuery(command.FilterDescriptors, query);
	//other items can be mapped here
}

private int MapTagToQuery(IList<IFilterDescriptor> dlist, IQueryOver<SomeEntity,SomeEntity> query)
{
	if (dlist == null)
		return 0;

	var count = 0;

	//reverse loop and remove items from list that are mapped
	for (var i = dlist.Count - 1; i >= 0; i--)
	{
		var descriptor = dlist[i];
		var fd = descriptor as FilterDescriptor;
		if (fd != null)
		{
			if (fd.Member.Equals("TagsInline"))
			{
				Tag tagAlias = null;
				SomeEntityTag ltAlias = null;
				var subQuery = QueryOver.Of<SomeEntityTag>(() => ltAlias)
					.JoinAlias(x => x.ForTag, () => tagAlias)
					.Select(lt => ltAlias.SomeEntityId);

				switch (fd.Operator)
				{
					case FilterOperator.IsEqualTo:
						subQuery.AndRestrictionOn(() => tagAlias.Name).IsLike(fd.Value.ToString(), MatchMode.Exact);
						query = query.WithSubquery.WhereProperty(x => x.Id).In(subQuery);
						break;
					case FilterOperator.Contains:
						subQuery.AndRestrictionOn(() => tagAlias.Name).IsLike(fd.Value.ToString(), MatchMode.Anywhere);
						query = query.WithSubquery.WhereProperty(x => x.Id).In(subQuery);
						break;
					case FilterOperator.StartsWith:
						subQuery.AndRestrictionOn(() => tagAlias.Name).IsLike(fd.Value.ToString(), MatchMode.Start);
						query = query.WithSubquery.WhereProperty(x => x.Id).In(subQuery);
						break;
					case FilterOperator.EndsWith:
						subQuery.AndRestrictionOn(() => tagAlias.Name).IsLike(fd.Value.ToString(), MatchMode.End);
						query = query.WithSubquery.WhereProperty(x => x.Id).In(subQuery);
						break;
					case FilterOperator.IsNotEqualTo:
						subQuery.AndRestrictionOn(() => tagAlias.Name).IsLike(fd.Value.ToString(), MatchMode.Exact);
						query = query.WithSubquery.WhereProperty(x => x.Id).NotIn(subQuery);
						break;
				}
				dlist.Remove(fd); //we don't want this filter to be processed by grid helper
				count++;
			}
		}

		var cfd = descriptor as CompositeFilterDescriptor;
		if (cfd != null)
			count += MapTagToQuery(cfd.FilterDescriptors, query); //recursive call
	}
	return count;
}
Coordinator
Mar 14, 2012 at 1:02 AM

Hi Alex,

Today we have two problems less to achieve the stable version!

They are: #3 (boolean problem) and #4 (Paging bug).

Please test your project with the latest release (Version 1.0.14.1-beta).

Coordinator
Mar 15, 2012 at 11:59 PM

Alex, I got an invitation to you, do not you like to create a fork and help meimplement this feature?

This is because, as you may have noticed, I would like to release a stable version of the project and shortly after that depart for improvements and new features for future versionsThink about it, anyway you've helped me a lot and am very grateful for that.

Another thing, in the next days until the weekend I'll be traveling on a important appointment so I should I leave for a while.

Do not forget to test my proposed solution to the problem with booleans, details on issue #4.

 

Seeya.

Mar 16, 2012 at 3:23 PM

Sure thing, I can work on this feature.  Do I need special access permissions from you on this project?  Are you trying to add this feature before or after the upcoming stable version?

Coordinator
Mar 19, 2012 at 1:03 AM
Edited Mar 19, 2012 at 1:05 AM

You do not need any special permission now, just navigate to the "Source Code" tab and click "Create Fork", then just follow the CodePlex instructions.

To use this approach you'll need some knowledge on mercurial version control system and I recommend using "TortoiseHG".

But if you do not want to have so much work you can simply download the latest changeset make your way through it.

If we finish it in few days we can launch this feature along with the stable version, but if it takes too long we leave for the future.

Mar 19, 2012 at 1:23 PM

I don't think I'll be able to get the code completed quickly, so I would recommend that you don't wait for me and release a stable version.  I'll play around with forking the project and go at my own pace.  I'll keep you updated on my progress.

Coordinator
Mar 19, 2012 at 9:39 PM

Please don't hurry, I still have some tweaking to do and if you need any help I will always be around here.