使用 PHP Doctrine ORM 的复杂 WHERE 子句

Complex WHERE clauses using the PHP Doctrine ORM

提问by Wickethewok

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):

我正在使用 PHP Doctrine ORM 来构建我的查询。但是，我似乎不太明白如何使用 DQL（Doctrine Query Language）编写以下 WHERE 子句：

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:

我目前在我的 PHP 代码中是这样的：

->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?

另外，“where”、“andWhere”和“addWhere”方法之间有区别吗？

UPDATEOk, 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:

UPDATE好吧，看起来你不能用 DQL 做复杂的查询，所以我一直在尝试手动编写 SQL 并使用 andWhere() 方法来添加它。但是，我正在使用 WHERE..IN 并且 Doctrine 似乎正在去掉我的括号：

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

回答by anushr

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

根据我的经验，每个复杂的where函数都在括号内分组（我使用的是 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:

产生以下 SQL：

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

回答by David Baucum

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.

这样做的正确方法可以在教义 2 - 查询构建器条件查询...如果语句中找到？正如@Jekis 所指出的。以下是如何使用表达式构建器来解决这个问题，就像@anushr 的例子一样。

$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);

回答by Wickethewok

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

由于您似乎无法使用 DQL 进行复杂查询，因此我编写了以下 SQL 以传递给 andWhere() 方法：

$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.

请注意“AND TRUE”，这是一个hack，以便解析器不会忽略外括号。

回答by Lashae

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

andWhere 可以概括为：
先前添加的条件感知 WHERE 语句

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

您可以放心地使用andWhere的就地哪里。（它引入了一个非常小的开销，这在下面的第二个列表项中说明。）

The implementation of andWhere is: (Doctrine 1.2.3)

andWhere的实现是：（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 ANDstatement to wherepart of the query, if another where statement was added before
3. append condition

1. 工艺参数
2. 如果之前添加了另一个 where 语句，则将AND语句附加到查询的where部分
3. 附加条件

回答by baboush

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 !

第一条语句写在 3 行上，第二条语句只写在一行上。我不相信，但有区别！

回答by Bob Gettys

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.

至于 where、andwhere 和 addwhere 之间的区别，我认为与上次阅读源代码时没有显着差异。但是，我鼓励您阅读 Doctrine 来源。这真的很简单，有助于填补文档中的漏洞（有很多）。至于复杂的 where 语句，我自己也想知道，但还没有需要。

回答by T. Kelher

$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

你愿意尝试这个变体吗，不确定它是否有效，但值得一试