When a SELECT query is issued, the add-in translates the SQL statement into a GraphQL query. The add-in automatically generates the necessary schema when connecting to the API. The sections below explain how to customize filtering, ordering, and pagination when manual configuration is required.

Filtering

Mapping Filters to GraphQL Arguments

Use other:filter and other:argumentType on a column definition to map a SQL WHERE condition to a GraphQL argument:

• other:filter is a semicolon-separated list of <argumentName>:<operators>, where <operators> is a comma-separated list of SQL operators the argument accepts. Valid operators are =, <, <=, >, and >=.
• other:argumentType specifies the GraphQL type of the argument as defined in the GraphQL schema type language.

For example, to push the following query condition server-side:

SELECT * FROM customers WHERE modifiedAt < '2019-10-30T04:00:00.000Z'

Configure other:filter on the column to specify how filter operators map to GraphQL arguments:

<attr name="modifiedAt" xs:type="datetime" other:relativePath="modifiedAt" other:argumentType="DateTime" other:filter="modifiedAtAfter:>;modifiedAtBefore:<" />

Then include the filter in other:possiblePaths so the add-in knows which path the filter applies to:

other:possiblePaths="{'path':'/customers','modifiedAt':{'path':'/customers'}}"

The following request body is generated:

{
    "variables": {
        "modifiedAt_modifiedAtBefore": "2019-10-30T04:00:00.000Z"
    },
    "query": "query($modifiedAt_modifiedAtBefore:DateTime) { customers(modifiedAtBefore:$modifiedAt_modifiedAtBefore) { id modifiedAt } }"
}

Note: Filters defined with other:filter must also appear as keys in other:possiblePaths to be pushed to the server. Filters not listed in other:possiblePaths are evaluated client-side after all data is fetched.

Possible Paths

Different GraphQL objects may be accessible through different paths and filter combinations. The other:possiblePaths table extra info defines all valid paths, filters, and combinations of filter requirements. The add-in intelligently selects the optimal path based on the filters provided in the query.

The JSON structure uses nesting to define filter requirements:

• A path key defines a path that can be queried at that level of nesting.
• A filter key with a sibling path key is optional for that path.
• A filter key with no sibling path key is required to access the paths nested within it.
• Filters nested inside other filters form a chain: all filters in the chain must be provided together to access the nested path.

No Filters Required

When the only key is path, the data is accessible without any filters:

other:possiblePaths="{'path':'/categories'}"

Any query on this table will work without filters:

SELECT * FROM categories

Optional Filters

When filter keys appear at the same level as a path key, those filters are optional:

other:possiblePaths="{'path':'/articles','status':{'path':'/articles'},'featured':{'path':'/articles'}}"

The data is accessible with or without filters. When status or featured are provided, they are sent as arguments to filter results on the server:

SELECT * FROM articles
SELECT * FROM articles WHERE status = 'PUBLISHED'
SELECT * FROM articles WHERE featured = true
SELECT * FROM articles WHERE status = 'PUBLISHED' AND featured = true

Required Filter

When a filter key has no sibling path key, it must be provided to access the paths nested within it:

other:possiblePaths="{'departmentId':{'path':'/users','role':{'path':'/users'}}}"

The departmentId filter is required. The role filter is optional because it has a sibling path key at the same level:

SELECT * FROM users WHERE departmentId = 'eng-001'
SELECT * FROM users WHERE departmentId = 'eng-001' AND role = 'ADMIN'

Chained Filter Requirements

When a GraphQL argument uses an input object type, its fields are exposed as individual filter columns. If the fields must be specified together, they are represented as a chain in other:possiblePaths. For example, priceRange_min and priceRange_max represent the min and max fields of a priceRange input argument:

other:possiblePaths="{'categoryId':{'path':'/products','inStock':{'path':'/products'},'priceRange_min':{'priceRange_max':{'path':'/products'}}}}"

The categoryId filter is required. The inStock filter is optional. The priceRange_min and priceRange_max filters are optional but must both be provided together:

