/** * @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()); }
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()); }
/** * 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(); }
public function getAllUsersHighlights() { $categoryHighlight = 2; $rwvTg = new TableGateway('relevance_worth_vote', $this->tableGateway->getAdapter()); $votesGroupSubQuerySelect = $rwvTg->getSql()->select(); $votesGroupSubQuerySelect->columns(array('NetVotes1' => new Expression('COALESCE(SUM(voteUp)-SUM(voteDown),0)'), 'vupCatId' => 'category_id')); $votesGroupSubQuerySelect->group('vupCatId'); // echo "<pre>";print_r($votesGroupSubQuerySelect);exit; // $resultSet1 = $rwvTg->selectWith($votesGroupSubQuerySelect); // echo "<pre>";print_r($resultSet1);exit; $select = $this->tableGateway->getSql()->select(); $select->join('category', 'user_categories.category_id=category.category_id', array('category_image', 'category_highlight'), 'left'); $select->join(array('rwvlf' => $votesGroupSubQuerySelect), 'user_categories.category_id=vupCatId', array('NetVotes' => new Expression('COALESCE(NetVotes1,0)'), 'vupCatId1' => 'vupCatId'), 'left'); $select->join('user', 'user_categories.user_id=user.user_id', array('ustatus' => 'status'), 'left'); $select->where('category.category_highlight="' . $categoryHighlight . '"'); $select->where('user_categories.status="1"'); $select->where('user.status="1"'); $select->order('highlights_prior_order asc'); $select->order('NetVotes desc'); $select->order('category.category_id desc'); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }
/** * @param Where|array $where * @return Paginator */ public function read($where = null) { $config = $this->dataConfig; if ($config['query_type'] == 'normal') { $table = new TableGateway($config['table'], $this->getDbAdapter()); $columns = []; foreach ($config['column_enable'] as $column => $enable) { if ($enable) { $alias = $config['column_alias'][$column] ?: $column; $columns[$alias] = $column; } } $select = $table->getSql()->select(); $select->columns($columns); if (!empty($where)) { $select->where($where); } return new Paginator($adapter = new DbSelect($select, $this->getDbAdapter())); } else { $params = []; if (!empty($where)) { $whereData = $where->getExpressionData(); foreach ($whereData as $key => $val) { //todo fix sql mode } } return $this->getDbAdapter()->query($config['sql'], $params)->toArray(); } }