Searching with Doctrine DBAL

This section of the manual explains how to install and configure the Doctrine DBAL extension. The code samples assume you already have Doctrine DBAL set-up, and know how to write SQL queries.

Following the installation instructions install the extension by running:

$ php composer.phar require rollerworks/search-doctrine-dbal

Enabling Integration

And enable the DoctrineDbalExtension for the SearchFactoryBuilder. To adds extra options for registering Value and Column Conversions (DBAL) and ensuring core types work properly.

use Rollerworks\Component\Search\Searches;
use Rollerworks\Component\Search\Extension\Doctrine\Dbal\DoctrineDbalExtension;
use Rollerworks\Component\Search\Extension\Core\CoreExtension;

$searchFactory = new Searches::createSearchFactoryBuilder()
    ->addExtension(new DoctrineDbalExtension())

    // ...
    ->getSearchFactory();

That’s it, you can use RollerworksSearch with Doctrine DBAL support enabled. Continue reading to learn how the query the database with a SearchCondition.

Querying the database

As you already know RollerworksSearch uses a SearchFactory for bootstrapping the search system. This factory however doesn’t know about integration extensions.

To Query a database with the Doctrine DBAL extension, you use the DoctrineDbalFactory.

Note

The DoctrineDbalFactory works next to the SearchFactory. It’s not a replacement for the SearchFactory.

You use the SearchFactory first, and the the DoctrineDbalFactory second.

The DoctrineDbalFactory class provides an entry point for creating SqlConditionGenerator and CachedConditionGenerator object instances.

Note

Both classes implement the ConditionGenerator, a ConditionGenerator is not to be confused a SearchCondition.

A ConditionGenerator generates an SQL where-clause ‘condition’.

Initiating the DoctrineDbalFactory is as simple as:

use Rollerworks\Component\Search\Doctrine\Dbal\DoctrineDbalFactory;

// \Psr\SimpleCache\CacheInterface | null
$cache = ...;

$doctrineDbalFactory = new DoctrineDbalFactory($cache);

The $cache must a PSR-16 (SimpleCache) implementation, or can it can be omitted to disable the caching of generated conditions.

See also: Caching

Using the ConditionGenerator

A ConditionGenerator generates an SQL Where-clause for a relational database like PostgreSQL, MySQL, MSSQL, or Oracle OCI.

Caution

A ConditionGenerator is configured with a Doctrine DBAL QueryBuilder and SearchCondition. So reusing a ConditionGenerator is not possible.

Secondly, the generated query is only valid for the give Database driver. Meaning that when you generated a query with the PostgreSQL database driver this query will not work on MySQL.

First create a ConditionGenerator:

// ...

// Doctrine\DBAL\Query\QueryBuilder object
$qb = ...;

// Rollerworks\Component\Search\SearchCondition object
$searchCondition = ...;

$conditionGenerator = $doctrineDbalFactory->createConditionGenerator($qb, $searchCondition);

Before the condition can be generated, the ConditionGenerator needs to know how your fields are mapped to which columns and table/schema. To configure this field-to-column mapping, use the setField method on the ConditionGenerator:

/**
 * Set the search field to database table-column mapping configuration.
 *
 * To map a field to more then one column use `field-name#mapping-name`
 * for the $fieldName argument. The `field-name` is the field name as registered
 * in the FieldSet, `mapping-name` allows to configure a (secondary) mapping for a field.
 *
 * Caution: A field can only have multiple mappings or one, omitting `#` will remove
 * any existing mappings for that field. Registering the field without `#` first and then
 * setting multiple mappings for that field will reset the single mapping.
 *
 * Tip: The `mapping-name` doesn't have to be same as $column, but using a clear name
 * will help with trouble shooting.
 *
 * @param string $fieldName Name of the search field as registered in the FieldSet or
 *                          `field-name#mapping-name` to configure a secondary mapping
 * @param string $column    Database table column-name
 * @param string $alias     Table alias as used in the query "u" for `FROM users AS u`
 * @param string $type      Doctrine DBAL supported type, eg. string (not text)
 *
 * @throws UnknownFieldException  When the field is not registered in the fieldset
 * @throws BadMethodCallException When the where-clause is already generated
 *
 * @return $this
 */
