public function getCurrentVersion($mdir_md5)
{
$select = new Select($this->tableGateway->getTable());
$select->where(array('mdir_md5' => $mdir_md5))->order('version DESC')->limit(1);
$result = $this->tableGateway->selectWith($select);
if (!$result->count()) {
return 0;
}
return $result->current()->getVersion();
}
/**
* @param Select $select
* @param array $options Paginator options
* @param ResultSetInterface $resultSet
* @return array|Paginator
*/
public function selectMany(Select $select, array $options = array(), ResultSetInterface $resultSet = null)
{
if (!$resultSet) {
$resultSet = $this->tableGateway->getResultSetPrototype();
}
if ($options) {
return $this->initPaginator($select, $options, $resultSet);
}
return $this->tableGateway->selectWith($select, $resultSet);
}
public function testClearExpiredLocks()
{
$this->throttle->clearExpiredLocks();
$select = $this->gateway->getSql()->select();
$select->where->like('key', '%::past');
$this->assertEmpty($this->gateway->selectWith($select)->count());
}
/**
* Returns EntitySet defined by $start and $stop range of key
*
* @param integer $start
* @param integer $stop
* @return TableEntitySetInterface
*/
public function getRangeByKey($start, $stop)
{
$sql = new Sql($this->getAdapter());
$select = $sql->select();
$select->from($this->tableGateway->getTable());
$select->where(new Predicate\Between($this->getItemSetProto()->getFactory()->getEntityProto()->getKeyName(), $start, $stop));
return $this->tableGateway->selectWith($select);
}
/**
* @param CriteriaInterface|array $criteria
* @return EntityInterface[]
*/
public function findMany($criteria)
{
if (is_array($criteria)) {
$criteria = $this->createCriteria($criteria);
}
if (empty($this->with)) {
return $this->entityRepository->findMany($criteria);
}
/** @var Select $select */
$select = $criteria->getQuery();
$rows = $this->tableGateway->selectWith($select)->toArray();
if (empty($rows)) {
return [];
}
$relatedEntityIds = [];
foreach ($rows as $row) {
foreach ($this->with as $relatedEntityName => $cascadeWith) {
$relatedField = $this->getRelatedField($relatedEntityName);
if (!array_key_exists($relatedField, $row)) {
throw new \RuntimeException(get_class($this) . ": relation field {$relatedEntityName} not fetched");
}
if (!isset($relatedEntityIds[$relatedEntityName])) {
$relatedEntityIds[$relatedEntityName] = new ArrayObject();
}
if (!in_array($row[$relatedField], (array) $relatedEntityIds[$relatedEntityName])) {
$relatedEntityIds[$relatedEntityName]->append($row[$relatedField]);
}
}
}
$relatedEntities = [];
foreach ($this->with as $relatedEntityName => $cascadeWith) {
if (empty((array) $relatedEntityIds[$relatedEntityName])) {
continue;
}
$criteria = $this->relatedRepository[$relatedEntityName]->createCriteria(['id.in' => (array) $relatedEntityIds[$relatedEntityName]]);
$relatedEntities[$relatedEntityName] = $this->relatedRepository[$relatedEntityName]->findMany($criteria);
}
$entitiesArgs = [];
foreach ($rows as &$row) {
$entityArgs = ['data' => $this->mapper->fromTableRow($row)];
foreach ($this->relationsConfig as $entityName => $joinRule) {
$relatedField = $this->getRelatedField($entityName);
if (isset($relatedEntities[$entityName])) {
if (isset($relatedEntities[$entityName][$row[$relatedField]])) {
$entityArgs['aggregateItems'][] = $relatedEntities[$entityName][$row[$relatedField]];
} else {
$entityArgs['aggregateItems'][] = null;
}
} else {
$entityArgs['aggregateItems'][] = null;
}
}
$entitiesArgs[] = $entityArgs;
}
$entities = $this->entityFactory->createCollection($entitiesArgs);
$this->with = null;
return $entities;
}
/**
* {@inheritDoc}
*/
public function getIdentityRoles()
{
$authService = $this->userService->getAuthService();
if (!$authService->hasIdentity()) {
return array($this->getDefaultRole());
}
// get roles associated with the logged in user
$sql = new Select();
$sql->from($this->tableName);
// @todo these fields should eventually be configurable
$sql->join('user_role', 'user_role.id = ' . $this->tableName . '.role_id');
$sql->where(array('user_id' => $authService->getIdentity()->getId()));
$results = $this->tableGateway->selectWith($sql);
$roles = array();
foreach ($results as $role) {
$roles[] = $role['role_id'];
}
return $roles;
}
/**
* Gets the set of ratings for the given type id.
*
* @param string $typeId The type identifier to get the set of ratings for.
* @return RatingSet
*/
public function getRatingSet($typeId)
{
$adapter = $this->gateway->getAdapter();
$sql = new Sql($adapter);
$select = $sql->select();
$select->from($this->gateway->getTable());
$select->columns(array('amount' => new Expression('COUNT(rating)'), 'avarage' => new Expression('AVG(rating)'), 'highest' => new Expression('MAX(rating)'), 'lowest' => new Expression('MIN(rating)')), false);
$rowset = $this->gateway->selectWith($select);
$row = $rowset->current();
return new RatingSet($typeId, $row['amount'], $row['avarage'], $row['highest'], $row['lowest']);
}
/**
* 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);
}
/**
* レコードを全件取得
*
* @param array $keys
* @param Sql\Select $select
* @return array|ModelInterface[]
*/
public function findAll(array $keys, Sql\Select $select = null)
{
$select = $this->buildSelect($keys, $select);
if ($this->resultArray) {
$statement = $this->tableData->getSql()->prepareStatementForSqlObject($select);
$result = $statement->execute();
} else {
$result = $this->tableData->selectWith($select);
}
$res = array();
foreach ($result as $row) {
$res[] = $row;
}
$this->resultArray = false;
return $res;
}
/**
* {@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;
}
/**
* Получить справочник.
*/
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;
}
/**
* Loads data from its id
* @param int $id
*/
public function loadFromId($id)
{
if (!is_object($this->table) || !$this->table instanceof \Zend\Db\TableGateway\TableGateway) {
throw new \Exception('No database table associated with this content', 1409011101);
}
$this->data = array();
$select = $this->table->getSql()->select()->where(array($this->primary => $id));
$data = $this->table->selectWith($select)->current();
$mysqli = $this->table->getAdapter()->getDriver()->getConnection()->getResource();
if (is_object($data)) {
$this->data = $data->getArrayCopy();
} else {
if ($mysqli->errno > 0) {
$this->data = array();
if (is_numeric($mysqli->errno) && $mysqli->errno != 0) {
throw new \Exception('Error on query ' . $select->getSqlString($this->table->getAdapter()->getPlatform()) . ' ' . $mysqli->errno . ' ' . $mysqli->error, 1401301242);
}
}
}
}
public function index03Action()
{
$resultSetPrototype = new ResultSet();
// dành cho tham số thu 4 của tableGateway
$resultSetPrototype->setArrayObjectPrototype(new \Database\Model\Entity\User());
// dành cho tham số thu 4 của tableGateway
$table = "user";
$adapter = $this->getServiceLocator()->get("db_books");
$tableGateway = new TableGateway($table, $adapter, null, $resultSetPrototype);
$sql = new Sql($adapter);
$select = $sql->select();
$select->from("user")->where("id <> 2");
$selectWith = $tableGateway->selectWith($select);
foreach ($selectWith as $row) {
echo "<pre style='font-weight:bold'>";
print_r($row);
echo "</pre>";
}
return false;
}
public function getRoles()
{
$tableGateway = new TableGateway($this->tableName, $this->sm->get('Zend\\Db\\Adapter\\Adapter'));
$sql = new Select();
$sql->from($this->tableName);
$rowset = $tableGateway->selectWith($sql);
$roles = array();
// Pass One: Build each object
foreach ($rowset as $row) {
$roleId = $row[$this->roleIdFieldName];
$roles[$roleId] = new Role($roleId, $row[$this->parentRoleFieldName]);
}
// Pass Two: Re-inject parent objects to preserve hierarchy
foreach ($roles as $roleId => $roleObj) {
$parentRoleObj = $roleObj->getParent();
if ($parentRoleObj) {
$roleObj->setParent($roles[$parentRoleObj->getRoleId()]);
}
}
return array_values($roles);
}
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()));
}
/**
* @param array $where ['equalTo' => ['type', 'single'], 'in' => ['id', [1, 2]], ]
* @param null $limit
* @param int $offset
* @param string $order
* @return null|\Zend\Db\ResultSet\ResultSetInterface
*/
public function getItemsWhere($where = [], $limit = null, $offset = 0, $order = 'id asc')
{
$select = $this->getSelect()->from($this->getTable());
if ($where) {
$whereObj = new \Zend\Db\Sql\Where();
foreach ($where as $key => $params) {
if (in_array($key, $this->fields)) {
if (is_array($params)) {
if ($params) {
$whereObj->in($key, $params);
} else {
// в случае когда передано $service->getItemsWhere(['id => []]);
return [];
}
} else {
$whereObj->equalTo($key, $params);
}
} else {
$allowedMethods = ['equalTo', 'notEqualTo', 'lessThan', 'greaterThan', 'lessThanOrEqualTo', 'greaterThanOrEqualTo', 'like', 'notLike', 'expression', 'literal', 'isNull', 'isNotNull', 'in', 'notIn', 'between'];
if (!in_array($key, $allowedMethods)) {
throw new \Exception("method '" . $key . "' not allowed in where.");
}
call_user_func_array([$whereObj, $key], $params);
}
}
$select->where($whereObj);
}
if ($limit !== null) {
$select->limit($limit);
}
if ($offset) {
$select->offset($offset);
}
$select->order($order);
return $this->tableGateway->selectWith($select);
}
/**
* {@inheritDoc}
*/
public function getRoles()
{
/* @var $adapter \Zend\Db\Adapter\Adapter */
$adapter = $this->serviceLocator->get($this->adapterName);
$tableGateway = new TableGateway($this->tableName, $adapter);
$sql = new Select();
$sql->from($this->tableName);
$rowset = $tableGateway->selectWith($sql);
$roles = array();
// Pass One: Build each object
foreach ($rowset as $row) {
$roleId = $row[$this->roleIdFieldName];
$roles[$roleId] = new Role($roleId, $row[$this->parentRoleFieldName]);
}
// Pass Two: Re-inject parent objects to preserve hierarchy
/* @var $roleObj Role */
foreach ($roles as $roleObj) {
$parentRoleObj = $roleObj->getParent();
if ($parentRoleObj && $parentRoleObj->getRoleId()) {
$roleObj->setParent($roles[$parentRoleObj->getRoleId()]);
}
}
return array_values($roles);
}
/**
* Returns the number of equipments belonging to this room.
* @return int
* @throws Exception if room not initialized
*/
public function getEquipmentCount()
{
if (!$this->is_init()) {
throw new \Exception('Room is not initialized.');
}
if (!$this->equipmentCount) {
$equipmentTable = new TableGateway('equipment', $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter'));
$select = new Sql\Select('equipment');
$select->columns(array('n' => new Sql\Expression('COUNT(*)')));
$where = new Sql\Where();
$where->equalTo('cid', $this->id);
$select->where($where);
$row = $equipmentTable->selectWith($select)->current();
$this->equipmentCount = $row->n;
}
return $this->equipmentCount;
}
public function selectWith(\Zend\Db\Sql\Select $select)
{
return $this->tableGateway->selectWith($select);
}
/**
* Allow custom comments selection.
*
* @param \Zend\Db\Sql\Select $select
* @return ResultSet
*/
public function fetchAllUsingSelect(\Zend\Db\Sql\Select $select)
{
return $this->tableGateway->selectWith($select);
}
public function isUniqueEmail($email)
{
$userTable = new TableGateway($this->table, $this->adapter);
$select = new Select();
$select->from($this->table);
$select->where(array('email' => $email));
$select->columns(array('iduser'));
$resultSet = $userTable->selectWith($select);
$resultRow = $resultSet->current();
if (!$resultRow) {
return true;
} else {
return false;
}
}
public static function ListaWhere($nazwaSlownika, $kolumna, $wartosc)
{
$projectTable = new TableGateway($nazwaSlownika, \Zend\Db\TableGateway\Feature\GlobalAdapterFeature::getStaticAdapter());
$select = new \Zend\Db\Sql\Select($nazwaSlownika);
$where = new Where();
$where->equalTo($kolumna, $wartosc);
$select->where($where);
$rowset = $projectTable->selectWith($select);
if ($rowset) {
return $rowset->current();
}
return array();
}
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;
}
