Data Retrieval with Grouping¶

Backendless offers a robust feature set for managing and querying your database, including the ability to retrieve data in grouped formats. While Aggregate Functions in Backendless provide a powerful API for summarizing and calculating data across groups, this chapter delves into a distinct and equally important capability: Grouped Data Retrieval.

This specialized functionality allows for the organization of records into groups based on specific criteria without necessarily performing aggregate calculations. It's an essential feature for applications requiring data to be displayed or processed in categorized formats, enhancing both the user experience and the efficiency of data analysis.

To illustrate, consider a scenario involving a database table of product records. In many applications, there's a need to display products grouped by characteristics such as category name and rating.

products-table-groupping

Below is the desired result, the products are grouped by category and then rating (some categories and ratings are collapsed for brevity):

grouped-data-products

The functionality covered in this chapter precisely addresses this requirement, enabling the retrieval of records from the database that are neatly organized into groups according to predefined criteria.By exploring the Grouped Data Retrieval feature, you'll learn how to leverage Backendless to achieve more refined data presentation and organization in your applications. This chapter aims to equip you with the knowledge to effectively use this capability, distinguishing it from aggregate functions and highlighting its unique benefits in application development.

Method¶

POST

Endpoint URL¶

Important

Make sure to replace xxxx in the domain name in the request specification below to the one assigned to your application.

https://xxxx.backendless.app/api/data/data-grouping/<tableOrViewName>

where:

Argument	Description
`<tableOrViewName>`	Name of a data table or view to retrieve grouped data from.

Request Headers¶

Content-Type:application/json

where:

Argument	Description
`Content-Type`	Must be set to `application/json`. This header is mandatory.

Request Body¶

The request body must be a JSON object with the structure shown below:

{  
  "groupBy": String,  
  "pageSize": int,  
  "groupPageSize": int,  
  "recordsPageSize": int,  
  "offset": int,  
  "where": String,  
  "distinct": boolean,  
  "excludeProps": String,  
  "props": String,  
  "loadRelations": String,  
  "relationsPageSize": int,  
  "relationsDepth": int,  
  "sortBy": String,  
  "groupPath": [  
    {  
      "column": String,  
      "value": any  
    }  
  ]  
}

where:

Argument	Description
`groupBy`	Sets the names of the columns to group the results by. Multiple column names must be separated by comma. If more than one column name is specified, returned records will be nested in the hierarchy of the specified columns. Suppose the request specifies two columns category and rating (as shown in the screenshot above): The top level of the hierarchy in the response will be a collection of categories. Each category group will have a collection of objects representing ratings. Each rating group will have a collection of records that belong to the corresponding category and rating combination.
`pageSize`	Optional parameter. Sets the page size for the top level group. If the value is specified, it dictates how many groups at the top level will be returned by the server. See the Response Body section to understand the "group level" concept better. In reference to the schematic structure of the response shown in the Response Body section, this parameter controls how many "Category" groups will be included in the response.
`groupPageSize`	Optional parameter. Sets the number of "intermediary" groups to be included in the response. This applies only if the `groupBy` parameter contains more than one group. Any group(s) of the second and lower orders are considered intermediary. See the Response Body section to understand the "group level" concept better. In reference to the schematic structure of the response shown in the Response Body section, this parameter controls how many "Rating" groups will be included in the response.
`recordsPageSize`	Optional parameter. Sets the maximum number of database records to be included into each "terminal" group.
`offset`	Optional parameter. Zero-based index of the object in the persistent store from which to run the search. This parameter should be used when implementing paged access to data. Suppose the first request returned 20 objects (if `pageSize` is set to 20) and there are 100 objects total. The subsequent request can set offset to 20, so the next batch of objects is loaded sequentially.
`where`	Optional parameter. A search query used by the server it to determine objects matching the condition. Refer to the Search With The Where Clause topic for more information.
`distinct`	Optional parameter. If set to `true`, eliminates all duplicate records returned by the query.
`excludeProps`	Optional parameter. Allows you to exclude specific properties(columns) from the retrieved data. If specifying multiple columns, they must be separated by comma.
`props`	Optional parameter. A string value that contains a comma-separated list of column names to be included in the response.

Argument	Description
`loadRelations`	Optional parameter. A string value that contains a list of related columns to be included into the response. Use the dot-notation to reference relation of a relation, for example `relatedAddressColumn.cityName`.
`relationsPageSize`	Optional parameter. Sets the number of related objects to be returned in the response.
`relationsDepth`	Optional parameter. Sets the hierarchical depth for the relations to be included with the retrieved records.
`sortBy`	Optional parameter. A string value that contains a comma-separated list of columns to sort the records by.
`groupPath`	Optional parameter. Used to set the scope of the request for paging purposes.

Return Value¶

The groupBy request argument is designed to organize query results according to specified column names, facilitating structured data retrieval based on one or more criteria. When using this grouping API in Backendless, the groupBy argument allows you to define the columns that will serve as the basis for grouping the returned records. To specify multiple columns for grouping, separate each column name with a comma.

When multiple column names are provided, the API generates a nested hierarchy within the returned records, reflecting the sequence of the specified columns. For example, if you specify "category" and "rating" as your grouping columns, the API response is structured as follows:

The highest level of the hierarchy features a collection of distinct categories.
Within each category, there is a further subdivision into rating groups.
Each rating group contains the records that match the specific category and rating criteria.

This hierarchical organization allows for a granular analysis of the data, enabling you to drill down into specific subsets based on the combination of grouping columns you define.

Here's a visual representation to illustrate this concept:

nested-groups-concept

This structure provides a clear and organized way to interpret the query results, making it easier to understand the relationships and distributions among the grouped data. Below is a sample JSON response showing groups of categories containing groups of ratings:

{
  "hasNextPage": true,
  "items": [
    {
      "groupBy": {
        "column": "category",
        "value": "Gizmo"
      },
      "hasNextPage": true,
      "items": [
        {
          "groupBy": {
            "column": "rating",
            "value": 4.6
          },
          "hasNextPage": false,
          "items": [
            {
              "name": "Rustic Paper Wallet",
              "___class": "Product",
              "price": 29.46,
              "objectId": "1"
            },
            {
              "name": "Aerodynamic Concrete Lamp",
              "___class": "Product",
              "price": 46.76,
              "objectId": "197"
            }
          ]
        },
        {
          "groupBy": {
            "column": "rating",
            "value": 4.3
          },
          "hasNextPage": true,
          "items": [
            {
              "name": "Mediocre Wooden Table",
              "___class": "Product",
              "price": 31.79,
              "objectId": "10"
            },
            {
              "name": "Gorgeous Aluminum Plate",
              "___class": "Product",
              "price": 50.05,
              "objectId": "128"
            }
          ]
        }
      ]
    },
    {
      "groupBy": {
        "column": "category",
        "value": "Gadget"
      },
      "hasNextPage": true,
      "items": [
        {
          "groupBy": {
            "column": "rating",
            "value": 3.6
          },
          "hasNextPage": false,
          "items": [
            {
              "name": "Durable Cotton Bench",
              "___class": "Product",
              "price": 93.22,
              "objectId": "101"
            },
            {
              "name": "Gorgeous Linen Keyboard",
              "___class": "Product",
              "price": 28.96,
              "objectId": "119"
            }
          ]
        },
        {
          "groupBy": {
            "column": "rating",
            "value": 4
          },
          "hasNextPage": true,
          "items": [
            {
              "name": "Lightweight Granite Hat",
              "___class": "Product",
              "price": 31.36,
              "objectId": "102"
            },
            {
              "name": "Fantastic Wool Shirt",
              "___class": "Product",
              "price": 35.15,
              "objectId": "105"
            }
          ]
        }
      ]
    }
  ]
}

Grouped Data Paging¶

In Backendless, data paging operates on two fundamental concepts: offset and pageSize. The offset defines the starting point in a dataset from which the server begins to return records, up to the number specified by pageSize. While these principles are thoroughly explained in the Data Retrieval with Paging chapter, their usage takes on additional complexity within the context of the Grouping API.

A pivotal aspect of paging through grouped data is understanding the unique functionality offered by the API. Specifically, it introduces three distinct pageSize parameters: pageSize, groupPageSize, and recordsPageSize, alongside a single offset parameter. The critical question arises: To which "pageSize" does the offset apply? The answer lies in the strategic use of the groupPath parameter, which determines the scope of paging.

Imagine a query designed to group data by country, region, and city. The application of various pageSize parameters is depicted in an illustrative figure below:

grouping-api-page-sizes

Navigating through the grouped data necessitates establishing a clear context, pinpointing the exact group level for pagination. This could range across several layers:

Country-level categories (top level group).
Regions (states or provinces) within a specified country.
Cities located within a particular region and country.
Individual records tied to a specific city, region, and country combination.

This context is precisely set using the groupPath parameter, a JSON array comprising one or more objects, each defining a paging scope through its structure:

