Filtering and ordering data collections

This page includes the following sections:

Filtering and ordering OData collections

OData feeds can be filtered (using the $filter query parameter) and ordered (using the $orderby query parameter). The sample application above demonstrates use of the $filter option to restrict the ScoringTasks returned when filtering on Group/Name and on Status.

The Delivery OData API imposes some restrictions on which properties can be used in $filter and $orderby parameters. The purpose of these restrictions is to ensure that OData requests do not result in costly queries being made to the database, especially where those queries are made against tables that are critical to the performance of the experience of those taking tests. All key fields can be used in both $filter and $orderby expressions.

If two entity types are related via a navigation property with a target multiplicity of 0..1 then it is possible to filter a feed of the first type by the value of a property of the second. For example, ScoringTask entities are related to a single Group entity through the Group navigation property. It is therefore possible to filter ScoringTask by Group/Name. This type of filter is described as having depth 1 as it traverses one navigation property. You should not rely on deeper filters. In the lists that follow, allowed filters are described in terms of their immediate parent entity (depth 0), however, if a filter is supported at depth 0 you may assume that it will also be supported at depth 1 when filtering related entities.

Supported filters

(CK) = an entity that has a composite key; you should not assume that you can filter or order by individual key properties.

Feed/entity name $filter $orderby
Administrator ID, Name ID, Name
Answers (CK) QuestionID, ResultID  
Assessments ID, Name ID, Name
DimensionScores (CK) QuestionID Order
Dimensions ID ID
Groups ID, Name ID, Name
Participants ID, Name ID, Name
QuestionTranslations (CK)    
Questions ID, QuestionType ID, QuestionType
Results ID, AssessmentID, ParticipantName, GroupName, WhenFinished ID, AssessmentID, ParticipantName, GroupName, WhenFinished
ScoringResults (CK) QuestionID, ResultID QuestionID, ResultID
ScoringTasks (CK) QuestionID, ResultID, Status QuestionID, ResultID, Status

Filtering examples

/deliveryodata/12345/ScoringTasks?$filter=Group/Name eq 'CS101' and Status lt 3

...returns all ScoringTask entities relating to the Group with name CS101 that have a status less then 3 (Scored).

ScoringTasks/$count?$filter=Group/Name eq 'CS101' and Status eq 2

...returns the count of all ScoringTask entities relating to the Group with name CS101 that have a status of 2 (Saved).

ScoringTasks?$filter=Group/Name eq 'CS101' and Assessment/ID eq 1234567890

...returns all ScoringTask entities relating to the Group with name CS101 and the assessment with ID 1234567890.

Results?$filter=GroupName eq 'CS101' and Assessment/ID eq 1234567890&$orderby=WhenFinished desc

...returns all result entities relating to the group with name CS101 and the assessment with ID 1234567890 ordered by the most recently submitted first.

Results?$filter=Assessment/ID eq 1234567890 and ParticipantName eq 'JaneSmith'&$orderby=WhenFinished

...returns all result entities relating to JaneSmith's attempts at the specific assessment with ID 1234567890 ordered from the first to the last attempt.

Other system query options

The Delivery OData API supports $expand on all navigation properties with the proviso that expanding ScoringResult within ScoringTask is not supported and may give misleading results. ScoringResults must be loaded individually by navigation from the associated ScoringTask as this triggers the start of the marking workflow.

All base feeds support $count, but $count is not currently supported when used in conjunction with navigation properties.

All feeds support $top and $skip to allow the client to control the page size. One of the advantages of OData over plain Atom or JSON formats is the additional URI conventions that allow data to be paged. For example, to get only the first 100 ScoringTasks you might use a URL like this:$top=100

OData feeds may return fewer entries than $top requests, either because the feed is exhausted or because an internal limit has been reached. In this case, OData provides a link to the next page of results, allowing you to exhaustively retrieve all entries in even the largest data sets. This technique was not possible with previous APIs, but it does require some special handling on the client side. Questionmark's OData feeds make extensive use of paging and also impose an upper limit on the number of records that will be returned for each request to improve performance of both the service and the client itself.