Create a gist now

Instantly share code, notes, and snippets.

ZF2 JOIN句に副問い合わせ
<?php
$sm = $this->getServiceLocator();
$adapter = $sm->get('Zend\Db\Adapter\Adapter');
$sql = new \Zend\Db\Sql\Sql($adapter);
$select = $sql->select();
$select->from('album');
//副問い合わせ用selectオブジェクト
$subselect = $sql->select();
$subselect->from('album');
$subselect->columns(array(
'id',
'longest_track_duration' => new \Zend\Db\Sql\Predicate\Expression('MAX(track.duration)')
));
$subselect->join(
'track',
'track.album_id = album.id',
array()
);
$subselect->group(array(
'id'
));
//JOIN句に副問い合わせを指定
$select->join(
array('longest' => $subselect),
'longest.id = album.id'
);
//echo $select->getSqlString($adapter->getPlatform());
$statement = $sql->prepareStatementForSqlObject($select);
$resultSet = $statement->execute();
//SELECT `album`.*, `longest`.* FROM `album` INNER JOIN (SELECT `album`.`id` AS `id`, MAX(track.duration) AS `longest_track_duration` FROM `album` INNER JOIN `track` ON `track`.`album_id` = `album`.`id` GROUP BY `id`) AS `longest` ON `longest`.`id` = `album`.`id`
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment