Anatomy of a complex query using extensions

This is for:

Developer

You can obtain insightful contextual results using nested queries on many lines using query extensions. Coveo for Salesforce takes advantage of such complex queries to present valuable contextual information in Coveo Insight Panels.

You first need to know that in a multi-line query, the lines are processed from the first to the last. The result set from the first line is available to the next lines and so forth.

Tip
Leading practice

Use aliases to split the query into chunks that are easier to understand, and then use one or more aliases as building blocks on subsequent lines to nest queries.

A minimum knowledge of standard query extensions and of the content of your index and Salesforce fields will help you understand existing complex queries and imagine new ones.

The following example presents how a complex query can be used to present recent emails in a Coveo Insight Panel:

{{accounts=$type(name:'Account') @title="{!>Name}"}}
{{contacts=$join(fromResultSet: {{accounts}}, toResultSet: $type(name: 'Contact'), field: '@sfaccountid')}}
{{emailAddresses=$valuesOfField(field: '@sfemail', resultSet: {{contacts}})}}
$emailsExchangedWithThoseAddresses(addresses: {{emailAddresses}})
NOT @isattachment
$sort(criteria: 'datedescending')

In short, this query lists decreasingly recent email messages exchanged with people connected to the account currently selected in Salesforce.

Line 1

{{accounts=$type(name:'Account') @title="{!>Name}"}}

Line 1 creates the accounts alias that will only contain search results of type Account matching the currently selected Salesforce account.

In more detail, the standard extension $type specifies that the result set of the alias contains items of type Account. The field query @title="{!>Name}" filters only accounts for which the content of the title index field is equal to the name of the currently selected account in Salesforce.

Note

In the index, the name of a Salesforce account object is stored in the title field. In the context of Salesforce, {!>Name} returns the name of the currently selected account.

Line 2

{{contacts=$join(fromResultSet: {{accounts}}, toResultSet: $type(name: 'Contact'), field: '@sfaccountid')}}

Line 2 creates the contacts alias that will contain all of the contacts connected with the accounts from Line 1.

In more detail, the standard extension $join specifies the content of the alias by taking the alias of Line 1 as the input (fromResultSet: {{accounts}}), and returns items of type Contact (toResultSet: $type(name: 'Contact')) that have the same Salesforce account ID (sfaccountid field value) as items in the fromResultSet.

Line 3

{{emailAddresses=$valuesOfField(field: '@sfemail', resultSet: {{contacts}})}}

Line 3 creates the emailAddresses alias that will contain the list of email addresses corresponding to the list of contacts from Line 2.

In more detail, the standard extension $valuesOfField specifies the content of the alias by building a list of Salesforce email addresses (field: '@sfemail') for the contacts in the contacts alias created on Line 2.

Line 4

$emailsExchangedWithThoseAddresses(addresses: {{emailAddresses}})

Line 4 uses the $emailsExchangedWithThoseAddresses standard extension to get all of the emails exchanged with the list of email addresses from the emailAddresses alias created with Line 3.

Line 5

NOT @isattachment

Line 5 filters out the results from Line 4 that are attachments.

In more detail, the line uses a Boolean operator (NOT) with a field query @isattachment to filter on the isattachment Boolean index field.

In the context of a Coveo Insight Panel, you usually only want the messages themselves. The user can always click a result to open the email in the original email application and review attachments in this context.

Line 6

$sort(criteria: 'datedescending')

Line 6 uses the $sort standard extension to sort email messages by inverse time values.

What’s next?

Review examples of query extensions that are specific to Coveo for Salesforce.