Filtering a Collection of Entities
A GET statement that returns a large amount of data can stress the database server and cause performance degradation for all users. To minimize the impact:
- filter the entities to be returned
- retrieve only the fields you actually need
To retrieve only the fields you need, add a fields clause to the URL.
To filter the entities to be returned, add a query to the URL. For example:
/tests?query={query statement}
.
A query can be applied to any GET on a collection of entities.
Queries sent by applications must be URL encoded. For example, to submit query {status[NOT (Ready or Design)]}
, send %7Bstatus%5BNOT%20(Ready%20or%20Design)%5D%7D
. See also URL encoding.
You can generally use spaces in an query entered in a browser.
The examples on this page are not URL encoded so that they are easier to read.
Query Syntax
- Comparison operators:<,>,<>,>=,<=
- Logical operators: and, or, not
- Literals: String that represent the value of an expression. If a value contains spaces, it must be contained within single or double quotes. Literals can contain the '*' wild card.
- Pairs of parenthesis
The filter of a query URL is contained in curly brackets "{}".
The expression applied to a field is contained in square brackets "[]".
The field delimiter is a semicolon ";" .
The only operation supported between fields is AND. The AND operation is implicit and is not specified in the query syntax. Only the ";" delimiter is specified.
For example:
tests?query={id[>1 AND NOT 5]; status[Ready or Design]}
This query specifies any test whose ID is above one, excluding test 5, and whose status is Ready or Design.
Field Names
Fields are identified by logical name. To get the list of fields for an entity, use the XML returned by a GET on the customization fields Collection.
This is an example of a Field element:
<Field PhysicalName="TS_ATTACHMENT" Name="attachment" Label="Attachment"">
The PhysicalName attribute of the Field is the column name in the project database. The Label is the display string that can be customized.
The Name attribute is the logical name. Use the logical name value to specify fields in:
- group specifications
- order specifications
- Input XMLs to create or update an entity
Specify fields?can-filter=true
to limit the results to the fields that can be used in a filter.
To specify fields in another entity, use the unique alias that represents the relation of the second entity to the entity your are querying. See Aliases. For instance:.../tests?fields=id,contains-test-folder.id,contains-test-folder.name
Cross Filters
If there is a relational connection between two entity types, a collection can be filtered on the related entities. Use the unique alias that represents the relation for the cross filter. For an example of how to know if an alias is unique, see the example in the Relations topic.
The expression for the related entity is <alias>.<logical field name><filter expression>.
For example, because tests can be linked to defects, the tests collection can be filtered by defect fields:
.../tests?query={connected-to-defect.name["Widget wobbles*"]}
See Relations, the relations resource and the Relation XSD.
Examples
Single entity expressions
tests?query={status[NOT (Ready or Design)]}
tests?query={status[(NOT Ready or Design]}
tests?query={status[Ready or NOT Design]}
tests?query={id[>=1 And NOT = 5]}
tests?query={exec-status['Not Completed']}
tests?query={exec-status['Not Com*']}
Cross filter expressions with entity names
Tests in status "Ready" that are linked to defects assigned to user SallyQA:
tests?query={status[Ready]; defect.owner[SallyQA]}
Tests that are linked to defects assigned to user joe:
tests?query={defect.owner[joe]}
Cross filter expressions with relation aliases
Design steps that call tests named D*:
design-steps?query={used-by-test.name[D*]}
Design steps that are part of tests named S*:
design-steps?query={has-parts-test.name[S*]}
Exclusive Filter
To return the resources that do not meet the conditions applied to an entity, add {entity}.inclusive-filter[false]
to the filter. The effect is to exclude the resources that would be returned if inclusive-filter[false] were not specified and to return the rest. For example:
/tests?query={defect.id[1];defect.status[Closed or Fixed];requirement.id[1];defect.inclusive-filter[false]}
This query returns tests that are linked to the requirement whose ID is 1, excluding tests linked to the defect whose ID is 1 and excluding tests linked to defects that are fixed or closed. Note that since inclusive-filter[false] is applied to the defect entity, only the clauses for defect are treated as exclusion clauses. The requirement clause is applied as an inclusion clause.