Doctrine DBAL and the LIKE operator

Doctrine is a great abstraction layer, and I've resolved to use it more and more through my projects to keep things simple and maintainable. The problem is, the documentation isn't always great for it. Take the below image as an example of the documentation for the building a "LIKE" clause for a query:

The above makes it look like binding parameter 2 would automatically set the relevant wildcards round the parameter, making things simple. If you try to do it this way, the results don't come up as expected. Looking at the ExpressionBuilder code outlines why this doesn't work.

The 'like' comparison builder code does the following:

/**
 * Creates a LIKE() comparison expression with the given arguments.
 *
 * @param string $x Field in string format to be inspected by LIKE() comparison.
 * @param mixed  $y Argument to be used in LIKE() comparison.
 */
public function like(string $x, $y/*, ?string $escapeChar = null */) : string
{
    return $this->comparison($x, 'LIKE', $y) .
        (func_num_args() >= 3 ? sprintf(' ESCAPE %s', func_get_arg(2)) : '');
}

The code is there from either a past escape character, or for future use, but this simply hands off the comparison building to the comparison() function of the class.

/**
 * Creates a comparison expression.
 *
 * @param mixed  $x        The left expression.
 * @param string $operator One of the ExpressionBuilder::* constants.
 * @param mixed  $y        The right expression.
 */
public function comparison($x, string $operator, $y) : string
{
    return $x . ' ' . $operator . ' ' . $y;
}

As shown above, this is a simple string concatenation, meaning nothing clever actually takes place as part of the LIKE comparison builder. Therefore, to get the LIKE to actually work, you need to bind the wildcards as part of the parameter substitution:

$result = $queryBuilder->select('*')
    ->from($this->getTable())
    ->where(
        $queryBuilder->expr()->like('name', ':name')
    )
    ->orderBy('name', 'asc')
    ->setParameter(':name', '%' . $name . '%')
    ->execute();

Arguably this provides a more flexible solution rather than needing to create methods for startsWith() which would do the following for the code above:

$result = $queryBuilder->select('*')
    ->from($this->getTable())
    ->where(
        $queryBuilder->expr()->like('name', ':name')
    )
    ->orderBy('name', 'asc')
    ->setParameter(':name', $name . '%')
    ->execute();

And then also needing an endsWith() function for creating the following:

$result = $queryBuilder->select('*')
    ->from($this->getTable())
    ->where(
        $queryBuilder->expr()->like('name', ':name')
    )
    ->orderBy('name', 'asc')
    ->setParameter(':name', '%' . $name)
    ->execute();

If there were functions for startsWith() and endsWith(), then there would logically need to be the inverse functions of notStartsWith() and notEndsWith(). These are essentially duplicates of the like() and notLike() functions, but with very specific customisation. They may increase the readability of the code, but don't offer much more than the existing functions.

The take away lesson from this is that you need to add the wildcard characters around the variable you are substituting to get the result you desire.

This post was originally posted on my blog at https://www.garybell.co.uk/doctrine-dbal-and-the-like-operator/ on 18th November 2019

Header photo by Fotis Fotopoulos on Unsplash