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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Get filtered/searched data subset AND optimize query processing?

Thread ID:

Created:

Updated:

Platform:

Replies:

140754 Nov 5,2018 04:18 PM UTC Feb 20,2019 10:03 AM UTC ASP.NET Core - EJ 2 18
loading
Tags: DataGrid
Philippe-Antoine Major
Asked On November 5, 2018 04:18 PM UTC

Initially posted on this github issue.

I'm trying to find a way to get the actual filtered/searched dataset so that I can perform operations on it (like select all resulting data after search/filter has been applied)

As mentioned on the Github issue, it is kind of possible to work around this issue by setting the "persistSelection" setting to true and then use the `selectAll` function to get the actual results. But this use case breaks another use case in which the user would like only the visible data to be selected when clicking on the `selectAll` checkbox on top of grid. 

As part of my investigation trying to find a way to get the filtered data directly from the API, I realized it was not implemented (not that I could find, but if it exists kindly let me know how and I will be pleased to use it :P ). I also discovered what seems to me like a good optimization point in the `processQuery` function of the JsonAdaptor.

If you look in the JsonAdaptor processQuery function, you will see that it always uses the full dataset (`dataManager.dataSource.json.slice(0);`) to perform the queries (searching, filtering, paging, etc...). To me, this seems suboptimal and could use a set of data that has already been filtered/searched instead of reusing the full dataset. Also, whenever a user is changing page, the whole dataset has to be reprocessed for search, filter and other operations before proceeding to the final paging part of the filters. That right there kills the potential performance of paging operation by a large margin. 

I did implement a custom way to only perform the whole queries pipeline only if a search or filter operation is part of the queries and reusing the filtered/searched dataset for all other operations. Would like to know what you think of this approach and maybe implement it in official source to let everyone benefit from it? 

Thanks in advance.



Mohammed Farook J [Syncfusion]
Replied On November 6, 2018 09:18 AM UTC

Hi Philippe, 
 
Thanks for contacting Syncfusion support. 
We have validated your and find the response as below 
By default the selectAll(checkbox) return all records of entire grid.  Selection can be persisted on all the operations using selectionSettings-> persistSelection property. For persisting selection on the Grid, any one of the column should be defined as a primary key using columns->isPrimaryKey property. 
Query :  if a search or filter operation is part of the queries and reusing the filtered/searched dataset for all other operations. 
We have already option for on-demand loading concept.  It loads the data based on your page request only. Please find the demo and documentation for your reference. 
 
Regards, 
J Mohammed Farook 


Mohammed Farook J [Syncfusion]
Replied On November 6, 2018 09:20 AM UTC

Hi Philippe, 
  
 
Ignore previous update. 
  
 
Thanks for contacting Syncfusion support. 
 
We have validated your and find the response as below 
 
By default the selectAll(checkbox) return all records of entire grid.  Selection can be persisted on all the operations using selectionSettings-> persistSelection property. For persisting selection on the Grid, any one of the column should be defined as a primary key using columns->isPrimaryKey property. 
 
Query :  if a search or filter operation is part of the queries and reusing the filtered/searched dataset for all other operations. 
We have already option for on-demand loading concept.  It loads the data based on your page request only. Please find the demo and documentation for your reference. 
 
 
Regards, 
J Mohammed Farook 


Philippe-Antoine Major
Replied On November 6, 2018 12:56 PM UTC

I think you might have misunderstood the requirement here, I don't want to only load the page data on demand, I would want all data to be loaded on the client (offline = "true") and then be able to search/query. This is doable right now, but there's no way to get back the selected/searched subset of data only, there's only the whole dataSource.json or the current page records, none of which correspond to what I'm after. 

Philippe-Antoine Major
Replied On November 6, 2018 12:58 PM UTC

Also as mentioned on the Github issue, this is what I'm really looking for (https://www.syncfusion.com/forums/118500/how-to-get-filtered-data-from-ejgrid)

Which is the getFilteredRecords()

Mohammed Farook J [Syncfusion]
Replied On November 7, 2018 11:13 AM UTC

Hi Philippe,  
 
