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