/**
* @dataProvider beforeGetListHarness
*/
public function testBeforeGetList($query, $expectedQuery, $expectedParams)
{
$this->queryBuilder->select('o.*')->from('posts', 'o');
$request = new Request($query);
$queryBuilder = $this->postDecorator->beforeGetList($this->queryBuilder, $request);
$this->assertEquals($expectedQuery, (string) $queryBuilder);
$this->assertEquals($expectedParams, $queryBuilder->getParameters());
}
protected function setUp()
{
if ($this->isDoctrineDbalNotAvailable()) {
$this->markTestSkipped('Doctrine DBAL is not available');
}
$conn = $this->getConnection();
$this->createSchema($conn);
$this->insertData($conn);
$this->qb = new QueryBuilder($conn);
$this->qb->select('p.*')->from('posts', 'p');
}
public function testPaginationWithDoctrineDbalAdapter()
{
$cqb = clone $this->qb;
$records = $this->qb->select('*')->from('sample', 'sample');
$cqb->select('*')->from('sample', 'sample')->setMaxResults(20)->setFirstResult(60);
$expected = $cqb->execute()->fetchAll();
/*var_dump($this->qb->execute()->fetchAll());
exit;*/
$paginatorClass = new Paginator();
$paginator = $paginatorClass->page(4)->perPage(20)->make($records);
$this->assertEquals(90, $paginator->total(), 'Failed asserting pagination total.');
$this->assertEquals($expected, $paginator->records(), 'Failed asserting pagination records.');
}
/**
* @param QueryBuilder $qb
* @param callable|null $countQueryBuilderModifier
*/
public function __construct(QueryBuilder $qb, callable $countQueryBuilderModifier = null)
{
$this->qb = $qb;
$this->countQueryBuilderModifier = $countQueryBuilderModifier ?: function (QueryBuilder $qb) {
return $qb->select('COUNT(*)');
};
}
/**
* @test
*/
public function selectDoesNotQuoteStarPlaceholder()
{
$this->connection->quoteIdentifier('aField')->shouldBeCalled()->willReturnArgument(0);
$this->connection->quoteIdentifier('*')->shouldNotBeCalled();
$this->concreteQueryBuilder->select(Argument::exact('aField'), Argument::exact('*'))->shouldBeCalled()->willReturn($this->subject);
$this->subject->select('aField', '*');
}
/**
* Build the count of filtered query
*
* @return QueryBuilder
*/
public function getFilteredCountQuery()
{
$this->buildFilteredQuery();
$this->currentQueryBuilder->select('COUNT(DISTINCT ' . $this->getDistinct() . ')');
$this->currentQueryBuilder->resetQueryPart('orderBy');
return $this->currentQueryBuilder;
}
public function testSetQueryBuilder02()
{
$contain = $this->testConstruct02();
$queryBuilder = new QueryBuilder($this->getConnection());
$queryBuilder->select('*')->from('table', 't');
$contain->setQueryBuilder($queryBuilder);
return $contain;
}
protected function getDataProvider()
{
if (!$this->provider) {
$builder = new QueryBuilder(Connection::get());
$builder->select('*')->from('test_users');
$this->provider = new DoctrineDataProvider($builder);
}
return $this->provider;
}
public function testItShouldWorksWithParametersInTheOrX()
{
$qb = new QueryBuilder($this->connection);
$qb->select('p.id')->from('posts', 'p')->setParameter('status', 'odd');
$aclFilter = new DoctrineDbalAclFilter($qb);
$aclFilter->setAclSchema($this->aclSchema);
$aclFilter->apply('p', 'id', 'post-', ['user-1'], 1, ['status = :status']);
$this->assertEquals(['status' => 'odd', 'acl_prefix' => 'post-', 'acl_identifiers' => ['user-1'], 'acl_mask' => 1], $qb->getParameters());
}
/**
* Load user by username
*
* @param string $username
* @return User
* @throws \Exception
*/
public function loadUserByUsername($username)
{
$qb = new QueryBuilder($this->db);
$stmt = $qb->select('username, password, roles')->from('users', 'u')->where('u.username = :username')->andWhere('u.active = 1')->setParameter('username', $username)->execute();
if (!($user = $stmt->fetchObject())) {
throw new \Exception(sprintf('User "%s" not found.', $username));
}
return new User($user->username, $user->password, explode(',', $user->roles), true, true, true, true);
}
public function setUp()
{
$builder = new QueryBuilder(Connection::get());
$builder->select('*')->from('test_users');
$this->data = $builder->execute()->fetchAll(\PDO::FETCH_OBJ);
$this->operations = new OperationCollection();
$this->service = new DoctrineProcessingService(new DoctrineProcessorResolver(), $this->operations, $builder);
$this->totalCount = (new QueryBuilder(Connection::get()))->select('count(*)')->from('test_users')->execute()->fetchColumn();
self::assertTrue($this->totalCount > 0);
}
/**
* Load roles at RBAC creation.
*
* @param Event $e
* @return array
*/
public function loadRoles(Event $e)
{
$builder = new QueryBuilder($this->connection);
$options = $this->options;
$builder->select("role.{$options->getNameColumn()} AS name, parent.{$options->getNameColumn()} AS parent")->from($options->getTable(), 'role')->leftJoin('role', $options->getTable(), 'parent', "role.{$options->getJoinColumn()} = parent.{$options->getIdColumn()}");
$result = $builder->execute();
$roles = array();
foreach ($result as $row) {
$parentName = isset($row['parent']) ? $row['parent'] : 0;
unset($row['parent']);
$roles[$parentName][] = $row['name'];
}
$this->recursiveRoles($e->getRbac(), $roles);
}
/**
* @test
*/
function shouldPaginateSimpleDoctrineQuery()
{
$this->populate();
$p = new Paginator();
$qb = new QueryBuilder($this->em->getConnection());
$qb->select('*')->from('Article', 'a');
$view = $p->paginate($qb, 1, 2);
$this->assertEquals(1, $view->getCurrentPageNumber());
$this->assertEquals(2, $view->getItemNumberPerPage());
$this->assertEquals(4, $view->getTotalItemCount());
$items = $view->getItems();
$this->assertEquals(2, count($items));
$this->assertEquals('summer', $items[0]['title']);
$this->assertEquals('winter', $items[1]['title']);
}
/**
* 初始化数据构造器
*
* @param int $type 要执行的SQL类型
* @return $this
*/
protected function _build($type)
{
switch ($type) {
case Db::SELECT:
$this->_dbBuilder = $this->_db->createQueryBuilder()->select();
break;
case Db::UPDATE:
$this->_dbBuilder = $this->_db->createQueryBuilder()->update($this->_tableName, $this->_objectName);
break;
case Db::DELETE:
$this->_dbBuilder = $this->_db->createQueryBuilder()->delete($this->_tableName);
}
$selectList = [];
foreach ($this->_dbPending as $method) {
$name = $method['name'];
$args = $method['args'];
// 如果是select，那么暂时先不执行
if ($name == 'select') {
$selectList = Arr::merge($selectList, $args);
continue;
}
$this->_dbApplied[$name] = $name;
call_user_func_array([$this->_dbBuilder, $name], $args);
}
$this->_dbBuilder->select($selectList);
return $this;
}
public function testCreatePositionalParameter()
{
$qb = new QueryBuilder($this->conn);
$qb->select('u.*')->from('users', 'u')->where($qb->expr()->eq('u.name', $qb->createPositionalParameter(10, \PDO::PARAM_INT)));
$this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string) $qb);
$this->assertEquals(10, $qb->getParameter(1));
}
/**
* @group DBAL-172
*/
public function testSelectFromMasterWithWhereOnJoinedTables()
{
$qb = new QueryBuilder($this->conn);
$qb->select('COUNT(DISTINCT news.id)')->from('newspages', 'news')->innerJoin('news', 'nodeversion', 'nv', "nv.refId = news.id AND nv.refEntityname='Entity\\News'")->innerJoin('nv', 'nodetranslation', 'nt', 'nv.nodetranslation = nt.id')->innerJoin('nt', 'node', 'n', 'nt.node = n.id')->where('nt.lang = ?')->andWhere('n.deleted = 0');
$this->assertEquals("SELECT COUNT(DISTINCT news.id) FROM newspages news INNER JOIN nodeversion nv ON nv.refId = news.id AND nv.refEntityname='Entity\\News' INNER JOIN nodetranslation nt ON nv.nodetranslation = nt.id INNER JOIN node n ON nt.node = n.id WHERE (nt.lang = ?) AND (n.deleted = 0)", $qb->getSQL());
}
/**
* Load the requested resources into RBAC.
*
* @param Rbac $rbac
* @param string $role
* @param string|null $permission
* @return \Doctrine\DBAL\Query\QueryBuilder
*/
protected function load($rbac, $role, $permission = null)
{
$options = $this->options;
$builder = new QueryBuilder($this->connection);
// Role always present
$builder->select('node.name')->from($options->getRoleTable(), 'node')->from($options->getRoleTable(), 'parent')->where('node.lft BETWEEN parent.lft AND parent.rgt')->andWhere('parent.name = :role')->orderBy('node.lft');
$builder->setParameter('role', $role);
// Permission optional
if ($permission) {
$builder->addSelect('permission.name AS permission')->leftJoin('node', 'role_permission', 'rp', 'node.id = rp.role_id')->leftJoin('node', 'permission', 'permission', 'rp.permission_id = permission.id')->andWhere('(permission.name = :permission OR permission.name IS NULL)');
$builder->setParameter('permission', $permission);
}
$parent = null;
foreach ($builder->execute() as $row) {
if ($parent) {
if (!$rbac->hasRole($row['name'])) {
$rbac->getRole($parent)->addChild($row['name']);
}
} elseif (!$rbac->hasRole($row['name'])) {
$rbac->addRole($row['name']);
}
if ($permission) {
if ($row['permission']) {
$rbac->getRole($row['name'])->addPermission($row['permission']);
}
}
$parent = $row['name'];
}
return $builder;
}
/**
* @param int[] $expected
* @param string $permission
* @param UserInterface $user
* @param string[] $orX
* @param string $where
*/
private function verify(array $expected, $permission, UserInterface $user = null, array $orX = [], $where = null)
{
$fails = [];
$permission = strtoupper($permission);
$DBALQueryBuilder = new DBALQueryBuilder($this->connection);
$DBALQueryBuilder->select('*')->from('posts', 'p');
if (null !== $where) {
$DBALQueryBuilder->where($where);
}
$this->aclFilter->apply($DBALQueryBuilder, $permission, 'Nuxia\\AclBundle\\Tests\\Model\\PostObject', 'p.id', $user, $orX);
try {
$this->assertEquals($expected, $this->getPostIds($DBALQueryBuilder), 'DBALQueryBuilder failed');
} catch (\PHPUnit_Framework_ExpectationFailedException $e) {
$fails[] = $e;
}
$ORMQueryBuilder = $this->em->createQueryBuilder();
$ORMQueryBuilder->select('p')->from('Nuxia\\AclBundle\\Tests\\Model\\PostObject', 'p');
if (null !== $where) {
$ORMQueryBuilder->where($where);
}
$cloneORMQueryBuilder = clone $ORMQueryBuilder;
$query = $this->aclFilter->apply($ORMQueryBuilder, $permission, 'Nuxia\\AclBundle\\Tests\\Model\\PostObject', 'p.id', $user, $orX);
try {
$this->assertEquals($expected, $this->getPostIds($query), 'ORMQueryBuilder failed');
} catch (\PHPUnit_Framework_ExpectationFailedException $e) {
$fails[] = $e;
}
$query = $this->aclFilter->apply($cloneORMQueryBuilder->getQuery(), $permission, 'Nuxia\\AclBundle\\Tests\\Model\\PostObject', 'p.id', $user, $orX);
try {
$this->assertEquals($expected, $this->getPostIds($query), 'ORMQuery failed');
} catch (\PHPUnit_Framework_ExpectationFailedException $e) {
$fails[] = $e;
}
if (!empty($fails)) {
$messages = [];
/** @var \PHPUnit_Framework_ExpectationFailedException $e */
foreach ($fails as $e) {
$messages[] = $e->getMessage() . $e->getComparisonFailure()->getDiff();
}
$this->fail(implode(PHP_EOL, $messages));
}
}
/**
* Get list of referers ordered by it's count
*
* @param \Doctrine\DBAL\Query\QueryBuilder $query
* @param int $limit
* @param int $offset
* @param string $column
* @param string $as
*
* @return array
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getMostVisited($query, $limit = 10, $offset = 0, $column = 'p.hits', $as = '')
{
if ($as) {
$as = ' as "' . $as . '"';
}
$query->select('p.title, p.id, ' . $column . $as)->groupBy('p.id, p.title, ' . $column)->orderBy($column, 'DESC')->setMaxResults($limit)->setFirstResult($offset);
return $query->execute()->fetchAll();
}
/**
* Specifies an item that is to be returned in the query result.
* Replaces any previously specified selections, if any.
*
* <code>
* $qb = $conn->getQueryBuilder()
* ->select('u.id', 'p.id')
* ->from('users', 'u')
* ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');
* </code>
*
* @param mixed $select The selection expressions.
*
* @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
*/
public function select($select = null)
{
$selects = is_array($select) ? $select : func_get_args();
$this->queryBuilder->select($this->helper->quoteColumnNames($selects));
return $this;
}
/**
* Calculates the search tolerance and adds an where condition
* to the query.
*
* @param QueryBuilder $query
*/
private function addToleranceCondition(QueryBuilder $query)
{
$query->select("MAX(" . $this->getRelevanceSelection() . ")");
/**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
$statement = $query->execute();
$highestRanking = $statement->fetch(\PDO::FETCH_COLUMN);
//calculates the tolerance limit
$distance = $this->config->get('fuzzySearchMinDistancenTop', 20);
if ($highestRanking && $distance) {
$toleranceLimit = $highestRanking / 100 * $distance;
$query->andWhere('(' . $this->getRelevanceSelection() . ') > ' . $toleranceLimit);
}
}
/**
* Specifies items that are to be returned in the query result.
* Replaces any previously specified selections, if any.
* This should only be used for literal SQL expressions as no
* quoting/escaping of any kind will be performed on the items.
*
* @param string[] $selects Literal SQL expressions to be selected. Warning: No quoting will be done!
* @return QueryBuilder This QueryBuilder instance.
*/
public function selectLiteral(string ...$selects) : QueryBuilder
{
$this->concreteQueryBuilder->select(...$selects);
return $this;
}
/**
* Specifies items that are to be returned in the query result.
* Replaces any previously specified selections, if any.
*
*
* @param string[] $selects
* @return QueryBuilder This QueryBuilder instance.
*/
public function select(string ...$selects) : QueryBuilder
{
$this->concreteQueryBuilder->select(...$this->quoteIdentifiersForSelect($selects));
return $this;
}
/**
* Modifies the passed DBAL query builder object to calculate
* the total count.
*
* @param QueryBuilder $builder
* @return $this
*/
private function addTotalCountSelect(QueryBuilder $builder)
{
$select = $builder->getQueryPart('select');
$select[0] = ' SQL_CALC_FOUND_ROWS ' . $select[0];
$builder->select($select);
return $this;
}
/**
* Get list of referers ordered by it's count
*
* @param \Doctrine\DBAL\Query\QueryBuilder $query
* @param int $limit
* @param int $offset
*
* @return array
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getReferers($query, $limit = 10, $offset = 0)
{
$query->select('h.referer, count(h.referer) as sessions')->groupBy('h.referer')->orderBy('sessions', 'DESC')->setMaxResults($limit)->setFirstResult($offset);
return $query->execute()->fetchAll();
}
/**
* @group DBAL-1137
*/
public function testJoinWithNonUniqueAliasThrowsException()
{
$qb = new QueryBuilder($this->conn);
$qb->select('a.id')->from('table_a', 'a')->join('a', 'table_b', 'a', 'a.fk_b = a.id');
$this->setExpectedException('Doctrine\\DBAL\\Query\\QueryException', "The given alias 'a' is not unique in FROM and JOIN clause table. The currently registered aliases are: a.");
$qb->getSQL();
}
public function getLogs()
{
$queryBuilder = new QueryBuilder($this->db);
$queryBuilder->select('*')->from('logs', 'l')->where('l.user_id = :id_user')->setParameter(':id_user', $this->id_user)->addOrderBy('l.id', 'ASC');
return $queryBuilder->execute()->fetchAll();
}
/**
* Modify the query to count how many rows is between a range of date diff in seconds
*
* @param QueryBuilder $query
* @param string $dateColumn1
* @param string $dateColumn2
* @param integer $startSecond
* @param integer $endSecond
* @param array $filters will be added to where claues
* @param string $tablePrefix
*/
public function modifyCountDateDiffQuery(QueryBuilder &$query, $dateColumn1, $dateColumn2, $startSecond = 0, $endSecond = 60, $tablePrefix = 't')
{
$query->select('COUNT(' . $tablePrefix . '.' . $dateColumn1 . ') AS count');
$query->where('TIMESTAMPDIFF(SECOND, ' . $tablePrefix . '.' . $dateColumn1 . ', ' . $tablePrefix . '.' . $dateColumn2 . ') >= :startSecond');
$query->andWhere('TIMESTAMPDIFF(SECOND, ' . $tablePrefix . '.' . $dateColumn1 . ', ' . $tablePrefix . '.' . $dateColumn2 . ') < :endSecond');
$query->setParameter('startSecond', $startSecond);
$query->setParameter('endSecond', $endSecond);
}
/**
* @param QueryBuilder $queryBuilder
*/
public function __construct(QueryBuilder $queryBuilder)
{
$this->queryBuilder = $queryBuilder;
$this->queryBuilder->select('*');
}
public function getCommandeByUser($id)
{
$queryBuilder = new QueryBuilder($this->db);
$queryBuilder->select('c.id', 'c.id_etat', 'c.user_id', 'c.prix', 'c.date_achat', 'u.email', 'u.key_activation', 'u.country', 'u.code_postal', 'u.city', 'u.adress', 'u.phone_number', 'u.first_name', 'u.last_name', 'c.date_expedition')->from('commandes', 'c')->join('c', 'users', 'u', 'c.user_id = u.id')->where('user_id = :id')->setParameter(':id', $id);
return $queryBuilder->execute()->fetch();
}
