Querying with Searchlight

Fetch records using a complex and powerful query language

When querying data, Lockstep uses Searchlight, a database query language designed to be comfortable for developers who are familiar with SQL. This language provides a comfortable syntax that is easy to understand, while it is still a compiled language that can execute on a variety of different database technologies.

Searchlight allows simple queries to be written in text. For example, if you wanted to fetch all companies whose name begins with the letter A:

GET /api/v1/companies/query?filter=companyName startswith A

In this example, Searchlight understands that you are querying based on the companyName field. You specified a rule called startswith, and you provided the parameter A. Searchlight can then compile this query into a task and search the Lockstep Platform for data that matches your request.

Basic Comparisons

Searchlight supports the following basic comparison rules. You can either use the symbol for this rule, or you can use the short textual abbreviation if you prefer. Using the short text can sometimes make your code easier to write since text does not need to be escaped when constructing a URL.

  • Equals (=, EQ)
  • Greater Than (>, GT)
  • Greater Than Or Equal (>=, GE)
  • Less Than (<, LT)
  • Less Than Or Equal (<=, LE)
  • Not Equal (!=, NE, <>)

Complex Comparisons

Searchlight supports advanced comparisons as well. You can use logic such as

  • IN - Test whether a field is within a range of possible values. For example, you can filter for invoices in one of three possible currencies: filter=currencyCode IN ('USD', 'EUR', 'GBP')
  • CONTAINS - Test whether a text string contains a specific value. This filter rule is case insensitive. For example, you can fetch comments that contain "payment": filter=noteText contains 'payment'
  • STARTSWITH - Test whether a text string starts with a specific value. This filter rule is case insensitive.
  • ENDSWITH - Test whether a text string ends with a specific value. This filter rule is case insensitive.
  • ISNULL - Check whether a value is specifically null, or empty. This filter rule can determine whether an element of data is missing.

Conjunctions

Searchlight allows you to construct more complex queries by chaining filter statements together. Just like in mathematics, you can wrap query elements in parenthesis and combine them. Each criteria can be joined to other criteria using AND and OR logical operators.

An example of multiple conjunctions could look like the following:

filter=(companyName startswith 'a' OR companyName startswith 'b') AND currencyCode eq 'USD'

Nested Fetch

Searchlight provides you with the ability to fetch multiple data sets with a single query. For example, you can specify ?include=Notes on any object in the database to retrieve user-provided comments related to that object.

Using nested fetch can allow you to retrieve multiple data sets with a single call and avoid multiple roundtrip calls to the server.

To see a list of available nested data objects for an API, see the documentation for the include parameter for a GET or QUERY call.

Sorting

Searchlight automatically sorts your records using the order parameter. You can specify a list of fields and rules such as "ascending" or "descending". For simplicity, these operators are referred to as ASC and DESC.

For example, to sort based on company status first and name second, you might use this query:

GET /api/v1/companies/query?order=companyStatus desc,companyName asc

Pagination

All requests to the Lockstep API are automatically filtered for pagination. You can specify in your query the size of each page and the page number to retrieve. All counts are zero-based.

To retrieve 100 companies at a time, and to use page 12, you would use this query:

GET /api/v1/companies/query?pageNumber=12&pageSize=100

The results for each request include the total number of records matching your filter. For ease of development, the results also include the page number and page size:

{
  "totalCount": 0,
  "pageSize": 0,
  "pageNumber": 0,
  "records": [ ... ]
}

Did this page help you?