Applying complex OData V4 filters to data sources programatically

[Paul]

As posted on stack overflow ...

    https://stackoverflow.com/questions/61956516/how-to-apply-complex-odata-filters-programatically-with-kendo-data-sources


I have a kendo data source that's bound an OData v4 endpoint.

I need to apply something like this filter to it ...

LINQ query:
ds.data().Where(i => i.References.Any(r => r.OfferLines.Any(l => l.OfferId == "myOfferId"))

OData query:
?$filter=References/any(r:r/OfferLines/any(l:l/OfferId eq 'myOfferId'))

How can i do this "after my grid has been initialised" programatically (with javascript)?

[Petar]

Hi Paul,

In general, there is no difference in the filtering of a DataSource bound to oData or JSON data.

I would suggest checking the following links that discuss the filtering of more complex DataSources: 

• https://stackoverflow.com/questions/19033065/kendo-grid-filtering-on-an-array-object
• https://stackoverflow.com/questions/38057848/kendo-ui-grid-filter-cell-that-contains-an-array and this example: http://dojo.telerik.com/ODiWa/3 that is discussed in the thread
• https://docs.telerik.com/kendo-ui/knowledge-base/grid-filter-column-with-dropdownlist

Maybe you've already checked it, but here is a link to the filter method of the DataSource. I hope the above links will help you implement the targeted functionality.

Regards,
Petar
Progress Telerik

[Paul]

Hi Petar,

I have been using kendo UI for some time, my issue isn't "how do I filter a data source" it's more "how do I do this complex filtering scenario specifically" as all the documentation / examples apply 1 or more filters to the first level properties on a simple object.

I'm trying to drill in to the OData graph and filter on criteria not in the model.

consider my intended OData V4 query output from this: "?$filter=References/any(r:r/OfferLines/any(l:l/OfferId eq 'myOfferId'))"

ds.filter({ "field": "x",  "operator": "eq", "value": "y" });

... this kind of filter just won't cut it here unless I can say something like ...

ds.filter({ "field": "References.OfferLines.OfferId",  "operator": "eq", "value": "y" });

... as you can see though I don't think kendo UI works this way, and what if one of the Any()'s were an "All()".
This is why I posted the C# LINQ query too in the hope that the complexity here might be easier to spot.

Do Kendo data sources configured to bind to OData endpoints allow for this sort of complex server filtering operation at all?

[Petar]

Hi Paul,

The complex filtering you want to implement cannot be achieved by the built-in operators. We have to define custom operator function like for example:

          dataSource.filter({
            field:"Order_Details", 
            operator:function (items, filterValue) {
              for (var i = 0; i < items.length; i++) {
                if (items[i].OrderID === 10344) {
                  return true;
                }
              }
              return false;
            }, 
            value:''});

Check this Dojo example. It has a Grid populated with oData data. If you click on the buttons above the Grid, the data is filtered by Order_Details.OrderID field. Using an approach similar to the demonstrated one, we can filter dataSources with a deeper structure like the one you've shared.

I hope the example demonstrates what you want to implement.  

Regards,
Petar
Progress Telerik

[Paul]

My understanding is that this will apply a client side filter, as i'm using OData here I'd like to take advantage of the power of my API and do the filtering on the server side.

I have been initialising the data source's transport information like this ...

transport: context.transport ? context.transport : {
    read: {
        url: function (data) {
            var result = baseUrl;
            if (typeof context.odataAppend !== 'undefined') { result += context.odataAppend; }
            return result;
        },
        dataType: 'json',
        beforeSend: function (xhr, request) {
            api.beforeSend(xhr);
            result.lastGet = request;
        }
    },
    update: { url: function (data) { return baseUrl + "(" + result.getIdForUrl(data) + ")"; }, dataType: 'json', beforeSend: api.beforeSend },
    destroy: { url: function (data) { return baseUrl + "(" + result.getIdForUrl(data) + ")"; }, dataType: 'json', beforeSend: api.beforeSend },
    create: { url: baseUrl, dataType: 'json', beforeSend: api.beforeSend },
    parameterMap: function (data, operation) {
        var d = kendo.data.transports['odata-v4'].parameterMap(data, operation);

        if (operation === "update") {
            delete data.type;  // this will annoy the server, so lets remove it before we send.
            delete data.source;
            delete data.context;
            return JSON.stringify(data);
        }

        return d;
    }
}

... basically before I begin constructing the source I build the base url ...

var baseUrl = context.base + context.endpoint;

... then as you can see from the transport code above if my construction args for the source (context) has an "odataAppend" property I append that in to the transports url building information for the callback to the server.

 

I was hoping that for the filtering I could "after the data source was built" somehow replace that value.
By the end of this same function but before I return back to the calling code I do this ...

var result = new kendo.data.DataSource(cfg);

... cfg is an object that contains the transport information below ... is there some way I can (assuming I save this object or the core information I need (like the base url) maybe write my own extension function that given the data source object could set that.

Or maybe there's a smarter way to init the transport that i'm missing ... 

So here's where i'm going (in theory) ...

var result = new kendo.data.DataSource(cfg);
result.baseUrl = baseUrl;
result.odataAppend = context.odataAppend;

... then when i build the transport info I could do it like ...

 transport: context.transport ? context.transport : {
    read: {
       url: this.baseUrl + this.odataAppend
       ...
    },

 

 

[Petar]

Hi Paul,

Based on the available information, I would suggest filtering the oData data using the approach demonstrated in this PREFILTERING A KENDO UI GRID WITH AN ODATA DATA SOURCE article. Inside it, you will find a description of how the using the filter configuration of the DataSource you can modify the request to the API.

Using the filter method, you should achieve the same functionality as the one described in the linked article. Here is a Dojo example of the usage of the filter method. Open your browser's network tab and press the filter button. You will see a request and if you inspect the response, it contains only one record. If you edit the marked in yellow in the following line to "che", the response will return you 4 records. 

dataSource.filter({ field: "ProductName", operator: "contains", value: "cheg" }); 

This is how I would suggest filtering the oData datasource using your API. I hope the above will help you implement the targeted functionality. 

Regards,
Petar
Progress Telerik

[Paul]

Hi Petar,

Again how would I express THIS SPECIFIC ODATA FILTER using that API call?

    ?$filter=References/any(r:r/OfferLines/any(l:l/OfferId eq 'myOfferId'))

...

With all due respect ... I don't think you've properly read my last post at all.

[Petar]

Hi Paul,

A possible approach I can suggest for the implementation of the targeted functionality is to try to use the data property of the transport.read/update etc. configurations. In the previous link, you can check how the property can be used/configured. With this data object, you can pass different parameters to the backend. Based on these parameters and custom server logic, the server will perform the filtering and return the needed data. 

If the above approach doesn't work in your scenario, to be able to help you, I will need a runnable example to test possible approaches for the implementation of the functionality you want to achieve.

Regards,
Petar
Progress Telerik

[Paul]

Apparently the spam filter won't let me post the fix ... it's fixed now though.

[Petar]

Hi Paul,

I am happy to hear that you've managed to resolve the issue. I don't know why you are not able to post the solution here.

Can you try to attach the solution as an archive? This could help someone who is trying to implement the same functionality as yours. 

Regards,
Petar
Progress Telerik

[Paul]

01.beforeSend: function (xhr, request) {
02.    api.beforeSend(xhr);
03.    request.url = request.url.replaceAll("%24", "$");
04.    console.log(request.url);
05.    if (request.url.indexOf("?") > 0) {
06.        var query = request.url.split("?");
07.        var params = query[1].split("&");
08.        if (params.filter(p => p.indexOf("$filter=") > -1).length > 1) {
09.            var filter = "$filter=" + params.filter(p => p.indexOf("$filter=") > -1).map(f => f.replace("$filter=", "")).join(" AND ");
10.            params = params.filter(p => p.indexOf("$filter=") === -1);
11.            params.push(filter);
12.            query[1] = params.join("&");
13.        }
14.
15.        request.url = query[0] + "?" + query[1];
16.        console.log(request.url);
17.    }
18.    result.lastGet = request;
19.}

[Paul]

Ok that worked ... see lines 5 to 15 in the above code sample.

[Paul]

To clarify it seemed like in my case the issue was that when I apply a default filter by initialising the source transport like this ...

transport: context.transport ? context.transport : {
    read: {
         url: function (data) {
             var result = baseUrl;
             if (typeof context.odataAppend !== 'undefined') { result += context.odataAppend; }
             return result;
    },

... it would apply a default filter as specified but then adding additional filters through the grid headers caused the OData url that kendo generated to have the $filter param twice.

So the code above basically combines both those params in to a single OData filter.

[Petar]

Hi Paul,

Thank you for sharing the solution with the community and for providing the additional details! Your implementation will surely help someone in the future.

Regards,
Petar
Progress Telerik