Complex WHERE clauses using the PHP Doctrine ORM

Question

I'm using the PHP Doctrine ORM to build my queries. However, I can't quite seem to figure how to write the following WHERE clause using DQL (Doctrine Query Language):

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

How can I specify where the parentheses go?

What I currently have in my PHP code is this:

->where('name = ?', 'ABC')
->andWhere('category1 = ?', 'X')
->orWhere('category2 = ?', 'X')
->orWhere('category3 = ?', 'X')
->andWhere('price > ?', 10)

But this produces something like

WHERE name='ABC' AND category1 = 'X' OR category2 = 'X' OR category3 = 'X' 
AND price > 10

which, due to order of operations, doesn't return the intended results.

Also, is there a difference between the "where", "andWhere", and "addWhere" methods?

UPDATE Ok, it seems like you can't do complex queries using DQL, so I've been trying to write the SQL manually and use the andWhere() method to add it. However, I'm using WHERE..IN and Doctrine seems to be stripping out my enclosing parentheses:

$q->andWhere("(category1 IN $subcategory_in_clause
            OR category2 IN $subcategory_in_clause 
            OR category3 IN $subcategory_in_clause)");

Hindoo

Another solution can be found here https://mcmap.net/q/375115/-doctrine-2-query-builder-conditional-queries-if-statements – Cajolery

Answer 1

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

Isoclinal

Answer 2

The correct way of doing this can be found at doctrine 2 - query builder conditional queries... If statements? as noted by @Jekis. Here is how to use the expression builder to solve this like in @anushr's example.

$qb->where($qb->expr()->eq('name', ':name'))
  ->andWhere(
    $qb->expr()->orX(
      $qb->expr()->eq('category1', ':category1'),
      $qb->expr()->eq('category2', ':category2'),
      $qb->expr()->eq('category3', ':category3')
  )
  ->andWhere($qb->expr()->lt('price', ':price')
  ->setParameter('name', 'ABC')
  ->setParameter('category1', 'X')
  ->setParameter('category2', 'X')
  ->setParameter('category3', 'X')
  ->setParameter('price', 10);

Pickaninny

This is exactly why I hate Doctrine. This is impossible to read and understand. – Hunfredo

you can also so do like this: $ors = $qb->expr()->orX(); $ors->add($qb->expr()->like('firstName', $qb->expr()->literal("%john%"))); $ors->add($qb->expr()->like('lastName', $qb->expr()->literal("%john%"))); $qb->andWhere($ors); – Unveiling

Or you could just skip all this noise and concatenate together a DQL string... Just be sure you're not opening yourself up to SQL injection. – Bedstraw

Answer 3

As it seems like you can't do complex queries using DQL, I wrote the following SQL to pass to the andWhere() method:

$q->andWhere("(category1 IN $subcategory_in_clause
OR category2 IN $subcategory_in_clause 
OR category3 IN $subcategory_in_clause) AND TRUE");

Note the "AND TRUE", a hack so that the parser wouldn't ignore the outer parentheses.

Hindoo

I think anushr's solution is much better. No hacking and use of prepared statements. – Shilohshim

Answer 4

andWhere can be summarized as:
Previously Added Condition(s) Aware WHERE Statement

You can safely use andWhere inplace of where. (it introduces a very tiny overhead, which is stated below in the 2nd list item.)

The implementation of andWhere is: (Doctrine 1.2.3)

public function andWhere($where, $params = array())
{
    if (is_array($params)) {
        $this->_params['where'] = array_merge($this->_params['where'], $params);
    } else {
        $this->_params['where'][] = $params;
    }

    if ($this->_hasDqlQueryPart('where')) {
        $this->_addDqlQueryPart('where', 'AND', true);
    }

    return $this->_addDqlQueryPart('where', $where, true);
}

which can be read as,

1. Process parameters
2. append AND statement to where part of the query, if another where statement was added before
3. append condition

Faun

Answer 5

As for the difference between where, andwhere, and addwhere, i don't believe there is a significant difference from the last time i read the source. I would encourage you to read the Doctrine source, however. It's really simple, and helps fills in the holes in the documentation (there are many). As for complex where statements, I've wondered this myself but haven't had a need for it yet.

Windward

Answer 6

In my experience, I have sometimes seen a difference between :

$q->andWhere("(category1 IN $subcategory_in_clause
            OR category2 IN $subcategory_in_clause 
            OR category3 IN $subcategory_in_clause)");

and

$q->andWhere("(category1 IN $subcategory_in_clause OR category2 IN $subcategory_in_clause OR category3 IN $subcategory_in_clause)");

The first statement is written on 3 lines, the second, on only one. I didn't believe it but THERE IS A DIFFERENCE !

Prolegomenon

Answer 7

$q->andWhere("category1 IN ( $subcategory_in_clause )
              OR category2 IN ( $subcategory_in_clause )
              OR category3 IN ( $subcategory_in_clause )");

would you be so kind to try this variant, not sure if it works, but worth a shot

Highoctane