Zend_DB subselect / subquery how to?

Question

I have this raw sql statement which I am trying to execute through Zend_DB.

$sql = 'SELECT relocationaction.id, relocationaction.vehicle, relocationaction.start,         relocationaction.end, relocationaction.return ' .
            'FROM relocationaction,
              (SELECT vehicle, MAX(end) AS maxend
              FROM relocationaction
              GROUP BY vehicle) AS co2
            WHERE co2.vehicle = relocationaction.vehicle
            AND(relocationaction.monitor = 1)
            AND (relocationaction.return IS NULL)
            AND (start <= ?)
            AND relocationaction.end = co2.maxend';

I have found a possible solution using this type of notation, but it is rendered to a totally different and wrong sql statement with joins and strange table names.

$tbl    = $this->getDbTable();
$select = $tbl->select()->setIntegrityCheck(false);

$subSelect = $select->from('relocationaction', array('vehicle', 'maxend' => 'MAX(relocationaction.end)'))
                    ->group('vehicle');
$subSelectString = '(' . $subSelect->__toString() . ')';

$select ->from(
                array('relocationaction'), array('id', 'date' => 'start', 'enddate' => 'end', 'return'),
                array('co2' => $subSelectString)
            )
        ->joinLeft('exhibitvehicle', 'exhibitvehicle.id = relocationaction.vehicle', array())
        ->where('co2.vehicle = relocationaction.vehicle')
        ->where('relocationaction.monitor = 1')
        ->where('relocationaction.return IS NULL')
        ->where('start <= ?', $start->get('yyyy-MM-dd'))
        ->where('relocationaction.end = co2.maxend');

Can anyone please give me a hint?

Thanks Jesse

UPDATE

This is the result of the second expression (total rubbish)

SELECT `relocationaction`.`vehicle`, 
    MAX(relocationaction.end) AS `maxend`, 
    `relocationaction_2`.`id`, 
    `relocationaction_2`.`start` AS `date`, 
    `relocationaction_2`.`end` AS `enddate`, 
    `relocationaction_2`.`return` 
FROM `relocationaction`
INNER JOIN `(
    SELECT ``relocationaction``.``vehicle``,
    MAX(relocationaction.end) AS ``maxend`` FROM ``relocationaction`` GROUP BY ``vehicle``)`.`relocationaction` AS `relocationaction_2`
LEFT JOIN `exhibitvehicle` ON exhibitvehicle.id = relocationaction.vehicle 
WHERE (col2.vehicle = relocationaction.vehicle) 
AND (relocationaction.monitor = 1) 
AND (relocationaction.return IS NULL) 
AND (start <= '2013-05-08') 
AND (relocationaction.end = col2.maxend) 
GROUP BY `vehicle`

Why not just use the sql statement in a model method. You can use PDO with zf you're not constrained to use zend_db_table if it doesn't suit your purpose. — user466764, May 08 '13 at 13:51
@user466764: You are right of course. I would love to know whether this is possible with Zend_Db_Table. If not I will stick to PDO — Jesse, May 08 '13 at 17:55

JBreton · Answer 1 · 2013-12-12T15:30:02.267

If you use a string in from(), Zend_Db_Select will consider it to be a table name so it escapes it.

The solution is to wrap your subselect into a Zend_Db_Expr.

$tbl    = $this->getDbTable();
$select = $tbl->select()->setIntegrityCheck(false);

$subSelect = $select->from('relocationaction', array('vehicle', 'maxend' => 'MAX(relocationaction.end)'))
                ->group('vehicle');
$subSelectString = '(' . $subSelect->__toString() . ')';

$select ->from(
            array('relocationaction'), array('id', 'date' => 'start', 'enddate' => 'end', 'return'),
            array('co2' => new Zend_Db_Expr($subSelectString))
        )
    ->joinLeft('exhibitvehicle', 'exhibitvehicle.id = relocationaction.vehicle',     array())
    ->where('co2.vehicle = relocationaction.vehicle')
    ->where('relocationaction.monitor = 1')
    ->where('relocationaction.return IS NULL')
    ->where('start <= ?', $start->get('yyyy-MM-dd'))
    ->where('relocationaction.end = co2.maxend');

score 0 · Accepted Answer · answered May 09 '13 at 08:41

Ok here we go. I tried hard to find a solution with Zend_Db_Table but failed big time. That's why I finally did it with PDO, as suggested by @user466764. Thanks for your help.

$tbl    = $this->getDbTable();
    $query = 'SELECT relocationaction.id,
                        relocationaction.vehicle,
                        relocationaction.start,
                        relocationaction.end,
                        relocationaction.return
            FROM relocationaction
              (SELECT vehicle, MAX(end) AS maxend
              FROM relocationaction
              GROUP BY vehicle) AS co2
            WHERE co2.vehicle = relocationaction.vehicle
            AND(relocationaction.monitor = 1)
            AND (relocationaction.return IS NULL)
            AND (start <= "' . $start->get('yyyy-MM-dd') . '")
            AND relocationaction.end = co2.maxend';

    $sth = $tbl->getAdapter()->prepare($query);
    $sth->execute();
    $entries = $sth->fetchAll();

Zend_DB subselect / subquery how to?

UPDATE

2 Answers2