We analyzed your query, Here we can achieved your requirement  by the following way. 
 
 
 
 
function btnClick(){ 
                var gridFilteredRecords; 
                var gridObj = document.getElementById('Grid')['ej2_instances'][0]; 
                var query = gridObj.getDataModule().generateQuery(gridObj.filterSettings.columns[0].properties); 
                gridObj.allowPaging = false; 
                gridObj.getDataModule().getData({}, query).then(function(e){ gridFilteredRecords = e.result }) 
                gridObj.allowPaging = true; 
} 
 
 
In above code example we can get the filtered record through button click event with gridData method of Grid.  
 
Regards, 
J Mohammed Farook 


Philippe-Antoine Major
Replied On November 7, 2018 01:07 PM UTC

Hi Mr Farook,

Thanks for the reply. First of all, I'd like to know what's the best way to communicate for this issue, is it here or through the github repository?

Also as I already mentioned on the github issue, don't you think this is an inefficient way to retrieve the actual filtered dataset? I could probably submit a PR if you think it would be worth it showing how I did implement a very basic way to save the actual filtered dataset in the JsonAdaptor processQuery function. 

This could also be useful to prevent having to reprocess all the filters/search on the whole dataset whenever a user only changes page in the grid. (Correct me if I'm wrong but this is how I read it in the code, the whole filters/queries are reprocessed against the json.dataSource everytime a new page is requested, in the processQuery function)



Mohammed Farook J [Syncfusion]
Replied On November 9, 2018 01:30 PM UTC

 Hi Philippe,  
 
Query : Thanks for the reply. First of all, I'd like to know what's the best way to communicate for this issue, is it here or through the github repository? 
 
We have provided  equal priority for Forum and Github. So For your convenience you asked the queries Forum / Github. 
 
 
Query : This could also be useful to prevent having to reprocess all the filters/search on the whole dataset whenever a user only changes page in the grid.  
 
 
By default, We can processed grid actions filtering /searching with whole dataset. Because the user can search in same column in multiple times without clear. 
 
For example  
 
 
 
 
 
 
 
In above two screenshot we can filter same column without clear.  Like this scenario we need to filter whole dataSource and need update the paging in every time.   
 
Before start your requirement we need to following confirmation. Do you want apply the filter/search query in filtered dataset (when filter other columns)? . This is not meet your requirement could you please explain more detail about your requirement. 
  
 
Regards, 
J Mohamemd Farook  


Philippe-Antoine Major
Replied On November 9, 2018 06:10 PM UTC

Hi Mr Farook, 

I agree with you that if a second search is performed, the whole dataset should be used again for the search operation. The solution I proposed does in no way affect that behavior.

Effectively, on every request I would check the query type and if it's a search or filter operation, use the whole dataset for the search/filter query. Otherwise I would use the already searched/filtered set. Is it more clear now?

I guess I'm really looking for the exact same behavior as what was possible with getFilteredRecords(), (I never used it personally but if the naming was done correctly, that would effectively return a subset of data, maybe after re-running the whole queries against the full dataset, in which case it would also not be really optimal in my opinion.



Venkatesh Ayothi Raman [Syncfusion]
Replied On November 12, 2018 11:54 AM UTC

Hi Philippe, 
 
Thanks for the update. 
 
 
Before achieving your requirement, could you please confirm whether following is your requirement or not? 
 
Do you want to perform filtering operation for already filtered dataset? For example, if we perform filter/search operation and the result contains 10 records. Now, do you want to perform filter/search operation from the result set datasource(ie.. from the result set of 10 records). 
 
If this is not your requirement, then please provide more information regarding your requirement. It will be more helpful to provide solution as early as possible. 
 
Regards, 
Venkatesh Ayothiraman. 


Philippe-Antoine Major
Replied On November 12, 2018 01:35 PM UTC

Hi there,

To answer your question directly, no, I don't want to perform a search over a previously searched dataset. That would effectively be a kind of "cascade search" feature, which is not what I'm asking for here. What I want is a way to retrieve the filtered/searched dataset so that I can do whatever I want with it (sending it to server for example for batch operation like toggling a state change or something like that).

I'm hence asking for a way to retrieve the results of the currently filtered/searched dataset. This is not possible right now as there is no variable keeping track of this subset of data. I'm not asking "how to" do something, I'm asking if it could be added as a new function in the DataManager, the JsonAdaptor or somewhere else where we could use the same function as what was available in previous versions (getFilteredRecords()), without re-running all the filters/searches etc... on the full dataset.

This also leads to the fact that it seems really inefficient at best to reprocess all queries on the whole dataset for a simple paging operation (there may be conditions that I overwatched which would make it mandatory to perform all the queries on the dataset prior to paging, but I can't think of one right now). 

My suggestion was to add a new variable within the DataManager or the JsonAdaptor which would be updated whenever a search/filter operation is performed on the dataset. Then when running the processQuery() function of the JsonAdaptor, check if there is a search/filter operation to be performed within the queries and if not, reuse the already filtered dataset, otherwise use the full dataset.




Mohammed Farook J [Syncfusion]
Replied On November 13, 2018 12:41 PM UTC

Hi Philippe, 
 
We have analyzed your query and made a custom sample on updating grid dataSouce with the searched data when search operation is performed. To achieve this, we have used actionComplete event of the grid. When search request is made with an empty string, initial dataSource is loaded to the grid. Please refer to the below sample for your reference. 
 
Code Example: 
 
 
[.cshtml] 
 
... 
<script> 
    var newData = []; 
    var gridData; 
    function load() { 
        gridData = document.getElementById("Grid").ej2_instances[0].dataSource;   //grid initial dataSource 
    }  
    function action(args) { 
        if (args.requestType == "searching") { 
            if (!args.searchString) { 
                this.dataSource = gridData; 
            } 
            else { 
                for (var i = 0; i < args.rows.length; i++) { 
                    newData.push(args.rows[i].data);                                     
                } 
                this.dataSource = newData;                         //searched records are given to the grid datasource 
                newData = []; 
            } 
       } 
 
    } 
</script> 
... 
 
 
 
 
Query : for filtering with already dataset. 
 
Please confirm filtering is also working same like for above searching sample? 
 
 
Query :   previous versions (getFilteredRecords()), without re-running all the filters/searches etc... on the full dataset. 
 
We have validated the your requirement and currently EJ2 is not support to getFilteredRecords method. So we have considered as usability “provide support for  get Filtered Records from the grid” feature. It will be available in any upcoming release. 
 
Regards, 
J Mohammed Farook  
 
 
 
Please get back to us for further assistance. 
 
Regards, 
J Mohammed Farook 


Philippe-Antoine Major
Replied On November 13, 2018 02:34 PM UTC

Hi Mr Farook. 

 

Yes what you proposed works perfectly (it is pretty much the same thing I was doing, except for the fact that you are doing it within the "onComplete" callback while I was proposing to do it directly within the "processQuery" function, which would help reduce the number of data to work with for other operations like paging. 

 

Query : for filtering with already dataset.  

Please confirm filtering is also working same like for above searching sample?

A: Yes it also works for filtering, simpy by changing the code a bit and "or'ing" the requestType check like so:

 

var newData = [];

var gridData;

function load() {

    gridData = document.getElementById("Grid").ej2_instances[0].dataSource;

}

function action(args) {

    if (args.requestType == "searching" || args.requestType == "filtering") {

        if (!args.searchString && !args.currentFilteringColumn) {

            this.dataSource = gridData;

        }

        else {

            for (var i = 0; i < args.rows.length; i++) {

                newData.push(args.rows[i].data);

            }

            this.dataSource = newData;

            newData = [];

        }

    }

}

 

The only thing that differs in the way I implemented it was that I was personally not replacing the original dataSource property of the DataManager. 


Instead, I created a new variable (dataSourceFiltered) which held the filtered/searched dataset and which I was using for the "processQuery" function instead of the original dataSource whenever the request type was anything else than "filtering" or "searching". 


In the end, the result is pretty much the same, except for the fact that your proposed alternative kind of "pollutes" the global scope, which could be seen as undesirable. If you could add a new property to the dataManager which would keep the reference to the original dataset (such as with the gridData variable in the code you posted), that would be awesome in my opinion! 


Then you could provide API endpoints like "getFilteredRecords()", which in your implementation would simply return the updated "dataSource" and another one like "getOriginalRecords()" or equivalent, which would return the content of "gridData" above.

  

Thanks for your time and hope you will implement it in the solution for everyone to take profit of the performance improvement for operations such as paging!


P.S: Sorry for the formatting, I don't know why but the editor does not respect the text format I entered at all and I have no menu displayed (top bar) when editing the response... so sorry for the look, but the content should be fine :) )


Mohammed Farook J [Syncfusion]
Replied On November 14, 2018 01:06 PM UTC

Hi Philippe,  
 
We have validated your requirement,  We suggest to use , you can store the grid original dataSource in global variable. You can reuse the grid original dataSource based on your needs.  
 
Here  we can defined the gridData as global variable and assign the grid original dataSource in load event of Grid. we Please find the code example. 
 
  
<div> 
    <ejs-grid id="Grid" dataSource="ViewBag.DataSource" load="load" allowResizing="true" allowPaging="true" allowSorting="true" toolbar="@(new List<string>() {"Add", "Edit", "Delete", "Cancel", "Update"})"> 
        <e-grid-editSettings allowAdding="true" allowDeleting="true" allowEditing="true" mode="Normal"></e-grid-editSettings> 
        <e-grid-columns> 
            <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" width="120"></e-grid-column> 
            <e-grid-column field="CustomerID" foreignKeyField="Name" foreignKeyValue="Name" headerText="Customer ID" width="120" edit="@(new {create="create" , read="read", write="write", destroy = "destroy" })"></e-grid-column> 
            <e-grid-column field="EmployeeID" foreignKeyField="ID" foreignKeyValue="ID" dataSource="ViewBag.DropDownData" headerText="Employee ID" width="120"></e-grid-column> 
            <e-grid-column field="ShipCountry" headerText="Ship Country" width="120"></e-grid-column> 
        </e-grid-columns> 
 
    </ejs-grid> 
</div> 
 
<script> 
    var gridData; 
    function load() { 
        gridData = this.dataSource; 
    } 
</script> 
 
 
 
 
Regards, 
J Mohammed Farook 
 
 


Philippe-Antoine Major
Replied On November 14, 2018 03:03 PM UTC

Yes effectively, it is the same as what you said in your earlier post (in which you also use the onComplete event and which works perfectly). I totally agree that this is working as expected, the only thing I was trying to say was that it could be better if there was a new variable added to the grid or the dataManager which would keep the "initial grid data" (the same data as what you pushed in gridData on load event). 

This way, you would prevent "polluting" the global javascript scope, that's it :)

The proposed solution works perfectly so I guess you can close this thread. I would like to be updated though on if/when this will be included in the default packages so that I can remove the custom code from mine.

Thanks again and have a good day!

Philippe-Antoine Major
Replied On November 14, 2018 03:31 PM UTC

There's only one point that remains to be addressed performance-wise and it's the fact that the jsonAdaptor (and possibly all other adaptors) will still re-process the search/filter queries on the dataset even if the queries haven't changed. 

That's a bit less important as the dataset will already have been reduced in size by simply updating the datasource after a search/filter has completed, but it's still inefficient to reprocess the search and filter queries every time a user only performs a paging operation, what do you think?

It could be feasible to check if the search string or the column filter(s) has changed since last performing the search/filter queries and only process them if it has, skip otherwise? Would that sound good to you?

Mohammed Farook J [Syncfusion]
Replied On November 15, 2018 01:08 PM UTC

Hi Philippe, 
 
We have considered provide support for grid query processing from current dataset as a feature. This feature will be available in any of our upcoming release. 
 
Regards, 
J Mohammed Farook  


Philippe-Antoine Major
Replied On February 19, 2019 03:06 PM UTC

Just to link the feedback note, here is the link for the "feature request/feedback" If you stumble upon this thread and would like it to be implemented, upvote the feature request.

Pavithra Subramaniyam [Syncfusion]
Replied On February 20, 2019 10:03 AM UTC

Hi Philippe, 
 
Thanks for your update. 
 
Regards, 
Pavithra S. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;