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

问题描述

我正在使用PHP Doctrine ORM构建我的查询。然而，我似乎不太明白如何使用DQL（Doctrine查询语言）编写以下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)

但是这样会产生一些类似于

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.

此外，where，andWhere和addWhere方法之间有区别吗？

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

UPDATE
好​​的，看起来你不能使用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)");

推荐答案

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

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