/**
* @param QueryBuilder $queryBuilder
* @param Parameter $parameters
* @return QueryBuilder
*/
protected function generateQueryParamFilter(QueryBuilder $queryBuilder, Parameters $parameters)
{
if (isset($parameters['query']) && is_array($parameters['query'])) {
$i = 1;
foreach ($parameters['query'] as $query) {
$type = '=';
if ($query['type'] == 'like') {
$type = 'LIKE';
}
$whereString = sprintf('row.%s %s :%sValue%d', $query['field'], $type, $query['field'], $i);
if (!isset($query['where']) || $query['where'] == 'and') {
$queryBuilder->andWhere($whereString);
} else {
if ($query['where'] == 'or') {
$queryBuilder->orWhere($whereString);
}
}
$queryBuilder->setParameter(sprintf('%sValue%d', $query['field'], $i), $query['value']);
$i++;
}
}
if (isset($parameters['orderBy']) && is_array($parameters['orderBy'])) {
foreach ($parameters['orderBy'] as $orderKey => $orderDirection) {
$queryBuilder->addOrderBy('row.' . $orderKey, strtoupper($orderDirection));
}
}
return $queryBuilder;
}
/**
* @dataProvider orderByProvider
*/
public function testAddOrderBy($alias, $orderBy, $expectedOrderBy)
{
foreach ($expectedOrderBy as $field => $order) {
$this->queryBuilder->addOrderBy($field, $order)->shouldBeCalledTimes(1);
}
$this->addOrderByFunction->invokeArgs($this->orderByTrait, [$this->queryBuilder->reveal(), $alias, $orderBy]);
}
/**
* {@inheritdoc}
*/
public function addFieldSorter($field, $direction, $locale = null, $scope = null)
{
$this->qb->addOrderBy($field, $direction);
$idField = $this->qb->getRootAlias() . '.id';
$this->qb->addOrderBy($idField);
return $this;
}
/**
* @param Request $request
* @param QueryBuilder $queryBuilder
*
* @return QueryBuilder
*/
public function execute(Request $request, QueryBuilder $queryBuilder)
{
$filteredProperties = array();
$sort = $request->get('sort');
if (!is_null($sort) && !is_array($sort)) {
$elements = explode(',', $sort, 99);
foreach ($elements as $element) {
if ($element) {
$sort = 'asc';
if (substr($element, 0, 1) == '+') {
$element = substr($element, 1);
} else {
if (substr($element, 0, 1) == '-') {
$element = substr($element, 1);
$sort = 'desc';
}
}
if (array_key_exists($element, $this->orderProperties)) {
$filteredProperties[] = $element;
$queryBuilder->addOrderBy($this->orderProperties[$element][0], $sort);
}
}
}
}
foreach ($this->orderProperties as $property => $value) {
if (!array_key_exists($property, $filteredProperties)) {
if (!is_null($value[1])) {
$queryBuilder->addOrderBy($value[0], $value[1]);
}
}
}
return $queryBuilder;
}
/**
* {@inheritdoc}
*/
public function addFieldSorter($field, $direction)
{
$alias = 'sorterCompleteness';
$util = new CompletenessJoin($this->qb);
$util->addJoins($alias);
$this->qb->addOrderBy($alias . '.ratio', $direction);
return $this;
}
/**
* {@inheritdoc}
*/
public function addFieldSorter($field, $direction, $locale = null, $scope = null)
{
$alias = 'inGroupSorter';
$inGroupExpr = 'CASE WHEN :currentGroup MEMBER OF p.groups THEN true ELSE false END';
$this->qb->addSelect(sprintf('%s AS %s', $inGroupExpr, $alias))->addOrderBy($alias, $direction);
$idField = $this->qb->getRootAlias() . '.id';
$this->qb->addOrderBy($idField);
return $this;
}
protected function genericBaseQueryAddOrderBy(QueryBuilder $qb, $orderBy = '')
{
if ($orderBy == 'sort asc') {
$qb->addOrderBy('tbl.group', 'asc');
$qb->addOrderBy('tbl.sort', 'asc');
return $qb;
} else {
return parent::genericBaseQueryAddOrderBy($qb, $orderBy);
}
}
/**
* {@inheritdoc}
*/
public function addFieldSorter($field, $direction, $locale = null, $scope = null)
{
$alias = 'sorterCompleteness';
$util = new CompletenessJoin($this->qb);
$util->addJoins($alias, $locale, $scope);
$this->qb->addOrderBy($alias . '.ratio', $direction);
$idField = $this->qb->getRootAlias() . '.id';
$this->qb->addOrderBy($idField);
return $this;
}
/**
* {@inheritdoc}
*/
public function addFieldSorter($field, $direction)
{
$rootAlias = $this->qb->getRootAlias();
$prefix = 'sorter';
$field = $prefix . 'familyLabel';
$family = $prefix . 'family';
$trans = $prefix . 'familyTranslations';
$this->qb->leftJoin($rootAlias . '.family', $family)->leftJoin($family . '.translations', $trans, 'WITH', $trans . '.locale = :dataLocale');
$this->qb->addSelect('COALESCE(' . $trans . '.label, CONCAT(\'[\', ' . $family . '.code, \']\')) as ' . $field);
$this->qb->addOrderBy($field, $direction);
return $this;
}
/**
* {@inheritdoc}
*/
public function addAttributeSorter(AttributeInterface $attribute, $direction, $locale = null, $scope = null)
{
$aliasPrefix = 'sorter';
$joinAlias = $aliasPrefix . 'V' . $attribute->getCode();
$backendType = $attribute->getBackendType();
// join to value
$condition = $this->prepareAttributeJoinCondition($attribute, $joinAlias, $locale, $scope);
$this->qb->leftJoin($this->qb->getRootAlias() . '.values', $joinAlias, 'WITH', $condition);
$joinAliasMetric = $aliasPrefix . 'M' . $attribute->getCode();
$this->qb->leftJoin($joinAlias . '.' . $backendType, $joinAliasMetric);
$this->qb->addOrderBy($joinAliasMetric . '.baseData', $direction);
$idField = $this->qb->getRootAlias() . '.id';
$this->qb->addOrderBy($idField);
return $this;
}
/**
* Adds conditions to the query builder related to the specification. The
* specification should add parameters as required and return the expression to be
* added to the QueryBuilder.
*
* @param \Doctrine\ORM\QueryBuilder $qb
* @param string $dqlAlias
* @return \Doctrine\ORM\Query\Expr
*/
public function match(QueryBuilder $qb, $dqlAlias)
{
foreach ($this->sortFields as $field => $direction) {
$qb->addOrderBy(sprintf('%s.%s', $dqlAlias, $field), $direction);
}
return parent::match($qb, $dqlAlias);
}
/**
* Custom findBy so we can filter by related entities
*
* @param QueryBuilder $qb
* @param array $criteria
* @param array $orderBy
* @param int $limit
* @param int $offset
*
* @return QueryBuilder
*/
protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
{
if (count($criteria)) {
foreach ($criteria as $key => $value) {
$values = is_array($value) ? $value : [$value];
$qb->andWhere($qb->expr()->in("a.{$key}", ":{$key}"));
$qb->setParameter(":{$key}", $values);
}
}
if (empty($orderBy)) {
$orderBy = ['user' => 'ASC'];
}
if (is_array($orderBy)) {
foreach ($orderBy as $sort => $order) {
$qb->addOrderBy('a.' . $sort, $order);
}
}
if ($offset) {
$qb->setFirstResult($offset);
}
if ($limit) {
$qb->setMaxResults($limit);
}
return $qb;
}
/**
* Adds common filter options (limit, offset, order) to a query builder
* @param QueryBuilder $qb
* @param array $options
* @return QueryBuilder
* @author Yohann Marillet
*/
protected function addCommonFilters(QueryBuilder $qb, array $options = array())
{
if (isset($options['_prefix'])) {
$options['_prefix'] = rtrim($options['_prefix'], '.') . '.';
} else {
$options['_prefix'] = '';
}
if (isset($options['limit']) && !empty($options['limit'])) {
$qb->setMaxResults((int) $options['limit']);
if (isset($options['offset'])) {
$qb->setFirstResult((int) $options['offset']);
}
}
if (isset($options['order'])) {
foreach ($options['order'] as $field => $order) {
if (is_int($field)) {
$field = $order;
$order = 'ASC';
}
$field = $options['_prefix'] . $field;
$qb->addOrderBy($this->replaceByJoinAlias($field, $qb), $order);
}
}
return $qb;
}
/**
* @param QueryBuilder $qb
* @param array $criteria
* @param array $orderBy
* @param int $limit
* @param int $offset
*
* @return QueryBuilder
*/
protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
{
if (array_key_exists('alerts', $criteria)) {
$ids = is_array($criteria['alerts']) ? $criteria['alerts'] : [$criteria['alerts']];
$qb->join('x.alerts', 'al');
$qb->andWhere($qb->expr()->in('al.id', ':alerts'));
$qb->setParameter(':alerts', $ids);
}
//cleanup all the possible relationship filters
unset($criteria['alerts']);
if (count($criteria)) {
foreach ($criteria as $key => $value) {
$values = is_array($value) ? $value : [$value];
$qb->andWhere($qb->expr()->in("x.{$key}", ":{$key}"));
$qb->setParameter(":{$key}", $values);
}
}
if (empty($orderBy)) {
$orderBy = ['id' => 'ASC'];
}
if (is_array($orderBy)) {
foreach ($orderBy as $sort => $order) {
$qb->addOrderBy('x.' . $sort, $order);
}
}
if ($offset) {
$qb->setFirstResult($offset);
}
if ($limit) {
$qb->setMaxResults($limit);
}
return $qb;
}
/**
* @param QueryBuilder $qb
* @param string $dqlAlias
*/
public function modify(QueryBuilder $qb, $dqlAlias)
{
if ($this->dqlAlias !== null) {
$dqlAlias = $this->dqlAlias;
}
$qb->addOrderBy(sprintf('%s.%s', $dqlAlias, $this->field), $this->order);
}
/**
* {@inheritdoc}
*
* Orders collection by properties. The order of the ordered properties is the same as the order specified in the
* query.
* For each property passed, if the resource does not have such property or if the order value is different from
* `asc` or `desc` (case insensitive), the property is ignored.
*/
public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder)
{
$request = $this->requestStack->getCurrentRequest();
if (null === $request) {
return;
}
$properties = $this->extractProperties($request);
foreach ($properties as $property => $order) {
if (!$this->isPropertyEnabled($property) || !$this->isPropertyMapped($property, $resource)) {
continue;
}
if (empty($order) && isset($this->properties[$property])) {
$order = $this->properties[$property];
}
$order = strtoupper($order);
if (!in_array($order, ['ASC', 'DESC'])) {
continue;
}
$alias = 'o';
$field = $property;
if ($this->isPropertyNested($property)) {
$propertyParts = $this->splitPropertyParts($property);
$parentAlias = $alias;
foreach ($propertyParts['associations'] as $association) {
$alias = QueryNameGenerator::generateJoinAlias($association);
$queryBuilder->leftJoin(sprintf('%s.%s', $parentAlias, $association), $alias);
$parentAlias = $alias;
}
$field = $propertyParts['field'];
}
$queryBuilder->addOrderBy(sprintf('%s.%s', $alias, $field), $order);
}
}
/**
* @return void
*/
private function setOrderBy()
{
$List = $this->getTableOrderBy();
foreach ((array) $List as $Literal) {
$this->QueryBuilder->addOrderBy($Literal);
}
}
/**
* Add order rules to the query builder
* NB! The alias of the main table must be "e".
* @param QueryBuilder $qb
* @throws Exception\InvalidArgument
*/
public function applyToQueryBuilder(QueryBuilder $qb, &$parameterOffset = 0)
{
$orderRules = $this->orderRules;
foreach ($orderRules as $orderRule) {
$field = $orderRule[self::FIELD_POS];
switch ($field) {
case self::LEFT_FIELD:
case self::RIGHT_FIELD:
case self::LEVEL_FIELD:
break;
default:
throw new Exception\InvalidArgument("Field {$field} is not recognized");
}
$field = "e.{$field}";
$directionSQL = null;
$direction = $orderRule[self::DIRECTION_POS];
switch ($direction) {
case self::DIRECTION_ASCENDING:
$directionSQL = 'ASC';
break;
case self::DIRECTION_DESCENDING:
$directionSQL = 'DESC';
break;
default:
throw new Exception\InvalidArgument("Direction {$direction} is not recognized");
}
$qb->addOrderBy($field, $directionSQL);
}
}
/**
* Sets sorting.
* @param array $sorting
*/
public function sort(array $sorting)
{
foreach ($sorting as $key => $value) {
$column = isset($this->sortMapping[$key]) ? $this->sortMapping[$key] : current($this->qb->getRootAliases()) . '.' . $key;
$this->qb->addOrderBy($column, $value);
}
}
/**
* @param QueryBuilder $queryBuilder
* @param array $sorting
*/
protected function applySorting(QueryBuilder $queryBuilder, array $sorting = [])
{
foreach ($sorting as $property => $order) {
if (!empty($order)) {
$queryBuilder->addOrderBy($this->getPropertyName($property), $order);
}
}
}
/**
* @return mixed
*/
public function getItems()
{
$this->qb->setMaxResults($this->getLimit());
$this->qb->setFirstResult($this->getOffset());
if (count($this->getSorting()) > 0) {
foreach ($this->getSorting() as $sortColumn => $sortType) {
if (isset($this->columnAliases[$sortColumn])) {
$sortColumn = $this->columnAliases[$sortColumn]->qbName;
} else {
$aliases = $this->qb->getRootAliases();
$sortColumn = $aliases[0] . '.' . $sortColumn;
}
$this->qb->addOrderBy($sortColumn, is_string($sortType) && strncasecmp($sortType, 'd', 1) || $sortType > 0 ? 'ASC' : 'DESC');
}
}
return $this->qb->getQuery()->getResult();
}
protected function orderBy($columnName, $direction)
{
if ($columnName === 'edit') {
$this->queryBuilder->addOrderBy($this->getEntityName() . '.' . 'id', $direction);
return;
}
$this->queryBuilder->addOrderBy($this->parseToQueryBuilderFormat($columnName), $direction);
}
/**
* {@inheritdoc}
*/
public function applyToCollection(ResourceInterface $resource, QueryBuilder $queryBuilder)
{
$classMetaData = $queryBuilder->getEntityManager()->getClassMetadata($resource->getEntityClass());
$identifiers = $classMetaData->getIdentifier();
if (null !== $this->order && 1 === count($identifiers)) {
$identifier = $identifiers[0];
$queryBuilder->addOrderBy('o.' . $identifier, $this->order);
}
}
/**
* {@inheritdoc}
*/
public function addAttributeSorter(AttributeInterface $attribute, $direction, $locale = null, $scope = null)
{
if (null === $locale) {
throw new \InvalidArgumentException(sprintf('Cannot prepare condition on type "%s" without locale', $attribute->getAttributeType()));
}
$aliasPrefix = 'sorter';
// join to values
$joinAlias = $aliasPrefix . 'V' . $attribute->getCode();
$condition = $this->prepareAttributeJoinCondition($attribute, $joinAlias, $locale, $scope);
$this->qb->leftJoin(current($this->qb->getRootAliases()) . '.values', $joinAlias, 'WITH', $condition);
// join to reference data
$joinAliasOpt = $attribute->getCode();
$this->qb->leftJoin($joinAlias . '.' . $attribute->getReferenceDataName(), $joinAliasOpt);
$this->qb->addOrderBy($joinAliasOpt . '.code', $direction);
$idField = current($this->qb->getRootAliases()) . '.id';
$this->qb->addOrderBy($idField);
return $this;
}
public function orderBy(QueryBuilder $queryBuilder, $metadata, $option)
{
if (!isset($option['alias'])) {
$option['alias'] = 'row';
}
if (!isset($option['direction']) || !in_array(strtolower($option['direction']), array('asc', 'desc'))) {
throw new Exception('Invalid direction in Field OrderBy');
}
$queryBuilder->addOrderBy($option['alias'] . '.' . $option['field'], $option['direction']);
}
/**
* {@inheritdoc}
*/
public function applyToCollection(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
{
$classMetaData = $queryBuilder->getEntityManager()->getClassMetadata($resourceClass);
$identifiers = $classMetaData->getIdentifier();
if (null !== $this->order) {
foreach ($identifiers as $identifier) {
$queryBuilder->addOrderBy('o.' . $identifier, $this->order);
}
}
}
/**
* Function adds order-by to querybuilder based on $sortBy-data given.
*
* @param QueryBuilder $queryBuilder
* @param array $sortBy
*/
protected function addOrderBy(QueryBuilder $queryBuilder, $alias, array $sortBy = [])
{
foreach ($sortBy as $field => $order) {
// if no relation is defined add alias by default
if (strpos($field, '.') === false) {
$field = $alias . '.' . $field;
}
$queryBuilder->addOrderBy($field, $order);
}
}
/**
* Create query based on current settings
*
* @param null $offset
* @param null $itemCountPerPage
*
* @return QueryBuilder
*/
public function createQuery($offset = null, $itemCountPerPage = null)
{
$service = $this->getService();
$alias = $service->getAlias();
$entity = $service->getEntityClass();
//gets custom query if set
if (!($this->_qb = $this->getGrid()->getCustomQueryBuilder())) {
$this->_qb = $this->getService()->getEntityManager()->createQueryBuilder();
}
$this->_qb->select($alias);
$this->_qb->from($entity, $alias);
if ($itemCountPerPage) {
$this->_qb->setMaxResults($itemCountPerPage);
}
if ($offset) {
$this->_qb->setFirstResult($offset);
}
$filter = $this->getFilter();
if (is_array($filter) && array_key_exists('field', $filter) && array_key_exists('value', $filter) && array_key_exists('expression', $filter) && array_key_exists('options', $filter)) {
$this->filter($filter['field'], $filter['value'], $filter['expression'], $filter['options']);
}
if ($treeFilter = $this->getTreeFilter()) {
$this->buildTreeFilterQuery($treeFilter);
}
$sort = $this->getSort();
if (is_array($sort)) {
$c = 0;
foreach ($sort as $s) {
if (isset($s['sidx'])) {
$field = $s['sidx'];
$direction = isset($s['sord']) ? $s['sord'] : 'asc';
if ($c) {
$this->_qb->addOrderBy($this->getService()->getAlias() . '.' . $field, $direction);
} else {
$this->_qb->orderBy($this->getService()->getAlias() . '.' . $field, $direction);
}
$c++;
}
}
}
return $this->_qb;
}
/**
* Manages the order by statement into queries.
*
* @param \Doctrine\ORM\QueryBuilder $queryBuilder The query builder
* @param string[] $sorting Array which contains the sorting as key value,
* if order is empty, the default value is DESC
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function orderBy(QueryBuilder $queryBuilder, array $sorting = [])
{
foreach ($sorting as $property => $order) {
if (!(array_keys($sorting) !== range(0, count($sorting) - 1))) {
$property = $order;
$order = 'DESC';
}
$queryBuilder->addOrderBy($this->getPropertyName($property), $order);
}
return $queryBuilder;
}
/**
* Create query object
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function createQuery()
{
$offset = ($this->_options->getPage() - 1) * $this->_options->getRows();
if (!($this->_qb = $this->_options->getCustomQueryBuilder())) {
$this->_qb = $this->getEntityManager()->createQueryBuilder();
}
$this->_qb->select($this->getAlias());
$this->_qb->from($this->getEntityClass(), $this->getAlias());
if ($joins = $this->getOptions()->getJoins()) {
foreach ($joins as $index => $join) {
$placeHolder = ':p' . $join['field'] . $index;
$this->_qb->innerJoin($this->getAlias() . '.' . $join['field'], $join['alias'])->andWhere($join['alias'] . '.id = ' . $placeHolder)->setParameter($placeHolder, $join['key']);
}
}
if ($itemCountPerPage = $this->_options->getRows()) {
$this->_qb->setMaxResults($itemCountPerPage);
}
if ($offset) {
$this->_qb->setFirstResult($offset);
}
if ($presets = $this->_options->getPresets()) {
$this->addPresets($presets);
}
$filter = $this->_options->getFilters();
if (is_array($filter) && array_key_exists('field', $filter) && array_key_exists('value', $filter) && array_key_exists('expression', $filter) && array_key_exists('options', $filter)) {
$this->filter($filter['field'], $filter['value'], $filter['expression'], $filter['options']);
}
if ($treeFilter = $this->_options->getTreeFilter()) {
$this->buildTreeFilterQuery($treeFilter);
}
$sort = $this->_options->getSortOrder();
if (is_array($sort)) {
$c = 0;
foreach ($sort as $s) {
if (!empty($s['sidx'])) {
$field = $s['sidx'];
$direction = isset($s['sord']) ? $s['sord'] : 'asc';
if ($c) {
$this->_qb->addOrderBy($this->getAlias() . '.' . $field, $direction);
} else {
$this->_qb->orderBy($this->getAlias() . '.' . $field, $direction);
}
$c++;
}
}
}
if ($subGridFilter = $this->_options->getSubGridFilter()) {
foreach ($subGridFilter as $field => $value) {
$this->_qb->andWhere($this->_qb->expr()->eq($this->getAlias() . '.' . $field, $value));
}
}
return $this->_qb;
}
