Anatomy of a Complex Query Using Extensions

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

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.

Use aliases to split the query in chunks that are easier to understand, and then use one or more aliases as building blocks on subsequent lines to nest queries (see Using Aliases).

A minimum knowledge of standard query extensions (see 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') @systitle="{!>Name}"}}
{{contacts=$join(fromResultSet: {{accounts}}, toResultSet: $type(name: 'Contact'), field: '@sfaccountid')}}
{{emailAddresses=$valuesOfField(field: '@sfemail', resultSet: {{contacts}})}}
$emailsExchangedWithThoseAddresses(addresses: {{emailAddresses}})
NOT @sysisattachment
$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') @systitle="{!>Name}"}}

Line 1 creates the accounts alias that will only contain search results of type Account matching the currently selected Salesforce account. In more details, the standard extension $type specifies that the result set of the alias contains items of type Account. The field query @systitle="{!>Name}" filters only accounts for which the content of the systitle index field is equal to the name of the currently selected account in Salesforce.

In the index, the name of a Salesforce account object is stored in the systitle 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 the contacts connected with the accounts from Line 1.

In more details, 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 details, 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 emails exchanged with the list of email addresses from the emailAddresses alias created with Line 3.

Line 5

NOT @sysisattachment

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

In more details, the line uses a Boolean operator (NOT) with a field query @sysisattachment to filter on the sysisattachment 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 more easily review attachments in this context.

Line 6

$sort(criteria: 'datedescending')

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

What’s Next?

Review examples specific to Coveo for Salesforce (see Query Extensions).