/**
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
// localize variables
foreach (get_object_vars($this->select) as $name => $value) {
$this->{$name} = $value;
}
parent::prepareStatement($adapter, $statementContainer);
}
/**
* @covers Zend\Db\Sql\Select::prepareStatement
*/
public function testPrepareStatement()
{
$mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface');
$mockAdapter = $this->getMock('Zend\\Db\\Adapter\\Adapter', null, array($mockDriver));
$mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface');
$mockStatement->expects($this->at(0))->method('setSql')->with($this->equalTo('SELECT "bee", "baz", "zac".* FROM "foo" INNER JOIN "zac" ON "m" = "n"'));
$mockStatement->expects($this->at(3))->method('setSql')->with($this->equalTo(' WHERE x = y'));
$this->select->from('foo')->columns(array('bee', 'baz'))->join('zac', 'm = n')->where('x = y');
$this->select->prepareStatement($mockAdapter, $mockStatement);
}
/**
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
// localize variables
foreach (get_object_vars($this->select) as $name => $value) {
$this->{$name} = $value;
}
if ($this->limit === null && $this->offset !== null) {
$this->specifications[self::LIMIT] = 'LIMIT 18446744073709551615';
}
parent::prepareStatement($adapter, $statementContainer);
}
/**
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
// localize variables
foreach (get_object_vars($this->select) as $name => $value) {
$this->{$name} = $value;
}
// set specifications
unset($this->specifications[self::LIMIT]);
unset($this->specifications[self::OFFSET]);
$this->specifications['LIMITOFFSET'] = null;
parent::prepareStatement($adapter, $statementContainer);
}
public function fetchAll()
{
$select = new Select();
$select->from('person')->columns(array('f_name' => 'person.firstname', 'l_name' => 'person.lastname', 'comp_name' => 'contact.companyname', 'cont_name' => 'contactstate.name'))->join('contact', 'contactstate', 'person.id = contact.person_id', 'contact.state_contactstate_id = contactstate.id');
$statement = $dbAdapter->createStatement();
$select->prepareStatement($dbAdapter, $statement);
$driverResult = $statment->execute();
$resultset = new ResultSet();
$resultset->setDataSource($driverResult);
foreach ($resultset as $row) {
// $row is an ArrayObject
}
}
/**
* Prepare statement
*
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
* @return void
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();
$parameterContainer = $statementContainer->getParameterContainer();
if (!$parameterContainer instanceof ParameterContainer) {
$parameterContainer = new ParameterContainer();
$statementContainer->setParameterContainer($parameterContainer);
}
$table = $this->table;
$schema = null;
// create quoted table name to use in insert processing
if ($table instanceof TableIdentifier) {
list($table, $schema) = $table->getTableAndSchema();
}
$table = $platform->quoteIdentifier($table);
if ($schema) {
$table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
}
$columns = array();
$values = array();
if (is_array($this->values)) {
foreach ($this->columns as $cIndex => $column) {
$columns[$cIndex] = $platform->quoteIdentifier($column);
if (isset($this->values[$cIndex]) && $this->values[$cIndex] instanceof Expression) {
$exprData = $this->processExpression($this->values[$cIndex], $platform, $driver);
$values[$cIndex] = $exprData->getSql();
$parameterContainer->merge($exprData->getParameterContainer());
} else {
$values[$cIndex] = $driver->formatParameterName($column);
if (isset($this->values[$cIndex])) {
$parameterContainer->offsetSet($column, $this->values[$cIndex]);
} else {
$parameterContainer->offsetSet($column, null);
}
}
}
$sql = sprintf($this->specifications[static::SPECIFICATION_INSERT], $table, implode(', ', $columns), implode(', ', $values));
} elseif ($this->values instanceof Select) {
$this->values->prepareStatement($adapter, $statementContainer);
$columns = array_map(array($platform, 'quoteIdentifier'), $this->columns);
$columns = implode(', ', $columns);
$sql = sprintf($this->specifications[static::SPECIFICATION_SELECT], $table, $columns ? "({$columns})" : "", $statementContainer->getSql());
} else {
throw new Exception\InvalidArgumentException('values or select should be present');
}
$statementContainer->setSql($sql);
}
/**
* @param Sql\Select $select
* @return null|ResultSet
* @throws \RuntimeException
*/
public function selectWith(Sql\Select $select)
{
$selectState = $select->getRawState();
if ($selectState['table'] != $this->tableName || $selectState['schema'] != $this->schema) {
throw new Exception\RuntimeException('The table name and schema of the provided select object must match that of the table');
}
if ($selectState['join'] != array()) {
throw new Exception\RuntimeException('The Select object provided to TableRowGateway cannot include join statements.');
}
$statement = $this->adapter->createStatement();
$select->prepareStatement($this->adapter, $statement);
$result = $statement->execute();
$resultSet = clone $this->selectResultPrototype;
$resultSet->setDataSource($result);
return $resultSet;
}
/**
* @param AdapterInterface $adapter
* @param StatementContainerInterface $statementContainer
*/
public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
{
// localize variables
foreach (get_object_vars($this->select) as $name => $value) {
$this->{$name} = $value;
}
// set specifications
unset($this->specifications[self::LIMIT]);
unset($this->specifications[self::OFFSET]);
$this->specifications['LIMITOFFSET'] = null;
parent::prepareStatement($adapter, $statementContainer);
//set statement cursor type
if ($statementContainer instanceof Statement) {
$statementContainer->setPrepareOptions(array('Scrollable' => \SQLSRV_CURSOR_STATIC));
}
}
/**
*
* @param Select $select
* @param $entityPrototype
*/
public function selectSingle(Select $select, $entityPrototype = null, HydratorInterface $hydrator = null)
{
$adapter = $this->getDbAdapter();
$statement = $adapter->createStatement();
$select->prepareStatement($adapter, $statement);
$result = $statement->execute();
if (!$hydrator) {
$hydrator = $this->getHydrator();
}
if (!$entityPrototype) {
$entityPrototype = $this->getEntityPrototype();
}
$current = $result->current();
if (!is_array($current)) {
return null;
}
return $hydrator->hydrate($current, $entityPrototype);
}
public function selectWith(Select $select, $scrollable = true)
{
// Get the data
$adapter = $this->getReadAdapter();
$statement = $adapter->createStatement();
$select->prepareStatement($adapter, $statement);
$result = $statement->execute();
if ($scrollable) {
// Convert data to an array so we can iterate more than once
$resultArray = array();
foreach ($result as $row) {
$resultArray[] = $this->toScalarValueArray($row);
}
$result = $resultArray;
}
// Create the ResultSet
$resultSet = new ResultSet();
$resultSet->setRowObjectPrototype($this->getModelPrototype());
$resultSet->setDataSource($result);
return $resultSet;
}
protected function processSubSelect(Select $subselect, PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if ($driver) {
$stmtContainer = new StatementContainer();
// Track subselect prefix and count for parameters
$this->processInfo['subselectCount']++;
$subselect->processInfo['subselectCount'] = $this->processInfo['subselectCount'];
$subselect->processInfo['paramPrefix'] = 'subselect' . $subselect->processInfo['subselectCount'];
// call subselect
$subselect->prepareStatement(new \Zend\Db\Adapter\Adapter($driver, $platform), $stmtContainer);
// copy count
$this->processInfo['subselectCount'] = $subselect->processInfo['subselectCount'];
$parameterContainer->merge($stmtContainer->getParameterContainer()->getNamedArray());
$sql = $stmtContainer->getSql();
} else {
$sql = $subselect->getSqlString($platform);
}
return $sql;
}
protected function _authenticateQuerySelect(Select $dbSelect)
{
$statement = $this->zendDb->createStatement();
$dbSelect->prepareStatement($this->zendDb, $statement);
$resultSet = new ResultSet();
try {
$resultSet->initialize($statement->execute(array($this->identity)));
$resultIdentities = $resultSet->toArray();
} catch (\Exception $e) {
throw new Exception\RuntimeException('The supplied parameters to DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.', 0, $e);
}
return $resultIdentities;
}
/**
* @testdox unit test: Test prepareStatement() will produce expected sql and parameters based on a variety of provided arguments [uses data provider]
* @covers Zend\Db\Sql\Select::prepareStatement
* @dataProvider providerData
*/
public function testPrepareStatement(Select $select, $expectedSqlString, $expectedParameters, $unused1, $unused2, $useNamedParameters = false)
{
$mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface');
$mockDriver->expects($this->any())->method('formatParameterName')->will($this->returnCallback(function ($name) use($useNamedParameters) {
return $useNamedParameters ? ':' . $name : '?';
}));
$mockAdapter = $this->getMock('Zend\\Db\\Adapter\\Adapter', null, array($mockDriver));
$parameterContainer = new ParameterContainer();
$mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface');
$mockStatement->expects($this->any())->method('getParameterContainer')->will($this->returnValue($parameterContainer));
$mockStatement->expects($this->any())->method('setSql')->with($this->equalTo($expectedSqlString));
$select->prepareStatement($mockAdapter, $mockStatement);
if ($expectedParameters) {
$this->assertEquals($expectedParameters, $parameterContainer->getNamedArray());
}
}
public function dailyGrowth()
{
$subSelect = new \Zend\Db\Sql\Select();
$subSelect->from('resource')->columns(array('id', 'created_date'));
$subSelect->where->notEqualTo('node_status', \Application\Model\Resource::NODE_STATUS_DISABLED);
$select = new \Zend\Db\Sql\Select();
$select->from('calendar_table')->columns(array('*', new Expression('COUNT(resource.id) AS total'), new Predicate\Expression('YEAR(calendar_table.calendar_date) AS year'), new Predicate\Expression('MONTH(calendar_table.calendar_date) AS month'), new Predicate\Expression('MONTHNAME(calendar_table.calendar_date) AS monthname'), new Predicate\Expression('DAY(calendar_table.calendar_date) AS day')));
$select->join(array('resource' => $subSelect), 'resource.created_date = calendar_table.calendar_date', array(), Select::JOIN_LEFT);
$select->group('calendar_date');
$statement = $this->getAdapter()->createStatement();
$select->prepareStatement($this->getAdapter(), $statement);
$resultset = new ResultSet();
return iterator_to_array($resultset->initialize($statement->execute()));
}
<?php
/** @var $adapter Zend\Db\Adapter\Adapter */
$adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php');
refresh_data($adapter);
use Zend\Db\Sql\Select, Zend\Db\ResultSet\ResultSet;
$select = new Select();
$select->from('artist')->columns(array())->join('album', 'artist.id = album.artist_id', array('title', 'release_date'))->order(array('release_date', 'title'))->where->like('artist.name', '%Brit%');
$statement = $adapter->createStatement();
$select->prepareStatement($adapter, $statement);
$resultSet = new ResultSet();
$resultSet->initialize($statement->execute());
$albums = array();
foreach ($resultSet as $row) {
$albums[] = $row->title . ' released on: ' . date('Y-m-d', strtotime($row->release_date));
}
assert_example_works($albums == array(0 => '...Baby One More Time released on: 1999-02-14', 1 => 'Oops!... I Did It Again released on: 2000-10-10', 2 => 'Britney released on: 2001-04-06', 3 => 'Blackout released on: 2007-10-10', 4 => 'Circus released on: 2008-11-23', 5 => 'Femme Fatale released on: 2011-10-10', 6 => 'In the Zone released on: 2011-10-10'));
/**
* @testdox unit test: Test prepareStatement() will produce expected sql and parameters based on a variety of provided arguments [uses data provider]
* @covers Zend\Db\Sql\Select::prepareStatement
* @dataProvider providerData
*/
public function testPrepareStatement(Select $select, $expectedSqlString, $expectedParameters)
{
$mockDriver = $this->getMock('Zend\Db\Adapter\Driver\DriverInterface');
$mockDriver->expects($this->any())->method('formatParameterName')->will($this->returnValue('?'));
$mockAdapter = $this->getMock('Zend\Db\Adapter\Adapter', null, array($mockDriver));
$parameterContainer = new ParameterContainer();
$mockStatement = $this->getMock('Zend\Db\Adapter\Driver\StatementInterface');
$mockStatement->expects($this->any())->method('getParameterContainer')->will($this->returnValue($parameterContainer));
$mockStatement->expects($this->any())->method('setSql')->with($this->equalTo($expectedSqlString));
$select->prepareStatement($mockAdapter, $mockStatement);
if ($expectedParameters) {
$this->assertEquals($expectedParameters, $parameterContainer->getNamedArray());
}
}
