Using OData's $top, $skip and $count

[Paul_todd]

This blog post is part of the ongoing series "OData FAQ's"

 

Today we will look at the system query definitions for $top, $skip to do pagination and $count/$inlinecount to get a count of the number of records in the record set.

 

 

How do restrict the amount of data returned?

This is done by calculating an offset and the number of records to return. The offset is set using the $skip system query option and the number of items returned via the $top system query option. If only the first n items are required then it is sufficient to omit the $skip directive.

 

http://odata.netflix.com/v2/Catalog/Titles?$top=5 will return the top 5 items from the titles catalog at netflix. Here are using an implicit $skip of 0 and a $top of 5.

 

Compare this with the following which returns the titles from 1-6 in the netflix catalog

 

http://odata.netflix.com/v2/Catalog/Titles?$top=5&$skip=1

 

Here we are using and explicit $top of 1 meaning skip the first record and then return the next 5 records which are titles 1-6 in the catalog.

 

 

How do I get the number of records in the set?

Obtaining the number of records in a set is normally done one of two ways depending on the context.

 

Using $count

The $count service request returns the number of records in a collection or if the collection has a filter, the number of records matching the filter.

 

 

For example to get a count of the number of titles that netflix has stored you would use:

http://odata.netflix.com/v2/Catalog/Titles/$count

 

As at writing this is reporting as 160050

 

Using $inlineCount

The other option is to use the $inlinecount directive to get the count as part of the response body. Let us say for example you would like to read the 5 records but would also like to know how many records there are in total so that the paging controls and record count label can for example be updated then the count will be returned in the count element in the metadata namespace.

 

 

<feed …. xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" ...>

...

<m:count>160050</m:count>

 

 

or in JSON

 

 

d: {

results: [.....],

"__count": "160050"

};

 

 

For example to get a count of the number of titles Netflix has available as well as the first 5 records, you will make the request

http://odata.netflix.com/v2/Catalog/Titles?$top=5&$inlinecount=allpages.

 

This will return the same number of records as $count in the count element or property but only the first 5 entities will be returned as requested by the $top system query option. This is very useful in the mobile scenario where pagination is typically used to improve performance. For example we can take the count and divide it by the number of items we want on a page and this will yield the number of pages in the recordset and then this makes it easy to calculate the $top and $skip values to navigate between pages or navigate to an arbitrary page.

 

In the next post we will look at the $select and $expand system query options to restrict the amount of data and/or reduce the number of round trips to the server.

[Pavan_Golesar]

Hello,

can anyone help?

[Paul_todd]

You /$count is in the wrong place, it should be before the query parameters on the URL (before the '?').

I would also replace the spaces with either %20 or the '+' character to the make the URL a valid URL.

Here is a sample from the public northwind database.

http://services.odata.org/V4/Northwind/Northwind.svc/Products/$count?$filter=ProductID eq 1

[former_member822159]

Thank you for the clarification,

My question here is how to store number of lines to later use it in backend (abap) or in frontend (sapui5 application)

 

Here's my URL : /sap/opu/odata/SAP/ZFIORI_PROJECT_SRV_02/PO_HeaderSet/$count?$filter=Bstyp eq 'A'

 

It returns the number of rows (which is 10; correct ) but it no longer returns the records and I don't really know where the count value is stored

 

Thank you in advance

 

 

 

[Paul_todd]

the count value is from the backend. The actual records will not be sent from the backend as you asked for just the number of records. Remove the $count and you will get the records.

 

Generally you should not have to worry about storing the value on the front end as this should be wrapped in a UI5 OData Model which handles all this magic.

 

 

Paul

[former_member822159]

The reason I need the counter is to display the number of purchase orders per category in UI5 app, Can this be handled in the front end based on what you said ?

 

I thought of removing $count and just fetch the records than just iterate and increment a counter or use length of tables (if available).

 

My point here is that I need both the records and the counting

 

Thank you in Advance Paul

[tudor_teoteoi]

You can request data with parameter $inlinecount=allpages in this case.

/sap/opu/odata/SAP/EAM_OBJPG_MAINTENANCEORDER_SRV/C_MaintOrderTypeVH?$inlinecount=allpages

 

You should get the count of the entries, as well as the entries themselves:

<feed xmlns="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xml:base="https://yourhost.com:45301/sap/opu/odata/SAP/EAM_OBJPG_MAINTENANCEORDER_SRV/">

<id>https://yourhost.com:45301/sap/opu/odata/SAP/EAM_OBJPG_MAINTENANCEORDER_SRV/C_MaintOrderTypeVH</id>

<title type="text">C_MaintOrderTypeVH</title>

<updated>2023-06-19T17:16:45Z</updated>

<author>

<name/>

</author>

<link href="C_MaintOrderTypeVH" rel="self" title="C_MaintOrderTypeVH"/>

<m:count>22</m:count>

<entry>

...

</entry>

<entry>

...

</entry>

<entry>

...

</entry>

<entry>

...

</entry>