Doctrine WHERE IN Problems

Question

Before I begin, I believe I have tried everything from this previous post: How to use WHERE IN with Doctrine 2

So I have a Silex application connected to a MySQL DB using Doctrine via composer (doctrine/dbal 2.2.*)

The query builder I am trying to run is this:

$qb = $this->db->createQueryBuilder();

$stmt = $qb->select('DAY(datefield1) x, COUNT(*) value')
    ->from('table1', 's')
    ->join('s', 'table2', 't', 't.key=s.key')
    ->where('MONTH(datefield1) = :billMonth')
    ->andWhere('YEAR(datefield1) = :billYear')
    ->andWhere('t.key IN (:keylist)')
    ->groupBy('x')
    ->orderBy('x', 'asc')
    ->setParameter(':billMonth', $month)
    ->setParameter(':billYear', $year)
    ->setParameter(':keylist', implode(",", $keylist))
    ->execute();

return $stmt->fetchAll(\PDO::FETCH_ASSOC);

The parameters are (month=8)(year=2014)(keylist=array(1,2,3,4))

The query does not fail but it curiously doesn't contain all the data that it should.

I have tried ->setParameter(':keylist', $keylist) to use the raw array, and this didn't work.

I have tried this kind of syntax too:

$qb->add('where', $qb->expr()->in('r.winner', array('?1')));

However that threw up an error because the in method wasn't available in expression builder class.

Please will someone cast an eye over this and save me from having to hardcode my SQL?

Answer 1

OK seeing as this old thread has seen some action since I last looked I wanted to confirm that the issue is long resolved - a third parameter in setParameter allows you to inform Doctrine how to handle the array:

    $qb = $this->db->createQueryBuilder();

    $stmt = $qb
        ->select('*')
        ->from(self::DB_TABLE, 'x')
        ->where('x.service IN (:services)')
        ->orderBy('x.session_end', 'DESC')
        ->addOrderBy('x.direction', 'DESC')
        ->setParameter(':services', $services, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY)
        ->setFirstResult($offset)
        ->setMaxResults($count)
        ->execute();

    $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

Answer 2

DB placeholders/parameters are for single values. You're passing in a monolithic string 1,2,3,4 due to calling implode() on the array. Given:

WHERE t.key IN (:keylist)

then this query will be executed as the equivalent of

WHERE t.key IN ('1,2,3,4')
                ^-------^---note the quotes

Since it's a string, and only one single string in the IN clause, it's the functional equivalent of

WHERE t.key = '1,2,3,4'

and not the

WHERE (t.key = 1 OR t.key = 2 OR ....)

you want it to be. Either set up multiple parameters, one for each value in your array, or embed your string in the query directly

->andWhere('t.key IN (' . implode(',', $keylist) . ')')

which of course opens you up to sql injection attack vulnerabilities.

Answer 3

Correct way to handle such IN clause is as simple as it can be:

$qb = $this->db->createQueryBuilder();

$stmt = $qb->(...)
    ->andWhere('t.key IN (:keylist)')
    ->setParameter(':keylist', $keylist)
    ->getQuery()
    ->getArrayResult();

I have used this dozen times and it works - doctrine is smart enough to handle your $keylist being array.

Other thing is that I don't know why you're using fetchAll method which is reduntant here - execute() is just enough. Maybe this is a root of your problem.

My suggestion: try to fire action in dev mode (app_dev.php) and check your app/logs/dev.log - you will find all sql queries performed. Verify that database returns data which you are expecting from Doctrine.

Answer 4

If you build the sql query yourself you can use DBAL's PARAM_INT_ARRAY type:

use Doctrine\DBAL\Connection as DB;

$db->executeQuery('SELECT DAY(datefield1) x, COUNT(*) value
FROM table1 s
JOIN table2 t ON t.key=s.key
WHERE MONTH(datefield1) = :billMonth
    AND YEAR(datefield1) = :billYear
    AND t.key IN (:keylist)
GROUP BY x
ORDER BY x ASC',
array(':billMonth' => $month, ':billYear' => $year, ':keylist' => $keylist),
array(':billMonth' => \PDO::PARAM_INT, ':billYear' => \PDO::PARAM_INT, ':keylist' => DB::PARAM_INT_ARRAY
)->fetchAll(\PDO::FETCH_ASSOC);

Answer 5

    ->add('where', $qb->expr()->andX(                    
                        $qb->expr()->in('r.winner', ':winner')
                    ))
                ->setParameters(array(
                        "winner" => $winners,
                        "billMonth", $month,
                     // add all params here
                    ));

Answer 6

You should use the in expression via the querybuilder like below, and change how you set the parameter:

->andWhere($qb->expr()->in('t.key', ':keylist'))

The complete code:

$qb = $this->db->createQueryBuilder();
$stmt = $qb->select('DAY(datefield1) x, COUNT(*) value')
    ->from('table1', 's')
    ->join('s', 'table2', 't', 't.key=s.key')
    ->where('MONTH(datefield1) = :billMonth')
    ->andWhere('YEAR(datefield1) = :billYear')
    ->andWhere('t.key')
    ->andWhere($qb->expr()->in('t.key', ':keylist'))
    ->groupBy('x')
    ->orderBy('x', 'asc')
    ->setParameter(':billMonth', $month)
    ->setParameter(':billYear', $year)
    ->setParameter(':keylist', $keylist)
    ->execute();

return $stmt->fetchAll(\PDO::FETCH_ASSOC);