Null handling when filtering for Not Equals/Contains - Oracle

Sep 14, 2012 at 5:18 PM
Edited Sep 14, 2012 at 5:21 PM

Right now a 'Not Equals' and 'Not Contains' filters don't return records with null values in the filtered field.  Do you think that null fields should automatically be returned for those fields since I don't think it's possible to filter for NULL?  See this thread on how to include a null into the result set.  You would basically follow this logic:

where ('blah' != col1 or col1 is null)
Coordinator
Sep 17, 2012 at 4:50 PM

I'm working on this, what do you expect about the API?

I'm thinking of three ways to configure this, one global (for any and all queries), one "by entity" and one last "by property".

Sep 17, 2012 at 7:50 PM

sounds good to me.  I would probably use the global, but I like the flexibility.

Coordinator
Oct 6, 2012 at 1:11 PM

Hello Alex, I'm sorry for taking so long. Besides other details this solution proved to be more complex than I expected, but I believe I could get a good result.

Please take a look at the latest release and see the implementation in the sample project and unit tests.

Tell me if you have problems.

Oct 8, 2012 at 2:58 PM

Hi Luis.  Thanks for the work.  I can see how I can search for null, which is a very powerful feature.

At this point I'm only interested in including null results with "not equals/contains" queries.  For example, when filtering "name not equals 'Alex'" I expect to get all non "Alex" records as well as any records that have the name set to NULL.  How do I configure that?

Coordinator
Oct 9, 2012 at 2:29 PM

You could use something like this (not very elegant, but it does the job);

[GridAction(EnableCustomBinding = true)]
public ActionResult FilteringNullableValuesData(GridCommand command)
{
    var query = NHibernateHelper.CurrentSession.Query<Order>();
            
    var gridHelper = new GridCustomBindingHelper<Order, OrderModel>(command, query)
        // Let's use projections here to avoid Select+1.
        .UseProjections(options => options.Use(order => new { order.OrderID, order.ShipRegion }));

    if (FilterDescriptorsHaveContainsOrNotEqualsOperators(command.FilterDescriptors))
    {
        gridHelper.AcceptNullValuesWhenFiltering();
    }
    else
    {
        gridHelper.AcceptNullValuesWhenFiltering(false);
    }

    var gridModel = gridHelper.BuildGridModel();

    return View(gridModel);
}

private static bool FilterDescriptorsHaveContainsOrNotEqualsOperators(IEnumerable<IFilterDescriptor> filterDescriptors)
{
    foreach (var filterDescriptor in filterDescriptors)
    {
        if (filterDescriptor is CompositeFilterDescriptor)
            return FilterDescriptorsHaveContainsOrNotEqualsOperators((filterDescriptor as CompositeFilterDescriptor).FilterDescriptors);

        var descriptor = filterDescriptor as FilterDescriptor;

        if (descriptor == null)
            continue;

        return descriptor.Operator == FilterOperator.Contains || descriptor.Operator == FilterOperator.IsNotEqualTo;
    }

    return false;
}

Coordinator
Oct 9, 2012 at 2:44 PM

Hmm, investigating this I found that there is a bug when the operator is a "IsNotEqualTo" or "DoesNotContain."

Although the above code is expected to work and I'm working to fix it, maybe a new version will come out soon.

Coordinator
Nov 9, 2012 at 12:02 PM

I hope all of this is fixed in the latest version, please check and keep me informed.