Zend_Db 的复杂 WHERE 子句使用多个 AND OR 运算符

2021-12-29 00:00:00 sql php zend-framework zend-db

我想在 Zend_Db 中生成这个复杂的 WHERE 子句:

I want to generate this complex WHERE clause in Zend_Db:

SELECT * 
FROM 'products' 
WHERE 
    status = 'active' 
    AND 
    (
        attribute = 'one' 
        OR 
        attribute = 'two' 
        OR 
        [...]
    )
;

我已经试过了:

$select->from('product');
$select->where('status = ?', $status);
$select->where('attribute = ?', $a1);
$select->orWhere('attribute = ?', $a2);

然后产生:

SELECT `product`.* 
FROM `product` 
WHERE 
    (status = 'active') 
    AND 
    (attribute = 'one') 
    OR 
    (attribute = 'two')
;

我确实想出了一种使这项工作起作用的方法，但我觉得通过先使用 PHP 组合OR"子句然后使用 Zend_Db where() 子句组合它们有点作弊".PHP代码:

I did figure out one method of making this work but I felt it was sort of 'cheating' by using PHP to combine the "OR" clauses first and then combine them using Zend_Db where() clause. PHP code:

$WHERE = array();
foreach($attributes as $a):
    #WHERE[] = "attribute = '" . $a . "'";
endforeach;
$WHERE = implode(' OR ', $WHERE);

$select->from('product');
$select->where('status = ?', $status);
$select->where($WHERE);

这产生了我正在寻找的东西.但是我很好奇是否有一种官方"的方式来使用 Zend_Db 工具来获得复杂的 WHERE 语句(这真的不是太复杂，只是添加了一些括号)，而不是先在 PHP 中组合它.

That produced what I was looking for. But I'm curious if there's an "official" way of getting that complex WHERE statement (which really isn't too complex, just adding some parenthesis) with using the Zend_Db tool, instead of combining it in PHP first.

干杯！

推荐答案

这将是获得指定括号的官方"方式(参见 Zend_Db_Select 文档):

This would be the 'official' way to get you the parentheses as specified (see Example #20 in the Zend_Db_Select documentation): 

$a1 = 'one';
$a2 = 'two';
$select->from('product');
$select->where('status = ?', $status);
$select->where("attribute = $a1 OR attribute = $a2");

因此，鉴于您事先不知道自己有多少属性，您所做的似乎是合理的.

So, what you are doing does seem reasonable, given that you do not know how many attributes you have ahead of time.

相关文章