Doctrine orWhere giving weird results

Question

I have a FaqEntry class. This object has a property categories, which is an array. Its possible values are "CAT1", "CAT2", "CAT3", "CAT4". I have a FaqEntryRepository class with a method that returns the querybuilder for most general usecases. I can give it an array of categories, and the FaqEntries will be filtered on the given categories.

The following is the piece of code I wanted to use to filter out all categories. There are NO other where(), andWhere() or orWhere() clauses, only an orderBy() and a setMaxResults().

if ($categories) {
    foreach ($categories as $category) {
        if (CategoryEnum::isValid($category)) {
            $queryBuilder
                ->orWhere('fe.categories LIKE :category')
                ->setParameter('category', '%' . $category . '%')
            ;
        }
    }
}

However, when I get the results, it only gets those where "CAT4" is set. So for example:

when all categories are selected, only the entries having "CAT4" are returned.
when filtering only on "CAT1", it returns only entries where "CAT1" AND "CAT4" are set.

This is a way to solve it, but I like it less because now I'm forging the SQL myself:

if ($categories) {
    $whereString = '';
    foreach ($categories as $category) {
        if (CategoryEnum::isValid($category)) {
            $whereString .= 'or fe.categories LIKE \'%' . $category . '%\'';             }
        }
    $whereString = substr($whereString, 3);
    $queryBuilder->andWhere($whereString);
}

Does anybody see what I am doing wrong?

score 0 · Accepted Answer · answered Jun 09 '19 at 12:55

I'm still not sure as to WHY it is happening, but here is a better solution using the Doctrine querybuilder. I found the solution here: https://stackoverflow.com/a/48906639/2619074

$orStatements = $queryBuilder->expr()->orX();
foreach ($categories as $category) {
    $orStatements->add(
        $queryBuilder->expr()->like(
            'fe.categories',
             $queryBuilder->expr()->literal('%' . $category . '%')
        )
    );
}
$queryBuilder->andWhere($orStatements);

Doctrine orWhere giving weird results

1 Answers1