public function testCreateDataSet_conflictingFilters()
{
$request = $this->getBaseRequest();
$this->qb->andWhere('p.id = ?0')->setParameter(0, 'Test');
$request->columnFilters[0] = 'test';
$dataSet = $this->dataSource->createDataSet($request);
$where = $this->qb->getDQLPart('where')->getParts();
$this->assertCount(2, $where);
$this->assertCount(2, $this->qb->getParameters());
}
/**
* {@inheritdoc}
*/
public function getSize()
{
if (null === $this->size) {
$qb = clone $this->queryBuilder;
$alias = current($qb->getRootAliases());
$qb->setParameters($this->queryBuilder->getParameters());
$qb->select("count({$alias})");
$this->size = (int) $qb->getQuery()->getSingleScalarResult();
}
return $this->size;
}
/**
* Clones a query.
*
* @param QueryBuilder $query The query.
*
* @return QueryBuilder The cloned query.
*/
public static function cloneQuery($query)
{
/* @var $cloneQuery QueryBuilder */
$cloneQuery = clone $query;
$cloneQuery->setParameters(clone $query->getParameters());
return $cloneQuery;
}
function it_uses_comparison_specific_dql_alias_if_passed(QueryBuilder $qb, ArrayCollection $parameters)
{
$this->beConstructedWith(Comparison::GT, 'age', 18, null);
$qb->getParameters()->willReturn($parameters);
$parameters->count()->willReturn(10);
$qb->setParameter('comparison_10', 18)->shouldBeCalled();
$this->getFilter($qb, 'x')->shouldReturn('x.age > :comparison_10');
}
public function it_ends_with_wildcard_when_using_starts_with(QueryBuilder $qb, ArrayCollection $parameters)
{
$this->beConstructedWith($this->field, $this->value, Like::STARTS_WITH, "dqlAlias");
$qb->getParameters()->willReturn($parameters);
$parameters->count()->willReturn(1);
$qb->setParameter("comparison_1", "bar%")->shouldBeCalled();
$this->match($qb, null);
}
function it_returns_expression_func_object(QueryBuilder $qb, ArrayCollection $parameters, Expr $expr)
{
$dqlAlias = 'a';
$qb->expr()->willReturn($expr);
$expr->in(sprintf('%s.%s', $dqlAlias, $this->field), ':in_10')->shouldBeCalled();
$qb->getParameters()->willReturn($parameters);
$parameters->count()->willReturn(10);
$qb->setParameter('in_10', $this->value)->shouldBeCalled();
$this->getFilter($qb, null);
}
/**
* 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);
}
/**
* @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;
}
/**
* 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);
}
/**
* Get Paginator
*
* @param integer $limit ( limit per page )
* @param array $options
* @return \Knp\Bundle\PaginatorBundle\Pagination\SlidingPagination
*/
protected function getPaginator($limit = 30, $options = array())
{
$page = $this->get('request')->query->get('page', 1);
$cnt = 0;
if ($this->get('request')->query->has('page')) {
$page = $this->get('request')->query->get('page');
$this->get('session')->set('_pager_' . $this->get('request')->get('_route'), $page);
} elseif ($this->get('session')->has('_pager_' . $this->get('request')->get('_route'))) {
$page = $this->get('session')->get('_pager_' . $this->get('request')->get('_route'));
}
if (isset($options['total_item_count']) and (int) $options['total_item_count']) {
$cnt = $options['total_item_count'];
}
$this->paginator = $this->get('knp_paginator');
if (is_array($this->query) or $this->query instanceof QueryBuilder) {
$pagination = $this->paginator->paginate($this->query, $page, $limit, $options);
} elseif ($this->query instanceof NativeQuery) {
$rsm = new ResultSetMapping();
$rsm->addScalarResult('cnt', 'cnt');
$q = strstr($this->query->getSQL(), " FROM ");
$q = "SELECT COUNT(*) cnt " . $q;
$cntQuery = $this->em->createNativeQuery($q, $rsm)->setParameters($this->query->getParameters());
try {
$cnt = $cntQuery->getSingleScalarResult();
} catch (\Doctrine\Orm\NoResultException $e) {
$cnt = 0;
}
$sql = $this->query->getSQL();
$pagination = $this->paginator->paginate(array());
$sort_name = $pagination->getPaginatorOption('sortFieldParameterName');
$sort_direction_name = $pagination->getPaginatorOption('sortDirectionParameterName');
if ($this->get('request')->query->has($sort_name) and $this->get('request')->query->has($sort_direction_name)) {
$sql .= ' ORDER BY ' . $this->get('request')->query->get($sort_name) . ' ' . $this->get('request')->query->get($sort_direction_name);
} elseif (isset($options['default_sort']) and $options['default_sort']) {
$sql .= ' ORDER BY ';
foreach ($options['default_sort'] as $field => $type) {
$sql .= $field . ' ' . $type . ',';
}
$sql = trim($sql, ',');
}
if (!isset($options['not_use_limit_offset'])) {
$offset = $limit * ($page - 1);
$this->query->setSQL($sql . ' LIMIT ' . $limit . ' OFFSET ' . $offset);
}
$pagination->setCurrentPageNumber($page);
$pagination->setItemNumberPerPage($limit);
$pagination->setTotalItemCount($cnt);
$pagination->setItems($this->query->getResult());
}
$pagination->setTemplate($this->container->getParameter('zk2_admin_panel.pagination_template'));
$pagination->setSortableTemplate($this->container->getParameter('zk2_admin_panel.sortable_template'));
return compact('pagination');
}
/**
* @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;
}
/**
* {@inherit}
*/
public function getItemsCountInCategory(CategoryInterface $category, QueryBuilder $categoryQb = null)
{
$qb = $this->em->createQueryBuilder();
$qb->select($qb->expr()->count('distinct i'));
$qb->from($this->entityName, 'i');
$qb->join('i.categories', 'node');
if (null === $categoryQb) {
$qb->where('node.id = :nodeId');
$qb->setParameter('nodeId', $category->getId());
} else {
$qb->where($categoryQb->getDqlPart('where'));
$qb->setParameters($categoryQb->getParameters());
}
return $qb->getQuery()->getSingleScalarResult();
}
/**
* Applies mass action parameters on the query builder
*
* @param QueryBuilder $qb
* @param bool $inset
* @param array $values
*/
public function applyMassActionParameters($qb, $inset, $values)
{
if (!empty($values)) {
$valueWhereCondition = $inset ? $qb->expr()->in($this->getAlias(), $values) : $qb->expr()->notIn($this->getAlias(), $values);
$qb->andWhere($valueWhereCondition);
}
if (null !== $qb->getDQLPart('where')) {
$whereParts = $qb->getDQLPart('where')->getParts();
$qb->resetDQLPart('where');
foreach ($whereParts as $part) {
if (!is_string($part) || !strpos($part, 'entityIds')) {
$qb->andWhere($part);
}
}
}
$qb->setParameters($qb->getParameters()->filter(function ($parameter) {
return $parameter->getName() !== 'entityIds';
}));
// remove limit of the query
$qb->setMaxResults(null);
}
/**
* @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);
}
/**
* @param QueryBuilder $qb
* @param Criteria $criteria
*/
public static function addCriteria(QueryBuilder $qb, Criteria $criteria)
{
$rootAlias = $qb->getRootAlias();
$visitor = new QueryExpressionVisitor($rootAlias);
if ($whereExpression = $criteria->getWhereExpression()) {
$qb->andWhere($visitor->dispatch($whereExpression));
foreach ($visitor->getParameters() as $parameter) {
$qb->getParameters()->add($parameter);
}
}
if ($criteria->getOrderings()) {
foreach ($criteria->getOrderings() as $sort => $order) {
$qb->addOrderBy($rootAlias . '.' . $sort, $order);
}
}
// Overwrite limits only if they was set in criteria
if (($firstResult = $criteria->getFirstResult()) !== null) {
$qb->setFirstResult($firstResult);
}
if (($maxResults = $criteria->getMaxResults()) !== null) {
$qb->setMaxResults($maxResults);
}
}
/**
* @internal
*/
private function incorporateOrmQueryBuilder(QueryBuilder $qb, FilterInterface $filter)
{
$criteria = $this->getFilteringCriteria($filter);
$rootAliases = $qb->getRootAliases();
$visitor = new OrmQueryExpressionVisitor($rootAliases, $this->fieldMap);
if ($whereExpression = $criteria->getWhereExpression()) {
$qb->andWhere($visitor->dispatch($whereExpression));
foreach ($visitor->getParameters() as $parameter) {
$qb->getParameters()->add($parameter);
}
}
if ($criteria->getOrderings()) {
foreach ($criteria->getOrderings() as $sort => $order) {
$qb->addOrderBy($visitor->getFieldName($sort), $order);
}
}
// Overwrite limits only if they was set in criteria
if (($firstResult = $criteria->getFirstResult()) !== null) {
$qb->setFirstResult($firstResult);
}
if (($maxResults = $criteria->getMaxResults()) !== null) {
$qb->setMaxResults($maxResults);
}
}
public function filter(QueryBuilder $queryBuilder, $metadata, $option)
{
if (isset($option['where'])) {
if ($option['where'] === 'and') {
$queryType = 'andWhere';
} elseif ($option['where'] === 'or') {
$queryType = 'orWhere';
}
}
if (!isset($queryType)) {
$queryType = 'andWhere';
}
$orX = $queryBuilder->expr()->orX();
$em = $queryBuilder->getEntityManager();
$qb = $em->createQueryBuilder();
foreach ($option['conditions'] as $condition) {
$filter = $this->getFilterManager()->get(strtolower($condition['type']), array($this->getFilterManager()));
$filter->filter($qb, $metadata, $condition);
}
$dqlParts = $qb->getDqlParts();
$orX->addMultiple($dqlParts['where']->getParts());
$queryBuilder->setParameters(new ArrayCollection(array_merge_recursive($queryBuilder->getParameters()->toArray(), $qb->getParameters()->toArray())));
$queryBuilder->{$queryType}($orX);
}
/**
* @param QueryBuilder $data_source
* @param string $primary_key
*/
public function __construct(QueryBuilder $data_source, $primary_key)
{
$this->placeholder = count($data_source->getParameters());
$this->data_source = $data_source;
$this->primary_key = $primary_key;
}
/**
* Add "filter" condition to the query builder
*
* @param \Doctrine\ORM\QueryBuilder $qb Current query builder
*
* @return \Doctrine\ORM\QueryBuilder
*/
protected function prepareOptionsAvailabilityCondition(\Doctrine\ORM\QueryBuilder $qb)
{
list($condition, $params) = $this->getOptionsAvailabilityCondition();
return isset($condition) ? $qb->andWhere($condition)->setParameters($qb->getParameters() + $params) : $qb;
}
/**
* @param QueryBuilder $queryBuilder
*
* @return string
*/
protected function generateParameterName(QueryBuilder $queryBuilder)
{
return sprintf('in_%d', count($queryBuilder->getParameters()));
}
/**
* Adds markup condition to the query builder object
*
* @param \Doctrine\ORM\QueryBuilder $qb Query builder object
* @param \XLite\Logic\Order\Modifier\Shipping $modifier Shipping order modifier
* @param integer $zoneId Zone Id
*
* @return \Doctrine\ORM\QueryBuilder
*/
protected function addMarkupCondition(\Doctrine\ORM\QueryBuilder $qb, \XLite\Logic\Order\Modifier\Shipping $modifier, $zoneId)
{
$prepareSum = array('m.markup_flat', '(m.markup_percent * :value / 100)', '(m.markup_per_item * :items)', '(m.markup_per_weight * :weight)');
return $qb->addSelect(implode(' + ', $prepareSum) . ' as markup_value')->innerJoin('m.zone', 'zone')->andWhere('m.min_weight <= :weight')->andWhere('zone.zone_id = :zoneId')->andWhere('m.max_weight >= :weight')->andWhere('m.min_total <= :total')->andWhere('m.max_total >= :total')->andWhere('m.min_items <= :items')->andWhere('m.max_items >= :items')->setParameters(array_merge($qb->getParameters(), array('zoneId' => $zoneId, 'weight' => $modifier->getWeight(), 'total' => $modifier->getSubtotal(), 'items' => $modifier->countItems(), 'value' => $modifier->getSubtotal())));
}
/**
* @param QueryBuilder $qb
* @param array $graph
* @return Expr\Andx|Expr\Orx|null
* @throws InvalidCriteriaException
*/
public function createQueryExpression(QueryBuilder $qb, array $graph = array())
{
$key = key($graph);
$expr = null;
if ($key === ResolverInterface::T_EXPR) {
$spec = key($graph[$key]);
$value = $graph[$key][$spec];
$paramIndex = count($qb->getParameters()) + 1;
list($field, $equation) = explode(' ', $spec, 2);
switch ($equation) {
case 'IS NULL':
$func = 'isNull';
break;
case 'IS NOT NULL':
$func = 'isNotNull';
break;
default:
list($comp, $valueSpec) = explode(' ', $equation, 2);
switch ($comp) {
case '=':
$func = 'eq';
break;
case '<>':
$func = 'neq';
break;
case '<':
$func = 'lt';
break;
case '>':
$func = 'gt';
break;
case '<=':
$func = 'lte';
break;
case '>=':
$func = 'gte';
break;
case 'IN':
$func = 'in';
break;
case 'NOT IN':
$func = 'notIn';
break;
case 'LIKE':
$func = 'like';
break;
case 'NOT LIKE':
$func = 'notLike';
break;
default:
throw new InvalidCriteriaException(sprintf('unknown comparison: %s', $comp));
}
if ($valueSpec === '?') {
$qb->setParameter($paramIndex, $value);
return $qb->expr()->{$func}($field, '?' . $paramIndex);
} elseif (strpos($valueSpec, ':') === 0) {
$qb->setParameter(substr($valueSpec, 1), $value);
return $qb->expr()->{$func}($field, $valueSpec);
} elseif ($value === null) {
return $field . ' ' . $comp . ' ' . $valueSpec;
}
throw new InvalidCriteriaException('invalid field spec: ' . $spec);
}
return $qb->expr()->{$func}($field);
} else {
if ($key === ResolverInterface::T_AND) {
$expr = $qb->expr()->andX();
} elseif ($key === ResolverInterface::T_OR) {
$expr = $qb->expr()->orX();
} else {
throw new InvalidCriteriaException('invalid graph given');
}
foreach ($graph[$key] as $sub) {
$expr->add($this->createQueryExpression($qb, $sub));
}
return $expr;
}
}
/**
* @param $filterCondition
* @param QueryBuilder $queryBuilder
*
* @return string
* @throws \Netdudes\DataSourceryBundle\DataSource\Driver\Doctrine\Exception\ColumnNotFoundException
*/
protected function buildUniqueToken(FilterCondition $filterCondition, QueryBuilder $queryBuilder)
{
return ':token_' . strtolower(str_replace(['.', '-'], '_', $filterCondition->getFieldName())) . '_' . $queryBuilder->getParameters()->count();
}
/**
* We need to drop the query builder, as it contains a PDO instance deep inside.
*
* @return array
*/
public function __sleep()
{
$this->parameters = $this->queryBuilder->getParameters();
return array('entityClassName', 'constraint', 'orderings', 'parameterIndex', 'limit', 'offset', 'parameters', 'joins');
}
/**
* @group DDC-1933
*/
public function testParametersAreCloned()
{
$originalQb = new QueryBuilder($this->_em);
$originalQb->setParameter('parameter1', 'value1');
$copy = clone $originalQb;
$copy->setParameter('parameter2', 'value2');
$this->assertCount(1, $originalQb->getParameters());
$this->assertSame('value1', $copy->getParameter('parameter1')->getValue());
$this->assertSame('value2', $copy->getParameter('parameter2')->getValue());
}
/**
* @param QueryBuilder $queryBuilder
*
* @return string
*/
protected function getQueryBuilderHash(QueryBuilder $queryBuilder)
{
return hash('sha256', json_encode(array('sql' => $queryBuilder->getQuery()->getSQL(), 'parameters' => $queryBuilder->getParameters())));
}
/**
* We consider two query builders with an equal SQL string and
* equal parameters to be equal.
*
* @param QueryBuilder $queryBuilder
*
* @return array
*
* @internal This method is public to be usable as callback. It should not
* be used in user code.
*/
public function getQueryBuilderPartsForCachingHash($queryBuilder)
{
return array($queryBuilder->getQuery()->getSQL(), $queryBuilder->getParameters()->toArray());
}
/**
* @param Request $request
* @return DataSet
*/
public function createDataSet(Request $request)
{
$dataSet = new DataSet();
$paginatorTotal = $this->createPaginator();
$dataSet->numberOfTotalRecords = $paginatorTotal->count();
// Add filters
$where = [];
foreach ($request->columnFilters as $column => $filter) {
$fieldName = $this->columnMapping[$column];
/**
* @todo refactor handling of composite/aggregate fields
*/
if (count(explode(".", $fieldName)) == 1) {
$fieldName = $this->retrieveExpressionByFieldName($fieldName);
}
// Treat '|' as OR and '~' for date ranges
if (false !== strpos($filter, '|')) {
$possibleValues = explode('|', $filter);
$orX = [];
foreach ($possibleValues as $value) {
$paramName = ':datatables_' . $this->qb->getParameters()->count();
$this->qb->setParameter($paramName, $value);
$orX[] = $this->qb->expr()->eq($fieldName, $paramName);
}
$where[] = call_user_func_array([$this->qb->expr(), 'orX'], $orX);
} elseif (preg_match('/^(\\d{2})-(\\d{2})-(\\d{4})~(\\d{2})-(\\d{2})-(\\d{4})$/', $filter)) {
$dates = explode('~', $filter);
$paramName = ':datatables_' . $this->qb->getParameters()->count();
$this->qb->setParameter($paramName, \DateTime::createFromFormat('d-m-Y', $dates[0]));
$where[] = $this->qb->expr()->gte($fieldName, $paramName);
$paramName = ':datatables_' . $this->qb->getParameters()->count();
$this->qb->setParameter($paramName, \DateTime::createFromFormat('d-m-Y', $dates[1]));
$where[] = $this->qb->expr()->lte($fieldName, $paramName);
} else {
if (false !== strpos($filter, '~')) {
continue;
}
$paramName = ':datatables_' . $this->qb->getParameters()->count();
$this->qb->setParameter($paramName, "%{$filter}%");
$where[] = $this->qb->expr()->like($fieldName, $paramName);
}
}
if (count($where) > 0) {
$this->qb->andWhere(call_user_func_array([$this->qb->expr(), 'andX'], $where));
}
// Add sorting
foreach ($request->columnSorts as $column => $direction) {
$this->qb->addOrderBy($this->columnMapping[$column], $direction);
}
$this->qb->setFirstResult($request->displayStart);
$this->qb->setMaxResults($request->displayLength);
$paginatorFiltered = $this->createPaginator();
$dataSet->numberOfFilteredRecords = $paginatorFiltered->count();
$dataSet->data = [];
$propertyPathMappingFactory = new PropertyPathMappingFactory();
$propertyPathMapping = $propertyPathMappingFactory->createPropertyPathMapping($this->qb, $this->columnMapping, $this->propertyPathMapping);
foreach ($paginatorFiltered->getIterator() as $item) {
$dataSet->data[] = $this->buildRow($item, $propertyPathMapping);
}
return $dataSet;
}
/**
* @param QueryBuilder $from
* @param QueryBuilder $to
*/
protected function copyParameters(QueryBuilder $from, QueryBuilder $to)
{
foreach ($from->getParameters() as $parameter) {
$to->setParameter($parameter->getName(), $parameter->getValue(), $parameter->getType());
}
}
/**
* @param string $type
* @param QueryBuilder $queryBuilder
*
* @return string
*/
private function generateParameterName($type, QueryBuilder $queryBuilder)
{
return sprintf('%s_%d', $type, count($queryBuilder->getParameters()));
}
