Doctrine DQL (Symfony2) - WHERE concatenated field is IN params

Question

I need to check if multiple items exists in a table. The problem is that I have to check strings in two fields concatenated togeher like if I would like to check first and last names together, but store them separately. I'm new to doctrine, and after an extensive research in the topic, I've tried two ways:

$params = array('two words', 'another here');

// writing the DQL
$query = $this->getEntityManager()
    ->createQuery("
        SELECT foo.f1, foo.f2
        FROM MyCompanyMyBundle:MyEntity foo
        WHERE CONCAT(CONCAT(foo.f1, ' '), foo.f2) IN (:params)
    ")->setParameter('params', implode(', ', $params));

NOTE: Double CONCAT is required becouse CONCAT_WS is not supported by Doctrine.

This should work (imo) but :params is replaced by one string ( 'two words, another here' ), not a series of strings separated by a comma. (I Tried palcing individual apostrophes around the elements in $params but that doesn't work either, it gets escaped by Docrine ( '\'two words\', \'another here\'') ).

// trying the query builder

$qb = $this->createQueryBuilder('foo');
$query = $qb
    ->select(array('foo.f1', 'foo.f2' ))
    ->where(
        $qb->expr()->in(
            $qb->expr()->concat( $qb->expr()->concat('foo.f1', ' '), 'foo.f2'),
            ':params'
        )
    )
    ->setParameter('params', implode(', ', $params))
    ->getQuery();

I guess I'm not even close with the query builder, so if that is the way to go I appreciate any help!

So my goal is to return the rows where the two columns values are correct together.

Which is the way to go, and what am I doing wrong?

Answer 1

Looks like the issue is the way you're using setParameter() with the IN(...) clause - you should pass an array, rather than imploding -

$params = array('two words', 'another here');

// writing the DQL
$query = $this->getEntityManager()
    ->createQuery("
        SELECT foo.f1, foo.f2
        FROM MyCompanyMyBundle:MyEntity foo
        WHERE CONCAT(CONCAT(foo.f1, ' '), foo.f2) IN (:params)
    ")->setParameter('params', $params);