Can I achieve a LIKE condition in a WHERE IN ARRAY query in doctrine?

arrays, doctrine-orm, php, sql-like, symfony

Solution

I have achieved something similar by doing:

foreach($emailEndings as $index => $ending) {
    $qb->orWhere("u.email LIKE :email$index");
    $qb->setParameter("email$index", $ending);
}

The index is important, as otherwise only the last entry of the array is selected for.

Be aware that if you have multiple where clauses you will get into trouble due to the `orWhere` clause. You cannot change that into an `andWhere`, as that would result in an empty resultset. In that use case, you will need to group the conditions in an `Orx` for an `andWhere` like this:

/**
 * @param QueryBuilder $qb
 */
protected function addInternalFilter(QueryBuilder $qb)
{
    $conditions = [];
    foreach ($this->emailEndings as $index => $ending) {
        $conditions[] = "u.email LIKE :string$index";
        $qb->setParameter("string$index", $ending);
    }

    if (empty($conditions)) {
        throw new \LogicException('Conditions are empty.');
    }

    $qb->andWhere(new Orx($conditions));
}

Problem

I am using Doctrine QueryBuilder to create a query to select a bunch of users by their email endings. I have an array of the e-mail endings, e.g. `['@someservice.com', '@anotherservice.com' ]`. I know I can select for string in an array via `WHERE IN`: ``` $qb= $this ->createQueryBuilder('u') ->orderBy('u.id', 'asc') ->where('u.email IN (:emails)') ->setParameter('emails', [ '@someservice.com', '@anotherservice.com' ]); ``` Yet this query for exact occurrences of the string and hence the query will of course return an empty resultset. That's why I want to do a LIKE search on the array, yet doing something along the lines of: ``` $qb= $this ->createQueryBuilder('u') ->orderBy('u.id', 'asc') ->where('u.email LIKE IN (:emails)') ->setParameter('emails', [ '%@someservice.com', '%@anotherservice.com' ]); ``` which unfortunately fails. Is there some syntactic sugar for doing a query like that or do I have to make query with a bunch of `orHaving` calls?

Original source