Building SOQL Queries

Building SOQL queries with es6 template strings isn't too bad (you can even access API names via Account.FIELDS), but we can do better! This library allows you to build "typed" queries:

1. import {buildQuery} from 'ts-force'
2. Add and run the following code:

let soqlQry = buildQuery(Account, fields => {
    return {
        select: [fields.select('id')]
    }
});
console.log(soqlQry); //SELECT Id FROM Account

QUERY PLAYGROUND

The first parameter of the buildQuery function is the type for the generated SObject that we want to query on. The second, is a function that takes a FieldResolver and must return a SOQLQueryParams obj. You can use the injected FieldResolver to map the generated fields back to API names and handles relationships in the context of the root object.

WARNING: While "built queries" ensures fields and basic SOQL semantics are correct, it is still possible to generate an invalid query.

Even better, the retrieve() method makes this even easier by allowing us to pass JUST the "builder" function:

let contacts = await Contact.retrieve((fields)=>{
    return {
        select: [
            fields.select('email'),
            ...fields.parent('account').select('name', 'nameCustom')
        ],
        limit: 10
    }
});

fields.select() & fields.parent()

In the above code, note how fields is being used in various places above.

1. fields.select('email')

   • returns Email
   • Change select('email') to select('efails') and see what happens...

2. fields.parent('account').select('name', 'nameCustom')

   • returns ['Account.Name', 'Account.Name__c'].
   • Change parent('account') to parent('owner') and see what happens...
   • Note how we use es6 ... syntax to merge these values

Child SubQueries

The FieldResolver also allows us to make sub-queries on a child relationships via a method called subQuery. It's similar to building a query with retrieve, except the first parameter of subQuery is the child relationship property we want to query on. For example, we can build the following query:

SELECT Id, (SELECT Name, Phone, Email From Contacts LIMIT 10)
FROM ACCOUNT
LIMIT 5

like so:

let accountsWithContacts = await Account.retrieve((aFields) => {
    return {
        select: [
            aFields.select('id'),
            aFields.subQuery('contacts', cFields => {
                return {
                    select: cFields.select('name', 'phone', 'email'),
                    limit: 10
                }
            })
        ],
        limit: 5
    }
})
console.log(accountsWithContacts.length);

Where Clause

To filter our data, we can add a where to our SOQLQueryParams: To generate the following query:

SELECT Id
FROM Account
WHERE AnnualRevenue > 100
AND (
    Type IN ('b2b', 'customer') OR CreatedDate > 2018-11-14T12:46:01z
)
AND Id NOT IN (SELECT AccountId FROM Contact)

We can do this:

let filteredAccounts = await Account.retrieve((fields) => {
    return {
        select: [
            fields.select('id'),
        ],
        where: [
            { field: fields.select('annualRevenue'), op: '>', val: 100 },
            'AND',
            [
                { field: fields.select('type'), op: 'IN', val: ['b2b', 'customer'] },
                'OR',
                { field: fields.select('createdDate'), op: '>', val: new Date() },
            ],
            'AND',
            {
                field: fields.select('id'),
                op: 'NOT IN',
                subqry: buildQuery(Contact, cFields => {
                    return {
                        select: [cFields.select('accountId')]
                    }
                })
            }
        ]
    }
});

Notice how...

1. val is automatically parsed to the correct format based on the type of the param. You can pass a format function, if you need to override the default formatting.

2. Logical Operators can be added between conditions using 'AND' | 'OR'. 'AND' is inferred if no operator is present

3. Logic Groupings can be created by starting a new array

4. You can add subQueries by passing SOQL into subqry.

TIP: You'll notice that you can only select relationship fields on objects that you have pulled down. If you need to filter on something outside your generated models, you can always just pass a string!

For more details on building queries, see this readme.

Next: Creating Reusable Views