Complement Information Retrieval with Subqueries for the Database Connector
The connector requires a mapping file to execute properly. For each mapping type, it is necessary to specify an Accessor representing the SQL query to execute.
Specifying Subqueries
To specify subqueries, you must set the type of the Accessor to query.
<Accessor type="query">
Following the Accessor definition, add an AccessorSubQueries node with all subqueries:
Note: The master key (value following SELECT) in the AccessorSubQuery node must match exactly the one returned by the server. The key you include must also have the same casing. The following error is thrown when the key could not be found:
Unable to index document : There is a formatting error in a sub query. Cannot find master key %[key].
<AccessorSubQueries>
<AccessorSubQuery name="FirstNameLastName" separator=";" behaviorOnMultiRows="join" allowDuplicates = "false">
SELECT firstName,
lastName
FROM employeelist
WHERE Email_id = %[sender]
</AccessorSubQuery>
</AccessorSubQueries>
Subquery Attributes
-
name
-
Subquery name referred to in section Fields of the mapping (see Example of a Configuration File for the Database Connector).
-
separator
-
Separator used when concatenating multiple rows.
-
behaviorOnMultiRows
-
Action to take when a subquery returns more than one row. The only supported behavior is join, which concatenates values with the provided separator.
-
allowDuplicates (optional)
-
This attribute is mainly used when your subquery returns multiple rows. If set to False, duplicates in the results are ignored in the concatenation of the results. If set to true, duplicates are present.
- singleQuoteEscapeSequence (optional) CES 7.0.5425+ (May 2013)
-
When the value of the returned field contains single quotes, these single quotes must be escaped. By default when you omit this attribute, the connector escapes the single quotes by doubling them (ex.: ''). This escaping mechanism should work in most cases. However, some database types require a different escaping sequence for single quotes. In such cases, use this attribute to specify the single quote escape sequence.
Example: For the MySQL database, the single quote escaping sequence is \'. In this case, in the AccessorSubQuery tag, include the singleQuoteEscapeSequence attribute as follows:
<AccessorSubQuery name="FirstNameLastName" separator=";" behaviorOnMultiRows="join" allowDuplicates = "false" singleQuoteEscapeSequence="\'">
Subquery Master Key
In a subquery, a master key used in the WHERE clause must respect the format %[fieldName], which corresponds to metadata acquired from the main accessor. The master key is used to make the join between the main query and subqueries.
Specifying subquery metadata for fields
The <Fields> section of the mapping file is used to specify the metadata to use for indexing.
Refer to the following example for a typical <Fields> section of a mapping file:
<Fields>
<Uri>http://www.coveo.com/Emails/details.aspx?Id=%[mid]</Uri>
<ClickableUri>http://www.coveo.com</ClickableUri>
<FileName>Message_%[mid].txt</FileName>
<Title>Message_%[mid]</Title>
<ModifiedDate>%[date]</ModifiedDate>
<Body>%[body]</Body>
<CustomFields>
<CustomField name="sysAuthor">%[sender]</CustomField>
<CustomField name="firstName">%[FirstNameLastName.firstName]</CustomField>
<CustomField name="lastName">%[FirstNameLastName.lastName]</CustomField>
</CustomFields>
</Fields>
The metadata of a subquery can be specified for a field or a custom field. The way to specify is similar to the way it is done when referring a field coming from the main accessor: %[subQueryName.fieldName]. In the above example, custom field firstName is referring subquery named FirstNameLastName and uses the firstName metadata.
Refer to the following for a complete mapping file, used in our unit tests:
<?xml version="1.0" encoding="utf-8" ?>
<ODBC>
<CommonMapping excludedItems="employeelist">
<AllowedUsers>
<AllowedUser type="Windows" allowed="true">
<Name>everyone</Name>
<Server></Server>
</AllowedUser>
</AllowedUsers>
</CommonMapping>
<Mapping type="message">
<Accessor type="query">
SELECT message.mid,
message.sender,
message.date,
message.message_id,
message.subject,
message.body,
message.folder
FROM message
WHERE DATE like '2001-04-07%'
</Accessor>
<AccessorSubQueries>
<AccessorSubQuery name="FirstNameLastName" separator=";" behaviorOnMultiRows="join">
SELECT firstName, lastName
FROM employeelist
WHERE Email_id = %[sender]
</AccessorSubQuery>
</AccessorSubQueries>
<Fields>
<Uri>http://www.coveo.com/Emails/details.aspx?Id=%[mid]</Uri>
<ClickableUri>http://www.coveo.com</ClickableUri>
<FileName>Message_%[mid].txt</FileName>
<Title>Message_%[mid]</Title>
<ModifiedDate>%[date]</ModifiedDate>
<Body>%[body]</Body>
<CustomFields>
<CustomField name="sysAuthor">%[sender]</CustomField>
<CustomField name="firstName">%[FirstNameLastName.firstName]</CustomField>
<CustomField name="lastName">%[FirstNameLastName.lastName]</CustomField>
</CustomFields>
</Fields>
<AllowedUsers>
<AllowedUser type="CustomGroup" allowed="true">
<Name>everyone</Name>
<Server></Server>
</AllowedUser>
</AllowedUsers>
</Mapping>
</ODBC>