SELECT * FROM products WHERE categoryId = 'electronics'
SELECT * FROM products WHERE categoryId = 'electronics' AND inStock = true
SELECT * FROM products WHERE categoryId = 'electronics' AND priceRange_min = 100 AND priceRange_max = 500

Multiple Possible Paths

When multiple paths are defined, the add-in selects the optimal path based on the filters provided. Each path can have different filter requirements and may point to different endpoints:

other:possiblePaths="{'path':'/businesses','id':{'path':'/business'}}"

Without filters, the add-in queries /businesses. When id is provided, the add-in switches to the more specific /business endpoint:

SELECT * FROM businesses
SELECT * FROM businesses WHERE id = 'QnVzaW5lc3M6ZTY4...'

The second query is converted to:

{
    "variables": {
        "id_id": "QnVzaW5lc3M6ZTY4..."
    },
    "query": "query($id_id:ID) { business(id:$id_id) { id name createdAt } }"
}

Ordering

To enable server-side sorting, configure the following on the column definition:

• other:orderByFormat defines the shape of the sort argument, using the placeholders {orderByArgumentValue} for the sort field and {sortOrder} for the direction. Can be set on the table or column.
  

  <attr name="createdAt" xs:type="datetime" other:relativePath="createdAt" other:orderByFormat="{field: {orderByArgumentValue}, direction: {sortOrder}}" other:orderBy="sort:CREATED_AT" />

• other:orderBy maps the column to a sort argument value. Format: <argumentName>:<value>. Set on the column only.
  

  <attr name="createdAt" xs:type="datetime" other:relativePath="createdAt" other:orderBy="sort:CREATED_AT" />

The query:

SELECT id FROM labels ORDER BY createdAt ASC

is converted to:

{
    "variables": {
        "first": <Pagesize>
    },
    "query": "query($first:Int) { repository { labels(sort:{field: CREATED_AT, direction: ASC}, first:$first) { edges { node { id } } pageInfo { endCursor hasNextPage } } } }"
}

Pagination

The add-in supports two pagination modes, configured via the other:paginationObjects table extra info.

Cursor-Based Pagination

other:paginationObjects = "{
    'labels': {
        'cursorPath': 'after',
        'cursorType': 'String',
        'pageSizeArgumentPath': 'first',
        'pageSizeArgumentType': 'Int',
        'depth': '2',
        'paginationType': 'Cursor',
        'isConnection': 'True',
        'pageInfo': ['endCursor', 'hasNextPage']
    }
}"

The following request body is generated:

{
    "variables": {
        "UserLogin_owner": "user",
        "RepositoryName_name": "test",
        "first": <Pagesize>
    },
    "query": "query($UserLogin_owner:String!, $RepositoryName_name:String!, $first:Int) { repository(owner:$UserLogin_owner, name:$RepositoryName_name) { labels(first:$first) { edges { node { id color createdAt description isDefault name resourcePath updatedAt url } } pageInfo { endCursor hasNextPage } } name } }"
}

Offset-Based Pagination

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

The following request body is generated:

{
    "variables": {
        "pageSize_1": <Pagesize>
    },
    "query": "query($pageSize_1:Int) { businesses(pageSize:$pageSize_1) { edges { node { id } } pageInfo { totalPages currentPage } } }"
}

Note: Input objects are supported as pagination arguments. Use a slash-separated path for offsetArgumentPath and pageSizeArgumentPath to target nested fields:

other:paginationObjects = "{
    'businesses': {
        'offsetArgumentPath': 'query/pagination/page',
        'offsetArgumentType': 'custom_query',
        'pageSizeArgumentPath': 'query/pagination/pageSize',
        'pageSizeArgumentType': 'custom_query',
        'depth': '1',
        'paginationType': 'Offset',
        'isConnectionObject': 'True',
        'pageInfo': ['currentPage', 'totalPages', 'totalCount']
    }
}"

The following request body is generated:

{
    "variables": {
        "query": {
            "pagination": {
                "pageSize": <Pagesize>
            }
        }
    },
    "query": "query($query:custom_query) { businesses(query:$query) { edges { node { id } } pageInfo { totalPages currentPage } } }"
}