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
Check If PHP-Page Is Accessed from an iOS Device
How to Run Cronjobs More Often Than Once Per Minute
Using MySQLi from Another Class in PHP
Php.Ini Is Nonexistent Loaded Configuration File (None)
How to Get Output of Proc_Open()
How to Zip Folder Without Full Path
MySQL Code Causes PHP Script to Crash at Popen/Exec
PHP Passing C Struct Data Throught Socket Programming
How to Get Beanstalkd Queue to Work for PHP
Trouble Using Posix_Kill in PHP
Run Shell Command and Send Output to File
Pkill -F Doesn't Work for Process Killing
Header in PDF Page Using Dompdf in PHP