php - 如何在zend框架中嵌套动态查询？

Question

我想为我的网站设计一个过滤系统。我有包含以下详细信息的项目:

 id     category     price
 1        2           11
 2        1           10
 3        1           23
 4        3           27
 5        3           36
 6        2           42

我想使用 zend 类编写类似这样的查询

`SELECT * FROM item WHERE category IN (1,2) AND (price BETWEEN 10 AND 20 OR price BETWEEN 41 AND 50)`

整个价格范围必须通过类似的代码生成

`$select->from('item');
$where = new \Zend\Db\Sql\Where();
$where->in('category', array(1,2));
$where->and->nest;
foreach($input as $in) {
    $where->between('price', $min, $max)->or;
}
$where->unnest;
$select->where($where);`

现在这段代码生成以下错误的查询

`SELECT * FROM item WHERE category IN (1,2) AND () AND price BETWEEN IN 10 AND 20 OR price BETWEEN 41 AND 50`

现在请帮助我使用 zend 框架类生成查询，就像前者一样。

Answer 1

嵌套函数返回一个将被嵌套的新 PredicateSet。这就是为什么它不适合你，所以如果你把它改成这样。它应该有效

$select = $sql->select('item');
$where = new Where();
$where->in('category', array(1,2));
$nest = $where->and->nest();
foreach($input as $in) {
    $nest->between('price', $min, $max)->or;
}
$select->where($where);