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