Query

Here we will detail how you can build your own queries to pass into the Pay Theory API.

First let's look at a basic query object, then we can break down its parts.

---

The Query Object

{
query_list: [QueryPair]
sort_list: [SortPair]
}

Key	type	description
query_list	[QueryPair]	A list of query pairs used to build out a query.
sort_list	[SortPair]	A list of sort pairs to define how the data should be sorted.

---

Query Pair

A query pair is the object to build out a query. There are some required fields and some optional fields.

{
    conjunctive_operator: ConjunctiveOperator
    in_values: [String]
    key: String
    operator: Operator
    query_group: [QueryPair]
    value: String
}

Key	type	description
key	String!	The key to the value you want to query against.
operator	Operator	The operator to use to compare the value to the data you are calling. More detail below.
value	String	The value to compare the data to. If using the LIKE or NOT_LIKE operator, this value can contain wildcard characters.
in_values	[String]	A list of values to compare the data to. This should be used instead of value if using the operators IN or NOT_IN.
conjunctive_operator	ConjunctiveOperator	The conjunctive operator to use to connect the query pair with the next query pair. More detail below.
query_group	[QueryPairs]	A list of query pairs to use to build out a nested query. A more detailed example is below under the examples section.

---

Operators

These operators are case-sensitive. The following are the available operators:

EQUAL The data is equal to the value.

NOT_EQUAL The data is not equal to the value.

LIKE The data is like the value. The value can contain wildcard characters.

NOT_LIKE The data is not like the value. The value can contain wildcard characters.

IN_LIST The data is in the list of values.

NOT_IN_LIST The data is not in the list of values.

GREATER_THAN The data is greater than the value.

GREATER_EQUAL The data is greater than or equal to the value.

LESS_THAN The data is less than the value.

LESS_EQUAL The data is less than or equal to the value.

---

Conjunctive Operators

These operators are case-sensitive. Conjunctive operators in the same array must match for a query to work. To mix operators use nested queries with query pairs containing a query_list. The following are the available conjunctive operators:

AND_NEXT The results of the query have to meet all the conditions in the query pair list.

OR_NEXT The results of the query have to meet one of the conditions in the query pair list.

NONE_NEXT The final query pair in the list should use this operator since it has nothing to connect to.

---

Sort Pair

A sort pair is the object used to tell a query how the data should be sorted.

{
    direction: SortDirection
    key: String
}

Key	type	description
direction	SortDirection	The direction to sort the data. These are case-sensitive.
key	String	The key to sort the data by.

---

Sort Direction

The direction to sort the data. These are case-sensitive.

ASC Begins with the least or smallest and ends with the greatest or largest

DESC Begins with the greatest or largest and ends with the least or smallest

---

Pagination

When querying lists of items from the Pay Theory API, you can use pagination to break up large result sets into manageable chunks.

The API uses cursor-based pagination, which is more reliable than traditional offset/limit pagination for real-time data.

How Pagination Works

The basic pagination parameters are:

• limit: The maximum number of items to return
• direction: The direction of pagination
  • FORWARD: Fetches the next set of items. Should be used if you are passing in the offset_id of the last item from the previous array.
  • BACKWARD: Fetches the previous set of items. Should be used if you are passing in the offset_id of the first item from the previous array.
• offset_id: The ID of the offset item from the previous page
• offset: The sort value of the offset item from the previous page (only required when sorting)

Basic Pagination Example

Here's a simple example of paginating through transactions without sorting:

{
  transactions(
    limit: 100,
    direction: FORWARD
  ) {
    items {
      transaction_id
      transaction_date
    }
    total_row_count
  }
}

For the next page, you would only need to provide the offset_id:

{
  transactions(
    limit: 100,
    direction: FORWARD,
    offset_id:  "ptl_txn_rbdg98004adg"  // Last transaction_id from previous data set
  ) {
    items {
      transaction_id
      transaction_date
    }
    total_row_count
  }
}

Pagination with Sorting

When your query includes sorting, you need to provide both the offset_id and the offset parameter. The offset should be the value of the sort column from the last item in your previous results.

For example, if sorting by transaction_date:

{
  transactions(
    limit: 100,
    direction: FORWARD,
    offset_id: "ptl_txn_rbdg98004adg",  // Last transaction_id from previous data set
    offset: "2024-01-15T14:30:00Z",  // transaction_date from the same last item
    query: {
      sort_list: [{
        key: "transaction_date",
        direction: DESC
      }]
    }
  ) {
    items {
      transaction_id
      transaction_date
    }
    total_row_count
  }
}

