Query

Skedulo’s Query builder to talk to Salesforce

This module allows users to build queries which compiles down to url query parameters and helps to make requests to Salesforce through Skedulo’s API.

Once a query is built using this module, requests can either be sent to Skedulo’s API using the HTTP or can be manually compiled and sent forward using any other HTTP request library. However, it is highly recommended that you use the provided HTTP Module’s APIRequest Method method when making requests since it hides away the complexities of making requests as well as unwrapping the resulting response record sent by Skedulo’s API.

The Query instance implements a Fluent Interface and each part of the build query is immutable.

Instantiate

import { Query } from 'uranium'
  
const q = new Query()

The Query class can be used to build queries to Skedulo which is then converted into SOQL queries and sent to the Skedulo server.

The query object is used in conjunction with the HTTP functions in order to fetch fields, objects and other related objects. The query instance is immutable and can be safely re-used as long as they are only modified using the given builder methods.

The syntax for the method signatures used here is TypeScript

Compile

Signature

.compile()

Returns : { [string]: string }

.compile() is used to compile the built query into an object which can then be serialized and sent forward to the appropriate Skedulo API endpoint as query params. The examples for each of the other builder methods below demonstrate the final output of the compile function.

Filter

Signature

type FilterValues = string | moment.Instance
.filter(q: string, values: [FilterValues, [FilterValues]])

Returns : Query.Instance

Represents the WHERE part of a typical SQL query. It allows filtering / querying for data from a Skedulo Salesforce Object or Skedulo Custom Fields / Objects.

Filter by ID

// Find by UID = a0Eq0000002uPKLEA2

const q = new Query()

const filterQuery = q.filter("UID == $1", ["a0Eq0000002uPKLEA2"])

// To get the compiled query result
filterQuery.compile() 

// Output : 
{ filter: '(UID = "a0Eq0000002uPKLEA2")' }

The query string can use any of the following logical operators :

AND / $$
OR / ||
LIKE / ===
NOTLIKE / !==
IN

Filter by String and Date

/*
 * WHERE "Start" > '2016-03-01' AND "End" <= '2016-04-01' AND UID = 'a0Eq0000002uPKLEA2'
 */

const moment = require('moment-timezone')

// Moments for Start and End Date
const startDate = moment('2016-03-01')
const endDate = moment('2016-04-1')

const q = new Query()
  // Run filter
  .filter(
    "UID == $1 AND Start > $2 AND End <= $3", 
    ["a0Eq0000002uPKLEA2", startDate, endDate]
  )

// To get the compiled query result
q.compile() 

// Output : 
{ filter: '(UID = "a0Eq0000002uPKLEA2" AND Start > 2016-02-29T14:00:00.000Z AND End <= 2016-02-29T14:00:00.000Z)' }

Order By

Signature

.orderBy(q: string)

Returns : Query.Instance

Sort query data set in either Ascending or Descending order

Order By Start Date

const moment = require('moment-timezone')

// Moments for Start and End Date
const startDate = moment('2016-03-01')
const endDate = moment('2016-04-1')

const q = new Query()
  // Run filter
  .filter(
    "Start > $2 AND End <= $3", 
    ["a0Eq0000002uPKLEA2", startDate, endDate]
  )

// Sorting in Ascending Order
let sortedASC = q.orderBy("START")
//					OR
let sortedASC = q.orderBy("START ASC")

// Sorting in Descending Order
let sortedDESC = q.orderBy("START DESC")

// To get the compiled query result
sortedASC.compile() 

// Output :
{ filter: '(UID = "a0Eq0000002uPKLEA2" AND Start > 2016-02-29T14:00:00.000Z AND End <= 2016-02-29T14:00:00.000Z)',
  orderBy: 'START ASC' }

sortedDESC.compile()

// Output : 
{ filter: '(UID = "a0Eq0000002uPKLEA2" AND Start > 2016-02-29T14:00:00.000Z AND End <= 2016-02-29T14:00:00.000Z)',
  orderBy: 'START DESC' }

Start, Limit and Range

Signature

