php - Convert SQL satement to Zend Db_Table_Abstract_Select code -

i'm trying fetch latest row each customer table below.

---------------------------------------------------------------------------------- |  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  | 

i have following code , sql statement works...

$type="sales";  $sql =  "         select cl1.*         {$this->_name} cl1         inner join  (                     select customer_id, max(timestamp) lasttimestamp                     {$this->_name}                     type = '{$type}'                     group customer_id                     ) cl2         on cl1.customer_id = cl2.customer_id , cl1.timestamp = cl2.lasttimestamp         ";  $stmt = $this->getadapter()->query($sql); 

which produces...

select cl1.* customer_contactlog cl1 inner join ( select customer_id, max(timestamp) lasttimestamp customer_contactlog type = 'sales' group customer_id ) cl2 on cl1.customer_id = cl2.customer_id , cl1.timestamp = cl2.lasttimestamp 

i have tried convert "the zend way" other models written way struggling. code have come is...

    $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) lasttimestamp')                                 )                     ->where     ('cl1.customer_id = ?', 'cl2.customer_id')                     ->where     ('cl1.timestamp = ?', 'cl2.lasttimestamp'); 

but produces...

    select `cl1`.*, `cl2`.`customer_id`, max(cl2.timestamp) `lasttimestamp` `customer_contactlog` `cl1` inner join `customer_contactlog` `cl2` on cl2.type = 'sales' (cl1.customer_id = 'cl2.customer_id') , (cl1.timestamp = 'cl2.lasttimestamp') 

can tell me going wrong?

thanks

you can use nested selects within zend db queries. need here - create subselect , join in main one.

$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 , cl1.timestamp = cl2.lasttimestamp',         null     );