Filtering
Overview
Filtering is a key feature of the API. You can specify the filter as a parameter to a connection. Let’s look at some examples:
In this first example, we fetch all the general ledger accounts that have the account number greater or equal than 6000 and lower than 7000.
query read($cid : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(
filter: {_and :[
{accountNo :{_gte : 6000}},
{accountNo :{_lt : 7000}}
]}
)
{
totalCount
items
{
accountNo
name
}
}
}
}
{
"data": {
"useCompany": {
"generalLedgerAccount": {
"totalCount": 46,
"items": [
{
"accountNo": 6000,
"name": "Avskr. bygn. og annen eiendom"
},
{
"accountNo": 6010,
"name": "Avskr. maskiner, inventar mv."
},
...
]
}
}
}
}
We can complicate the query a bit, and ask only for those general ledger accounts in the range 6000 - 7000 that start with “Leie”. In this case, the filter would look as follows:
query read($cid : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(
filter: {_and :[
{accountNo :{_gte : 6000}},
{accountNo :{_lt : 7000}},
{name :{_like:"Leie%"}}
]}
)
{
totalCount
items
{
accountNo
name
}
}
}
}
{
"data": {
"useCompany": {
"generalLedgerAccount": {
"totalCount": 6,
"items": [
{
"accountNo": 6300,
"name": "Leie lokaler"
},
{
"accountNo": 6400,
"name": "Leie maskiner"
},
{
"accountNo": 6410,
"name": "Leie inventar"
},
{
"accountNo": 6420,
"name": "Leie datasystemer"
},
{
"accountNo": 6430,
"name": "Leie andre kontormaskiner"
},
{
"accountNo": 6440,
"name": "Leie transportmidler"
}
]
}
}
}
}
In the next example, we fetch all the associates that are either customers with the number in the range 10000 - 10010, or suppliers, with the number in the range 50000 - 50010.
query read($cid : Int!)
{
useCompany(no: $cid) {
associate (
filter:{
_or:
[
{
_and : [
{customerNo :{_gt:10000}},
{customerNo :{_lt:10010}}
]
},
{
_and : [
{supplierNo : {_gt:50000}},
{supplierNo : {_lt:50010}},
]
}
]
}
)
{
totalCount
items {
name
customerNo
supplierNo
}
}
}
}
{
"data": {
"useCompany": {
"associate": {
"totalCount": 18,
"items": [
{
"name": "YoYo Solutions AS",
"customerNo": 10001,
"supplierNo": 0
},
{
"name": "Spire Business",
"customerNo": 10002,
"supplierNo": 0
},
...
{
"name": "Scorpio Services Limited",
"customerNo": 0,
"supplierNo": 50001
},
{
"name": "Atlas Systems",
"customerNo": 0,
"supplierNo": 50002
},
...
]
}
}
}
}
Filtering is very similar to an open-source GraphQL implementation for Postgres databases. You can read more about that here. This document can be used for finding more examples.
The filter for each table type (connection) has its own type. For instance, the filter type for the Associate table is called FilterExpression_Associate. You can see this if you explore the schema with GraphiQL, for instance:
FilterExpression_Associate type, in turn, has field for each column in the table. This fields are of a type called _FilterClause, such as Int32_FilterClause, LimitedString_FilterClause, etc. Two additional fields, _and and _or, of the same FilterExpression_Associate allow composing complex filter with the AND and OR logical operators.
The filter clause type, in turn, has fields representing operators, such as _lt or “lower than” or _like for the text comparison like operator.
The following table lists the available operators:
| Operator | Description |
|---|---|
_eq |
equal to value |
_gt |
greater than value |
_gte |
greater than or equal to value |
_in |
in list of values |
_is_not_null |
is not null |
_is_null |
is null |
_is_off |
is off - available for bit field columns only |
_is_on |
is on - available for bit field columns only |
_lt |
less than value |
_lte |
less than or equal to value |
_like |
like value |
_not_eq |
not equal value |
_not_in |
not in list of values |
_not_like |
not like value |
_between |
between from and to (inclusive bounds) |
_not_between |
not between from and to (exclusive bounds) |
The filter can also be provided as an argument to the query. The filter value is an object of the filter expression type. This is shown in the following example:
query GetGLAs(
$companyNo: Int,
$filter : FilterExpression_GeneralLedgerAccount)
{
useCompany(no: $companyNo)
{
generalLedgerAccount(filter: $filter)
{
totalCount
items
{
accountNo
name
}
}
}
}
{
"companyNo": 9112233,
"filter": {"_and" :[
{"accountNo" :{"_gte" : 6000}},
{"accountNo" :{"_lt" : 7000}}]}
}
_in and _not_in operators
Most of the operators take a single value. The _in and _not_in operators, however, take an array of values, as show in the following example:
query read($cid : Int!)
{
useCompany(no: $cid)
{
associate (filter : {customerNo : {_in: [10001,10002,10003]}})
{
totalCount
items
{
name
associateNo
customerNo
supplierNo
}
}
}
}
These two operators (_in and _not_in) are helpful for simplifying more complex filters. The following two tables show a filter using these two operators, and the equivalent expression without them.
query read($cid : Int!)
{
useCompany(no: $cid)
{
associate (filter : {
customerNo : {_in: [10001,10002,10003]}})
{
totalCount
items
{
name
associateNo
customerNo
supplierNo
}
}
}
}
query read($cid : Int!)
{
useCompany(no: $cid)
{
associate (filter : {_or: [
customerNo : {_eq: 10001},
customerNo : {_eq: 10002},
customerNo : {_eq: 10003}
]})
{
totalCount
items
{
name
associateNo
customerNo
supplierNo
}
}
}
}
query read($cid : Int!)
{
useCompany(no: $cid)
{
associate (filter : {
customerNo : {_not_in: [10001,10002,10003]}})
{
totalCount
items
{
name
associateNo
customerNo
supplierNo
}
}
}
}
query read($cid : Int!)
{
useCompany(no: $cid)
{
associate (filter : {_and: [
customerNo : {_not_eq: 10001},
customerNo : {_not_eq: 10002},
customerNo : {_not_eq: 10003}
]})
{
totalCount
items
{
name
associateNo
customerNo
supplierNo
}
}
}
}
If the argument for the _in or _not_in operators is an empty array, the result will be an empty set.
An example is shown below, where we first query the Order table, exporting the value of the sellerOrBuyer column to the variable sellers. We then query the Associate table, filtering for the employees with the numbers in the sellers array. Because no order matches the filter, the result is an empty sellers array, which results in no data being returned.
query find_selers($cid: Int!, $sellers: [Int!] = [])
{
useCompany(no: $cid)
{
order(
filter: {sellerOrBuyer: {_gt: 999}},
first: 5)
{
items
{
sellerOrBuyer @export(as: "sellers")
}
}
associate(
filter: {employeeNo: {_in: $sellers}})
{
items
{
employeeNo
name
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"items": null
},
"associate": {
"items": null
}
}
}
}
If the _in or _not_in operators are used in an AND expression, and the argument is an empty array, the resulting filter will be an empty expression, and no data will be returned.
order(
filter: {_and: [
{changeDateTime : {_gte: {"2024-01-24T12:00:00"}}}
{orderNo: {_in: []}}
]})
{
items
{
orderNo
dueDate
}
}
This results in an empty filter. No data is returned when a filter is provided but it is empty. On the other hand, when no filter is provided, all the data is returned.
If the _in or _not_in operators are used in an OR expression and the argument is an empty array, the subexpression is removed from the filter expression, which may still produce a valid filter that can return data.
filter: {_or: [
{changeDateTime :
{_gte: {"2024-01-24T12:00:00"}}}
{orderNo: {_in: []}}
]}
filter: {_or: [
{changeDateTime :
{_gte: {"2024-01-24T12:00:00"}}}
]}
_between / _not_between operators
These two operators require two values that define the lower and upper bounds of the range. The range is inclusive, meaning that the values that are equal to the lower or upper bound are included in the result.
query read_glas($cid : Int!, $pagesize : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(
first: $pagesize,
filter : {accountNo : {_between : {from: 1000, to: 2000}}})
{
totalCount
items
{
accountNo
name
}
}
}
}
These operators are support for fields of the following types:
- numeric (
Int,Long,Decimal, etc.) - Date
- Time
- DateTime
The following example shows an expression using both the _between operator (for an int field) and the _not_between operator (for a datetime field):
query read_glas($cid : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(filter: { _and : [
{accountNo : {
_not_between : {
from : 3000,
to : 5000
}
}},
{changedDateTime : {
_between : {
from : "2024-05-01T12:00:00",
to : "2024-05-30T23:59:59"
}
}}
]})
{
totalCount
items
{
accountNo
name
}
}
}
}
Comparing with current date/time
The model define various fields for date and time. All the nodes that have the type Date_FilterClause, Time_FilterClause, or DateTime_FilterClause can be compared with the current date/time. These GraphQL types, have, in addition for fields defining operators, an additional field called _cmp whose type is ReferenceDateTime_FilterClause, which looks as follows:
This type features nodes for all the comparison operators available for date and time fields. However, their type is ReferenceDateTime that has the following fields:
| Name | Type | Description |
|---|---|---|
day |
ReferenceDays |
The day this moment refers to. One of YESTERDAY, TODAY, or TOMORROW. |
dayOffset |
Int |
The number of days to offset from the current date. For instance TODAY with an offset of one is TOMORROW, and TODAY with an offset of -1 is YESTERDAY. |
time |
ReferenceTimePoints |
Can only have the value NOW. |
minuteOffset |
Int |
The number of minutes to offset from the current time. |
The following example shows a query that fetches all orders that where changed in the last hour:
query read_orders ($cid: Int)
{
useCompany(no: $cid)
{
order(filter :
{
_and : [
{
changedDateAsDate : {
_cmp : {
_eq : {day : TODAY, dayOffset : 0}
}
}
},
{
changedTimeAsTime : {
_cmp : {
_gte : {time : NOW, minuteOffset: -60}
}
}
}
]
})
{
totalCount
items
{
orderNo
}
}
}
}
This particular query can be simplified by using the createdDateTime field, as shown bellow:
query read_orders ($cid: Int)
{
useCompany(no: $cid)
{
order(filter :
{
createdDateTime : {
_cmp : {
_gte : {
day : TODAY,
time : NOW,
minuteOffset : -60}
}
}
})
{
totalCount
items
{
orderNo
}
}
}
}
Having expressions
The same operators described here for filters can be used for having clauses in queries that include aggregations.
To learn more about this, see Grouping: The having argument.