Doctrine - DQL- multiple ORs nested within one encapsulating AND

Question

I can't find an example of DQL, here's what the semi-pseudocode would look like:

Bring back invoices
- Where company id = 5
AND
    (
      ->where('DATE(i.startPeriod) BETWEEN :startDate AND :endDate')
      ->orWhere('DATE(i.endPeriod) BETWEEN :startDate AND :endDate')
      ->orWhere(':startDate BETWEEN DATE(i.startPeriod) and DATE(i.endPeriod)')
      ->orWhere(':endDate BETWEEN DATE(i.startPeriod) and DATE(i.endPeriod)')
    )

So you have four OR's nested within one encapsulating AND.

Does anyone know how to do that with Doctrine DQL? Nest a bunch of OR's within one giant AND?

Possible duplicate of [How to use andWhere and orWhere in Doctrine?](https://stackoverflow.com/questions/9095113/how-to-use-andwhere-and-orwhere-in-doctrine) — dmnptr, Nov 09 '17 at 00:27
I don't know if you would want to use only one "Where" condition with parentesis to nest other conditions ->where(company id = 5 AND ('DATE(i.startPeriod) BETWEEN :startDate AND :endDate' OR 'DATE(i.endPeriod) BETWEEN :startDate AND :endDate' OR . . . .) ) — Mz1907, Nov 09 '17 at 00:44
it's called an Exclusive Or. I did it in doctrine a while ago I would have to look it you have to use the `$expr` thing. — ArtisticPhoenix, Nov 09 '17 at 01:38

score 5 · Accepted Answer · edited Nov 09 '17 at 08:24

5

You would need to use the Expr() class with the query builder.

// $qb instanceof QueryBuilder

$qb->select('i')
   ->from('invoices', 'i')
   ->where('c.id = :cid')
   ->andWhere($qb->expr()->orX(
       $qb->expr()->between('i.startPeriod',':startdate',':enddate'),
       $qb->expr()->between('i.endPeriod',':startdate',':enddate'),
...

You can read more about the Expr() class in the documentation.

EDIT:

Just realized your initial question asked specifically about DQL. You can use parens in DQL to group things, so something like this.

$query = $em->createQuery(
   'SELECT i FROM Invoices i 
    WHERE c.id = :id 
    AND (
        (i.startPeriod BETWEEN :startDate AND :endDate) 
        OR 
        (i.endPeriod BETWEEN :startDate AND :endDate)
        OR 
        (:startDate BETWEEN i.startPeriod AND i.endPeriod)
        OR
        (:endDate BETWEEN i.startPeriod AND i.endPeriod)
    ) JOIN i.company c');

edited Nov 09 '17 at 08:24

DevDonkey

4,835
2
27
41

answered Nov 09 '17 at 07:32

blhylton

699
4
16

It's still cool to see another way of doing it like that though, cool thanks. – Brent Heigold Nov 09 '17 at 19:09
2

I prefer the query builder personally, because it makes it easier to build a query programmatically instead of having to build a string. That's why my brain went there first. – blhylton Nov 09 '17 at 19:19
That is true. Agreed. – Brent Heigold Nov 09 '17 at 21:30

Doctrine - DQL- multiple ORs nested within one encapsulating AND

1 Answers1