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