/**
* @test
*/
public function should_create_complex_query()
{
$specification = $this->userSpecificationFactory->status('approved')->orX($this->userSpecificationFactory->status('declined')->andX($this->userSpecificationFactory->name('John')));
$this->queryBuilder->select('user')->from('User', 'user')->where($specification->expr())->setParameters($specification->parameters());
$this->assertEquals(['status_1' => 'approved', 'status_2' => 'declined', 'name_3' => 'John'], $specification->parameters());
$this->assertEquals('SELECT user FROM User user WHERE user.status = :status_1 OR (user.status = :status_2 AND user.name = :name_3)', $this->queryBuilder->getDQL());
}
private function getNumRowsSubQuery()
{
if (!$this->numRowsSubQuery) {
$this->numRowsSubQuery = $this->createNumRowsSubQuery();
}
return $this->numRowsSubQuery->getDQL();
}
/**
* @param QueryBuilder $query
* @param QueryBuilder $subQuery
* @return QueryBuilder $query with query's and subQuery's parameters.
*/
private function andWherePupilNotIn(QueryBuilder $query, QueryBuilder $subQuery, $EleveQueryAlias)
{
$query->andWhere($query->expr()->notIn($EleveQueryAlias . ".id", $subQuery->getDQL()));
/**
* @var Parameter $param
*/
foreach ($subQuery->getParameters() as $param) {
$query->setParameter($param->getName(), $param->getValue(), $param->getType());
}
return $query;
}
/**
* We update the query to count, get ids and fetch data, so, we can lost expected query builder parameters,
* and we have to remove them
*
* @param QueryBuilder $qb
*/
public static function removeExtraParameters(QueryBuilder $qb)
{
$parameters = $qb->getParameters();
$dql = $qb->getDQL();
foreach ($parameters as $parameter) {
if (strpos($dql, ':' . $parameter->getName()) === false) {
$parameters->removeElement($parameter);
}
}
$qb->setParameters($parameters);
}
/**
* Removes unused parameters from query builder
*
* @param QueryBuilder $qb
*/
public function fixUnusedParameters(QueryBuilder $qb)
{
$dql = $qb->getDQL();
$usedParameters = [];
/** @var $parameter \Doctrine\ORM\Query\Parameter */
foreach ($qb->getParameters() as $parameter) {
if ($this->dqlContainsParameter($dql, $parameter->getName())) {
$usedParameters[$parameter->getName()] = $parameter->getValue();
}
}
$qb->setParameters($usedParameters);
}
/**
* @param QueryBuilder $qb
*
* @return string
*/
public function getRawSQLFromORMQueryBuilder(QueryBuilder $qb)
{
$sql = $qb->getDQL();
$parameters = $qb->getParameters();
/** @var Query\Parameter $param */
foreach ($parameters as $param) {
if (is_array($param->getValue()) && !empty($param->getValue())) {
$sql = str_replace(':' . $param->getName(), "'" . implode("', '", $param->getValue()) . "'", $sql);
} else {
$sql = str_replace(':' . $param->getName(), "'" . $param->getValue() . "'", $sql);
}
}
return $sql;
}
/**
* Constructor.
*
* @access public
* @param QueryBuilder $oQuery
* @param string $sMainTableAlias
* @param integer $iResultsPerPage
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
* @internal param string $sGetName
* @since 1.0.0-alpha
* @version 1.0.0-alpha
*/
public function __construct(QueryBuilder &$oQuery, $sMainTableAlias, $iResultsPerPage = 15)
{
Log::insert('Pager helper initialized for query: "' . $oQuery->getDQL() . '".');
$iPageParamValue = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
if (!empty($iPageParamValue)) {
$this->iPage = $iPageParamValue;
}
$oQueryAmount = clone $oQuery;
$aAmount = $oQueryAmount->select('COUNT(' . $sMainTableAlias . ')')->getQuery()->getSingleResult();
$this->iAmount = (int) $aAmount[1];
$this->iResultsPerPage = (int) $iResultsPerPage;
$iFirstResult = $this->iResultsPerPage * ($this->iPage - 1);
$oQuery->setMaxResults($this->iResultsPerPage)->setFirstResult($iFirstResult);
}
/**
* @dataProvider applyDataProvider
*/
public function testApply($platform, $value, $comparisonType, $expectedDql, $expectedParams)
{
$paramCounter = 0;
$em = $this->getMock('Doctrine\\ORM\\EntityManagerInterface');
$qb = new QueryBuilder($em);
$ds = $this->getMock('Oro\\Bundle\\FilterBundle\\Datasource\\Orm\\OrmFilterDatasourceAdapter', ['generateParameterName', 'getDatabasePlatform'], [$qb]);
$ds->expects($this->any())->method('generateParameterName')->willReturnCallback(function () use(&$paramCounter) {
return sprintf('param%s', ++$paramCounter);
});
$ds->expects($this->any())->method('getDatabasePlatform')->willReturn($platform);
$data = ['type' => $comparisonType, 'value' => $value];
$em->expects($this->any())->method('getExpressionBuilder')->willReturn(new Query\Expr());
$qb->select('j')->from('TestEntity', 'j');
$this->filter->apply($ds, $data);
$this->assertEquals($expectedDql, $qb->getDQL());
$params = [];
/** @var Query\Parameter $param */
foreach ($qb->getParameters() as $param) {
$params[$param->getName()] = $param->getValue();
}
$this->assertEquals($expectedParams, $params);
}
/**
* @expectedException LogicException
* @expectedExceptionMessage Computed conditions cannot be mixed with uncomputed.
*/
public function testComputedWithUnComputedRestrictionsTogetherShouldReturnExceptionWhenRestrictionsAreMixed()
{
$qb = new QueryBuilder($this->getTestEntityManager());
$qb->select(['u.status, COUNT(u.id)'])->from('Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser', 'u')->groupBy('u.status');
$ds = new GroupingOrmFilterDatasourceAdapter($qb);
$ds->addRestriction($qb->expr()->eq('u.id', '1'), FilterUtility::CONDITION_AND);
$ds->addRestriction($qb->expr()->eq('COUNT(u.id)', '2'), FilterUtility::CONDITION_AND, true);
$ds->addRestriction($qb->expr()->eq('COUNT(u.id)', '3'), FilterUtility::CONDITION_OR);
$ds->applyRestrictions();
$this->assertEquals('SELECT u.status, COUNT(u.id) FROM Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser u ' . 'WHERE u.id = 1 AND u.id = 2 ' . 'GROUP BY u.status ' . 'HAVING COUNT(u.id) = 3 OR COUNT(u.id) = 4', $qb->getDQL());
}
/**
* Add the given subquery as a new clause in the main query's
* WHERE clause using and an AND or OR operator.
* <p>
* Important: The method *APPENDS* the suquery as a new restriction in the
* WHERE clause, forming a logical AND/OR conjunction with any
* **PREVIOUSLY** specified restrictions in the WHERE clause.
*
* @param QueryBuilder $sQ
* @param String $operator AND, OR or NOT
*/
private function addSubQueryToMainQuery($sQ, $operator)
{
//Get the query that was passed at initialization
$qb = $this->getQB();
// QueryBuilder orWhere() + addWhere() *APPENDS* the specified restrictions to the
// query results, forming a logical AND/OR conjunction with any **PREVIOUSLY**
// specified restrictions! Therefore, the order of AND/OR in URL query string
// is significant (we iterate subqueries from left to right, each time
// adding the new conjunction to the previous restrictions.
switch ($operator) {
case 'OR':
//OR
$qb->orWhere($qb->expr()->in($this->ltype, $sQ->getDQL()));
break;
case 'AND':
//AND
$qb->andWhere($qb->expr()->in($this->ltype, $sQ->getDQL()));
break;
case 'NOT':
//NOT
$qb->andWhere($qb->expr()->notIn($this->ltype, $sQ->getDQL()));
break;
}
/*finally replace original query with updated query ready for fetching
*by the calling class*/
$this->setQB($qb);
}
protected function applySorters(QueryBuilder $qb, array $sorters, callable $handler = null)
{
foreach ($sorters as $key => $direction) {
// custom handling
if (null !== $handler) {
$dql = $qb->getDQL();
// will check for difference
call_user_func_array($handler, [$qb, $key, $direction]);
if ($qb->getDQL() !== $dql) {
continue;
// custom sorter handler has handled the parameter
}
}
$qb->addOrderBy($key, in_array(strtoupper($direction), ['ASC', 'DESC']) ? $direction : 'ASC');
}
}
public function testComplexExpr()
{
$qb = new QueryBuilder($this->getTestEntityManager());
$qb->select(['u.id'])->from('Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser', 'u')->where('u.id = 0');
$ds = new GroupingOrmFilterDatasourceAdapter($qb);
// src: (1 AND ((2 AND (3 OR 4)) OR (5) OR (6 AND 7)) AND 8)
// dest: (1 AND ((2 AND (3 OR 4)) OR 5 OR (6 AND 7)) AND 8)
$ds->addRestriction($qb->expr()->eq('u.name', '1'), FilterUtility::CONDITION_AND);
$ds->beginRestrictionGroup(FilterUtility::CONDITION_AND);
$ds->beginRestrictionGroup(FilterUtility::CONDITION_AND);
$ds->addRestriction($qb->expr()->eq('u.name', '2'), FilterUtility::CONDITION_AND);
$ds->beginRestrictionGroup(FilterUtility::CONDITION_AND);
$ds->addRestriction($qb->expr()->eq('u.name', '3'), FilterUtility::CONDITION_AND);
$ds->addRestriction($qb->expr()->eq('u.name', '4'), FilterUtility::CONDITION_OR);
$ds->endRestrictionGroup();
$ds->endRestrictionGroup();
$ds->beginRestrictionGroup(FilterUtility::CONDITION_OR);
$ds->addRestriction($qb->expr()->eq('u.name', '5'), FilterUtility::CONDITION_AND);
$ds->endRestrictionGroup();
$ds->beginRestrictionGroup(FilterUtility::CONDITION_OR);
$ds->addRestriction($qb->expr()->eq('u.name', '6'), FilterUtility::CONDITION_AND);
$ds->addRestriction($qb->expr()->eq('u.name', '7'), FilterUtility::CONDITION_AND);
$ds->endRestrictionGroup();
$ds->endRestrictionGroup();
$ds->addRestriction($qb->expr()->eq('u.name', '8'), FilterUtility::CONDITION_AND);
$ds->applyRestrictions();
$this->assertEquals('SELECT u.id FROM Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser u ' . 'WHERE u.id = 0 AND ' . '(u.name = 1 AND ' . '((u.name = 2 AND (u.name = 3 OR u.name = 4)) OR u.name = 5 OR (u.name = 6 AND u.name = 7)) AND ' . 'u.name = 8)', $qb->getDQL());
}
/**
* @param QueryBuilder $pqb
* @param QueryBuilder $aqb
*/
private function getPeopleNotInTable($pqb, $aqb)
{
$pqb->andWhere($pqb->expr()->notIn('p.id', $aqb->getDQL()));
}
