• 3
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

How to deal with “IN” in WHERE-clause in Doctrine2

Here is the dql-query

$dql = "SELECT t Entities\Table t WHERE t.field IN (?1)";
    $q = $em->createQuery($dql)
                ->setParameter(1, '108919,108920');
    $result = $q->execute();

if i pass parameters through setParameter doctrine returns only first result, but if i put them directly into the dql-query it returns 2 results (this is correct):

$dql = "SELECT t Entities\Table t WHERE t.field1 IN (108919,108920)";

How to deal with "IN" in WHERE-clause through setParameter?

Be aware that this only works for numbered parameters, and not named parameters.

$searchParameters = array(108919, 108920);

$dql = "SELECT t Entities\Table t WHERE t.field IN (?1)";
$q = $em->createQuery($dql)
   ->setParameter(1, $searchParameters);

$result = $q->execute();
  • 9
Reply Report
      • 1
    • I just came across this question and tried it with a named parameter anyway. Seems like somewhere between this answer and now, Doctrine was changed to allow this construction for named parameters as well.
      • 2
    • I was tearing my hair out trying to figure out why I couldn't get array parameters to work - replacing named parameter with numbered saved my day :-)

The following should work fine:

$searchParameters = array(108919, 108920);

$dql = "SELECT t Entities\Table t WHERE t.field IN (?1)";
$q = $em->createQuery($dql)
   ->setParameter(1, $searchParameters);

$result = $q->execute();

You can pass in an array, without using implode() and doctrine will handle it properly (as a list of integers).

Note: if you're already working with the string '108919, 108920' -- you will need to use the explode and trim functions.

This is also mentioned here: How to use the in statement in DQL in Doctrine 2.0

  • 6
Reply Report

The following should work as expected (for an arbitrary numer of arguments to the IN clause)

$params = array(1 => 108919, 2 => 108920);
$qb = $em->createQueryBuilder();
$qb->select(array('t'))
   ->from('Entities\Table', 't')
   ->where($qb->expr()-> in('t.field', array_map(function($p) { return '?'.$p; }, array_keys($params)))
   ->setParameters($params);
$q = $qb->getQuery();
$r = $q->getResult();
  • 1
Reply Report

Solution 1 :

$dql = "SELECT t Entities\Table t WHERE t.field IN (?1, ?2)";
$q = $em->createQuery($dql)
->setParameters(array(1 =>'108919', 2 => '108920'));
$result = $q->execute();

Solution 2 (more elegant) :

$parameters = array(1 =>'108919', 2 => '108920');
$dql = 'SELECT t Entities\Table t WHERE t.field IN (?'.implode(', ?', array_keys($parameters)).')';
$q = $em->createQuery($dql)
->setParameters($parameters);
$result = $q->execute();
  • 0
Reply Report

This is working

public function searchCategory($target){

      $query = $this->getEntityManager()
                    ->createQuery("SELECT ct.id as id,ct.name as name, ct.target as target FROM LoveThatFitAdminBundle:ClothingType ct WHERE ct.target IN (:target)")
                     ->setParameter('target',$target['target']);
                     try {
                     return $query->getResult();
                } catch (\Doctrine\ORM\NoResultException $e) {
                return null;
                }

 }
  • 0
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions