SELECT Execution

When a SELECT query is issued, the adapter executes the GET method of the schema, which invokes the adapter's built-in operations to process GraphQL. In the GET method you have control over the request for data. The following procedures show several ways to use this: search the remote data, server-side, with SELECT WHERE, or implement paging.

Mapping SELECT criteria to GraphQL arguments

The following sections show how to translate a SELECT WHERE statement into a GraphQL query to GraphQL APIs. The procedure uses the following statement:

SELECT * 
FROM <table> 
WHERE ModifiedAt<'2019-10-30 05:05:36.001'

If this filter is supported on the server via query parameters, you can use the other:filter property of the api:info column definition to specify the desired mapping. For the above query, the adapter will use this property to map the modifiedAt < '<date>' filter to the query parameter that returns results that were modifed before a given date, and the modifedAt > '<date>' filter to the query parameter that filters results that were modifed after.

other:filter is a semicolon separated list of <parameter name>:<operator list>. <parameter name> is the name of the query parameter and <operator list> is a comma-separated list of operators used for the mapping. Valid operators are <, <=, =, > and >=.
other:argumentType is a required extra info. It should contain the type of the argument based on the GraphQL schema type language.

To perform this mapping, the adapter would use the following markup for the modifedAt column definition:

<attr name="ModifiedAt" xs:type="datetime" other:relativePath="modifiedAt" other:argumentType="DateTime" description="When the vendor was last modified." other:filter="modifiedAtAfter:>;modifiedAtBefore:<" />

This query results in the following postdata:

{
    "variables": {
        "ModifiedAt_modifiedAtBefore": "2019-10-30T09:05:36.001Z"
    },
    "query": "query($ModifiedAt_modifiedAtBefore:DateTime) {\r\nbusinesses {\r\nedges {\r\nnode {\r\ncustomers(modifiedAtBefore:$ModifiedAt_modifiedAtBefore) {\r\nedges {\r\nnode {\r\nid\r\nmodifiedAt\r\n}\r\n}\r\npageInfo {\r\ntotalPages\r\ncurrentPage\r\n}\r\n}\r\nid\r\n}\r\n}\r\npageInfo {\r\ntotalPages\r\ncurrentPage\r\n}\r\n}\r\n}\r\n"
}

Path filters

There exist GraphQL services where the GraphQL argument is not enough to process the filter serverside. The path should be changed. In order to correctly use a path filter you need to complete the following steps:

Add the path to the other:possiblePaths extra info and map it with the column name you want to filter.
Ex: other:possiblepaths="{'path':'/businesses/edges/node','id':{'path':'/business'}}"

Set the other:isPathFilter to TRUE in the column defintion.

<attr  name="Id"  xs:type="string" key="true" other:relativePath="id" other:isPathFilter="true" other:filter="id:="  />

After completing these steps the following SQL query

SELECT Id,Name,CreatedAt FROM Businesses WHERE Id='QnVzaW5M6ZTY4ZDA2MmQtYzkzZS00MGZkLTk4YWUtNDg2YzcxMmExNzFl'

will be converted to the postdata:

{
    "variables": {
        "Id_id": "QnVzaW5M6ZTY4ZDA2MmQtYzkzZS00MGZkLTk4YWUtNDg2YzcxMmExNzFl"
    },
    "query": "query($Id_id:ID) {\r\nbusiness(id:$Id_id) {\r\nid\r\nname\r\ncreatedAt\r\n}\r\n}\r\n"
}

Pagination

The driver supports two pagination modes.

Cursor

other:paginationObjects = "{
    'labels': {
        'cursorName': 'after',
        'cursorType': 'String',
        'pageSizeArgumentName': 'first',
        'pageSizeArgumentType': 'Int',
	'depth':'1',
        'paginationType': 'Cursor',
        'isConnection': 'True',
        'pageInfo': ['endCursor', 'hasNextPage', 'hasPreviousPage', 'startCursor']
    }
}"

The following postdata will be generated after processing the other:paginationObjects table extra info specified above:

{
    "variables": {
        "UserLogin_owner": "testaccount71",
        "RepositoryName_name": "test",
        "first": <Pagesize>
    },
    "query": "query($UserLogin_owner:String!, $RepositoryName_name:String!, $first:Int) {\r\nrepository(owner:$UserLogin_owner, name:$RepositoryName_name) {\r\nlabels(first:$first) {\r\nedges {\r\nnode {\r\nid\r\ncolor\r\ncreatedAt\r\ndescription\r\nisDefault\r\nname\r\nresourcePath\r\nupdatedAt\r\nurl\r\n}\r\n}\r\npageInfo {\r\nendCursor\r\nhasNextPage\r\n}\r\n}\r\nname\r\n}\r\n}\r\n"
}

Offset

other:paginationObjects="{
    'businesses': {
        'offsetArgumentName': 'page',
        'offsetArgumentType': 'Int',
        'pageSizeArgumentName': 'pageSize',
        'pageSizeArgumentType': 'Int',
	'depth':'1',
        'paginationType': 'Offset',
        'isConnectionObject': 'True',
        'pageInfo': ['currentPage', 'totalPages', 'totalCount']
    }
}"

The following postdata will be generated after processing the other:paginationObjects table extra info specified above:

{
    "variables": {
        "pageSize_1": <Pagesize>
    },
    "query": "query($pageSize_1:Int) {\r\nbusinesses(pageSize:$pageSize_1) {\r\nedges {\r\nnode {\r\nid\r\n}\r\n}\r\npageInfo {\r\ntotalPages\r\ncurrentPage\r\n}\r\n}\r\n}\r\n"
}

Process Other SELECT Statements Server Side

ORDER BY

You can sort the results serverside if the GraphQL service supports it by specifying the following properties:

The other:orderByFormat can be specified in the table definition or in the column definition.

<rsb:info title="Labels" desc="Lists information about the different labels you can apply on an issue." other:orderByFormat="{field: {orderByArgumentValue}, direction: {sortOrder}}">
<attr name="CreatedAt" xs:type="datetime" other:relativePath="createdAt"  other:orderByFormat="{field: {orderByArgumentValue}, direction: {sortOrder}}" other:orderBy="orderBy:CREATED_AT" />

The other:orderBy should be specified only in the column definition. The format of this property is <orderByArgumentName>:<orderByArgumentValue>
```
<attr name="CreatedAt" xs:type="datetime" other:relativePath="createdAt"  other:orderBy="orderBy:CREATED_AT" />
```