如何在zend_db中实现sql between运算符 - How to implement sql between operator in zend_db

How to implement sql between operator in zend_db

本文关键字：实现 sql between 运算符 db zend | 更新日期: 2023-09-27

我有以下查询，用于计算过去5个月发生的总订单销售，

>     SELECT SUM(o.total), DATE(o.order_date) dateonly FROM `order` as o where (o.order_date BETWEEN  last_day(NOW() - INTERVAL 5 MONTH) AND 
> date_format(NOW(), '%Y-%m-%d')  AND (o.order_status = "P" OR
> o.order_status = "T" OR o.order_status = "S" OR o.order_status = "D"
> )) GROUP BY month(o.order_date)

我正试图在zend实现上述查询。但我坚持在中间。我有代码从模型/DbTable，

class Default_Model_DbTable_Order extends Zend_Db_Table_Abstract
{
    protected $_name = 'order';
    protected $_primary = 'order_id';
    public function month_based_orderlist()
    {
        $oDb = Zend_Registry::get("db");        
        $whereSQL  = 'o.order_id > 0';
        $whereSQL .= ' AND o.status = 1';
        $whereSQL .= ' AND o.order_date BETWEEN  last_day(NOW() - INTERVAL 5 MONTH) AND  date_format(NOW(),'.%Y-%m-%d.')';
        $whereSQL .= ' AND o.order_status = "P"';
        $whereSQL .= ' OR o.order_status = "T"';
        $whereSQL .= ' OR o.order_status = "S"';
        $whereSQL .= ' OR o.order_status = "D"';
        $select = $oDb->select()
            ->from(
                array('o' => $this->_name), 
                array(
                    'lifetimesale' => new Zend_Db_Expr('SUM(o.total)'),
                    'dateonly' => DATE('o.order_date')
                )
            )->where($whereSQL);
        //echo $select; exit;
        $result = $this->getAdapter()->fetchAll($select);
        // print_obj($result);
        return $result;            
    }
}
?>

我正试图实现上面的sql查询，我在这方面做错了什么。

bug在这里:

.%Y-%m-%d.

编辑

Phil指出我的解决方案是错误的。我重新检查了一下，他是对的。解决方案应该是去掉引号，并放入双引号:

// assume $date is set 
$whereSQL .= ' AND o.order_date BETWEEN last_day(NOW() -'
          . ' INTERVAL 5 MONTH) AND date_format(NOW(), "%Y-%m-%d")';

或者，您可以使用反斜杠转义单引号，并删除字符串连接操作符:

... date_format(NOW(), ''%Y-%m-%d'')';

另外，我看到您正在扩展Zend_Db_Table_Abstract，因此，您不需要以下行:

$oDb = Zend_Registry::get('db');

省略它，用$this代替。它看起来像这样(简化与GROUP BY子句添加):

$select = $this->select()
    ->from($fromTable, $columns)
    ->where($whereSQL)
    ->group('month(o.order_date)');
$result = $this->fetchAll($select);

要添加GROUP BY子句，只需这样做(或使用方法链接查看上面的示例):

$select->group('month(o.order_date)');

谢谢Phil的帮助!