In a previous article, we briefly described how a grid control communicates to a remote server using the OData protocol. The majority of modern grid controls allow convenient data grouping by several columns. Let’s consider what queries are generated by the grid and sent to a REST server when the grid performs automatic data grouping.
Convenience (why it is needed)
Grid controls that enable grouping entries across multiple columns greatly simplify life for both developers and end users. It is very handy to be able to group, for example, a task list, highlight the problems requiring assistance, separate them by priority, etc.
In the above example, you can group the tasks by employees and look through all the tasks (which are at the same time grouped by status and priority) for the selected employee.
What is particularly nice for the developers is that creating such a comfortable interface requires only a few lines of code.
How to do that
We have prepared a sample task list.
http://databoom.space/samples_devexpress_grouping.html
For this example, we used dxDataGrid by Devexpress (this control is part of DevExtreme Web collection of components).
More details can be found in dxDataGrid documentation:
http://js.devexpress.com/Documentation/ApiReference/UI_Widgets/dxDataGrid/
Examples can be found here:
Example of using the grid with databoom server:
http://databoom.space/samples_devexpress_grid.html
To make the grid work with databoom it is sufficient to specify the URL to the list of data to display in the grid (for example, a list of people):
https://samples.databoom.space/api1/sampledb/collections/persons
In order to turn on the grouping in the grid it is enough to set the property
groupPanel: {visible: true}
After that you can group data just by dragging the column headings to the grouping panel.
To make the grid group data by some columns from the beginning, it is necessary to specify in the columns description:
groupIndex: 0 (for the first column in the group)
groupIndex: 1 (for the second column, … etc.)
Just a couple of settings and the end users will be thankful
How does it work
We pointed to the grid the URL where it can get the task list.
https://samples.databoom.space/api1/sampledb/collections/
The grid adds various query terms to the URL in order to automatically generate a request to the server.
The first query that the grid sends to the server to display a list of tasks grouped by status and priority:
- $orderby=Task_Status,Task_Priority – sort the entries by status and priority
- $top=31 – limit the result by 31 records
- $inlinecount=allpages – return the total number of records in the collection
Since the data is sorted, then the grid takes the field values “Task_Status” and “Task_Priority” for the first record as a group. There are a few records with duplicate values of these fields. Next come a few records with the priority changed. That is, in this case a very simple request is enough to get the sorted data.
If we scroll down the grid to see the rest of the records, the grid will query the server for the subsequent records. For example:
One more query parameter has been added:
- $skip=29 – skip the specified number of records
This query is identical to the simple query that the grid would send to the server to display a list of tasks sorted by status and priority.
Now let’s collapse a group of records with the status “Completed”. The grid sends two requests to the server:
- 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
The first query
- $filter=(Task_Status eq ‘Completed’) – select all the records that have status “Completed” (those we are collapsing)
- $top=1 – The grid requests only one record
- $inlinecount=allpages – The grid asks the server to return the total number of records found
This query is needed in order to know the number of records with status “Completed” (those we are collapsing).
The second query
- $orderby=Task_Status,Task_Priority – sort data by the specified fields
- $filter=(Task_Status ne ‘Completed’) – select all the records that have a status other than “Completed” (the records going after the collapsed ones)
- $top=31 – the Grid requests 31 records
- $inlinecount=allpages – the Grid requires the total number of records with the given status
If we collapse the subsequent group, the first of these two queries also requests the number of records in the group to collapse. But the second query now includes exclusion from the records in the two collapsed groups:
- $filter=(Task_Status ne ‘Completed’) and (Task_Status ne ‘Deferred’)
Thus, it is enough for grouping to use queries with sorting, filtering, and pagination.
Notes (how it could work)
Let’s consider a situation with a large number of groups. The query would then contain a large number of conditions ($filter=(field1 ne “Chicago”) and (field1 ne “Boston”) and … and … and…). The query becomes too long and ineffective.
It would be better to execute an aggregate query to get a list of groups with the count of records within each group. Then, to expand a group we’d need to select a small number of records within the group. That’s a query (given the necessary indices) that is very fast.
But for using this method the standard should support the aggregation queries.
The ability to perform data aggregation queries emerged just recently in OData version 4.0. Grid control manufacturers are going to implement support for these features in new versions of their products.
Databoom 