We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
  1. Home
  2. Forum
  3. ASP.NET MVC - EJ 2
  4. Providing server-side filter list when using URL data adaptor

Providing server-side filter list when using URL data adaptor

We are using the grid with a very large data set (originating in a SQL database). I have intercepted the Data Manager requests server-side to create SQL filters that can be used in the database - which works very well. We return the data in pages of typically 25 rows at a time.

However, when the user wants to filter certain columns they click on the filter icon - and this generates a Data Manager request with a skip of 0, a take of 1000 rows and no other filters applied. This randomly selects the first 1000 rows from the database - and it is a matter of luck as to whether or not all the distinct values needed for the filter are included in those rows. This is also quite an inefficient way to generate the list.

We want instead to intercept this and create our own custom SQL queries to produce each filter list - thus returning from the database the distinct list of only the 20 or so actual values we need to appear in the dropdown, rather than 1000 complete rows which may or may not contain the distinct values we need. There is currently nothing in the Data Manager request object to indicate that the request is for a filter, nor which columns it's for - so we clearly need to intercept this earlier in the chain using a local Javascript function referenced by the ActionBegin property. The problem is it's not clear to me what we should so in that function - should we make our own Ajax call to the server to get the list of values? And if so, what do we do with those, and how do we prevent the default server-side call. Or can we tweak the Data Manager request to include additional parameters so we know what to return (and in this case how should we return them, since we're only return 1 column's worth of data)?


14 Replies
TS Thavasianand Sankaranarayanan Syncfusion Team January 16, 2019 11:47 AM UTC

Hi Charles, 

Greetings from Syncfusion. 

By default, In excel filter we have limited the filter choice record count as 1000 to prevent the performance issue. In your sample, you have some large records but it return first 1000 records so that it cause the reported problem . You can use the below way to set the filter choice count to achieve your requirement.  
 
In the below code example, we have set the filter choice count in actionBegin event by using filterChoiceCount argument property. 
 
Kindly refer to the below code example for more information. 

        function actionBegin (args) { 
            if (args.requestType == "filterchoicerequest" || args.requestType === 'filtersearchbegin') { 
                // you can set filter choice count based on your datasource count, by default count 1000 
                args['filterChoiceCount'] = 30000;      
            } 
        } 

Refer the help documentation. 


Please get back to us if you need further assistance on this. 
Regards,
Thavasianand S. 


CS Charles Southey January 17, 2019 10:24 AM UTC

Hi - thanks for the reply. Increasing the sample count would mean we picked up more of the values - but it would be very inefficient to pull 30,000 large records every time just to find 20-30 selector values. Is there a way to intercept the process completely and provide a custom list using my own code?

Charles

TS Thavasianand Sankaranarayanan Syncfusion Team January 18, 2019 12:33 PM UTC

Hi Charles, 

We have analyzed your query. We suggest you to update your own custom query in the “actionBegin” event, so that the list will be generated based on the result from the  provided query. Please refer the code example below, 

<script> 
    function actionBegin(args) { 
        if (args.requestType == "filterchoicerequest" || args.requestType === 'filtersearchbegin') { 
            // you can set your custom query here 
            var predicate = new ej.data.Predicate("EmployeeID", "greaterthan", 1); 
            predicate = predicate.and("EmployeeID", "lessthan", 4); 
            args.query.where(predicate); 
        }  
    } 
</script> 

 
Filter choice will be displayed as like the below screenshot for the above provided query(values between 1 and 4 will be displayed in List). 

 

Please get back to us if you need further assistance. 

Regards, 
Thavasianand S. 


CS Charles Southey January 18, 2019 09:44 PM UTC

Thank you for your help so far: we are nearly there - we can produce server-side filter lists as per your suggestions: in fact we are simply adding another parameter to the query (the name of the field for which we need the filter values) - and the server returns a single column table of distinct values for that field. This works well when filtering on a single column.

However, we are hitting one strange problem when filtering on multiple columns: if we already have a filter applied on one column, and we click the filter icon on another column, the filter list on the second column will only appear if more than 50% of the values in the first column are selected. If fewer than 50% of the values are selected, the control ignores the server-provided list of filter values and displays "No matches found".

I am assuming this is related to an optimisation somewhere which flips between "equal" and "notequal" predicates to minimise the length of the list of entries, but we can't see how that should affect what's happening in the other column. Do you have any idea why that is?