Determining if More Pages Exist

Queries that can be paginated can return a total_row_count field that tells you the total number of items matching your query. You can compare this with the number of items you've retrieved to determine if there is more data to fetch.

Pagination Best Practices

1. When sorting results, always use both offset_id and offset to ensure consistent pagination
2. When not sorting, only offset_id is needed
3. Keep track of the total_row_count to know when you've retrieved all items
4. Maintain the same sort criteria throughout your pagination sequence

Remember that when using sorting, the values for offset_id and offset should always come from the first or last item in your previous query's results to ensure consistent pagination through your data set.

---

Examples

Settlements With Gross Amount over $10

If you wanted to build a query that looked for any settlements that had a gross_amount over $10 and was sorted by gross_amount in ascending order, you would do the following:

{
  settlements(limit: 10, query:
  {
    query_list: [
      {
        key: "gross_amount",
        value: "1000",
        operator: GREATER_THAN,
        conjunctive_operator: NONE_NEXT
      }
    ],
    sort_pair: [{
      direction: ASC,
      key: "gross_amount"
    }]
  }) {
    items {
      currency
      gross_amount
    }
    total_row_count
  }
}

---

Transactions With Status SETTLED and the reference starts with test

If you wanted to build a query that looked for any transactions that had a status of SETTLED and the reference starts with test, you would do the following:

{
  transactions(limit: 5, query: {query_list: [
    {
      key: "reference",
      value: "test%",
      operator: LIKE,
      conjunctive_operator: AND_NEXT
    },
    {
      key: "status",
      value: "SETTLED",
      operator: EQUAL,
      conjunctive_operator: NONE_NEXT
    }
  ]}) {
    items {
      transaction_id
      reference
      gross_amount
    }
    total_row_count
  }
}

---

Transactions with a nested query

To build nested queries you can use multiple query pairs with at least one containing a query_group.

{
  transactions(limit: 5, query: {query_list: [
    {
      query_group: [
        {
          key: "status",
          value: "SETTLED",
          operator: EQUAL,
          conjunctive_operator: AND_NEXT
        },
        {
          key: "gross_amount",
          value: "100",
          operator: GREATER_THAN,
          conjunctive_operator: NONE_NEXT
        }

      ],
      conjunctive_operator: OR_NEXT
    },
    {
      key: "gross_amount",
      value: "1000",
      operator: GREATER_THAN,
      conjunctive_operator: NONE_NEXT
    }
  ]}) {
    items {
      full_name
      gross_amount
    }
    total_row_count
  }
}

This query would return any transactions where the status is SETTLED and the gross_amount is greater than 100 or transactions where the gross_amount is greater than 1000.

This allows for more advanced queries and for you to group AND_NEXT and OR_NEXT in a single query.

---

Querying Sub Objects

Due to the fact payment method is a nested data object payment method queries be made by passing a separate array of query pairs for the metadata.

{
  transactions(limit: 10, query:
  {
    query_list: [
      {
        key: "gross_amount",
        value: "1000",
        operator: GREATER_THAN,
        conjunctive_operator: NONE_NEXT
      }
    ],
    sort_pair: [{
      direction: ASC,
      key: "gross_amount"
    }]
  }
  ) {
    items {
      currency
      gross_amount
      payment_method(query_list: [
        {
          key: "last_four",
          value: "1234",
          operator: EQUAL
        }
      ])
    }
    total_row_count
  }
}

This would return 10 transactions where the gross_amount is greater than 1000 and the payment has a payment method in which last_four is equal to 1234. It would be sorted by gross_amount in ascending order.

---

Querying On Metadata

Metadata queries work similarly but do not support nested queries using query_group.

{
  transactions(limit: 10, query:
  {
    query_list: [
      {
        key: "gross_amount",
        value: "1000",
        operator: GREATER_THAN,
        conjunctive_operator: NONE_NEXT
      }
    ],
    sort_pair: [{
      direction: ASC,
      key: "gross_amount"
    }]
  }
  ) {
    items {
      currency
      gross_amount
      metadata(query_list: [
        {
          key:"user_defined_payer_id",
          value:"1234",
          operator: EQUAL,
          conjunctive_operator: NONE_NEXT
        }
      ])
    }
    total_row_count
  }
}

This would return 10 transactions where the gross_amount is greater than 1000 and the payment has metadata user_defined_payer_id is equal to 1234. It would be sorted by gross_amount in ascending order.