AND and OR expression in Doctrine ODM

Question

I need to transform a SQL request into a DQL request :

SELECT * FROM object WHERE stardate >= GETDATE() AND enddate <= GETDATE() OR stardate >= GETDATE() AND enddate IS NULL

I tried with :

$now = new \DateTime();
$qb = $this->createQueryBuilder()
    ->field('startDate')->lte($now)
    ->field('endDate')->gte($now);

$qb->addOr(
    $qb->expr()
        ->field('startDate')->lte($now)
        ->field('endDate')->exists(false)
);

return $qb->getQuery()->execute();

But It's not correct.

score 2 · Accepted Answer · answered Apr 03 '14 at 09:34

2

The solution :

$now = new \DateTime('01/01/2018');
$qb = $this->createQueryBuilder();
$qb->addOr(
    $qb->expr()
        ->field('startDate')->lte($now)
        ->field('endDate')->gte($now)
);
$qb->addOr(
    $qb->expr()
        ->field('startDate')->lte($now)
        ->field('endDate')->exists(false)
);

return $qb->getQuery()->execute();

answered Apr 03 '14 at 09:34

Cyril F

1,247
3
16
31

1

It may be simpler if you just use the Doctrine Query Language directly instead of a Query Builder, since it's syntax is very close to SQL. http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/reference/dql-doctrine-query-language.html – RoToRa Apr 03 '14 at 09:39
1

Example: `SELECT x FROM YourProject\Model\YourTable WHERE x.stardate >= CURRENT_DATE() AND x.enddate <= CURRENT_DATE() OR x.stardate >= CURRENT_DATE() AND x.enddate IS NULL` – RoToRa Apr 03 '14 at 09:41
in fact, it seems that the DQL is not available for the ODM (and not ORM) part of doctrine. – Cyril F Apr 03 '14 at 11:39
Ah, ok. I missed the ODM part. But it makes sense. – RoToRa Apr 03 '14 at 12:04

AND and OR expression in Doctrine ODM

1 Answers1