使用 PHP Doctrine ORM 的复杂 WHERE 子句 [英] Complex WHERE clauses using the PHP Doctrine ORM

问题描述

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

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

如何指定括号的位置?

我目前的 PHP 代码是这样的:

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)

但这会产生类似的东西

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.

另外，where"、andWhere"和addWhere"方法有区别吗?

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

更新好的，看起来你不能使用 DQL 进行复杂的查询，所以我一直在尝试手动编写 SQL 并使用 andWhere() 方法来添加它.但是，我正在使用 WHERE..IN 并且 Doctrine 似乎正在剥离我的括号:

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

推荐答案

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

产生以下 SQL:

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

这篇关于使用 PHP Doctrine ORM 的复杂 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！