Read extensions
BNXT GraphQL schema extensions for data model extensions reads are documented in this section.
There is no difference between querying the core model or a model extension. However, it is recommended that you use the extensions schema for DMEs only.
Querying an extension column
To query an extension column, there are two options:
- use an alias to indicate the actual column name (
customerNo : integerValue), or - use the
nameargument to indicate the column name (cno : integerValue(name : "customerNo"))
The case of the name value is irrelevant. But if both an alias and the name argument are present, the name argument is used to identify the column.
Each table type has a special field along the table column fields used for querying the extension columns. This field is called extensions. This field includes the following subfields:
integerValuedecimalValuestringValuebooleanValuedateValuetimeValuetimestampValue
Each column has a domain with a specific data type. Data types can be integer, decimal, boolean, string, date, timestamp. If the requested data type (such as integer for integerValue) does not match the domain data type of the extension column, the value null is returned.
In the following example, extensions is used to fetch the value of the customerNo and createdDate fields from the Order table.
query read_order($cid:Int!)
{
useCompany(no:$cid)
{
order(first : 1)
{
items
{
orderNo
orderDate
extensions
{
customerNo : integerValue
createdAt : dateValue(name: "createdDate")
}
}
}
}
}{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 1,
"orderDate": 20210212,
"extensions": {
"customerNo": 10002,
"createdAt": "2020-05-11"
}
}
]
}
}
}
}Joinup extensions
Data model extensions can include relations. These can be one-to-one (called joinup) or one-to-many (called joindown).
Custom joinup relations can be querying by specifying:
- the relation name (identifier)
- optionally the source and target table name, if the relation name is ambiguous
The following example shows up-joining from the Order table to Associate, to Country, and finally to Language. All the joins are done using the extension joinup field.
query read_order($cid:Int!)
{
useCompany(no:$cid)
{
order(first : 1)
{
items
{
orderNo
extensions
{
joinup(relation : "Customer")
{
name : stringValue
joinup(relation: "Country")
{
name : stringValue
joinup(relation: "Language")
{
name : stringValue
}
}
}
}
}
}
}
}{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 1,
"extensions": {
"joinup": {
"name": "Access Vital AS",
"joinup": {
"name": "Norway",
"joinup": {
"name": "Norsk"
}
}
}
}
}
]
}
}
}
}Joindown extensions
The joindown relations are similar to joinup, except that their GraphQL type is a connection type.
The following example shows fetching the order lines for an order using the extensions field.
query read_order($cid:Int!)
{
useCompany(no:$cid)
{
order(first : 1)
{
items
{
orderNo
orderDate
extensions
{
joindown(relation: "Order", from: "OrderLine")
{
items
{
orderNo : integerValue
lineNo : integerValue
productNo : stringValue
joinup(relation: "Product")
{
name : stringValue(name: "description")
}
}
}
}
}
}
}
}{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 1,
"orderDate": 20210212,
"extensions": {
"joindown": {
"items": [
{
"orderNo": 1,
"lineNo": 1,
"productNo": "312",
"joinup": {
"name": "Callaway Big Bertha 4-PW grafitt Herre"
}
},
{
"orderNo": 1,
"lineNo": 2,
"productNo": "313",
"joinup": {
"name": "Callaway Big Bertha Fusion jern"
}
}
]
}
}
}
]
}
}
}
}Filter extensions
Extension columns can be used in filters too. However, arguments are defined by input types and these cannot have arguments themselves. Therefore, for all input types, the schema is sligtly different. For columns, you need to specify the column name and the value expression.
The following examples shows how to fetch orders that belong to the customer with number 10010:
query read_order($cid:Int!)
{
useCompany(no:$cid)
{
order(
first : 3,
filter : {
extensions : {
column : "customerNo",
integerValue : {
_eq : 10010
}
}
}
)
{
items
{
orderNo
customerNo
}
}
}
}{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 86,
"customerNo": 10010
},
{
"orderNo": 97,
"customerNo": 10010
},
{
"orderNo": 136,
"customerNo": 10010
}
]
}
}
}
}Filter joinup extensions
It is possible to filter on the value of columns from joined tables. For one-to-one relations, this is done using the joinup input field.
In the previous example, we queried orders for a customer indicated by its number. However, we can do the same using the name of the customer, from the Associate table. This is possible with a filter join. For extensions, this requires the extensions and the joinup fields:
query read_order($cid:Int!)
{
useCompany(no:$cid)
{
order(
first : 3,
filter : {
extensions : {
joinup : {
relation : {
name : "Customer",
to : "Associate"
},
column : "Name",
stringValue : {
_eq : "American Designers"
}
}
}
}
)
{
items
{
orderNo
customerNo
}
}
}
}{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 86,
"customerNo": 10010
},
{
"orderNo": 97,
"customerNo": 10010
},
{
"orderNo": 136,
"customerNo": 10010
}
]
}
}
}
}In this example, notice the following:
- The input field
relationis used to indicate the relation to join through. It contains fields for the relation name as well as optionally for thefromandtotable names. When thefromargument is missing, it’s implied as the name of the table from which the join is defined. - The
columnfield is used to indicate the name of the column in the target table (Associatein this case) to filter on. - The actual filter expression is provided in the field corresponding to the data type of the column (
stringValuein this case).
Filter joindown extensions
It’s possible to use joins to filter on one-to-multiple relations too. This is possible using the equivalent joindown input field.
For instance, in the following example we query orders that have at least one order line for product number "103":
query read_order($cid:Int!)
{
useCompany(no:$cid)
{
order(
first : 1,
filter : {
extensions : {
joindown : {
relation : {
name : "Order",
from : "OrderLine"
},
_some : {
column : "productNo",
stringValue : {
_eq : "103"
}
}
}
}
}
)
{
items
{
orderNo
joindown_OrderLine_via_Order
{
items
{
lineNo
productNo
}
}
}
}
}
}{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 4,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1,
"productNo": "311"
},
{
"lineNo": 2,
"productNo": "311"
},
{
"lineNo": 3,
"productNo": "1001"
},
{
"lineNo": 4,
"productNo": "103"
}
]
}
}
]
}
}
}
}We can expand the example, to perform the same query but using the name of the product, as defined in the Product table, instead of its number:
query read_order($cid:Int!)
{
useCompany(no:$cid)
{
order(
first : 1,
filter : {
extensions : {
joindown : {
relation : {
name : "Order",
from : "OrderLine"
},
_some : {
joinup : {
relation : {
name : "Product"
},
column : "Description",
stringValue : { _eq : "Product 103" }
}
}
}
}
}
)
{
items
{
orderNo
joindown_OrderLine_via_Order
{
items
{
lineNo
productNo
joinup_Product
{
description
}
}
}
}
}
}
}{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 4,
"joindown_OrderLine_via_Order": {
"items": [
{
"lineNo": 1,
"productNo": "311",
"joinup_Product": {
"description": "Callaway Big Bertha Fusion FT 3 Driver"
}
},
{
"lineNo": 2,
"productNo": "311",
"joinup_Product": {
"description": "Callaway Big Bertha Fusion FT 3 Driver"
}
},
{
"lineNo": 3,
"productNo": "1001",
"joinup_Product": {
"description": "Olyo MS 0703 Carbon Crown Rescue Wood"
}
},
{
"lineNo": 4,
"productNo": "103",
"joinup_Product": {
"description": "Product 103"
}
}
]
}
}
]
}
}
}
}Ordering extensions
Extension columns can be used to define the sorting order too. In this case, you need to specify the column name and, optionally, the order direction. If the order direction is not specified, ascending order is used by default.
The following example shows how to orders by their customerNo extension column in descending order:
query read_order($cid:Int!)
{
useCompany(no:$cid)
{
order(
first : 3,
orderBy : [
{
extensions : {
column : "customerNo",
order : DESC
}
}
]
)
{
items
{
orderNo
customerNo
}
}
}
}{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 47,
"customerNo": 49999
},
{
"orderNo": 356,
"customerNo": 30018
},
{
"orderNo": 358,
"customerNo": 30012
}
]
}
}
}
}Grouping extensions
To group by column extensions, use the extensions field. You need to specify the column name and, optionally, the grouping order, which can be either DEFAULT or ROLLUP. If the grouping order is not specified, DEFAULT is used.
The following example shows fetching data from the general ledger transactions table, on account 3000 and year 2020, grouping the results by the AccountNo column with ROLLUP grouping, and the period, also with the ROLLUP grouping:
query read_gla_transactions_grouped($cid : Int)
{
useCompany(no : $cid)
{
generalLedgerTransaction(
filter : {
_and: [
{accountNo : {_gte : 3000}},
{year : {_gte : 2020}}
]},
groupBy: [
{
extensions : {
column : "AccountNo",
grouping : ROLLUP
}
},
{period : ROLLUP}
])
{
items
{
accountNo
period
aggregates
{
sum
{
postedAmountDomestic
}
}
}
}
}
}{
"data": {
"useCompany": {
"generalLedgerTransaction": {
"items": [
{
"accountNo": 3000,
"period": 4,
"aggregates": {
"sum": {
"postedAmountDomestic": -100
}
}
},
{
"accountNo": 3000,
"period": 12,
"aggregates": {
"sum": {
"postedAmountDomestic": -4950
}
}
},
{
"accountNo": 3000,
"period": 0,
"aggregates": {
"sum": {
"postedAmountDomestic": -5050
}
}
},
{
"accountNo": 4000,
"period": 12,
"aggregates": {
"sum": {
"postedAmountDomestic": 450
}
}
},
{
"accountNo": 4000,
"period": 0,
"aggregates": {
"sum": {
"postedAmountDomestic": 450
}
}
},
{
"accountNo": 4410,
"period": 4,
"aggregates": {
"sum": {
"postedAmountDomestic": 400
}
}
},
{
"accountNo": 4410,
"period": 12,
"aggregates": {
"sum": {
"postedAmountDomestic": 13035
}
}
},
{
"accountNo": 4410,
"period": 0,
"aggregates": {
"sum": {
"postedAmountDomestic": 13435
}
}
},
{
"accountNo": 0,
"period": 0,
"aggregates": {
"sum": {
"postedAmountDomestic": 8835
}
}
}
]
}
}
}
}Having clause extensions
The having clause expressions are very similar to the filter expressions. You need to specify the column name and its value expression.
In the following example, we run the same query as above, except that a having clause is used to filter and retain only the groups where the sum of the postedAmountDomestic is greater than 100:
query read_gla_transactions_grouped($cid : Int)
{
useCompany(no : $cid)
{
generalLedgerTransaction(
filter : {
_and: [
{accountNo : {_gte : 3000}},
{year : {_gte : 2020}}
]},
groupBy: [
{
extensions : {
column : "AccountNo",
grouping : ROLLUP
}
},
{period : ROLLUP}
],
having : {
_sum : {
extensions : {
column : "postedAmountDomestic"
decimalValue : {
_gt : 100
}
}
}
})
{
items
{
accountNo
period
aggregates
{
sum
{
postedAmountDomestic
}
}
}
}
}
}{
"data": {
"useCompany": {
"generalLedgerTransaction": {
"items": [
{
"accountNo": 4000,
"period": 12,
"aggregates": {
"sum": {
"postedAmountDomestic": 450
}
}
},
{
"accountNo": 4000,
"period": 0,
"aggregates": {
"sum": {
"postedAmountDomestic": 450
}
}
},
{
"accountNo": 4410,
"period": 4,
"aggregates": {
"sum": {
"postedAmountDomestic": 400
}
}
},
{
"accountNo": 4410,
"period": 12,
"aggregates": {
"sum": {
"postedAmountDomestic": 13035
}
}
},
{
"accountNo": 4410,
"period": 0,
"aggregates": {
"sum": {
"postedAmountDomestic": 13435
}
}
},
{
"accountNo": 0,
"period": 0,
"aggregates": {
"sum": {
"postedAmountDomestic": 8835
}
}
}
]
}
}
}
}Aggregate extensions
Previously, we have see how to use extension columns in grouping and having clauses. These are connection arguments. But the extensions columns can be used in the projected aggregated results too. The syntax is similar to that of regular columns.
In the following example, the sum of the postedAmountDomestic is fetched using the schema extensions:
query read_gla_transactions_grouped($cid : Int)
{
useCompany(no : $cid)
{
generalLedgerTransaction(
filter : {
_and: [
{accountNo : {_gte : 3000}},
{year : {_gte : 2020}}
]},
groupBy: [
{
extensions : {
column : "AccountNo",
grouping : ROLLUP
}
},
{period : ROLLUP}
],
having : {
_sum : {
extensions : {
column : "postedAmountDomestic"
decimalValue : {
_gt : 100
}
}
}
})
{
items
{
accountNo
period
aggregates
{
sum
{
extensions
{
postedAmountDomestic : decimalValue
}
}
}
}
}
}
}{
"data": {
"useCompany": {
"generalLedgerTransaction": {
"items": [
{
"accountNo": 4000,
"period": 12,
"aggregates": {
"sum": {
"extensions": {
"postedAmountDomestic": 450
}
}
}
},
{
"accountNo": 4000,
"period": 0,
"aggregates": {
"sum": {
"extensions": {
"postedAmountDomestic": 450
}
}
}
},
{
"accountNo": 4410,
"period": 4,
"aggregates": {
"sum": {
"extensions": {
"postedAmountDomestic": 400
}
}
}
},
{
"accountNo": 4410,
"period": 12,
"aggregates": {
"sum": {
"extensions": {
"postedAmountDomestic": 13035
}
}
}
},
{
"accountNo": 4410,
"period": 0,
"aggregates": {
"sum": {
"extensions": {
"postedAmountDomestic": 13435
}
}
}
},
{
"accountNo": 0,
"period": 0,
"aggregates": {
"sum": {
"extensions": {
"postedAmountDomestic": 8835
}
}
}
}
]
}
}
}
}Table extensions
The data model extensions allows adding new tables, as well as relations between these tables and existing (core model) tables.
To query an extension table, you need to use the generic table connection. This is defined by a connection type like all the other connection fields in the schema, with the same arguments. However, instead of defining name columns, it uses all the extension features shown above. The key difference is that the table connection being an extension itself, it does not define inner fields called extensions, like we have seen above.
The following example shows how to query a table using the table connection.
query read_table($cid:Int!)
{
useCompany(no:$cid)
{
table(
name : "order",
first: 3,
filter : {
joindown : {
relation : {
name : "Order",
from : "OrderLine"
}
_some : {
joinup :{
relation : {
name : "Product"
},
column : "Description",
stringValue : {
_eq : "Product 103"
}
}
}
}
}
orderBy : [
{column : "customerNo", order : DESC},
{column : "orderDate", order : ASC},
{column : "orderNo"}
])
{
items
{
orderNo : integerValue
customerNo : integerValue
address : stringValue (name : "addressLine1")
orderDate : dateValue
createdDate : dateValue
createdTime : timeValue
createdTimestamp : timestampValue
totalDiscountPercent : decimalValue
editStatus : integerValue
joindown(relation : "Order", from : "OrderLine")
{
items
{
orderNo : integerValue
lineNo : integerValue
joinup(relation : "Product")
{
description : stringValue
}
}
}
joinup(relation : "Customer")
{
name : stringValue
joinup(relation : "Country")
{
name : stringValue
joinup(relation : "Language")
{
name : stringValue
}
}
}
}
}
}
}