Skip to content

Instantly share code, notes, and snippets.

@nicklevett
Last active December 27, 2015 02:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nicklevett/7253154 to your computer and use it in GitHub Desktop.
Save nicklevett/7253154 to your computer and use it in GitHub Desktop.
ZF2 Paginator with nested selects bind paramaters error
<?php
class DbTest
{
public function testSubSelects()
{
$adapter = new \Zend\Db\Adapter\Adapter(array(
'driver' => 'Mysqli',
'database' => 'test',
'username' => 'root',
'password' => '',
'host' => 'localhost'
));
$adapter->query('DROP TABLE IF EXISTS `address', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$adapter->query('DROP TABLE IF EXISTS `client', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$adapter->query('DROP TABLE IF EXISTS `owner_has_object', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$adapter->query('CREATE TABLE `address` (
`address_id` int(11) NOT NULL AUTO_INCREMENT,
`postcode` varchar(10) DEFAULT NULL,
`active_flag` tinyint(1) unsigned DEFAULT 1,
PRIMARY KEY (`address_id`),
KEY `postcode` (`postcode`)
) ENGINE=InnoDB', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$adapter->query('CREATE TABLE `client` (
`client_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`client_status_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`client_id`)
) ENGINE=InnoDB', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$adapter->query('CREATE TABLE `owner_has_object` (
`owner_object_id` int(10) unsigned NOT NULL,
`owner_id` int(11) unsigned NOT NULL,
`owner_type_id` int(10) unsigned NOT NULL,
`object_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`owner_object_id`,`owner_id`,`owner_type_id`,`object_id`)
) ENGINE=InnoDB', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$adapter->query('INSERT INTO `address` VALUES (1, "AB11 2BB", 1), (2, "AB22 1AA", 1)', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$adapter->query('INSERT INTO `client` VALUES (1, 1), (2, 2)', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$adapter->query('INSERT INTO `owner_has_object` VALUES (1, 1, 4, 1), (1, 2, 1, 2)', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$sql = new \Zend\Db\Sql\Sql($adapter);
$select = $sql->select();
$select->from(array(
'c' => new \Zend\Db\Sql\TableIdentifier('client', 'test'),
));
$select->columns(array(
'*',
));
$subSelect = new \Zend\Db\Sql\Select;
$subSelect->from(array(
'c' => new \Zend\Db\Sql\TableIdentifier('client', 'test'),
));
$subSelect->columns(array(
'client_id',
));
$subSelect->join(
array('o' => new \Zend\Db\Sql\TableIdentifier('owner_has_object', 'test')),
'o.owner_id = c.client_id',
array(),
$subSelect::JOIN_LEFT
);
$subSelect->where->equalTo('o.owner_object_id', 1);
$subSelect->join(
array(
'a' => new \Zend\Db\Sql\TableIdentifier('address', 'test'),
),
'a.address_id = o.object_id',
array('postcode'),
$subSelect::JOIN_LEFT
);
$subSelect->where
->nest()
->nest()
->equalTo('c.client_status_id', 4)
->and
->equalTo('o.owner_type_id', 1)
->unnest()
->or
->nest()
->equalTo('c.client_status_id', 2)
->and
->equalTo('o.owner_type_id', 1)
->unnest();
$subSelect->where->equalTo('a.active_flag', true);
$select->join(
array('b' => $subSelect),
'b.client_id = c.client_id',
array(),
$select::JOIN_LEFT
);
foreach (array('b.postcode' => 'AB') as $key => $value) {
if (!empty($value)) {
$select->where->like($key, sprintf('%%%s%%', $value));
}
}
$padapter = new \Zend\Paginator\Adapter\DbSelect($select, $sql);
$paginator = new \Zend\Paginator\Paginator($padapter);
$paginator->setCurrentPageNumber(1)
->setPageRange(2)
->setItemCountPerPage(5);
}
}
$obj = new DbTest;
$obj->testSubSelects();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment