Community

Product DocsMenu

Coveo Platform 7.0 >
Administrator Help > Connectors > Database Connector > Complement Information Retrieval with Subqueries

Complement Information Retrieval with Subqueries for the Database Connector

The Database connector acquires information about each indexed document through a query performed against a database. For each query, it is possible to associate one or more subqueries to be executed and used to complement information.

Example: You can run a main query, and for each row, run a subquery that crawls more/different information. All the results of a single row from the main query, along with everything from the subquery, are merged into a single document.

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>
People who viewed this topic also viewed