How to Use Where in with Doctrine 2

How to use WHERE IN with Doctrine 2

In researching this issue, I found something that will be important to anyone running into this same issue and looking for a solution.

From the original post, the following line of code:

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

Wrapping the named parameter as an array causes the bound parameter number issue. By removing it from its array wrapping:

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

This issue should be fixed. This might have been a problem in previous versions of Doctrine, but it is fixed in the most recent versions of 2.0.

Doctrine 2 WHERE IN clause using a collection of entities

A pull request I made about this has been merged into Doctrine ORM 2.5, so you can simply do this now:

$q = $this->createQueryBuilder('v')
->select('v')
->andWhere('v.workingHours IN (:workingHours)')
->setParameter('workingHours', $workingHours);
;

The latest version of Doctrine now allows collection parameters and will automatically make use of the primary key of each of the collection entries.

How to use andWhere and orWhere in Doctrine?

$q->where("a = 1")
->andWhere("b = 1 OR b = 2")
->andWhere("c = 2 OR c = 2")
;

Doing a WHERE .. IN subquery in Doctrine 2

This is how I would try it:

/** @var Doctrine\ORM\EntityManager $em */
$expr = $em->getExpressionBuilder();
$em->createQueryBuilder()
->select(array('DISTINCT i.id', 'i.name', 'o.name'))
->from('Item', 'i')
->join('i.order', 'o')
->where(
$expr->in(
'o.id',
$em->createQueryBuilder()
->select('o2.id')
->from('Order', 'o2')
->join('Item',
'i2',
\Doctrine\ORM\Query\Expr\Join::WITH,
$expr->andX(
$expr->eq('i2.order', 'o2'),
$expr->eq('i2.id', '?1')
)
)
->getDQL()
)
)
->andWhere($expr->neq('i.id', '?2'))
->orderBy('o.orderdate', 'DESC')
->setParameter(1, 5)
->setParameter(2, 5)
;

I didn't test this of course, and made some assumptions about your models. Possible problems:

  • Limit: this has been somewhat of a problem in Doctrine 2, it seems query builder is not very good at accepting limits. Do take a look here, here and here.
  • The IN clause is usually used with an array, but I think it will work with a subquery.
  • You probably can use the same parameter ?1, instead of two parameters (because they're the same value), but I'm not sure.

Concluding, this may not work first time, but will surely put you on the right track. Do tell us the final 100% correct answer afterwards.

Doctrine multiple where condition

For first one change your where clause like,

->where('e.id IN (:ids)') 
->setParameter('ids', $ids)

Where $ids = array('10','100','');

And to use and condition for your second query it should be something like,

$qry = $this->manager()->create()
->select('e')
->from($this->entity, 'e')
->where('e.source_id = :id')
->andWhere('source_name=?', 'test')
->andWhere('source_val=?', '30')

Doctrine 2 - How to use discriminator column in where clause

I think that you should use INSTANCE OF

Complex WHERE clauses using the PHP Doctrine ORM

From my experience, each complex where function is grouped within parenthesis (I'm using Doctrine 1.2.1).

$q->where('name = ?', 'ABC')
->andWhere('category1 = ? OR category2 = ? OR category3 = ?', array('X', 'X', 'X'))
->andWhere('price < ?', 10)

produces the following SQL:

WHERE name = 'ABC' 
AND (category1 = 'X' OR category2 = 'X' OR category3 = 'X')
AND price < 10

Doctrine 2 date in where-clause (querybuilder)

That's how I would build the where clause:

    //CONDITION 1 -> STATE = 1
$state = $qb->expr()->eq( 'state', ':state' );

//CONDITION 2 -> startdate <= CURRENT_DATE AND enddate >= CURRENT_DATE
$betweenDates = $qb->expr()->andX(
$qb->expr()->lte("u.startdate", ":currentDate"),
$qb->expr()->gte("u.enddate", ":currentDate")
);

//CONDITION 3 -> startdate == NULL
$startDateNull = $qb->expr()->isNull( 'startdate' );

//CONDITION 4 -> enddate == NULL
$endDateNull = $qb->expr()->isNull( 'enddate' );

//CONDITION 5 -> <CONDITION 2> OR <CONDITION 3> OR <CONDITION 4>
$dates = $qb->expr()->orX( $betweenDates, $startDateNull, $endDateNull );

//CONDITION 6 -> <CONDITION 1> AND <CONDITION 5>
$whereClause = $qb->expr()->andX( $state, $dates );


Related Topics



Leave a reply



Submit