CS Charles Southey January 21, 2019 11:46 AM UTC

Hi - further update on this. Having done some more playing around with it I believe that what is happening is:
  • When you click on the filter button, the default request for data to the server has no "WHERE" component, even if there are other filters applied. Instead, the control expects the full, unfiltered set of rows to be returned, and then applies the other filters client-side.

  • In order to do this (client-side filtering), it expects the other columns to which filters have been applied to be returned in the data set. The fact that I am returning only one column (the one needed for the filter I clicked on) means that the client-side filtering is failing if any of those filters use an "EQUALS" predicate - such as you get if fewer than 50% of values are selected ("EmployeeID equals "A" OR EmployeeID equals "B" OR EmployeeID equals "C" etc.").

  • If more than 50% of the values are selected in one of the other columns, it generates predicates based on "NOTEQUALS" (i.e. "EmployeeID notequal "C" AND EmployeeID  notequal "D"). This still works even though I haven't returned the EmployeeID column, presumably because "notequals null" evaluates to true for any value.
I cannot resolve this by simply providing all the columns, because that would cause my result set of distinct values to become huge again. What would solve this completely, however, is if the other existing filter values could be passed in the DataManager object's WHERE component as they would be for a data refresh. I can see that in the BeforeAction event handler the local object has all the information it needs about the other predicates to do that, but how can I use that to force the WHERE component to be rendered exactly as required for those other filters without having to write reams of my own code to interpret them?

If I can get the filters passed in the WHERE clause I can apply the filtering server-side, and return all the necessary columns but only containing such data as already matches the filters (so the increase in the number of results rows will be small, if any).

Is there a way to do that?

TS Thavasianand Sankaranarayanan Syncfusion Team January 21, 2019 12:44 PM UTC

Hi Charles, 

We have analyzed your query and we have prepared the sample to reproduce the reported issue. But it was unsuccessful at our end. So we have attached our sample with this update for your reference. Please share the following details to reproduce the reported issue at our end, 

  1. In your query, you have mentioned (in fact we are simply adding another parameter to the query). So could you please share the details about how you have changed that query.
  2. Share the Full Grid code snippet.
  3. Share exact scenario of the issue and replication procedure of the issue.
  4. If possible, try to reproduce the reported issue in our sample and send back to us.
  5. Syncfusion package version.


Regards, 
Thavasianand S. 


CS Charles Southey January 29, 2019 09:32 PM UTC

Hi Thavasianand,

Sorry for the delay getting back to you. The basic problem can be demonstrated quite simply using the sample solution you provided as follows:

1. Comment out the JS code in actionBegin (Index.cshtml) - we don't need to apply a custom query.
2. Apply a filter on EmployeeID so it can be 2, 3 or 4.
3. Put a breakpoint on the first line of the UrlDataSource method in HomeController.
4. Click any of the other filters (e.g. Customer Name)
5. Examine the "dm" object that has been passed to UrlDataSource. The "Where" object is null.

Here is the object as shown in the "Immediate Window":

dm
{Syncfusion.EJ2.Base.DataManagerRequest}
    Aggregates: null
    Expand: null
    Group: null
    RequiresCounts: true
    Search: null
    Select: null
    Skip: 0
    Sorted: null
    Table: null
    Take: 1000
    Where: null
    antiForgery: null

So the real question (which will solve the problem completely for us) is how can we force the "Where" object to be populated with the existing filter on the EmployeeID column (and any other existing filters) when trying to retrieve the data for another filter list? It is populated when the control is trying to refresh the data, but not when it's just trying to build the list of values. We need to know what the other filters are at this stage so we can efficiently return the values needed to satisfy them, and no more.





CS Charles Southey January 30, 2019 11:34 AM UTC

Hi - further to this I have modified the sample you provided to show the problem exactly. If you compile and run this you will find that it works fine when applying one filter at a time, but if you the following:

1. Add a filter to EmployeeID so only 2, 3, or 4 are permitted values.
2. Click any other filter - no results returned.

If you change the EmployeeID filter so 7 or more values are permitted the other filter lists will work, but only because it has now flipped to doing "Not Equals" against null rather than "Equals" against null. 

The underlying cause it that we are not returning all the columns when it is a FilterChoiceRequest - but we wouldn't need to if we knew which existing filters were applied (because then we could apply the same filter SQL-side, and also return all the necessary columns for the other filters to work). All we need is the dm.Where object gets populated for a FilterChoiceRequest in exactly the same way as it does for a normal data refresh.


Attachment: F142044_58f31794.zip

TS Thavasianand Sankaranarayanan Syncfusion Team January 31, 2019 01:04 PM UTC

Hi Charles,  

We have validated the reported issue by using your sample and found that you have returned only the current filter column values from the server. This is the cause of this issue. Because we have generated the query based on the first filter column values, when you open the second filter dialog. For example, If you filtered the EmployeeID column at first. After that you will filter CustomerID column. In this case we have created the query based on EmployeeID column values to get the CustomerID values. In your case you have only returned the CustomerID field values. So the query getting failed and the filter list will display “No records found”.  

Please return all column values from the controller to resolve this issue. 

Note: We have handled the distinct values in our source level. So please return all the column values from the controller and Grid will handle the distinct values by itself. 

Please remove the following select query in all the fields to resolve this issue, 

DataSource = order.Select(p => new {p.OrderID}).Distinct().ToList(); 

Regards, 
Thavasianand S. 


CS Charles Southey January 31, 2019 03:04 PM UTC

Hi Thavasianand,

I know that is what is causing the problem, unfortunately returning all the columns doesn't resolve it for us, because it defeats the whole purpose of doing server-side (indeed database-side) filtering. If I return all columns I have to return all DISTINCT rows for all columns, which therefore means returning the entire dataset - maybe tens of thousands of rows, just to get maybe 4-5 strings for a filter list! 

With the server-side filtering that we have built it is beautifully efficient when retrieving the actual page of data - if I need only 10 rows based on the filters applied and/or the page size then only 10 rows are returned from the database. That's because the dm.Where object is populated, and so we can use it to generate the necessary WHERE clause in SQL.

However, if - when clicking on a filter symbol just to change the filter settings - I need to send from database to application server and from application server to browser the entire contents of the database table, that is incredibly inefficient and makes all the server-side filtering totally pointless.

This could be very easily solved if the grid control simply populates the dm.Where object for a FilterChoiceRequest in exactly the same way as it does when retrieving a page of data. Is there any way to do that?

BTW we have already implemented a partial fix for this ourselves in the ActionBegin handler as follows:

function actionBegin(args)
{
    switch (args.requestType)
    {
        ...
        ...
        case "filterchoicerequest":
            var field = args.filterModel.options.field;
            args.query.addParams('filterField', field);
            var predicate = null;
            $.each(args.filterModel.filterSettings.properties.columns,
                function(index, item)
                {
                    if (item.properties.operator === "equal" && item.properties.field != field)
                    {
                        predicate = predicate === null ? new ej.data.Predicate(item.properties.field, item.properties.operator, item.properties.value) : predicate.or(item.properties.field, item.properties.operator, item.properties.value);
                    }
                });
            if(predicate !== null) args.query.where(predicate); 
            break;
        ...
        ...
    }
}

This works with the most basic filters (ticked lists). However this will not work with complex filter queries. It really should not be necessary for us to write client-side code to replicate what the grid control natively does anyway when retrieving pages of data - but if that is the only way please could you refine the code sample above to cope with the use of more complex / compound filters?

Many thanks,

Charles




TS Thavasianand Sankaranarayanan Syncfusion Team February 4, 2019 12:55 PM UTC

Hi Charles, 

We suggest to use our DataManager Select query to achieve this requirement. Please refer the following code snippet, 

Index.html 
 
<script type="text/javascript"> 
     function actionBegin(args)  
     { 
         if (args.requestType === "filterchoicerequest")  
         { 
             var filterfields = []; 
             var objFilter = Object.keys(args.filterModel.existingPredicate); 
             for (var i = 0; i < objFilter.length; i++) { 
                 filterfields.push(objFilter[i]); 
             } 
             filterfields.push(args.filterModel.options.field); 
             args.query.distincts = []; 
             args.query.select(filterfields); // Created the select query 
         } 
    } 
 
</script> 

Controller: 
 
public ActionResult UrlDatasource(DataManagerRequest dm) 
        { 
            IEnumerable DataSource = order; 
            var count = 0; 
             
            DataOperations operation = new DataOperations(); 
            List<string> str = new List<string>(); 
            if (dm.Search != null && dm.Search.Count > 0) 
            { 
                DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search 
            } 
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 
            { 
                DataSource = operation.PerformSorting(DataSource, dm.Sorted); 
            } 
            if (dm.Where != null && dm.Where.Count > 0) //Filtering 
            { 
                DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); 
            } 
            count = DataSource.Cast<Orders>().Count(); 
            if (dm.Select != null) 
            { 
                DataSource = operation.PerformSelect(DataSource, dm.Select);  // Selected the columns value based on the filter request 
                DataSource = DataSource.Cast<dynamic>().Distinct().AsEnumerable(); // Get the distinct values from the selected column 
            } 
            if (dm.Skip != 0) 
            { 
                DataSource = operation.PerformSkip(DataSource, dm.Skip);         //Paging 
            } 
            if (dm.Take != 0) 
            { 
                DataSource = operation.PerformTake(DataSource, dm.Take); 
            } 
            return dm.RequiresCounts ? Json(new { result = DataSource, count }) : Json(DataSource); 
        } 

In this code, we have created the select query for previous filtered and current filtering column. We have used Grid actionBegin event to create this select query. We have used this select query to select the columns and its distinct values and retuned this selected values from the server. Also, we have modified your sample with this code snippet and that can be download from the below link, 


Refer the below link to know about Select query if the EJ2-DataManager, 


Please get back to us, If you need further assistance on this. 

Regards, 
Thavasianand S. 


SH Sascha Herrmann replied to Charles Southey March 1, 2019 07:47 AM UTC

Hi Thavasianand,

I know that is what is causing the problem, unfortunately returning all the columns doesn't resolve it for us, because it defeats the whole purpose of doing server-side (indeed database-side) filtering. If I return all columns I have to return all DISTINCT rows for all columns, which therefore means returning the entire dataset - maybe tens of thousands of rows, just to get maybe 4-5 strings for a filter list! 

With the server-side filtering that we have built it is beautifully efficient when retrieving the actual page of data - if I need only 10 rows based on the filters applied and/or the page size then only 10 rows are returned from the database. That's because the dm.Where object is populated, and so we can use it to generate the necessary WHERE clause in SQL.

However, if - when clicking on a filter symbol just to change the filter settings - I need to send from database to application server and from application server to browser the entire contents of the database table, that is incredibly inefficient and makes all the server-side filtering totally pointless.