.start(num: number)
.limit(num: number)

.range(start: number, limit: number)

Returns : Query.Instance

Represents the OFFSET and LIMIT part of a typical SQL query. Useful for getting subsets of data.

A helper function .range(start, limit) is also provided which allows setting both start and limit in one call.

Start, Limit and Range

const moment = require('moment-timezone')
const q = new Query()

/* 
 * Finding items by date.
 * Library accepts moment objects for date values natively
 */ 
const startDate = moment("2016-01-01")
const jobListQuery = q.filter("Start >= $1", [startDate])

jobListQuery.compile()
// Output : Normalized to GMT time based on local timezone. 
{ filter: '(Start >= 2015-12-31T14:00:00.000Z)' }

// Limiting Result set
let limitedJobListQuery = jobListQuery.limit(50)
limitedJobListQuery.compile()
// Output : 
{ filter: '(Start >= 2015-12-31T14:00:00.000Z)', limit: '50' }

// Pagination
let rangeQuery = jobListQuery.start(100).limit(50)
// OR Both start and limit can be combined into a single query
let rangeQuery = jobListQuery.range(100, 50)

rangeQuery.compile()
// Output : 
{ 
  filter: '(Start >= 2015-12-31T14:00:00.000Z)',
  limit: '50',
  start: '100' 
}
                                               
rangeQuery.compile()

Count

Signature

.count(get: boolean)

Returns : Query.Instance

.count will return total counts of results matching queries. It can be used in combination with a regular filter query along with .start or .limit which will return the total count of results along with the subset of queried data.

Count

let q = new Query()

/* 
 * Finding items by date.
 * Library accepts moment objects for date values natively
 */ 
let startDate = moment("2016-01-01")
let jobListCountQuery = q.filter("Start >= $1", [startDate]).count(true)

jobListCountQuery.compile()
// Output : Normalized to GMT time based on local timezone. 
{ filter: '(Start >= 2015-12-31T14:00:00.000Z)', count: 'true' }

Select

Signature

.withFields(fieldNames: string)

Returns : Query.Instance

Specify the list of attributes to be returned by the model. The default is to return all attributes associated with the model the request is made against.

NOTE : .onlyFields builder method is required, to limit the result set from the main model the query is made against.

Select

let q = new Query()

/* 
 * Returning a subset of attributes from the model
 * This query only returns "Start" and "End" properties.
 */ 
let jobId = "a0Eq0000002uPKLEA2"
let jobQuery = q
.filter("UID == $1", [jobId])
.withFields("Start, End")
.onlyFields(true)
// ^ .onlyFields is REQUIRED to only return Start & End. Default is to return all values from model.

// Output : 
{
  filter: '(UID == "a0Eq0000002uPKLEA2")',
  fieldNames: 'Start, End',
  onlyFields: 'true' 
}

The .withFields attribute is really useful when attempting to fetch associated / related objects from Salesforce.

Request

const jobId = "a0E9000001B4etxEAB"

const query = new Query()
  .filter("JobId == $1", [jobId])
  .withFields("Job.UID, Job.Name, Job.Start, Job.End, Job.Description")
  .orderBy("PF")
  .limit(1000);

// Make JSON requests to Skedulo's API
const httpAPI = HTTP.APIRequest()

return query.makeRequest(httpAPI, "PatientForms");

Response

{
    "PatientForms": {
        "records": [
            {
                "Name": null,
                "RepresentativeSignatureDate": null,
                "JobId": "a0E9000001B4etxEAB",
                "PatientSignatureDate": null,
                "Job": {
                    "Name": "JOB-0413",
                    "Description": "asdf",
                    "Start": "2016-04-13T04:45:00.000Z",
                    "UID": "a0E9000001B4etxEAB",
                    "End": "2016-04-13T05:00:00.000Z"
                },
                "FormText": null,
                "UID": "a0S9000000cEH3REAW",
                "PF": 41,
                "Relationship": null
            }
        ]
    }
}

Last modified August 2, 2019: Updated fonts and finished guide 1:1 (fe87bc2)