SELECT Execution

When a SELECT query is issued, the add-in executes the GET method of the schema, which invokes the add-in'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 add-in 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 add-in 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" />
```