grid filter on object column where value can be null

[kva]

I found this question, which has an answer on how to implement filter on an object column. But my object can be null, and I can't find a way to bind to its string property or empty string.

[Mihaela]

Hi,

In general the filtering does not work with complex objects, but only with flat data structure as noted in the documentation here:

https://docs.telerik.com/aspnet-core/html-helpers/data-management/grid/filtering

To make the data operations (filtering, sorting, grouping) work, you could bind the column of the Grid to a property of the object. As a result, the column binds to a simple type, and all operations will work out of the box. The DropDownList editor will still edit the whole object.

For example, the following Grid is configured for InCell editing, the column uses a custom editor template, and the server operations are performed client-side (ServerOperation(false)):

//Grid View
@(Html.Kendo().Grid<OrderViewModel>()
    .Name("grid")
    .Sortable()
    .Filterable()
    .Columns(columns =>
    {
        columns.Bound(column => column.Category.Name).EditorTemplateName("CategoryEditor");
        ...
    })
    .ToolBar(toolbar =>
    {
        toolbar.Save();
        toolbar.Create();
    })
    .Editable(e => e.Mode(GridEditMode.InCell))
    .DataSource(ds => ds
        .Ajax()
        .Batch(true)
        .ServerOperation(false)
        .Model(m =>
        {
            m.Id(id => id.OrderID);
            m.Field(f => f.Category).DefaultValue(new CategoryViewModel() { Id = 0, Name = null }); //Set a default option when adding a new record
        })
        ...
    )
)

//CategoryEditor.cshtml
@model CategoryViewModel

@(Html.Kendo().DropDownListFor(m => m)
    .DataValueField("Id")
    .DataTextField("Name")
    .HtmlAttributes(new { data_bind = "value: Category" })
    .BindTo((System.Collections.IEnumerable)ViewData["categories"])
)

//Controller
public HomeController()
{
            var categories = new List<CategoryViewModel>();
            categories.Add(new CategoryViewModel
            {
                Id = 0,
                Name = null //The "Name" property can be null
            });
            categories.Add(new CategoryViewModel
            {
                Id = 1,
                Name = "Categpry 1"
            });
            categories.Add(new CategoryViewModel
            {
                Id = 2,
                Name = "Categpry 2"
            });

            ViewData["categories"] = categories;
}

In terms of the filtering of the "Category" column, if you use the filter operator "IsNull", the records with "Name" property "null" will be filtered as expected:

Here is a REPL sample, where you can add a new record with empty Category, save the changes, and filter the column by using the filter operator "IsNull":

https://netcorerepl.telerik.com/wnaMYZPx36HwIPje05

If you prefer to keep the data operations on the server (ServerOperation(true)), you need to process the data filtering on the server by using a custom comparer since the Grid sends primitive data and the LINQ cannot compare different data types. I would suggest following the online demo below, which demonstrates how to handle the filter operation by yourself:

https://demos.telerik.com/aspnet-core/grid/customajaxbinding

In the source code, you could review the method "ApplyOrdersFiltering" that uses "IFilterDescriptor" to filter the passed data.

In addition, check out the following SO thread, where it is discussed how to compare complex objects:

https://stackoverflow.com/questions/10454519/best-way-to-compare-two-complex-objects

I hope this information will help you out with your scenario.

 

Regards, Mihaela Progress Telerik

[kva]

Thanks for providing the comprehensive answer, but as I wrote in the body, my object can be null and accessing its property will throw an exception and hence your answer on client-side filtering doesn't apply.

I found that method Sortable can accept a JS function that will handle sorting, could I specify a similar function for filtering?

Explain, please, how to specify a separate read endpoint for applying server operation, not initial data reading.

[Mihaela]

Here is my feedback on your additional questions:

• If the "DataValueField' of the DropDownList is null, you can enable the ValuePrimitive() option. In case the issue persists, please consider sharing the Grid configuration, the DropDownList editor, and any related logic to review it. It will help me to get a better understanding of the overall scenario to provide a better suggestion.
• You could handle the FilterMenuInit event of the Grid and manually filter the data items for the custom object (if the server operations are disabled).
• You could set the AutoBind(false) option to disable the Read requests during the Grid initialization and then call the read() method to trigger the Read request.

[kva]

2. That's what I meant:

columns.Bound(p => p.User).Title("Сотрудник").Width(180)
    .ClientTemplate("#=data.User?.DisplayName ?? ''#")
    .EditorTemplateName("Employee")
    .Sortable(s => s.Compare("(a, b) => compareUser(a.User, b.User)"));

function compareUser(u1, u2) {
    if (u1 && u2) {
        return u1.TranslatedLastName < u2.TranslatedLastName
            ? -1
            : u1.TranslatedLastName == u2.TranslatedLastName
                ? 0
                : 1;
    }

    if (!u2) {
        return -1;
    }
}

Is there the same way of specifying function-handler of filtering for specific column.

As you wrote, I specified handler for filter event, because I don't use menu, and this handler received object that don't even have attached object's value inside him, so I didn't understand how to apply filtering at all and your documentation doesn't state that.

[kva]

Please, provide example on server-side filtering with object column, I don't understand how to do this from our answer.

[Mihaela]

2) When using the Filter Row functionality, you could handle the filtering of a specified column by using the Filterable(f => f.Cell(c => c.Template("customFilterInput"))) configuration, as per the example below:

//The server operations are disabled (.ServerOperation(false))
@(Html.Kendo().Grid<GridViewModel>()
    .Name("Grid")
    .Filterable(f => f.Mode(GridFilterMode.Row))
    .Columns(columns =>
    {
        columns.Bound(column => column.User).EditorTemplateName("Employee").ClientTemplate("#=User.DisplayName#")
        .Filterable(ff => ff.Cell(c => c.Template("customFilterInput").ShowOperators(false)));
    })
    ...
)