This could be very easily solved if the grid control simply populates the dm.Where object for a FilterChoiceRequest in exactly the same way as it does when retrieving a page of data. Is there any way to do that?

BTW we have already implemented a partial fix for this ourselves in the ActionBegin handler as follows:

function actionBegin(args)
{
    switch (args.requestType)
    {
        ...
        ...
        case "filterchoicerequest":
            var field = args.filterModel.options.field;
            args.query.addParams('filterField', field);
            var predicate = null;
            $.each(args.filterModel.filterSettings.properties.columns,
                function(index, item)
                {
                    if (item.properties.operator === "equal" && item.properties.field != field)
                    {
                        predicate = predicate === null ? new ej.data.Predicate(item.properties.field, item.properties.operator, item.properties.value) : predicate.or(item.properties.field, item.properties.operator, item.properties.value);
                    }
                });
            if(predicate !== null) args.query.where(predicate); 
            break;
        ...
        ...
    }
}

This works with the most basic filters (ticked lists). However this will not work with complex filter queries. It really should not be necessary for us to write client-side code to replicate what the grid control natively does anyway when retrieving pages of data - but if that is the only way please could you refine the code sample above to cope with the use of more complex / compound filters?

Many thanks,

Charles




Hello,

i have a similar problem with server side filtering. I narrowed the problem down to a single line of code. I commented a file with my onActionBegin method.
I have spent hours trying to get around the problem, including adding the existing predicates to where myself. It did not work, always a json-exception.

Any hint much appreciated.

Regards,

Sascha Herrmann



Attachment: onActionBegin_edfa70bc.7z

DM Daniel Monshausen replied to Sascha Herrmann March 4, 2019 06:34 AM UTC

Hi Thavasianand,

I know that is what is causing the problem, unfortunately returning all the columns doesn't resolve it for us, because it defeats the whole purpose of doing server-side (indeed database-side) filtering. If I return all columns I have to return all DISTINCT rows for all columns, which therefore means returning the entire dataset - maybe tens of thousands of rows, just to get maybe 4-5 strings for a filter list! 

With the server-side filtering that we have built it is beautifully efficient when retrieving the actual page of data - if I need only 10 rows based on the filters applied and/or the page size then only 10 rows are returned from the database. That's because the dm.Where object is populated, and so we can use it to generate the necessary WHERE clause in SQL.

