/**
* @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();
}
}
