PHP TableGateway::getSql方法代码示例

 /**
  * @param string $key
  */
 public function clearExpiredLock($key = null)
 {
     $currDate = date($this->options->getDbDateTimeFormat());
     $delete = $this->gateway->getSql()->delete();
     $pred = $delete->where->lessThanOrEqualTo('end_datetime', $currDate);
     if ($key) {
         $pred = $pred->and->equalTo('key', $key);
     }
     $this->gateway->deleteWith($delete);
 }

 public function testClearExpiredLocks()
 {
     $this->throttle->clearExpiredLocks();
     $select = $this->gateway->getSql()->select();
     $select->where->like('key', '%::past');
     $this->assertEmpty($this->gateway->selectWith($select)->count());
 }

 /**
  * Adds a data
  */
 public function insert()
 {
     $id = $this->table->insert($this->data);
     if (is_null($id)) {
         if (array_key_exists('firephp', $GLOBALS)) {
             $GLOBALS['firephp']->error('Error on query ' . $this->table->getSql()->getSqlPlatform()->getSqlString($this->table->getAdapter()->getPlatform()));
         }
         throw new Exception('Error on query ' . $this->table->getSql()->getSqlPlatform()->getSqlString($this->table->getAdapter()->getPlatform()));
     }
     $this->data[$this->primary] = $this->table->getLastInsertValue();
 }

 /**
  * Constructs instance.
  *
  * @param TableGateway                $tableGateway
  * @param Where|\Closure|string|array $where
  * @param null                        $order
  */
 public function __construct(TableGateway $tableGateway, $where = null, $order = null)
 {
     $select = $tableGateway->getSql()->select();
     if ($where) {
         $select->where($where);
     }
     if ($order) {
         $select->order($order);
     }
     $dbAdapter = $tableGateway->getAdapter();
     $resultSetPrototype = $tableGateway->getResultSetPrototype();
     parent::__construct($select, $dbAdapter, $resultSetPrototype);
 }

 /**
  * Assert that a table has specified number for rows
  * 
  * @param integer $count
  * @param string|TableGateway $table
  * @param string|array|Closure|PredicateInterface $where
  * @throws \Exception on $table not a table name or TableGateway instance
  */
 protected function assertTableRowCount($count, $table, $where = null)
 {
     if (is_string($table)) {
         $table = new TableGateway\TableGateway($table, $this->getServices()->get('db'));
     }
     if (!$table instanceof TableGateway\TableGatewayInterface) {
         throw new \Exception("{$table} must be a table name or TableGateway object");
     }
     $select = $table->getSql()->select()->columns(array('count' => new Expression('count(*)')));
     if (null !== $where) {
         $select->where($where);
     }
     $actual = $table->selectWith($select)->current()->count;
     parent::assertEquals($count, $actual);
 }

 public function getSqlQuery(Query $query)
 {
     $conditionBuilder = new SqlConditionBuilder($this->dbTable->getAdapter(), $this->dbTable->getTable());
     $selectSQL = $this->dbTable->getSql()->select();
     $selectSQL->where($conditionBuilder($query->getQuery()));
     $selectSQL = $this->setSelectOrder($selectSQL, $query);
     $selectSQL = $this->setSelectLimitOffset($selectSQL, $query);
     $selectSQL = $this->setSelectColumns($selectSQL, $query);
     $selectSQL = $this->setSelectJoin($selectSQL, $query);
     $selectSQL = $this->makeExternalSql($selectSQL);
     //build sql string
     $sql = $this->dbTable->getSql()->buildSqlString($selectSQL);
     //replace double ` char to single.
     $sql = str_replace(["`(", ")`", "``"], ['(', ')', "`"], $sql);
     return $sql;
 }

 /**
  * {@inheritdoc}
  *
  * {@inheritdoc}
  */
 protected function getKeys()
 {
     $identifier = $this->getIdentifier();
     $select = $this->dbTable->getSql()->select();
     $select->columns(array($identifier));
     $rowset = $this->dbTable->selectWith($select);
     $keysArrays = $rowset->toArray();
     if (PHP_VERSION_ID >= 50500) {
         $keys = array_column($keysArrays, $identifier);
     } else {
         $keys = array();
         foreach ($keysArrays as $value) {
             $keys[] = $value[$identifier];
         }
     }
     return $keys;
 }

 /**
  * Constructs instance.
  *
  * @param TableGateway                      $tableGateway
  * @param null|Where|\Closure|string|array  $where
  * @param null|string|array                 $order
  * @param null|string|array                 $group
  * @param null|Having|\Closure|string|array $having
  */
 public function __construct(TableGateway $tableGateway, $where = null, $order = null, $group = null, $having = null)
 {
     $sql = $tableGateway->getSql();
     $select = $sql->select();
     if ($where) {
         $select->where($where);
     }
     if ($order) {
         $select->order($order);
     }
     if ($group) {
         $select->group($group);
     }
     if ($having) {
         $select->having($having);
     }
     $resultSetPrototype = $tableGateway->getResultSetPrototype();
     parent::__construct($select, $sql, $resultSetPrototype);
 }

 /**
  * Получить справочник.
  */
 public function getList()
 {
     $result = new JsonModel();
     $ref = (string) $this->params()->fromQuery("ref");
     $refmap = array("ownership_type" => array("fieldmap" => array("id" => "id", "text" => "name")));
     // Запрос
     $tableRef = new TableGateway($ref, $this->getAdapter());
     $select = $tableRef->getSql()->select();
     $data = $tableRef->selectWith($select)->toArray();
     // Переименовывание полей таблицы
     $_data = array();
     foreach ($data as $row) {
         $_data[] = array("id" => (int) $row[$refmap[$ref]["fieldmap"]["id"]], "text" => $row[$refmap[$ref]["fieldmap"]["text"]]);
     }
     $data = $_data;
     $result->success = true;
     $result->data = $data;
     return $result;
 }

 public function test__construct()
 {
     // constructor with only required args
     $table = new TableGateway('foo', $this->mockAdapter);
     $this->assertEquals('foo', $table->getTable());
     $this->assertSame($this->mockAdapter, $table->getAdapter());
     $this->assertInstanceOf('Zend\\Db\\TableGateway\\Feature\\FeatureSet', $table->getFeatureSet());
     $this->assertInstanceOf('Zend\\Db\\ResultSet\\ResultSet', $table->getResultSetPrototype());
     $this->assertInstanceOf('Zend\\Db\\Sql\\Sql', $table->getSql());
     // injecting all args
     $table = new TableGateway('foo', $this->mockAdapter, $featureSet = new Feature\FeatureSet(), $resultSet = new ResultSet(), $sql = new Sql($this->mockAdapter, 'foo'));
     $this->assertEquals('foo', $table->getTable());
     $this->assertSame($this->mockAdapter, $table->getAdapter());
     $this->assertSame($featureSet, $table->getFeatureSet());
     $this->assertSame($resultSet, $table->getResultSetPrototype());
     $this->assertSame($sql, $table->getSql());
     // constructor expects exception
     $this->setExpectedException('Zend\\Db\\TableGateway\\Exception\\InvalidArgumentException', 'Table name must be a string or an instance of Zend\\Db\\Sql\\TableIdentifier');
     new TableGateway(null, $this->mockAdapter);
 }

 public function execute(Request $request, Response $response, callable $next = null)
 {
     $collectionname = $request->getAttribute('collectionname');
     $collection = new TableGateway($collectionname, $this->boot()->db);
     $range = $request->getHeader('Range');
     $lexer = new \Xiag\Rql\Parser\Lexer();
     $tokens = $lexer->tokenize($request->getUri()->getQuery());
     $parser = \Xiag\Rql\Parser\Parser::createDefault();
     //file_put_contents('xxxx', print_r($parser->parse($tokens), true));
     $rql = $parser->parse($tokens);
     $select = $collection->getSql()->select();
     $sort = $rql->getSort();
     if ($sort) {
         foreach ($sort->getFields() as $field => $descending) {
             //file_put_contents('xxxx', print_r($field, true));
             $select->order("{$field} " . ($descending > 0 ? 'ASC' : 'DESC'));
         }
     }
     $result = $collection->selectWith($select);
     return $response->withHeader('Content-Type', 'application/json')->withBody($this->toJsonStream($result->toArray()));
 }

 /**
  * {@inheritdoc}
  */
 public function __invoke(ContainerInterface $container, $requestedName, array $options = null)
 {
     $config = $this->config[$requestedName];
     $dbAdapter = $container->get(isset($config['adapter']) ? $config['adapter'] : 'db');
     if (isset($config['schema'])) {
         $config['table'] = new TableIdentifier($config['table'], $config['schema']);
     }
     $featureSet = new FeatureSet();
     $featureSet->addFeature(new CommonCallFeature($config['primary']));
     if (isset($config['invokable'])) {
         $config['class'] = $config['invokable'];
     }
     if (isset($config['class'])) {
         if (!class_exists($config['class'])) {
             throw new \RuntimeException("Class '{$config['class']}' not found ");
         }
         /** @var \Zend\Db\TableGateway\TableGateway $table */
         $table = new $config['class']($config['table'], $dbAdapter, $featureSet);
     } else {
         $table = new TableGateway($config['table'], $dbAdapter, $featureSet);
     }
     if (isset($config['row'])) {
         if ($config['row'] === true) {
             $config['row'] = 'Zend\\Db\\RowGateway\\RowGateway';
         }
         if (is_string($config['row'])) {
             if (!class_exists($config['row'])) {
                 throw new \RuntimeException("Class '{$config['row']}' not found ");
             }
             $rowGatewayPrototype = new $config['row']($config['primary'], $config['table'], $dbAdapter, $table->getSql());
         } elseif (is_object($config['row'])) {
             $rowGatewayPrototype = $config['row'];
         } else {
             throw new \InvalidArgumentException('Error row argument');
         }
         $table->getResultSetPrototype()->setArrayObjectPrototype($rowGatewayPrototype);
     }
     return $table;
 }

 public function getOrderByRand($table, $where, $limit = 0)
 {
     $table = new TableGateway($table, $this->dbAdapter);
     $select = $table->getSql()->select();
     $select->where($where);
     if (!empty($limit)) {
         $select->limit($limit);
     }
     $rand = new \Zend\Db\Sql\Expression('RAND()');
     $select->order($rand);
     $resultSet = $table->selectWith($select);
     $result = $resultSet->toArray();
     return $result;
 }

 /**
  * @covers Zend\Db\TableGateway\TableGateway::getSql
  */
 public function testGetSql()
 {
     $this->assertInstanceOf('Zend\\Db\\Sql\\Sql', $this->table->getSql());
 }

 /**
  * @return Sql\Select
  */
 public function getSelectSql()
 {
     return $this->tableData->getSql()->select();
 }