PHP Query::setDQL方法代码示例

 /**
  * Return users indexed by id
  *
  * @param Query  $query
  * @param string $fieldName
  *
  * @return \Doctrine\ORM\AbstractQuery
  */
 public function setIndexBy(Query $query, $fieldName)
 {
     if (!strpos($query->getDQL(), 'WHERE')) {
         return $query->setDQL($query->getDQL() . ' INDEX BY ' . $fieldName);
     }
     return $query->setDQL(str_replace('WHERE', 'INDEX BY ' . $fieldName . ' WHERE', $query->getDQL()));
 }

 /**
  * @param string|array $columns
  * @throws InvalidStateException
  * @return ResultSet
  */
 public function applySorting($columns)
 {
     $this->updating();
     $sorting = [];
     foreach (is_array($columns) ? $columns : func_get_args() as $name => $column) {
         if (!is_numeric($name)) {
             $column = $name . ' ' . $column;
         }
         if (!preg_match('~\\s+(DESC|ASC)\\s*\\z~i', $column = trim($column))) {
             $column .= ' ASC';
         }
         $sorting[] = $column;
     }
     if ($sorting) {
         $dql = Strings::normalize($this->query->getDQL());
         if (!preg_match('~^(.+)\\s+(ORDER BY\\s+((?!FROM|WHERE|ORDER\\s+BY|GROUP\\sBY|JOIN).)*)\\z~si', $dql, $m)) {
             $dql .= ' ORDER BY ';
         } else {
             $dql .= ', ';
         }
         $this->query->setDQL($dql . implode(', ', $sorting));
     }
     $this->iterator = NULL;
     return $this;
 }

 /**
  * Updates the configured query object with the where-clause and query-hints.
  *
  * @param string $prependQuery Prepends this string to the where-clause
  *                             (" WHERE " or " AND " for example)
  *
  * @return self
  */
 public function updateQuery($prependQuery = ' WHERE ')
 {
     $whereCase = $this->getWhereClause($prependQuery);
     if ('' !== $whereCase) {
         $this->query->setDQL($this->query->getDQL() . $whereCase);
         $this->query->setHint($this->getQueryHintName(), $this->getQueryHintValue());
     }
     return $this;
 }

 /**
  * @dataProvider functionsDataProvider
  * @param array $functions
  * @param string $dql
  * @param string $sql
  * @param string $expectedResult
  */
 public function testDqlFunction(array $functions, $dql, $sql, $expectedResult)
 {
     $configuration = $this->entityManager->getConfiguration();
     foreach ($functions as $function) {
         $this->registerDqlFunction($function['type'], $function['name'], $function['className'], $configuration);
     }
     $query = new Query($this->entityManager);
     $query->setDQL($dql);
     $this->assertEquals($sql, $query->getSQL(), sprintf('Unexpected SQL for "%s"', $dql));
     $result = $query->getArrayResult();
     $this->assertNotEmpty($result);
     $this->assertEquals($expectedResult, array_values(array_shift($result)), sprintf('Unexpected result for "%s"', $dql));
 }

 /**
  * @param string $dql
  * @param string $expectedCountQuery
  * @param array  $sqlParameters
  * @param array  $types
  * @param array  $queryParameters
  *
  * @dataProvider getCountDataProvider
  */
 public function testCalculateCount($dql, $expectedCountQuery, array $sqlParameters = [], array $types = [], array $queryParameters = [])
 {
     /** @var $entityManager EntityManager|\PHPUnit_Framework_MockObject_MockObject */
     /** @var $connection Connection|\PHPUnit_Framework_MockObject_MockObject */
     /** @var $statement Statement|\PHPUnit_Framework_MockObject_MockObject */
     list($entityManager, $connection, $statement) = $this->prepareMocks();
     $query = new Query($entityManager);
     $query->setDQL($dql);
     $query->setParameters($queryParameters);
     $connection->expects($this->once())->method('executeQuery')->with($expectedCountQuery, $sqlParameters, $types)->will($this->returnValue($statement));
     $statement->expects($this->any())->method('fetch')->will($this->onConsecutiveCalls(['sclr_0' => self::TEST_COUNT], false));
     $statement->expects($this->any())->method('fetchColumn')->will($this->returnValue(self::TEST_COUNT));
     $this->assertEquals(self::TEST_COUNT, QueryCountCalculator::calculateCount($query));
 }

 /**
  * @param string $dql
  * @param array $sqlParameters
  * @param array $types
  * @param array $queryParameters
  *
  * @dataProvider getCountDataProvider
  */
 public function testCalculateCount($dql, array $sqlParameters, array $types, array $queryParameters = array())
 {
     /** @var $entityManager EntityManager|\PHPUnit_Framework_MockObject_MockObject */
     /** @var $connection Connection|\PHPUnit_Framework_MockObject_MockObject */
     /** @var $statement Statement|\PHPUnit_Framework_MockObject_MockObject */
     list($entityManager, $connection, $statement) = $this->prepareMocks();
     $query = new Query($entityManager);
     $query->setDQL($dql);
     $query->setParameters($queryParameters);
     $expectedSql = 'SELECT COUNT(*) FROM (' . $query->getSQL() . ') AS e';
     $connection->expects($this->once())->method('executeQuery')->with($expectedSql, $sqlParameters, $types)->will($this->returnValue($statement));
     $statement->expects($this->once())->method('fetchColumn')->with()->will($this->returnValue(self::TEST_COUNT));
     $this->assertEquals(self::TEST_COUNT, QueryCountCalculator::calculateCount($query));
 }

 /**
  * @param string $dql
  * @param string $expectedSql
  * @param array  $exception
  *
  * @dataProvider parseDataProvider
  */
 public function testParse($dql, $expectedSql, array $exception)
 {
     if (!empty($exception)) {
         list($exception, $message) = $exception;
         $this->setExpectedException($exception, $message);
     }
     $em = $this->getMockBuilder('Doctrine\\ORM\\EntityManager')->disableOriginalConstructor()->getMock();
     $query = new Query($em);
     $query->setDQL($dql);
     $parser = new Parser($query);
     $this->function->parse($parser);
     $sqlWalker = $this->getMockBuilder('Doctrine\\ORM\\Query\\SqlWalker')->disableOriginalConstructor()->getMock();
     $sqlWalker->expects($this->any())->method('walkPathExpression')->will($this->returnValue('field'));
     $sqlWalker->expects($this->any())->method('walkOrderByClause')->will($this->returnValue('ORDER BY orderField'));
     $sqlWalker->expects($this->any())->method('walkStringPrimary')->will($this->returnValue('\', \''));
     $this->assertEquals($expectedSql, $this->function->getSql($sqlWalker));
 }

 /**
  * @dataProvider hintConfigProvider
  */
 public function testHints($hints, $expected)
 {
     $entityClass = 'Oro\\Bundle\\DataGridBundle\\Tests\\Unit\\DataFixtures\\Stub\\SomeClass';
     $configs['query'] = ['select' => ['t'], 'from' => [['table' => 'Test', 'alias' => 't']]];
     if (null !== $hints) {
         $configs['hints'] = $hints;
     }
     $this->prepareEntityManagerForTestHints($entityClass);
     $query = new Query($this->em);
     $query->setDQL("SELECT t FROM {$entityClass} t");
     $this->em->expects($this->any())->method('getClassMetadata')->with($entityClass)->will($this->returnValue(new ClassMetadata($entityClass)));
     $qb = $this->getMockBuilder('Doctrine\\ORM\\QueryBuilder')->setConstructorArgs([$this->em])->getMock();
     $this->em->expects($this->once())->method('createQueryBuilder')->will($this->returnValue($qb));
     $qb->expects($this->once())->method('getQuery')->will($this->returnValue($query));
     $datagrid = $this->getMock('Oro\\Bundle\\DataGridBundle\\Datagrid\\DatagridInterface');
     $this->datasource->process($datagrid, $configs);
     $this->datasource->getResults();
     $this->assertEquals($expected, $query->getHints());
 }

 /**
  * @dataProvider functionsDataProvider
  * @param string $type
  * @param string $functionName
  * @param string $functionClass
  * @param string $dql
  * @param string $sql
  * @param string $expectedResult
  */
 public function testDateFunction($type, $functionName, $functionClass, $dql, $sql, $expectedResult)
 {
     $configuration = $this->entityManager->getConfiguration();
     switch ($type) {
         case 'datetime':
             $configuration->addCustomDatetimeFunction($functionName, $functionClass);
             break;
         case 'numeric':
             $configuration->addCustomNumericFunction($functionName, $functionClass);
             break;
         case 'string':
         default:
             $configuration->addCustomStringFunction($functionName, $functionClass);
     }
     $query = new Query($this->entityManager);
     $query->setDQL($dql);
     $this->assertEquals($sql, $query->getSQL(), sprintf('Unexpected SQL for "%s"', $dql));
     $result = $query->getArrayResult();
     $this->assertNotEmpty($result);
     $this->assertEquals($expectedResult, array_values(array_shift($result)), sprintf('Unexpected result for "%s"', $dql));
 }

 /**
  * @dataProvider functionsDataProvider
  * @param array $functions
  * @param string $dql
  * @param string $sql
  * @param string $expectedResult
  */
 public function testDqlFunction(array $functions, $dql, $sql, $expectedResult)
 {
     $configuration = $this->entityManager->getConfiguration();
     foreach ($functions as $function) {
         $this->registerDqlFunction($function['type'], $function['name'], $function['className'], $configuration);
     }
     $query = new Query($this->entityManager);
     $query->setDQL($dql);
     if (is_array($sql)) {
         $constraints = array();
         foreach ($sql as $sqlVariant) {
             $constraints[] = $this->equalTo($sqlVariant);
         }
         $constraint = new \PHPUnit_Framework_Constraint_Or();
         $constraint->setConstraints($constraints);
         $this->assertThat($query->getSQL(), $constraint);
     } else {
         $this->assertEquals($sql, $query->getSQL(), sprintf('Unexpected SQL for "%s"', $dql));
     }
     $result = $query->getArrayResult();
     $this->assertNotEmpty($result);
     $this->assertEquals($expectedResult, array_values(array_shift($result)), sprintf('Unexpected result for "%s"', $dql));
 }

 /**
  * {@inheritdoc}
  */
 public function findCollectionBreadcrumbById($id)
 {
     try {
         $sql = sprintf('SELECT n, collectionMeta, defaultMeta
              FROM %s AS p,
                   %s AS n
                     LEFT JOIN n.meta AS collectionMeta
                     LEFT JOIN n.defaultMeta AS defaultMeta
              WHERE p.id = :id AND p.lft > n.lft AND p.rgt < n.rgt
              ORDER BY n.lft', $this->_entityName, $this->_entityName);
         $query = new Query($this->_em);
         $query->setDQL($sql);
         $query->setParameter('id', $id);
         return $query->getResult();
     } catch (NoResultException $ex) {
         return [];
     }
 }

 /**
  * {@inheritDoc}
  */
 public function createQuery($dql = '')
 {
     $query = new Query($this);
     if (!empty($dql)) {
         $query->setDQL($dql);
     }
     return $query;
 }

 public function testStaticApply()
 {
     $staticSegmentStub = new Segment();
     $staticSegmentStub->setType(new SegmentType(SegmentType::TYPE_STATIC));
     $filterData = ['value' => $staticSegmentStub];
     $subquery = 'SELECT ts1.entity_id FROM OroSegmentBundle:SegmentSnapshot ts1 WHERE ts1.segmentId = :segment';
     $em = $this->getTestEntityManager();
     $qb = new QueryBuilder($em);
     $qb->select(['t1.name'])->from('OroSegmentBundle:CmsUser', 't1');
     $ds = new OrmFilterDatasourceAdapter($qb);
     $query = new Query($em);
     $query->setDQL($subquery);
     $query->setParameter('segment', $staticSegmentStub);
     $this->staticSegmentQueryBuilder->expects($this->once())->method('build')->with($staticSegmentStub)->will($this->returnValue($query));
     $this->filter->init('someName', [FilterUtility::DATA_NAME_KEY => self::TEST_FIELD_NAME]);
     $this->filter->apply($ds, $filterData);
     $expectedResult = ['SELECT t1.name FROM OroSegmentBundle:CmsUser t1 WHERE t1.id', 'IN(SELECT ts1.entity_id FROM OroSegmentBundle:SegmentSnapshot ts1 WHERE ts1.segmentId = :segment)'];
     $expectedResult = implode(' ', $expectedResult);
     $this->assertEquals($expectedResult, $ds->getQueryBuilder()->getDQL());
     $params = $ds->getQueryBuilder()->getParameters();
     $this->assertCount(1, $params, 'Should pass params to main query builder');
     $this->assertEquals($staticSegmentStub, $params[0]->getValue());
 }

 public function testSubmit()
 {
     list($queryBuilderUpdater, $paginator, $dispatcher, $entityMgr) = $this->getArguments(true);
     $queryBuilderUpdater->expects($this->once())->method('addFilterConditions');
     $query = new Query($entityMgr);
     $query->setDQL('SELECT s FROM NSFilteredPaginationBundle:Payment s');
     $paginator->expects($this->once())->method('paginate')->with($query, 1, 10)->willReturn(array());
     $formData = array('date' => '', 'amount' => '12.30', 'filter' => '');
     $filteredPagination = new FilteredPagination($paginator, $this->factory, $queryBuilderUpdater, $dispatcher);
     $session = new Session(new MockArraySessionStorage());
     $session->set(self::TEST_KEY, 'something');
     $this->assertEquals('something', $session->get(self::TEST_KEY));
     $request = new Request();
     $request->request->set('FilteredPaginationForm', $formData);
     $this->assertEquals($formData, $request->request->get('FilteredPaginationForm'));
     $request->setSession($session);
     list($form, $pagination, $redirect) = $filteredPagination->process($request, 'NS\\FilteredPaginationBundle\\Tests\\FilteredPaginationForm', $query, self::TEST_KEY);
     $this->assertInstanceOf('Symfony\\Component\\Form\\Form', $form);
     $this->assertEquals(array(), $pagination);
     $this->assertFalse($redirect);
     $this->assertEquals($formData, $request->getSession()->get(self::TEST_KEY));
 }

        /**
     * @route: blog_new
     * Tag Controller
     */
    public function newAction()
    {
        // entity manager and query builder objects
        $em = $this->getEm();


        $query = new Query($em);
        $query->setDQL(
            'SELECT p,comments
                FROM Bundle\BlogBundle\Entity\Post p
                JOIN p.comments comments
                ORDER BY p.date DESC'
        );
        $query->setMaxResults(1);
        //$query->setParameter(1, $slug);
        $posts = $query->getResult();


        return $this->render('BlogBundle:Blog:post.html.twig', array(
            'post' => $posts[0]
        ));
    }