<script>
    function customFilterInput(args) { //The function which will customize how the input for the filter value is rendered.
        let inputElement = args.element; // "args.element" is the default input inside the filter cell
        $(inputElement).attr("data-bind", "value: Id"); //Bind the custom input to the nested Model property
        $(inputElement).kendoAutoComplete({ //Initialize the desired UI component (i.e., AutoComplete)
            dataTextField: "Name",
            dataSource: { //You could configure the DataSource to use remote data binding
                data: [
                    { "Name": null, "Id": 0 },
                    { "Name": "User 1", "Id": 1 },
                    ...
                ]
            },
            valuePrimitive: true,
            change: function(e) { //Handle the "change" event of the AutoComplete
                let selectedOperator = $("#UserFieldOperator").data("kendoDropDownList").value(); //get the selected operator
                if (e.sender.value()) {
                    $("#Grid").data("kendoGrid").dataSource.filter({ field: "User.Name", operator: selectedOperator, value: e.sender.value() }); //Filter the Grid based on the selected AutoComplete option
                } else {
                    $("#Grid").data("kendoGrid").dataSource.filter({}); //reset the Grid filters
                }
            }
        });

        $('<input class="k-dropdown-operator" data-bind="value: operator" id="UserFieldOperator">').insertAfter(".k-autocomplete") //Initialize the DropDownList for the available operators
            .kendoDropDownList({
                dataTextField: "text",
                dataValueField: "value",
                autoWidth: true,
                dataSource: {
                    data: [
                        {
                            "text": "Is equal to",
                            "value": "eq"
                        },
                        {
                            "text": "Is not equal to",
                            "value": "neq"
                        },
                        {
                            "text": "Starts with",
                            "value": "startswith"
                        },
                        {
                            "text": "Contains",
                            "value": "contains"
                        },
                        {
                            "text": "Does not contain",
                            "value": "doesnotcontain"
                        },
                        {
                            "text": "Ends with",
                            "value": "endswith"
                        },
                        {
                            "text": "Is null",
                            "value": "isnull"
                        },
                        {
                            "text": "Is not null",
                            "value": "isnotnull"
                        },
                        {
                            "text": "Has no value",
                            "value": "isnullorempty"
                        },
                        {
                            "text": "Has value",
                            "value": "isnotnullorempty"
                        }
                    ]
                }
            });
    }
</script>

This is a custom approach that is not described in the Grid documentation, because the Grid's DataSource does not work with complex objects by default.

3) Server-side filtering of a complex object -  Generally, there is no monolithic approach to filter the complex property, as the processing of the data on the backend may vary and would be within the developer's hands to induct such an implementation as per his requirements. 

You can follow the approach in the online demo as guidance.

public static IQueryable<OrderViewModel> ApplyOrdersFiltering(this IQueryable<GridViewModel> data,
   IList<IFilterDescriptor> filterDescriptors)
{
 //loop through the filters in filterDescriptos
  if (filterDescriptors.Member == "User")
  {
    ...
 }
  ...
}

[kva]

2) Thanks for detailed response. But I have operator already selected, and I don't need to get it from user:

            .Filterable(ftb => ftb.Cell(cell =>
            {
                cell.Operator("startswith");
                cell.ShowOperators(false);
            }));

I would like to get this info inside function. Also, please, write how to specify remote data source for autocomplete.

[Mihaela]

In this case, you can hard-code the operator in the filter() method:

                if (e.sender.value()) {
                    $("#Grid").data("kendoGrid").dataSource.filter({ field: "User.Name", operator: "startswith", value: e.sender.value() }); //Filter the Grid based on the selected AutoComplete option
                }

 

In terms of the AutoComplete, you can configure it to use remote data as follows:

$(inputElement).kendoAutoComplete({
            dataTextField: "Name",
            dataSource: {
                transport: {
                    read: '@Url.Action("ReadUsers","Home")'
                }
            },
            valuePrimitive: true,
             ...
});

//HomeController.cs
public IActionResult ReadUsers()
{
   List<UserViewModel> users = GetData();
   return Json(users);
}

 

[kva]

        columns.Bound(p => p.Department).Width(230)
            .ClientTemplate("#=data.Department?.Name ?? ''#")
            .EditorTemplateName("Department")
            .Filterable(ftb => ftb.Cell(cell =>
            
                cell.Template("customFilterInput").ShowOperators(false)
            ));

<script>
	// https://www.telerik.com/forums/grid-filter-on-object-column-where-value-can-be-null
     function customFilterInput(args) {
		console.log('called');
        let inputElement = args.element;
        $(inputElement).attr('data-bind', 'value: Id');
        $(inputElement).kendoAutoComplete({
            dataTextField: 'Name',
            dataSource: {
                transport: {
                    read: '@Url.Action("GetDepartments", "Home")'
                }
            },
            valuePrimitive: true,
            change: function(e) {
                if (e.sender.value()) {
                    $('.telerik-grid').data('kendoGrid').dataSource.filter({ field: 'Department.Name', operator: 'startswith', value: e.sender.value() });
                } else {
                    $('.telerik-grid').data('kendoGrid').dataSource.filter({});
                }
            }
        });
     }
</script>

Get this:

ReferenceError: customFilterInput is not defined in /Directory/Users

[Mihaela]

Since I cannot reproduce the issue you are experiencing at my end, I would recommend submitting a support thread with the Grid configuration and all relevant custom logic. Furthermore, a sample where the issue replicates would be of great help to pinpoint what is causing the behavior.