However, if - when clicking on a filter symbol just to change the filter settings - I need to send from database to application server and from application server to browser the entire contents of the database table, that is incredibly inefficient and makes all the server-side filtering totally pointless.

This could be very easily solved if the grid control simply populates the dm.Where object for a FilterChoiceRequest in exactly the same way as it does when retrieving a page of data. Is there any way to do that?

BTW we have already implemented a partial fix for this ourselves in the ActionBegin handler as follows:

function actionBegin(args)
{
    switch (args.requestType)
    {
        ...
        ...
        case "filterchoicerequest":
            var field = args.filterModel.options.field;
            args.query.addParams('filterField', field);
            var predicate = null;
            $.each(args.filterModel.filterSettings.properties.columns,
                function(index, item)
                {
                    if (item.properties.operator === "equal" && item.properties.field != field)
                    {
                        predicate = predicate === null ? new ej.data.Predicate(item.properties.field, item.properties.operator, item.properties.value) : predicate.or(item.properties.field, item.properties.operator, item.properties.value);
                    }
                });
            if(predicate !== null) args.query.where(predicate); 
            break;
        ...
        ...
    }
}

This works with the most basic filters (ticked lists). However this will not work with complex filter queries. It really should not be necessary for us to write client-side code to replicate what the grid control natively does anyway when retrieving pages of data - but if that is the only way please could you refine the code sample above to cope with the use of more complex / compound filters?

Many thanks,

Charles




Hello,

i have a similar problem with server side filtering. I narrowed the problem down to a single line of code. I commented a file with my onActionBegin method.
I have spent hours trying to get around the problem, including adding the existing predicates to where myself. It did not work, always a json-exception.

Any hint much appreciated.

Regards,

Sascha Herrmann



Attachment: onActionBegin_edfa70bc.7z

Related incident: https://www.syncfusion.com/support/directtrac/incidents/229453



TS Thavasianand Sankaranarayanan Syncfusion Team March 4, 2019 08:45 AM UTC

Hi Sascha, 

Query #1: Why is this in here? It only adds Fields to "SELECT" which are not selected on the server side. 

We have generated the second column filter query based on the first filter column values, when you open the second filter dialog. For example, If you filtered the OrderID column at first. After that you will filter EmployeeID column. In this case we have filter the EmployeeID column value based the previously filtered OrderID column value. So we must need to send previously filtered column details also to server side. In your code, you have sent “args.filterModel.options.field” to the server. But this has currently filter column name only. For this reason, we have used that code to collect and send the current and previously filtered column names to sever. This code is working fine at our end. It perfectly return selected field values from the server. 
 
Query #2: args.filterModel.existingPredicate: The existing predicates **ARE NOT SENT** to the server side. Why?  
 
By default, Grid cannot send the existing predicate details while open the filter dialog. If you want to send this predicates while open the dialog, then please refer the following code to achieve this requirement, 

<script type="text/javascript"> 
    function actionBegin(args) { 
        if (args.requestType === "filterchoicerequest") { 
            var filterfields = []; 
            var objFilter = Object.keys(args.filterModel.existingPredicate); 
            for (var i = 0; i < objFilter.length; i++) { 
                filterfields.push(objFilter[i]); 
            } 
            if (filterfields.indexOf(args.filterModel.options.field) == -1) 
                filterfields.push(args.filterModel.options.field); 
            args.query.distincts = []; 
            args.query.select(filterfields); // Created the select query 
            if (this.filterSettings.columns.length) { 
                var pred = new ej.data.Predicate(this.filterSettings.columns[0].properties.field, this.filterSettings.columns[0].properties.operator, this.filterSettings.columns[0].properties.value); 
                for (var i = 1; i < this.filterSettings.columns.length; i++) { 
                    pred = pred[this.filterSettings.columns[i].properties.predicate](this.filterSettings.columns[i].properties.field, this.filterSettings.columns[i].properties.operator, this.filterSettings.columns[i].properties.value); 
                } 
                args.query.where(pred); 
            } 
        } 
    } 
</script> 

We have prepared the sample with this requirement and attached that sample with this update for your reference. You can find that sample in the below link, 


If we misunderstood your query, please share your exact requirement for further assistance. 

Regards, 
Thavasianand S. 


Loader.
Up arrow icon