This week we will cover the another two of the system query options. These will be the $select and the $orderby query options.
How do I return only a subset of properties of a large number of rows?
When working with collections it is common to only require a small sub of properties to display for example in a list box. In a Customer Relationship Mangement application for example you may only require a list of all the contacts in the company but only their name and phone number.
This is solved using the $select system query option that sets which properties of an entity get returned to the caller.
By reducing the traffic between the device this helps in two significant ways. Firstly the volume of data can be substantially reduced in both the data being downloaded and the data being parsed and held in memory. On a mobile device which is typically has less powerful processor and significantly less memory than a desktop device whilst also having low bandwidth and high latency device these savings can be significant.
For example the netflix title entity has around 20 properties but if you only want to display the name and synopsis then by adding a $select with those fields
http://odata.netflix.com/v2/Catalog/Titles?$top=1&$select=Name,Synopsis
the amount of data returned is substantially reduced. It is also important to note that the field names specified in the $select are case sensitive.
How do I get the collection data in a specific order?
It is common to require the data being returned to be sorted in a specific order. This is often importantuse case on mobile devices where the data set may be so large it could not be put onto the device or the processing to sort the data is computationally intensive and so cannot be done easily on the device within the constaints of the processor performance or memory size.
To sort the data the $orderby system query option is used together with one or more properties and an optional tag specifiying if the property is to be sorted in ascending order or descending order
For example the netflix service offers sixteen thousand titles but we want to find the oldest title with the highest rating:
http://odata.netflix.com/v2/Catalog/Titles?$orderby=AverageRating desc,ReleaseYear asc&$top=1
Notice how the record set is first sorted by the average rating from highest to lowest and then by the release year by lowest to highest. The final result set would contain all the titles sorted by these keys however we want to constrain the set to only the first record, though, in practise a real world solution would use $skip and $top to pagingate the result set to improve performance on mobile form factors.