{  
  "column": "name of the column",  
  "value": "specific value to match"   
}

The table below offers a detailed view on how the groupPath parameter dynamically adjusts the paging scope, exemplified by the "country,region,city" grouping:

Paging Context	groupPath value	Notes
Country-level categories (top-level group)	N/A (not present)	When the `groupPath` parameter is not present, subsequent requests to the grouping API (assuming the application properly increments the `offset` parameter) will result in paging through the top-level categories.
Regions (states or provinces) within a specific country	`"groupPath" : [` `{` `"column":"country",` `"value":"United States"` `}` `]`	Server will page through the states (regions) in the specified country. The format of the response is the same, however, there will be only one country - "United States" as specified in the `groupPath`parameter.
Cities located in a specific region and country	`"groupPath" : [` `{` `"column":"country",` `"value":"United States"` `},` `{` `"column":"region",` `"value":"Texas"` `}` `]`	Server will page through the groups of cities belonging to the region (state) and the country specified in the request.
Individual records in a specific city/region/country	`"groupPath" : [` `{` `"column":"country",` `"value":"United States"` `},` `{` `"column":"region",` `"value":"Texas"` `},` `{` `"column":"city",` `"value":"Austin"` `}` `]`	Server will page through the records which belong to the specified city/region/country.

Codeless Reference¶

data_retrieval_with_grouping_1

where:

Argument	Description
`groupBy`	Sets the names of the columns to group the results by. Multiple column names must be separated by comma. If more than one column name is specified, returned records will be nested in the hierarchy of the specified columns. Suppose the request specifies two columns category and rating (as shown in the screenshot above): The top level of the hierarchy in the response will be a collection of categories. Each category group will have a collection of objects representing ratings. Each rating group will have a collection of records that belong to the corresponding category and rating combination.
`pageSize`	Optional parameter. Sets the page size for the top level group. If the value is specified, it dictates how many groups at the top level will be returned by the server. See the Response Body section to understand the "group level" concept better. In reference to the schematic structure of the response shown in the Response Body section, this parameter controls how many "Category" groups will be included in the response.
`groupPageSize`	Optional parameter. Sets the number of "intermediary" groups to be included in the response. This applies only if the `groupBy` parameter contains more than one group. Any group(s) of the second and lower orders are considered intermediary. See the Response Body section to understand the "group level" concept better. In reference to the schematic structure of the response shown in the Response Body section, this parameter controls how many "Rating" groups will be included in the response.
`recordsPageSize`	Optional parameter. Sets the maximum number of database records to be included into each "terminal" group.
`offset`	Optional parameter. Zero-based index of the object in the persistent store from which to run the search. This parameter should be used when implementing paged access to data. Suppose the first request returned 20 objects (if `pageSize` is set to 20) and there are 100 objects total. The subsequent request can set offset to 20, so the next batch of objects is loaded sequentially.
`where`	Optional parameter. A search query used by the server it to determine objects matching the condition. Refer to the Search With The Where Clause topic for more information.
`distinct`	Optional parameter. If set to `true`, eliminates all duplicate records returned by the query.
`excludeProps`	Optional parameter. Allows you to exclude specific properties(columns) from the retrieved data. If specifying multiple columns, they must be separated by comma.
`props`	Optional parameter. A string value that contains a comma-separated list of column names to be included in the response.
`loadRelations`	Optional parameter. A string value that contains a list of related columns to be included into the response. Use the dot-notation to reference relation of a relation, for example `relatedAddressColumn.cityName`.
`relationsPageSize`	Optional parameter. Sets the number of related objects to be returned in the response.
`relationsDepth`	Optional parameter. Sets the hierarchical depth for the relations to be included with the retrieved records.
`sortBy`	Optional parameter. A string value that contains a comma-separated list of columns to sort the records by.
`groupPath`	Optional parameter. Used to set the scope of the request for paging purposes.

Codeless Implementation Primer¶

For a comprehensive guide on leveraging the Grouping API with Codeless and UI Builder within Backendless, please visit our support forum. This detailed article offers step-by-step instructions and insights into effectively utilizing the Grouping API to organize and display your data. It covers both the setup process in Codeless and how to visually represent grouped data using UI Builder, ensuring you have all the necessary tools to enhance your application's data management capabilities.

To access the article, follow this link: Visit How-to Guide on Grouping API with Codeless and UI Builder.

This resource is designed to assist developers in navigating the integration of Backendless's powerful grouping functionalities into their projects, providing a clear path to implementing sophisticated data visualization and organization features.