Doctrine WHERE IN Problems

1
votes

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?

6
phpmysqldoctrine-ormdoctrinesilex
->setParameter(':keylist', $keylist) is the correct syntax. D2 is smart enough to handle arrays for parameters. Are you sure you are not getting all your data? You can try pasting the generated sql into your database connector. - Cerad
Yeah i have tried it and its producing different data. In fact its returning NO data - i.e. the query fails :( Maybe it too is suffering from the int/string conversion issue? - puppyFlo
If you take out the other where conditions do your get the $keylist records? Is t.key a string or integer? You don't have a database column named key do you? Because that is a reserved word and will cause select issues. When you say the query fails are you getting an actual sql error or just no records? - Cerad
You are using the doctrine connection object and not the orm entity manager. So having a column named key is a no no. D2 does not escape column names. - Cerad
I do not actually have a column named key, apologies for the confusion - i was trying to generalise the statement but see how that's made it look like an issue (i don't post here often). The column called something completely different "t.i_tariff". The only reserved word is the Value alias but with the suggested change below that still works. - puppyFlo

6 Answers

2
votes

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);
1
votes

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.

1
votes

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);
0
votes

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.

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

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);