public function setField(string $fieldName, string $column, string $alias = null, string $type = 'string');

The first parameter is the search field-name as registered in the provided FieldSet (with optionally a mapping-name to allow mapping a field to multiple columns).

Order fields must be prefixed with @ as their actual name, either @id.

Followed by the database column-name (without any quoting), the table alias that corresponds with the table alias in the Query, and last the dbal-type (as provided by Doctrine DBAL).

Note

The db-type must correspond to a Doctrine DBAL supported Type. So instead of using varchar you use string.

See Doctrine DBAL Types for a complete list of types and options.

If you have a type which requires the setting of options you may need to use a ValueConversion instead.

Caution

Only SearchFields in the FieldSet that have a column-mapping configured will be processed. All other fields are ignored.

If you try to configure a column-mapping for a unregistered SearchField the ConditionGenerator will fail with an exception.

After configuring you are ready to generate the query condition.

Generating the Condition

// Doctrine\DBAL\Query\QueryBuilder object
$qb = $connection->createQueryBuilder();

// ...

$qb
    ->select('u.name AS user_name', 'u.id AS user_id')
    ->from('users', 'u')
    ->leftJoin('u', 'contacts', 'c', 'c.user_id = u.id')
;

$conditionGenerator = $doctrineDbalFactory->createConditionGenerator($qb, $searchCondition);

// Set the field to column mapping
$conditionGenerator->setField('user_id', 'u', 'id', 'integer');
$conditionGenerator->setField('user_name', 'u', 'name', 'string');
$conditionGenerator->setField('contact_name', 'c', 'name', 'string');

// Apply the condition (with ordering, if any) to the QueryBuilder
$conditionGenerator->apply();

// Get all the records
// See http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#data-retrieval
$result = $qb->execute();

Tip

To prevent certain users from getting results they are not allowed to see you can add an addition and-condition the QueryBuilder or you can use a pre_condition for better portability.

Mapping a field to multiple columns

Instead of searching in a single column it’s possible to search in multiple columns for the same field. In practice this will work the same as using the same values for other fields.

In the example below field name will search in both the user’s first and last name columns (as OR case). And it’s still possible to search with only the first and/or last name.

$conditionGenerator = $doctrineDbalFactory->createConditionGenerator($connection, $searchCondition);
$conditionGenerator->setField('name#first', 'first', 'u', 'string');
$conditionGenerator->setField('name#last', 'last', 'u', 'string');
$conditionGenerator->setField('first-name', 'first', 'u', 'string');
$conditionGenerator->setField('last-name', 'last', 'u', 'string');

Caching the Where-clause

Generating a Where-clause may require quite some time and system resources, which is why it’s recommended to cache the generated query for future usage.

Fortunately the factory allows to create a CachedConditionGenerator which can handle caching of the ConditionGenerator for you.

Plus, usage is no different then using the SqlConditionGenerator, the CachedConditionGenerator decorates the SqlConditionGenerator and can be configured very similar:

// ...

// The third parameter is the cache lifetime in as supported by PSR-16
$cacheConditionGenerator = $doctrineDbalFactory->createCachedConditionGenerator($qb, $conditionGenerator, null);
$cacheConditionGenerator->setField('first-name', 'first', 'u', 'string');
$cacheConditionGenerator->setField('last-name', 'last', 'u', 'string');

// Apply the condition (with ordering, if any) to the QueryBuilder
$cacheConditionGenerator->apply();

The cache-key is a hashed (sha256) combination of the SearchCondition (root ValuesGroup and FieldSet set-name) and configured field mappings.

Note

Changes in the FieldSet’s Fields configuration are not automatically detected. Keep your cache life-time short and purge existing entries when changing your FieldSet configurations.

Next Steps

Now that you have completed the basic installation and configuration, and know how to query the database for results. You are ready to learn about more advanced features and usages of this extension.

You may have noticed the word “conversions”, now it’s time learn more about this! Value and Column Conversions (DBAL).

And if you get stuck with querying, there is a Troubleshooter to help you. Good luck.