About Foreign Keys
CES 7.0.5388+ (April 2013)
In a system where a document contains a field that is an ID to another document, it is often needed to be able to index the referenced document, not the ID. It is also needed to be able to know when a referenced item changes so that the parent can be updated in the index on the next incremental refresh, even when the parent content did not change. A foreign key addresses these needs.
Example: In Salesforce, the account name may be available only in the Accounts documents. Only the Account ID is set on account children such as opportunities or cases. By setting an Account to Name foreign key relationship, at query time, you can query for @syssfaccountname==ACompanyName and find matching documents even when they do not have the syssfaccountname field but have the syssfaccountid field. The link is made under the hood, in a seamless manner. Also, the syssfaccountname field can be put on the results for display, if needed.
<ForeignKeys>
<ForeignKey ID="123">
<KeyField>syssfaccountid</KeyField>
<ValueField>syssfaccountname</ValueField>
<FreeTextSearch>true</FreeTextSearch>
</ForeignKey>
</ForeignKeys> Foreign keys also support sub-string matches.
Example: If an account name is "Government of Canada", the query @syssfaccountname=Canada returns this account as well as any other accounts with Canada in the account name.
The FreeTextSearch option, when set to true allows end-users to also search the content of the FreeTextSearch value fields.
Example: Again, if an account name is "Government of Canada", with the FreeTextSearch option set to true, entering Canada in the search box returns this account, even when the term Canada is only present in the foreign key value field.
The foreign key relationship can also be used by facets or ListFacetFieldValues using a look up field. You can build the facet on the syssfaccountid field that is set on all documents and have the syssfaccountname field displayed instead, even when this field is only set on a very small subset of documents.
The foreign key feature works seamlessly across two index slices so that when an account and a related opportunity are stored in different index slices, the look up works.
Note: CES 7.0.6424+ (February 2014) Foreign keys support multiple key fields mapping to a single value field.
Example: A travel website, contains the AirportName field set on Airport records as well as the DepartureAirportID and ArrivalAirportID fields set on Flight records.
When you search for @airportname=gaulle assuming to find all documents related to the Charles de Gaulle Paris Airport (which
ID is 9), the search must be:
@airportname=gaulle OR @DepartureAirportID=9 OR @ArrivalAirportID=9.
The following foreign key definition for multiple key field mapping to a single value field allows to automatically return the expected results for the @airportname=gaulle query:
<ForeignKey ID="1">
<KeyField>departureairportid</KeyField>
<ValueField>airportname</ValueField>
</ForeignKey>
<ForeignKey ID="2">
<KeyField>arrivalairportid</KeyField>
<ValueField>airportname</ValueField>
</ForeignKey>Limitations
-
Currently, the foreign key configuration must be done manually. Because, such configuration may have important advert consequences, you must contact Coveo Support for assistance to set up foreign keys.
-
Both the key and value fields must be:
-
String type
-
facet field (see Adding a Facet Field)
-
-
Wildcard matches are not supported.
Example: The query @syssfaccountname=Cana* will not work.
-
Foreign key relationships are not specific to a source or a collection. When the key and value field names are common, the foreign key relation applies to any source in which these fields are present.
-
For very high cardinality fields (field having more than one million distinct values), the initial start of the index can take a minute or more. New values are added through the secondary commit, so in the worst case scenario, an ID gets its value mapped about two 2 minutes after indexing is done.