Get Sandbox Access

Paybase Developer Centre

OverviewAPI GuidesGetting StartedRecipesGig Economy PlatformsSharing Economy PlatformsMarketplacesBlockchain BusinessesEscrowRolesRulesDue DiligenceCustomersAccountsBank AccountsCardsTransactionsIntroductionInboundGetting money into the systemTransaction ReferenceAccount ReferenceInternalOutboundEscrowSplit PaymentsRefundsStrong Customer Authentication3D Secure AuthenticationIntroductionCreate a cardCreate a transactionDocument UploadStatementsWebhooksErrorsPQLAPI ReferenceAccountCreate an accountRetrieve an accountTransition account statusList all accountsAnnotate an accountDelete annotation from an accountTag an accountDelete tag from an accountBank AccountCreate a bank accountRetrieve a bank accountUpdate a bank accountTransition bank account statusList all bank accountsAnnotate a bank accountDelete annotation from a bank accountTag a bank accountDelete tag from a bank accountCardCreate a cardRetrieve a cardUpdate a cardTransition card statusList all cardsAnnotate a cardDelete annotation from a cardTag a cardDelete tag from a cardCardholderCreate a cardholderRetrieve a cardholderUpdate a cardholderTransition cardholder statusList all cardholdersAnnotate a cardholderDelete annotation from a cardholderTag a cardholderDelete tag from a cardholderCreate an authentication tokenCheckCreate a checkCustomerIndividual CustomerCreate a customerRetrieve a customerUpdate a customerSole TraderCreate a customerRetrieve a customerUpdate a customerOrganisationCreate a CustomerRetrieve a CustomerUpdate a CustomerIncorporated BusinessCreate a customerRetrieve a customerUpdate a customerBusiness PersonAdd a business personRetrieve a business personUpdate a business personDelete a business personRetrieve a customerTransition state of a customerList all customersAnnotate a customerDelete annotation from a customerTag a customerRemove tag from a customerCreate an authentication tokenTouch a customerDocumentCreate a documentRetrieve a documentList Document TypesReferenceRetrieve a referenceStatementRetrieve a statementStatusRetrieve API statusTransactionCreate inbound transactionCreate internal transactionCreate outbound transactionRetrieve a transactionTransition transaction statusList all transactionsAnnotate a transactionDelete annotation from a transactionTag a transactionDelete tag from a transaction
API version: 7c99a7a

Introducing PQL

PQL (Paybase Query Language) is a sophisticated query language allowing complex criteria to be applied when fetching data from the Paybase system.

The query Parameter

All listing endpoints support a query request parameter. This parameter should be written in PQL and can be used to filter, sort, and paginate through the result set returned by the platform.

The PQL language is flexible enough to support complex queries containing multiple filter clauses, whilst also adhering to logical operator precedence.

PQL Syntax

The syntax for the query parameter is as follows:

1
2
3
4
[WHERE <condition>[ AND <condition>][ OR <condition>]]
[ORDER BY <field> <direction>]
[OFFSET <position>]
[LIMIT <max>]

All keywords can be used together to build a complex query, however, care should be taken to not use a terminating expression (ORDER BY, LIMIT, OFFSET) mid-way through a filter query.

Example:

1
2
3
4
5
6
7
8
9
10
11
12
/*
Filter clauses should never be broken by terminating expressions
*/
WHERE a = 1 AND b = 2
OFFSET 4
WHERE c = 3 AND d = "four";
/*
In all cases the filter conditions should be kept as one large set of conditions
*/
WHERE a = 1 AND b = 2
AND c = 3 AND d = "four"
OFFSET 4;

Filtering with WHERE

1
[WHERE <condition>[ AND <condition>][ OR <condition>]]

Filtering of data is done with the WHERE keyword. Generally, the structure of data to query is the same as the response payload of that entity.

For example, given the structure below, we can use nested field names to query data like so: WHERE id = "123" AND profile.lastName LIKE Apple%.

1
2
3
4
5
6
7
8
9
{
  "id": "123",
  "profile": {
    "firstName": "Zehan",
    "lastName": "Applebee"
  },
  "role": "buyer",
  "tags": ["person", "cars"]
}

Conditions for WHERE clauses should be in the format <field|value> <operator> <field|value>. In most cases, each side of the expression can either be a <field> or a <value>.

This allows some interesting use-cases to arise, for example, to find all customers who are tagged with "person", we can use the query WHERE "person" IN tags. It should be noted, that in some cases a <value> on the left side will be invalid.

  • Arrays should never be used on the left-hand side of an expression, ie. WHERE ("person", "cars") IN tags, use WHERE "person" IN tags AND "cars" IN tags instead.
  • LIKE matchers should never be used on the left-hand side of an expression, ie. WHERE Apple% LIKE profile.lastName, use WHERE profile.lastName LIKE Apple% instead.

Supported <operator>s include:

OperatorDescription
=Equal to
<>|!=Not equal to
>Greater than
>=Greater than or equal to
<Less than
<=Less than or equal to
INAppears in value where value is ("id1",...) or an array <field>
NOT INDoes not appear in value where value is ("id1",...) or an array <field>
LIKEWhere <field> matches <value>
ILIKEWhere <field> matches <value>, case insensitive
NOT LIKEWhere <field> does not match <value>
NOT ILIKEWhere <field> does not match <value>, case insensitive

Multiple <condition>s can be asserted by using the OR and AND keywords appropriately. Sub-queries can be used to make more complex logical filters. OR takes operator precedence over AND, so the following are not equivalent:

1
2
3
4
5
6
7
8
9
10
11
/*
  - Matches id with "123" and role not equal to "buyer"
  - but also any profile with a firstName beginning with "Z"
*/
WHERE id = "123" AND role <> "buyer" OR profile.firstName LIKE Z%

/*
  - Matches id with "123"
  - and role not equal to "buyer" or profile firstName begins with "Z"
*/
WHERE id = "123" AND (role <> "buyer" OR profile.firstName LIKE Z%)

Using (I)LIKE/NOT (I)LIKE to match data

Matcher expressions have 2 special characters available to them to denote wildcards. These characters can be used multiple times in one expression.

  • _ denotes one unknown/wildcard character
  • % denotes zero or more unknown/wildcard characters

Examples:

  • _pple will not match Applebee but _pple% will
  • Z_an will not match Zehan but Z%han will
  • b_r will not match buyer but b%r will

Comparing dates using the DATE() expression

Sometimes it may be a requirement to retrieve all entities created within a certain date range. When this need arises, the DATE() call expression can be used to normalise queried dates to a standard format.

The DATE() call expression takes a date in the following formats for comparison:

Example:

1
WHERE createdAt > DATE("Jun 15, 2018 04:20:00") AND createdAt < DATE("Jun 17, 2018 04:21:00")

Sorting with ORDER BY

Sorting of data is done with the ORDER BY keywords.

1
[ORDER BY <field> <direction>]

The <direction> should either be ASC for ascending or DESC for descending.

Example:

1
ORDER BY profile.dob DESC

Paginating

You can paginate through data with the LIMIT and OFFSET keywords.

1
2
[OFFSET <position>]
[LIMIT <max>]

When using OFFSET, <position> is the number of records to skip in the result set before returning. When using LIMIT, <max> is the maximum number of records to return.

Example:

1
OFFSET 10 LIMIT 10

Next up...