Doctrine array of values in nested like clause

Question

I'm trying to update a Doctrine query to accept an array of values to search on a field. I need them to be case insensitive matches so I'm using like clauses, otherwise I could just use in which would make things much simpler. What I want to end up with is a query like like:

"SELECT * FROM tableName t WHERE (t.option LIKE "option1" OR t.option LIKE "option2" OR t.option LIKE "option3");

So I tried the following:

function getItems( $options = array() ) {

    $qb = $this->_em->createQueryBuilder();
    $qb->from('tableName', 't')
        ->select('t');

        $conditions = $qb->expr()->orX();
        foreach ($options as $option) {
            $conditions->add($qb->expr()->like('option', $option));
        }
        $qb->andWhere($conditions);
    }

    return $qb->getQuery()->getArrayResult();
}

getItems( array('option1','option2','option3') );

But the values don't get escaped so you end up with a query like the below which throws an error:

SELECT * FROM tableName t WHERE (t.option LIKE option1 OR t.option LIKE option2 OR t.option LIKE option3)

This is normally where you would use named parameters like:

$qb->expr()->like('option', ':option');
$qb->setParameter( 'option', $option );

But you can't set the same parameter name multiple times with an array of values. I'm probably missing something obvious, bit of a novice with doctrine. Any ideas?

First, you can use ->where(' A LIKE "B" '), second, you can escape your $option in the foreach before passing to like(). — Sergio Ivanuzzo
– Sergio Ivanuzzo, Commented Nov 20, 2015 at 7:12

Spas Bobchev · Accepted Answer · 2015-11-20 09:19:07Z

1

You should be using the literal expression:

$conditions->add($qb->expr()->like('option', $qb->expr()->literal($option)));

answered Nov 20, 2015 at 9:19

Spas Bobchev

10411 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Doctrine array of values in nested like clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related