将SQL状态转换为Zend Db_Table_Abstract_Select代码 - Convert SQL satement to Zend Db_Table_Abstract_Select code

Convert SQL satement to Zend Db_Table_Abstract_Select code

我正试图从下表中为每个客户获取最新的一行。

----------------------------------------------------------------------------------
|  id  |  customer_id  |  type   |  type_id  |    notes    |  timestamp   |  uid |
----------------------------------------------------------------------------------
|  1   |       1       |  sales  |     9     |  Note 1...  |  1432781613  |   9  |
|  2   |       2       |  sales  |     9     |  Note 1...  |  1432791204  |   9  |
|  3   |       3       |  sales  |     9     |  Note 1...  |  1432881619  |   9  |
|  4   |       1       |  sales  |     9     |  Note 2...  |  1442771601  |   9  |
|  5   |       1       |  sales  |     9     |  Note 3...  |  1462781617  |   9  |

我有下面的代码和SQL语句，它可以工作。。。

$type="sales";
$sql =  "
        SELECT cl1.*
        FROM {$this->_name} cl1
        INNER JOIN  (
                    SELECT customer_id, MAX(timestamp) AS lastTimestamp
                    FROM {$this->_name}
                    WHERE type = '{$type}'
                    GROUP BY customer_id
                    ) cl2
        ON cl1.customer_id = cl2.customer_id AND cl1.timestamp = cl2.lastTimestamp
        ";
$stmt = $this->getAdapter()->query($sql);

它产生。。。

SELECT cl1.* FROM customer_contactLog cl1 INNER JOIN ( SELECT customer_id, MAX(timestamp) AS lastTimestamp FROM customer_contactLog WHERE type = 'sales' GROUP BY customer_id ) cl2 ON cl1.customer_id = cl2.customer_id AND cl1.timestamp = cl2.lastTimestamp

我试着把它转换成"Zend方式"，因为我所有的其他模型都是这样写的，但我很挣扎。我想出的代码是…

    $select = $this ->select()
                    ->from      (
                                array('cl1' => $this->_name),
                                array('cl1.*')
                                )
                    ->join  (
                                array('cl2' => $this->_name),
                                "cl2.type = '{$type}'",
                                array('cl2.customer_id', 'MAX(cl2.timestamp) AS lastTimestamp')
                                )
                    ->where     ('cl1.customer_id = ?', 'cl2.customer_id')
                    ->where     ('cl1.timestamp = ?', 'cl2.lastTimestamp');

但这会产生。。。

    SELECT `cl1`.*, `cl2`.`customer_id`, MAX(cl2.timestamp) AS `lastTimestamp` FROM `customer_contactLog` AS `cl1` INNER JOIN `customer_contactLog` AS `cl2` ON cl2.type = 'sales' WHERE (cl1.customer_id = 'cl2.customer_id') AND (cl1.timestamp = 'cl2.lastTimestamp')

有人能告诉我哪里出了问题吗？

感谢

您可以在Zend DB查询中使用嵌套选择。这正是您在这里所需要的——创建一个子select，然后将其加入main。

$maxTimestampSelect = $this->select()
    ->from(
        $this->_name, 
        array('customer_id', 'lastTimestamp' => new Zend_Db_Expr('MAX(timestamp)'))
    )
    ->where('type = ?', $type)
    ->group('customer_id');
$select = $this->select()
    ->from(
        array('cl1' => $this->_name), 
        array('cl1.*')
    )
    ->join(
        array('cl2' => $maxTimestampSelect), 
        'cl1.customer_id = cl2.customer_id AND cl1.timestamp = cl2.lastTimestamp',
        null
    );