Writing a Subquery Using Zend Db

Writing a subquery using Zend DB

This:

    $select = $db->select()->from(array("s" => "sles"), array("s.id","i.id as instanceid","i.reference","i.name","i.sic_code","i.start_date"))
                                 ->join(array('i' => "sle_instances"),"s.id = i.sle_id",array())
                                 ->where("i.id = (select max(id) from sle_instances where sle_id = s.id)")
                                 ->order('i.name asc');

Gives this:

"SELECT `s`.`id`, `i`.`id` AS `instanceid`, `i`.`reference`, `i`.`name`, `i`.`sic_code`, `i`.`start_date` FROM `sles` AS `s`
 INNER JOIN `sle_instances` AS `i` ON s.id = i.sle_id WHERE (i.id = (select max(id) from sle_instances where sle_id = s.id)) ORDER BY `i`.`name` asc"

Zend\Db: Select from subquery

EDIT: Actually, I now see that my query was flawed. It won't work as expected with MySQL, which means I still have to write specialized queries. See GROUP_CONCAT change GROUP BY order

---

After going through the code of Zend\Db\Sql\Select I found these lines:

if ($table instanceof Select) {
    $table = '(' . $this->processSubselect($table, $platform, $driver, $parameterContainer) . ')';
} else {
    $table = $platform->quoteIdentifier($table);
}

So the answer is actually quite simple, all I had to do was to provide a Zend\Db\Sql\Select object to from(), without wrapping it in a Zend\Db\Sql\Expression like I used to with ZF1.

Code example:

$adapter = $this->getAdapter(); // Returns Zend\Db\Adapter\Adapter
$sql = new Zend\Db\Sql\Sql($adapter);

$from = $sql->select()
    ->from(static::$table)
    ->columns(array(
        'full_name',
        'value',
    ))
    ->order('id DESC');

$select = $sql->select()
    ->from(array(
        'subtable' => $from,
    ))
    ->columns(array(
        'full_name' => 'full_name',
        'value' => new Expression('GROUP_CONCAT(value)'),
    ))
    ->group('full_name')
    ->order('full_name DESC');

$selectString = $sql->getSqlStringForSqlObject($select);

$resultSet = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);

return $resultSet->toArray();

Zend_DB subselect / subquery how to?

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 Framework 2: sql subquery

Please try this.

$sql = new Sql($this->_adapter);
$mainSelect = $sql->select()->from('mytable');
$subQry = $sql->select()
        ->from('orders')
        ->columns(array('orderCount' => new \Zend\Db\Sql\Expression('COUNT(orders.id)')));
$mainSelect->columns(
        array(
            'id', 
            'orders_total' => new \Zend\Db\Sql\Expression('?', array($subQry)),
        )
);

$statement = $sql->prepareStatementForSqlObject($mainSelect);
$comments = $statement->execute();
$resultSet = new ResultSet();
$resultSet->initialize($comments);

return $resultSet->toArray();

Link: ZF2 - subqueries

Zend Framework 2 - sql subquery

First of all, you can do this without a sub-query:

SELECT listingId, COUNT(DISTINCT locationId) AS num
FROM l_f_locations
WHERE listingId IN(7,9,10)
GROUP BY listingId
HAVING num = 3;

For future reference, however, you could do the query you mention using a pair of Zend_Db_Select objects, one for the sub-query and another for the main:

$subQuery = $dbAdapter->select()
    ->from('l_f_locations', array('listingId', 'locationId'))
    ->where('locationId IN(7,9,10)')
    ->group('listingId')
    ->group('locationId');

$select = $dbAdapter->select()
    ->from($subQuery, array('*', 'num' => 'COUNT(*)'))
    ->group('listingId')
    ->having('num = 3');

$result = $select->query()->fetchAll();

---

Related Topics