DEV Community

loading...
Cover image for Doctrine DBAL and the LIKE operator

Doctrine DBAL and the LIKE operator

_garybell profile image Gary Bell Originally published at garybell.co.uk ・2 min read

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:
Doctrine documentation for the DBAL like operator.
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)) : '');
}
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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

Discussion

pic
Editor guide