Distinct values
It is possible to fetch distinct values from a table using the distinct argument. This is a boolean parameter that must be set to true to enable distinct data fetching. This translates into the SELECT DISTINCT SQL statement.
The following example shows how to fetch the distinct account groups from the general ledger table:
query read_distinct_account_groups($cid : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(distinct : true)
{
items
{
accountGroup
}
}
}
}
{
"data": {
"useCompany": {
"generalLedgerAccount": {
"items": [
{
"accountGroup": ""
},
{
"accountGroup": "100K_FORSKNING_UTVIKLING"
},
{
"accountGroup": "102K_KONSESJON_PATENT_LISENSER"
},
{
"accountGroup": "107K_UTSATT_SKATTEFORDEL"
},
{
"accountGroup": "108K_GOODWILL"
}
...
]
}
}
}
}
It is possible to select distinct values for multiple fields. The following example shows how to distinct customers and order types from the order table:
query read_orders($cid : Int!, $dt : Int) {
useCompany(no: $cid) {
order(
filter : {changedDate : {_gt : $dt}},
distinct : true,
first : 10)
{
pageInfo
{
hasNextPage
startCursor
endCursor
}
items
{
customerNo
orderType
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"pageInfo": {
"hasNextPage": false,
"startCursor": "MQ==",
"endCursor": "NQ=="
},
"items": [
{
"customerNo": 0,
"orderType": 1
},
{
"customerNo": 10000,
"orderType": 1
},
{
"customerNo": 10001,
"orderType": 1
},
{
"customerNo": 10002,
"orderType": 2
},
{
"customerNo": 10286,
"orderType": 1
}
]
}
}
}
}
As mentioned before, the use of the distinct argument will produce a SELECT DISTINCT SQL statement for fetching data. The following two SQL statements are roughly equivalent:
SELECT DISTINCT a,b,c FROM T
SELECT a,b,c FROM T GROUP BY a,b,c
Therefore, the same results can be achieved, typically, by using the groupBy argument. The following example shows how to fetch the same distinct customers and order types from the order table using the groupBy argument:
query read_orders($cid : Int!, $dt : Int) {
useCompany(no: $cid) {
order(
filter : {changedDate : {_gt : $dt}},
groupBy : [
{customerNo : DEFAULT},
{orderType : DEFAULT}
],
sortOrder : {
customerNo : ASC,
_thenBy : { orderType : DESC}
}
first : 10)
{
pageInfo
{
hasNextPage
startCursor
endCursor
}
items
{
customerNo
orderType
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"pageInfo": {
"hasNextPage": false,
"startCursor": "MQ==",
"endCursor": "NQ=="
},
"items": [
{
"customerNo": 0,
"orderType": 1
},
{
"customerNo": 10000,
"orderType": 1
},
{
"customerNo": 10001,
"orderType": 1
},
{
"customerNo": 10002,
"orderType": 2
},
{
"customerNo": 10286,
"orderType": 1
}
]
}
}
}
}
Note
When requesting distinct values, the totalCount field will still return the total number of records in the table that match the provided filter (if any was given), not the number of distinct records.
It is recommended that you do not use totalCount when fetching distinct values, as it will indicate a misleading quantity.
Obsolete distinct values
Warning
This schema section is obsolete and will be removed in the future.
It is recommended to use the distinct argument for fetching distinct values, as described above.
An option to fetch distinct values of a field is also available under the aggregates field with a subfield called distinct.
Aggregate types have the name of the form <tablename>Aggregate, such as AssociateAggregate.
On the other hand, the field used for fetching distinct values have their own type, using the format <tablename>Distinct, such as AssociateDistinct.
The distinct type have the same fields as the aggregate types, which are the columns of the database tables. However, their type is a list of values and not a single value.
The following image shows a snippet of the AssociateDistinct type:
The following table shows an example for fetching the distinct post codes and country codes from the Associate table:
query($cid:Int)
{
useCompany(no: $cid)
{
associate_aggregate
{
distinct
{
postCode
countryNo
}
}
}
}
{
"data": {
"useCompany": {
"associate_aggregate": {
"distinct": {
"postCode": [
"",
"AL20 0XX",
"AL3 8JH",
"B11 2BH",
"B11 3RR",
"B42 1DU",
"B60 3DR",
...
"WV10 7Ln",
"WV14 OQL",
"WV15 5HR"
],
"countryNo": [
0,
1,
33,
44,
46,
47,
353
]
}
}
}
}
}
You can use the same filters as for the aggregate functions. Here is an example:
query read($cid:Int)
{
useCompany(no: $cid)
{
associate_aggregate(filter:{
postalArea : {_eq : "London"}
})
{
distinct
{
postCode
}
}
}
}
{
"data": {
"useCompany": {
"associate_aggregate": {
"distinct": {
"postCode": [
"L63 4DJ",
"MW10 2XA"
]
}
}
}
}
}
Fetching distinct values for unique primary keys will not provide any benefit over a regular query for that specific field. In other words, the following two queries will return the same date, although in different forms.
query read($cid:Int)
{
useCompany(no: $cid)
{
associate_aggregate(filter:{
postalArea : {_eq : "London"}
})
{
distinct
{
associateNo
}
}
associate(filter:{
postalArea : {_eq : "London"}
})
{
items
{
associateNo
}
}
}
}
{
"data": {
"useCompany": {
"associate_aggregate": {
"distinct": {
"associateNo": [
28,
149
]
}
},
"associate": {
"items": [
{
"associateNo": 28
},
{
"associateNo": 149
}
]
}
}
}
}