This is a migrated thread and some comments may be shown as answers.

Applying complex OData V4 filters to data sources programatically

13 Answers 764 Views
Data Source
This is a migrated thread and some comments may be shown as answers.
Paul
Top achievements
Rank 1
Paul asked on 22 May 2020, 02:57 PM

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)?

13 Answers, 1 is accepted

Sort by
0
Petar
Telerik team
answered on 26 May 2020, 01:13 PM

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: 

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

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
0
Paul
Top achievements
Rank 1
answered on 26 May 2020, 03:11 PM

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?

0
Petar
Telerik team
answered on 28 May 2020, 02:27 PM

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

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
0
Paul
Top achievements
Rank 1
answered on 12 Jun 2020, 04:45 PM

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
       ...
    },

 

 

0
Petar
Telerik team
answered on 16 Jun 2020, 04:08 PM

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

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
0
Paul
Top achievements
Rank 1
answered on 16 Jun 2020, 09:17 PM

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.

0
Petar
Telerik team
answered on 18 Jun 2020, 02:21 PM

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

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
0
Paul
Top achievements
Rank 1
answered on 24 Jun 2020, 02:46 PM
Apparently the spam filter won't let me post the fix ... it's fixed now though.
0
Petar
Telerik team
answered on 26 Jun 2020, 08:59 AM

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

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
0
Paul
Top achievements
Rank 1
answered on 26 Jun 2020, 03:55 PM
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.}
0
Paul
Top achievements
Rank 1
answered on 26 Jun 2020, 03:56 PM
Ok that worked ... see lines 5 to 15 in the above code sample.
0
Paul
Top achievements
Rank 1
answered on 28 Jun 2020, 09:29 AM

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.

0
Petar
Telerik team
answered on 30 Jun 2020, 08:38 AM

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

Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
Our thoughts here at Progress are with those affected by the outbreak.
Tags
Data Source
Asked by
Paul
Top achievements
Rank 1
Answers by
Petar
Telerik team
Paul
Top achievements
Rank 1
Share this question
or