Value and Column Conversions (DBAL)

Conversions for Doctrine DBAL are similar to the DataTransformers used for transforming user-input to a model data-format. Except that the transformation happens in a single direction and are applied SQL.

So why are are they useful? The power of relational databases is storing complex data structures, and allow to retrieve these records into a transformed and combined result.

For example the “birthday” field type accepts both an actual (birth)date or an age value like “9”. But you’re not going to store the actual age, as this would require constant updating. Instead you calculate the age by the date that is stored in the database (you transform the data). This transformation process is called a conversion.

Note

Some values don’t have to be converted if the Doctrine DBAL Type is already able to process the value as-is.

For example a DateTime object can be safely used if the mapping-type is properly configured with “date” or “datetime” as column type.

There are two types of converters. Which can be combined together in one single class. Conversions can happen at the column and/or value. So you can really utilize the power of your SQL queries.

Note

Unlike DataTransformers you`re limited to one converter per search field. So setting an new conversion will overwrite previous one.

Conversions are registered by setting the conversion object on the SearchField by using the configureOptions method of the field type.

Using:

public function configureOptions(OptionsResolver $resolver)
{
    $resolver->setDefaults([
        'doctrine_dbal_conversion' => new MyConversionClass(),
    ]);
}

Tip

The doctrine_dbal_conversion option also accepts a Closure object for lazy initializing, the closure is executed when initializing the condition-generator.
public function configureOptions(OptionsResolver $resolver)
{
    $resolver->setDefaults([
        'doctrine_dbal_conversion' => function () {
            return new MyConversionClass())
        },
    ]);
}

Before you get started, it’s important to know the following about conversions:

  1. Conversion should be stateless, meaning they don’t remember anything about there operations. This is because the calling order of conversion methods is not predictable and conversions are only executed during the generation process, so using a cached result does not execute them.
  2. Each method receives a ConversionHints object which provides access to the used database connection, SearchField configuration, column, and helper methods for using parameter-placeholders and getting the actual value that is currently being processed (in the column context).
  3. The $options array provides the options of the SearchField.

See existing conversions for a more detailed example. https://github.com/rollerworks/search/tree/master/lib/Doctrine/Dbal/Extension/Conversion

Tip

To use a conversion for an existing FieldType use a FieldTypeExtension.

ColumnConversion

A ColumnConversion transforms the provided column to an SQL part like a sub-query or user-defined functional call.

The ColumnConversion requires the implementation of one method that must return the column or anything that can be used as a replacement.

This example shows how to get the age of a person in years from their date of birth. In short, the u.birthdate column is converted to an actual age in years:

namespace Acme\User\Search\Dbal\Conversion;

use Rollerworks\Component\Search\Doctrine\Dbal\ConversionHints;
use Rollerworks\Component\Search\Doctrine\Dbal\ColumnConversion;

class AgeConversion implements ColumnConversion
{
    public function convertColumn(string $column, array $options, ConversionHints $hints): string
    {
        if ('postgresql' === $hints->connection->getDatabasePlatform()->getName()) {
            return "TO_CHAR('YYYY', AGE($column))";
        } else {
            // Return unconverted
            return $fieldName;
        }
    }
}

The u.birthdate column reference is wrapped inside two function calls, the first function converts the date to an Interval and then the second function extracts the years of the Interval and then casts the extracted years to a integer. Now you easily search for users with a certain age.

Value Specific Conversion

Most column versions are singular, but in some cases you might need to apply a different conversion depending on the value that is being processed at the moment.

For the Rollerworks\\Component\\Search\\Extension\\Doctrine\\Dbal\\Conversion\\DateIntervalConversion you need to know whether the value needs to be subtracted or added, depending on the processing context.

For the MoneyValueConversion you need to know the unit (precision) the Currency, but don’t have access to the database value.

  • The $context property of the ConversionHints provides the current processing-context, see the CONTEXT_ constants of the ConversionHints for possible options;
  • The $originalValue holds the actual value-holder that is currently being processed, depending on the context this either a Range, Compare value-holder object or mixed type value for CONTEXT_SIMPLE_VALUE.

When you only need the value (regardless of the context) use the getProcessingValue() method.

ValueConversion

A ValueConversion converts the provided value to an SQL part like a sub-query or user-defined functional call.

The ValueConversion requires the implementation of one method that must return the value as SQL query-fragment.

Warning

The convertValue method is required to return an SQL query-fragment that will be applied as-is!

Avoid embedding the values directly, use the createParamReferenceFor on the $hints instead.

Failing to do this can easily lead to a category of security holes called SQL injection, where a third party can modify the executed SQL and even execute their own queries through clever exploiting of the security hole!

The only only save way to embed a value is with:

$hints->createParamReferenceFor($value); // will return param-name `:search_x` where x an incremented number

Don’t try to replace the escaping with your own implementation as this may not provide a full protection against SQL injections.

One of these values is Spatial data which requires a special type of input. The input must be provided using an SQL function, and there for this can not be done with only PHP.

This example describes how to implement a MySQL specific column type called Point.

The point class:

namespace Acme\Geo;

class Point
{
    private $latitude;
    private $longitude;

    public function __construct(float $latitude, float $longitude)
    {
        $this->latitude  = $latitude;
        $this->longitude = $longitude;
    }

    public function getLatitude(): float
    {
        return $this->latitude;
    }

    public function getLongitude(): float
    {
        return $this->longitude;
    }
}

And the GeoConversion class:

namespace Acme\Geo\Search\Dbal\Conversion;

use Acme\Geo\Point;
use Doctrine\DBAL\Types\Type;
use Rollerworks\Component\Search\Doctrine\Dbal\ConversionHints;
use Rollerworks\Component\Search\Doctrine\Dbal\SqlValueConversionInterface;

class GeoConversion implements ValueConversion
{
    public function convertValue($input, array $options, ConversionHints $hints): string
    {
        if ($value instanceof Point) {
            // The second argument is a Doctrine DBAL type used for the binding-type and
            // any SQL specific transformation (otherwise the value is marked as text and used as-is).
            $long = $hints->createParamReferenceFor($input->getLongitude(), Type::getType('decimal'));
            $lat = $hints->createParamReferenceFor($input->getLatitude(), Type::getType('decimal'));

            $value = sprintf('POINT(%s, %s)', $long, $lat);
        }

        return $value;
    }
}

Note

Alternatively you can choose to create a custom Type for Doctrine DBAL. See Custom Mapping Types in the Doctrine DBAL manual for more information.

But doing this may cause issues with certain database vendors as the generator doesn’t now the value is wrapped inside a function and there for is unable to adjust the generation process for better interoperability.

Testing Conversions

To test if the conversions work as expected your can compare the generated, SQL with what your expecting, however there’s no promise that the SQL structure will remain the same for the future releases.

The only way to ensure your conversions work is to run it against an actual database with existing records.