Grid, Data Grouping and OData
In one of the previous articles, we briefly described how grid controls work with a remote server using the OData protocol. Most modern grid controls allow you to conveniently group data into several columns. Let us take a closer look at how requests to the REST server are formulated, in the case when the grid performs automatic grouping of data.
Grid controls, allowing you to group records in several columns, greatly simplify life for both the programmer and the end user. It is very convenient to be able to group, for example, a list of tasks, select tasks that need help, divide them by priorities, etc.
Then you can group them by employee and view all tasks for the selected employee, also grouped by status and priority.
')
It is especially pleasant for developers that a few lines of code are enough to create such a comfortable interface.
We have prepared an example of a task list.
databoom.space/samples_devexpress_grouping.html
For this example, we used Devexpress's dxDataGrid (this grid is included in the DevExtreme Web component set)
To enable grouping in the grid, it is enough to specify the property
groupPanel: {visible: true}
After that, the grid allows you to group data by dragging column headers to the grouping panel.
For the first display of the grid to group the data by any columns, it is necessary to indicate in the description of the columns:
groupIndex: 0 (for the first column, the contents of which group data)
groupIndex: 1 (for the second column, etc.)
Just a couple of settings, and users will be grateful to you :)
We specified the URL of the grid where it can get a list of tasks.
samples.databoom.space/api1/sampledb/collections
Next, the grid automatically generates a request to the server by adding various query conditions to the URL.
The first request that the grid sends to the server to display a list of tasks grouped by status and priority:
https://samples.databoom.space/api1/sampledb/collections/tasks?$orderby=Task_Status,Task_Priority,id&$top=21&$inlinecount=allpages
Since the data is sorted, it takes as the group the values ​​of the Task_Status and Task_Priority fields for the first record. Next are a few entries with duplicate values ​​of these fields. Next comes a few entries that have changed their priority. That is, in this case, a simple query is enough to get the sorted data.
If we scroll the grid down to view the remaining records, the grid will ask the server for the following records, for example:
https://samples.databoom.space/api1/sampledb/collections/tasks?$orderby=Task_Status,Task_Priority,id&$skip=29&$top=32&$inlinecount=allpages
In this case, another query parameter was added:
The above requests are no different from simple requests that the grid would send to the server to display a list of tasks sorted by status and priority.
Now let's try to collapse the group of records with the status “Completed”. Grid sends to the server 2 requests:
First request
This query is needed in order to find out the number of records with the status “Completed” (those that we collapse)
Second request
If we collapse the next group, then the first of these two queries also asks for the number of records for the collapsed group, but the following query now includes exceptions from the records in the two collapsed groups:
Thus, the grid is quite well managed with simple queries, using sorting, simple filtering, and pagination conditions.
In the case of a large number of groups, queries with filtering of all collapsed groups, with a large number of inequality conditions ($ filter = ... and ... and ... and ... and ...) become too large and not sufficiently effective.
One could just get a list of groups, and then ask for records on the equality of fields (all records belonging to a particular group), with a request to sort by these fields. Such a query, if there are necessary indices, quickly obtains a small number of records within one group and then sorts this small amount.
But to work in this way it would be necessary to have the possibility of standardized execution of requests for aggregation of data.
The ability to perform requests for data aggregation appeared in the OData standard, version 4.0 , and grid controllers are going to implement support for these features in new versions of products.
Convenience (why it is needed)
Grid controls, allowing you to group records in several columns, greatly simplify life for both the programmer and the end user. It is very convenient to be able to group, for example, a list of tasks, select tasks that need help, divide them by priorities, etc.
Then you can group them by employee and view all tasks for the selected employee, also grouped by status and priority.
')
It is especially pleasant for developers that a few lines of code are enough to create such a comfortable interface.
How to do it
We have prepared an example of a task list.
databoom.space/samples_devexpress_grouping.html
For this example, we used Devexpress's dxDataGrid (this grid is included in the DevExtreme Web component set)
More details can be found in the dxDataGrid documentation:
js.devexpress.com/Documentation/ApiReference/UI_Widgets/dxDataGrid
See examples:
js.devexpress.com/Demos/WidgetsGallery/#demo/datagridgridpagingandscrollingpager/generic/light/default
A simple example of a grid operation without grouping with the databoom server:
databoom.space/samples_devexpress_grid.html
In order for the grid to work with databoom, it is enough to specify a URL with a list of data to be shown in the grid, for example, a list of people:
samples.databoom.space/api1/sampledb/collections/persons
To enable grouping in the grid, it is enough to specify the property
groupPanel: {visible: true}
After that, the grid allows you to group data by dragging column headers to the grouping panel.
For the first display of the grid to group the data by any columns, it is necessary to indicate in the description of the columns:
groupIndex: 0 (for the first column, the contents of which group data)
groupIndex: 1 (for the second column, etc.)
Just a couple of settings, and users will be grateful to you :)
How it works
We specified the URL of the grid where it can get a list of tasks.
samples.databoom.space/api1/sampledb/collections
Next, the grid automatically generates a request to the server by adding various query conditions to the URL.
The first request that the grid sends to the server to display a list of tasks grouped by status and priority:
https://samples.databoom.space/api1/sampledb/collections/tasks?$orderby=Task_Status,Task_Priority,id&$top=21&$inlinecount=allpages
- $ orderby = Task_Status, Task_Priority - sort entries by status and priority
- $ top = 31 - grid asks to limit the result and send 31 entries
- $ inlinecount = allpages - the grid asks to return the total number of records in the collection
Since the data is sorted, it takes as the group the values ​​of the Task_Status and Task_Priority fields for the first record. Next are a few entries with duplicate values ​​of these fields. Next comes a few entries that have changed their priority. That is, in this case, a simple query is enough to get the sorted data.
If we scroll the grid down to view the remaining records, the grid will ask the server for the following records, for example:
https://samples.databoom.space/api1/sampledb/collections/tasks?$orderby=Task_Status,Task_Priority,id&$skip=29&$top=32&$inlinecount=allpages
In this case, another query parameter was added:
- $ skip = 29 – skip the specified number of records
The above requests are no different from simple requests that the grid would send to the server to display a list of tasks sorted by status and priority.
Now let's try to collapse the group of records with the status “Completed”. Grid sends to the server 2 requests:
- https://samples.databoom.space/api1/sampledb/collections/tasks?$top=1&$filter=(Task_Status eq 'Completed') & $ inlinecount = allpages
- https://samples.databoom.space/api1/sampledb/collections/tasks?$orderby=Task_Status,Task_Priority,id&$top=31&$filter=((Task_Status ne 'Completed')) & $ inlinecount = allpages
First request
- $ filter = (Task_Status eq 'Completed') - select all records with the status "Completed" (the ones that we slam)
- $ top = 1 - grid asks only one entry
- $ inlinecount = allpages - the grid asks to return the total number of records with this status
This query is needed in order to find out the number of records with the status “Completed” (those that we collapse)
Second request
- $ orderby = Task_Status, Task_Priority - sort data by the specified fields
- $ filter = (Task_Status ne 'Completed') - select all records with a status other than “Completed” (those records that go after collapsed with the status “Completed”)
- $ top = 31 - grid requests 31 entries
- $ inlinecount = allpages - the grid asks to return the total number of records with this status
If we collapse the next group, then the first of these two queries also asks for the number of records for the collapsed group, but the following query now includes exceptions from the records in the two collapsed groups:
- $ filter = (Task_Status ne 'Completed') and (Task_Status ne 'Deferred')
Thus, the grid is quite well managed with simple queries, using sorting, simple filtering, and pagination conditions.
Remarks (how it might work)
In the case of a large number of groups, queries with filtering of all collapsed groups, with a large number of inequality conditions ($ filter = ... and ... and ... and ... and ...) become too large and not sufficiently effective.
One could just get a list of groups, and then ask for records on the equality of fields (all records belonging to a particular group), with a request to sort by these fields. Such a query, if there are necessary indices, quickly obtains a small number of records within one group and then sorts this small amount.
But to work in this way it would be necessary to have the possibility of standardized execution of requests for aggregation of data.
The ability to perform requests for data aggregation appeared in the OData standard, version 4.0 , and grid controllers are going to implement support for these features in new versions of products.
Source: https://habr.com/ru/post/254413/
All Articles