SAP Cloud Platform SDK for service development: Create OData Service [11] skip + orderby

[CarlosRoggan]

Time has come (it is 8 o'clock in the morning, btw) to talk about less convenient topics....
With other words, it was nice marketing when I spent flattering words to point out how nicely the SAP Cloud Platform SDK for service development helps us developers with the creation of OData services.
I believe that all of you were waiting for the moment of awakening from that beautiful dream….
Be sure: the moment has come...it is time to wake up and face the reality:

The SDK cannot do EVERYTHING for us…

For example, the framework cannot generically handle the $skip and the $orderby.

So we have to implement it manually.
Hope that this reality hasn’t been a too terrifying shock…
And you don't need to worry, as long as you have some nice tutorials ….
You know that you’re not alone…

Note:
If you don’t like reading, just scroll down to the end of this page to view the full source code.

 

Background

With the query option $skip, the user of an OData service can specify the number of entries that should be ignored at the beginning of a collection. So if a user specifies $skip=1 then our OData service has to return the list of entries starting at position 2
One important rule that we have to consider is described by the OData V4 specification:

“Where $top and $skip are used together, $skip MUST be applied before $top, regardless of the order in which they appear in the request.”

This means for us that we add the code for $skip before the code for $top.

In the previous blogs, we’ve already talked about System Query Options.
To be honest, we have to confess that it was rather small-talk…
Yes, because talking about query options that come out-of-the-box, provided as a feature by the framework, that is too easy…
Now the moment of truth has come: no more gifts donated by the generous framework.
We have to implement the query options $skip and $orderby manually.

The good news:
You’re not alone in this tough times – you have some friendly series of blogs…. 😉

Another good info:
If you've reached this blog for the first time, you may check here for prerequisites and follow this blog for a detailed description on how to create a project.

 

$skip

Let’s start with the $skip, this is the easier task.
It is supposed to do exactly what it says:
it has to skip.
This is my (silly) translation of what the OData-spec says:

The $skip system query option specifies a non-negative integer n that excludes the first n items of the queried collection from the result. The service returns items starting at position n+1.

Another (silly) translation could be: skip is the opposite of top:
I don’t want the first entries, I want to skip the first entries and get only the last.

Another (less silly) translation could be to look at an example:
On the left side the full collection, on the right side after applying $skip=3



 

Implementation

Implementing it requires only few lines.

First we anyways get the full list, just as usual (note that this is just dummy mock code):

List<Map<String, Object>> peopleList = getAllPeople();

Then we have to get the $skip query option from the request

int skipNumber = request.getSkipOptionValue();

Then we reduce the full list by removing the first entries.
The quantity is in the skipNumber:

peopleList = peopleList.subList(skipNumber, fullSize);

That’s it.

In addition, little handling is required:
We have to check if $skip was specified in the URL at all.
If no $skip was specified, then we get a value of -1
We can safely check for that.
Background is that the OData spec clearly states that $skip allows only non-negative values.

 

This is how our @Query method could look like:

 

List<Map<String, Object>> allPeopleList = getAllPeople();

		

int skip = request.getSkipOptionValue();

		

if(skip > -1) { 

	int fullSize = peopleList.size();

	if(skip >= fullSize) {  

		// e.g. $skip=999 . we need to handle this to avoid exceptions

		return QueryResponse.setSuccess().setDataAsMap(Collections.emptyList()).response();

	}else {

		// now handle skip

		List<Map<String, Object>> skipList =  peopleList.subList(skip, fullSize);

		return QueryResponse.setSuccess().setDataAsMap(skipList).response();

	}

}else {

	return QueryResponse.setSuccess().setDataAsMap(allPeopleList).response();

}

 

Combining $skip and $top

Yes, we have to go through this section.
But the good thing is: we don’t have much to worry.

Background: we have a list and we want to cut it at beginning and at end.
It makes a difference if we first cut the beginning and then the end, or vice versa.

To solve this doubt, the OData spec has decided:

“Where $top and $skip are used together, $skip MUST be applied before $top, regardless of the order in which they appear in the request.”

This means, in our code we obtain the full list
Then remove the beginning (apply $skip)
The send the reduced list to the FWK
Then the FWK applies the $top

This means, we don’t have to worry at all about the $top (as long as we leave it to the FWK).

 

Try it

First, the normal collection:
http://<yourHost>/DemoProject/odata/v4/DemoService/People
As a result, we get our 6 entries

Add $skip to our query:
http://<yourHost>/DemoProject/odata/v4/DemoService/People?$skip=3
As a result, we get 3 entries: PersonId:4 and 5 and 6

Combine $skip and $top :
http://<yourHost>/DemoProject10skip/odata/v4/DemoService/People?$skip=3&$top=2
As a result. we get 2 entries: PersonId:4 and 5

changing the order of $skip and $top shouldn’t make any difference:
http://<yourHost>/DemoProject10skip/odata/v4/DemoService/People?$top=2&$skip=3
As a result, we get the same result like in the previous example


Check for proper reaction on some edge cases:

Try value 0:
http://<yourHost>/DemoProject10skip/odata/v4/DemoService/People?$top=2&$skip=0
As a result, we see that skipping nothing is properly handled: we get PersonId:1 and 2

Try meaningless value, it doesn’t hurt:
http://<yourHost>/DemoProject/odata/v4/DemoService/People?$top=2&$skip=99
As a result, we get an empty list, because we skip all and even more than all

Negative values are not allowed, that’s handled by the framework:
http://<yourHost>/DemoProject/odata/v4/DemoService/People?$top=2&$skip=-1
As a result, the framework sends a meaningful error message and status code 400

 

 

$orderby

$orderby is the URL parameter used in OData to specify the sorting of the collection.

As per default there’s no sorting.
In our dummy example, our dummy entries are shown in the dummy order in which they were added by a dummy developer to the dummy list.

It is a legal requirement, that smart users might wish to get the data in a desired meaningful order.

In our example, we could say that we want to group all our people according to their job:

http://<yourHost>/DemoProject/odata/v4/DemoService/People?$orderby=JobId

 

Implementation

Since there’s no out-of-the-box support, we have to implement the sorting manually.

Note:
This note is rather a BTW:
As usual, we have to know exactly what we’re doing, so we need to have a look at the spec

 

Back to code:
Since sorting can only be applied to a list, we have to add our code to the @Query method
As expected, we get the information about the query option from the QueryRequest instance

List<OrderByExpression> expressionsList = request.getOrderByProperties();

The return value is never null.
If there’s no $orderby statement in the URL, then the list is empty (one thing we have to check in the code).

Why is it a list at all?
Because in OData, the sorting can be done on multiple properties.

For example, if there are multiple entries with the same JobId, then we might want to sort these multiple entries additionally by their Name.

Good, but we don’t want that 😉

We keep our code very simple and support only one property for sorting:

OrderByExpression expression = expressionsList.get(0);

Next question: why do we get a list of Expressions, not PropertyNames?
Because in OData, the instruction for sorting not only supports the sort-by-property. After sorting, we can as well invert the complete order. This is specified as ascending or descending (asc and desc, respectively)

For now we retrieve just the property name:

String propertyName = expression.getOrderByProperty();

Now we have all we need:

The list of people.
The property name for sorting.

Now we can sort.

We’re living in the Java world, so we use Java means to solve this requirement:

Collections.sort(allPeopleList, new Comparator<Map<String, Object>>() {

     ...

We let Java sort the list, but we have to implement the java.util.Comparator.

The Comparator will have to compare our entries, our entries are of type java.util.Map.

public int compare(Map<String, Object> o1, Map<String, Object> o2) {

From the Map instance, we have to extract the property which has to be sorted (which means, every map has to be compared with others)
The properties can have any data type (which is supported by OData).
Great, but we don’t want that 😉

To remain very very simple, we only support properties of type Edm.String

To be honest, the reason is that we can easily delegate the real work of comparing to the java.lang.String Object:

String s1 = (String)o1.get(propertyName);

String s2 = (String)o2.get(propertyName);



return s1.compareTo(s2);

The most simple implementation:

@Query(serviceName="DemoService", entity="People")

public QueryResponse getPeople(QueryRequest request) {

	List<Map<String, Object>> allPeopleList = getAllPeople();

		

	List<OrderByExpression> expressionsList = request.getOrderByProperties();

	if(expressionsList != null && ! expressionsList.isEmpty()) { 

		OrderByExpression expression = expressionsList.get(0); 

		String propertyName = expression.getOrderByProperty();

		Collections.sort(allPeopleList, new Comparator<Map<String, Object>>() {

			@Override

			public int compare(Map<String, Object> o1, Map<String, Object> o2) {

				String s1 = (String)o1.get(propertyName);

				String s2 = (String)o2.get(propertyName);

					

				return s1.compareTo(s2);

			}

		});

	}

	

	return QueryResponse.setSuccess().setDataAsMap(allPeopleList).response();

}	

Adding support for sort order (asc and desc) can be simply done as follows:

boolean isDescending = expression.isDescending();

if (isDescending) {

          result = -result; // change the (normal) ordering to the contrary

}

Next step would be to differentiate between several data types and properties.

Here you may see it together:

@Query(serviceName="DemoService", entity="People")

public QueryResponse getPeople(QueryRequest request) {

	List<Map<String, Object>> allPeopleList = getAllPeople();

	

	// handle orderby

	List<OrderByExpression> orderByProperties = request.getOrderByProperties();

	if(orderByProperties != null && ! orderByProperties.isEmpty()) { 

		// apply $orderby

		OrderByExpression expression = orderByProperties.get(0); 

			

		String propertyName = expression.getOrderByProperty();

		boolean isDescending = expression.isDescending();

			

		// do the sorting

		Collections.sort(allPeopleList, new Comparator<Map<String, Object>>() {

			@Override

			public int compare(Map<String, Object> o1, Map<String, Object> o2) {

				int result = 0;

					

				if(propertyName.equals("PersonId") || propertyName.equals("JobId") ) {

					// handle integers

					Integer id1 = (Integer)o1.get(propertyName);

					Integer id2 = (Integer)o2.get(propertyName);

						

					result = id1.compareTo(id2);

				}else if (propertyName.equals("IsFriend") ){

					// handle booleans

					Boolean b1 = (Boolean)o1.get(propertyName);

					Boolean b2 = (Boolean)o2.get(propertyName);

						

					result = b1.compareTo(b2);

				}else if (propertyName.equals("Email") ){

					// special handling for mail: lets sort by mail provider.

					String mail1 = (String)o1.get(propertyName);

					String s1 = mail1.substring(mail1.indexOf("@") + 1);

					

					String mail2 = (String)o2.get(propertyName);

					String s2 = mail2.substring(mail2.indexOf("@") + 1);

						

					result = s1.compareTo(s2);

				}else {

					// handle strings

					String s1 = (String)o1.get(propertyName);

					String s2 = (String)o2.get(propertyName);

						

					result = s1.compareTo(s2);

				}

					

				// finally we need to handle the asc versus desc

				if (isDescending) {

					result = -result; // change the (normal) ordering to the contrary

				}

					

				return result;

			}

		});

	}

		

	// no sorting required, just return as usual

	return QueryResponse.setSuccess().setDataAsMap(allPeopleList).response();

}

 

 

 

Try it

Let’s try a few requests.

Note:
If the desired sort order is ascending, we don’t need to specify asc, because it is the default

 

Sort by a normal string:
http://<host>/DemoProject/odata/v4/DemoService/People?$orderby=FirstName

Sort by Integer descending:
http://<host>/DemoProject/odata/v4/DemoService/People?$orderby=JobId desc

See the result of our funny email sorting:
http://<host>/DemoProject/odata/v4/DemoService/People?$orderby=Email

See our friends first:
http://<host>/DemoProject/odata/v4/DemoService/People?$orderby=IsFriend desc

Finally, here's a URL which contains 2 orderby expressions:
/odata/v4/DemoService/People?$orderby=IsFriend desc,LastName asc
Cool, but we don’t want that 😉 only to illustrate how it could be used

 

Combining $orderby and $skip and $top

Finally, we need to spend a few words about combining the system query options.
It makes a difference if we apply skip/top to a list with different order.

You’d like an example?

Order our list by first name, then skip=5 and as a result you get Fanny
Order our list by last name, then skip=5 and as a result you get Eric
Don't specify any order, then skip=5 and as a result you get Berta

In these examples, we’ve first ordered the list, then removed the 5 entries of the beginning.

Or should we remove the 5 entries, and only afterwards do the sorting on the remaining entries of the list?

This is a valid question and the answer should be given in this blog.

...

And here it comes already:

The spec says:

If no unique ordering is imposed through an $orderby query option, the service MUST impose a stable ordering across requests that include $skip.

As a consequence, in our code, we have to first apply the $orderby and only afterwards apply the $skip

Implementation

Please refer to the full source code at the end of this page, it contains both implementation for $skip and $orderby

 

After having both orderby and skip in your code, you may …

…Try it…

… as follows

 

.../odata/v4/DemoService/People?$top=4&$skip=1&$orderby=IsFriend%20desc

.../odata/v4/DemoService/People?$top=3&$skip=1&$orderby=IsFriend%20asc

 

etc

 

Navigation

Whenever you navigate to a collection, then you get a collection, and whenever you get a collection, then you can apply query options.
Cute, but we don’t want that 😉

No, not in this blog, but in general: yes, we do want it, because we can just apply what we’ve learned here.

So we can take away the following takeaway:

In some previous blogs, we implemented the navigation.
We had a one-to-many navigation, so the response was built with a java.util.List:
Remember?

// Navigation from one job to all people who have this job. 

// Example URI: /srv/Jobs(1)/Employees

@Query(serviceName = "DemoService", entity = "People", sourceEntity = "Jobs")

public QueryResponse getPeopleForJob(QueryRequest request) {

If you want to try, you can just apply the same code which you’ve written here, to that list of target employees.

For example:

// 1-to-many navigation, then apply $orderby and $skip

@Query(serviceName = "DemoService", entity = "People", sourceEntity = "Jobs")

public QueryResponse getPeopleForJob(QueryRequest request) {

	List<Map<String,Object>> responsePeopleList = new ArrayList<Map<String, Object>>();



	// retrieve the navigation target people list

	int jobId = (int) request.getSourceKeys().get("JobId");

	responsePeopleList = getPeopleWithJob(jobId);

		

	// after getting the navigation result list, now apply system query options

		

	// handle $orderby on top of the list of target employees

	responsePeopleList = applyOrderby(responsePeopleList, request.getOrderByProperties());

		

	// finally apply $skip

	responsePeopleList = applySkip(responsePeopleList, request.getSkipOptionValue());

		

	// return the list, it might have been modified by above code, or not

	return QueryResponse.setSuccess().setDataAsMap(responsePeopleList).response();		

}

 

 

Summary

Hope you agree that awakening from the do-nothing-dream can be entertaining 😉

 

Links

Overview of blog series and link collection.

 

Appendix 1: Source code of Model file: DemoService.xml

<?xml version="1.0" encoding="utf-8"?>

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">

	<edmx:DataServices>

		<Schema Namespace="demo" xmlns="http://docs.oasis-open.org/odata/ns/edm">

			<EntityType Name="Person">

				<Key>

					<PropertyRef Name="PersonId" />

				</Key>

				<Property Name="PersonId" Type="Edm.Int32"/>

				<Property Name="FirstName" Type="Edm.String"/>

				<Property Name="LastName" Type="Edm.String"/>

				<Property Name="Email" Type="Edm.String"/>

				<Property Name="JobId" Type="Edm.Int32" />

				<Property Name="IsFriend" Type="Edm.Boolean"/>

			</EntityType>

			<EntityContainer Name="container">

				<EntitySet Name="People" EntityType="demo.Person"/>

			</EntityContainer>

		</Schema>

	</edmx:DataServices>

</edmx:Edmx>

 

Appendix 2: Source code of Java class file: ServiceImplementation.java

package com.example.DemoProject;



import java.util.ArrayList;

import java.util.Collections;

import java.util.Comparator;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import com.sap.cloud.sdk.service.prov.api.operations.Query;

import com.sap.cloud.sdk.service.prov.api.request.OrderByExpression;

import com.sap.cloud.sdk.service.prov.api.request.QueryRequest;

import com.sap.cloud.sdk.service.prov.api.response.QueryResponse;



public class ServiceImplementation {

	

	

	@Query(serviceName="DemoService", entity="People")

	public QueryResponse getPeople(QueryRequest request) {

		// first get the requested list including all people. Afterwards we apply query options, if required

		List<Map<String, Object>> peopleList = getAllPeople();

		

		// handle $orderby

		List<OrderByExpression> orderByProperties = request.getOrderByProperties();

		if(orderByProperties != null && ! orderByProperties.isEmpty()) { // if not orderby was added to URL, then we get an empty list

			// apply $orderby

			OrderByExpression expression = orderByProperties.get(0); // let's support only one property for sorting

			

			String propertyName = expression.getOrderByProperty();

			boolean isDescending = expression.isDescending();

			

			// do the sorting

			Collections.sort(peopleList, new Comparator<Map<String, Object>>() {

				@Override

				public int compare(Map<String, Object> o1, Map<String, Object> o2) {

					int result = 0;

					

					if(propertyName.equals("PersonId") || propertyName.equals("JobId") ) {

						// handle integers

						Integer id1 = (Integer)o1.get(propertyName);

						Integer id2 = (Integer)o2.get(propertyName);

						

						result = id1.compareTo(id2);

					}else if (propertyName.equals("IsFriend") ){

						// handle booleans

						Boolean b1 = (Boolean)o1.get(propertyName);

						Boolean b2 = (Boolean)o2.get(propertyName);

						

						result = b1.compareTo(b2);

					}else if (propertyName.equals("Email") ){

						// special handling for mail: lets sort by mail provider. Just for fun

						String mail1 = (String)o1.get(propertyName);

						String s1 = mail1.substring(mail1.indexOf("@") + 1);

						

						String mail2 = (String)o2.get(propertyName);

						String s2 = mail2.substring(mail2.indexOf("@") + 1);

						

						result = s1.compareTo(s2);

					}else {

						// handle strings

						String s1 = (String)o1.get(propertyName);

						String s2 = (String)o2.get(propertyName);

						

						result = s1.compareTo(s2);

					}

					

					// finally we need to handle the asc versus desc

					if (isDescending) {

						result = -result; // change the (normal) ordering to the contrary

					}

					

					return result;

				}

			});

		}

		

		// afterwards, apply $skip on the ordered list

		int skip = request.getSkipOptionValue();

		if(skip > -1) { // note that we always get a value, even if there's no skip in the URL

			int fullSize = peopleList.size();

			if(skip >= fullSize) {  

				// e.g. $skip=999 . we need to handle this to avoid exceptions

				peopleList.clear();

			}else {

				// handle skip

				peopleList = peopleList.subList(skip, fullSize);

			}

		}

		



		// finally, return the list, it might have been modified by above code, or not

		return QueryResponse.setSuccess().setDataAsMap(peopleList).response();

	}



	

	

	/* Dummy Database */

	

	private List<Map<String, Object>> getAllPeople(){

		List<Map<String, Object>> peopleList = new ArrayList<Map<String, Object>>();

		// init the "database"

		if(peopleList.isEmpty()) {

			peopleList.add(createPerson(1, "Anna", "Martinez", "Anna@fastmail.com", 1, true));

			peopleList.add(createPerson(2, "Eric", "Richards", "err@nicemail.com", 3, false));

			peopleList.add(createPerson(3, "Debbie", "Huffington", "debbie@ahamail.com", 2, true));

			peopleList.add(createPerson(4, "Fanny", "Mendelssohn", "fanny@funnymail.com", 3, true));

			peopleList.add(createPerson(5, "Claudia", "Forrester", "CF@goodmail.com", 1, false));

			peopleList.add(createPerson(6, "Berta", "Hughes", "Berta@webmail.com", 2, false));

		}

		

		return peopleList;

	}

	

	private Map<String, Object> createPerson(int personId, String firstName, String lastName, String mail, int jobId, boolean isFriend){

		Map<String, Object> personMap = new HashMap<String, Object>();

		

		personMap.put("PersonId", personId); 

		personMap.put("FirstName", firstName); 

		personMap.put("LastName", lastName);

		personMap.put("JobId", jobId);

		personMap.put("Email", mail);

		personMap.put("IsFriend", isFriend);

		

		return personMap;

	}

}