Performing OData Queries

OData specifies a simple, yet powerful query language that allows a client to request arbitrary filtering, sorting, paging and so on. A client is able to express, via query string parameters, the amount and order of the data that an OData service returns for the resource identified by the URI. The names of all query string parameters defined by OData are prefixed with a "$" character.

An OData request consists of a server address, service URI, entity set, and additional query options. There are many combinations of query options possible. A full documentation is available at http://www.odata.org.

$select is used to limit the result set for a consumer application by limiting the number of columns that are retrieved by the consumer. Here we only want to retrieve the business partner ID and the name of a business partner.

The query option $select is supported by the framework. The developer can, however, use this information to also limit the amount of data that is retrieved from the database if the interface being called also supports the use of select options.

$orderby is used to sort the result set for a consumer application by one or many columns. The sorting can be done ascending or descending following the natural order of characters and numbers.

The query option $orderby must be implemented by the developer. This is usually done by the SORT ABAP command or when selecting the data from the database.

$filter is used to limit the result set for a consumer application by limiting the number of rows that are retrieved. The following requests could be made by using $filter:

• Get all business partners whose company name starts with the letter "S".
• Get all sales orders with a total sum larger than €10,000.
• Get all products with a weight lesser then 1 kg.

With $filter, you can apply several options to filter a result set. A filter can be applied on all properties of an entity set. These should be marked as sap:filterable.

For the business partner, you could, for example, filter the name or the business partner ID. You could also perform a wild card search. The SAP Gateway framework will (if possible) convert the filter string passed with $filter into a select option table or oSQL statement, which can be used in the service implementation by the ABAP developer.

The use case of the $count OData option is to retrieve the full number of entries of a collection. This way the client developer can show the user how many objects were found.

Though the framework supports $count out of the box in OData V2, by default it performs a full table scan. It can, however, be made significantly faster by the developer.

In OData V2, $count is an OData option but not a query option. That means it can not be combined with other query options in one request. In OData V4, it is a query option and replaces the $inlinecount taking over its behavior from V2.

With $inlinecount=allpages, no separate call for $count is necessary in OData V2. This is important because every additional call to the service causes an additional round trip between the consumer and service. This must be avoided since latency is the biggest problem for response times, especially with mobile scenarios.

The query option $inlinecount must be implemented by the developer. Although there is no SQL equivalent, ABAP provides the number of read data sets of a SELECT statement in the sy-dbcnt system field.

In case of client side paging, you often need to determine the total number of values. Together with the result set, the number of all values that fits to the current filter criteria is provided to the client.

The query options $top and $skip can be used for client side paging.

When solely using the query option $top, the service will only return the first results specified by the number passed via $top, so that it fits on the first screen of the client application.

When navigating to the second page, you must use the $skip parameter. The client retrieves only six items ($top) but skips the first 6.

$top and $skip must be implemented by the developer. But depending on the service architecture used during development, the code for paging can be provided by the SAP Gateway framework. Two examples are Service Adaptation Definition Language (SADL) or Soft State Based Query Result Cache (SQRT).

Note

SELECT...OFFSET is available since AS ABAP 7.52.

Using the query options $top, $skip, and $inlinecount, the results of a query can be displayed in several pages through which the consumer can navigate. This functionality is called client side paging since the client forces the server to respond in a certain way.

When using client side paging with paged results, the client controls how many values are retrieved. This could lead to problems if, for example, a client tries to retrieve a large amount of data from the back-end.

It is important that the server is somehow shielded from such unintentional Denial of Service attacks. This is where server side paging comes into play.

The service can be implemented with only a limited number of rows to be returned. There is no conflict for the usage of client base and server based paging since the maximum chunks of data that can be retrieved should be much larger than the screen of a mobile device.

$expand is used to retrieve several entities that are connected via navigation properties in one request or response cycle.

When using $expand, you follow the navigation properties of an OData service. In our example, $expand is used to retrieve all sales orders and sales order items of a business partner.

The OData service requirements in this case include the following:

• 3 entity sets
• 2 associations
• 2 navigation properties

In the example not using $expand, three calls are needed at minimum to retrieve the data:

• First call /BusinessPartners(1).
• Second call /BusinessPartners(1)/SalesOrders.
• Third call /SalesOrders/SalesOrderItems

In the example using $expand, only one call is needed. This saves an additional roundtrip in the HTTP communication between client and server:

/BusinessPartners(1)?$expand=SalesOrders/SalesOrdetItems

Looking on the response of $expand, the leading entity is placed first in the result set followed by dependent entities when using JSON. When using Atom, the leading entity is found at the end of the result set.

In principal, the calls can be as complex as possible. The developer would have to decide up to which complexity $expand is handled generically by the SAP Gateway framework.

That means that if you implemented two entities with an appropriate navigation property, and if you were able to retrieve the sales orders of a business partner by using the foreign key retrieved from the selected business partner, the framework will deliver all entries.

How is $expand handled by the framework? It calls the implementation of the individual navigation properties. In our example, this is one method call for the business partner, one method call for the sales orders of the business partner, and one method call for the sales order items of each sales order. So the more sales orders there are, the more methods are called.

Hint

In OData V2, $filter only works on the top level element. In OData V4, $filter works on all levels.

OData batch processing enables you to send multiple operations in a single HTTP request. You can express that all these operations must be executed on the server with an all-or-nothing logic. In an OData batch request, the payload is represented as a multipart message. A batch request is sent to the batch endpoint of an OData service. The URI of the batch endpoint is the service root URI appended by $batch.