Programming Language PHP
Namespace Oro\Component\DoctrineUtils\ORM
Class QueryBuilderUtil
Total Examples 59
59 code examples of PHP Oro\Component\DoctrineUtils\ORM\QueryBuilderUtil extracted from open source projects
/**
* Gets the root alias of the given query.
*
*
*
* @throws QueryException if the given query builder does not have a root alias or has more than one root aliases
*/
public function getRootAlias(QueryBuilder $qb): string
{
return QueryBuilderUtil::getSingleRootAlias($qb);
}
/**
* @inheritdoc
*/
protected function buildExpr(FilterDatasourceAdapterInterface $ds, $comparisonType, $fieldName, $data)
{
QueryBuilderUtil::checkIdentifier($fieldName);
/* @var WorkflowDefinition $definition */
$definition = reset($data['value']);
$keyParameter = $ds->generateParameterName('key');
$domainParameter = $ds->generateParameterName('domain');
$expr = $ds->expr()->andX(
$ds->expr()->eq(sprintf('%s.domain', $fieldName), $domainParameter, true),
$ds->expr()->like(sprintf('%s.key', $fieldName), $keyParameter, true)
);
$key = $this->getGenerator()->generate(
new TranslationKeySource(new WorkflowTemplate(), ['workflow_name' => $definition->getName()])
);
$ds->setParameter($keyParameter, $key . '%');
$ds->setParameter($domainParameter, 'workflows');
return $expr;
}
/**
* @param int[] $roleIds
*
* @return int[]
*/
protected function getUserOwnerIdsByRoles(array $roleIds): array
{
if (!$roleIds) {
return [];
}
$qb = $this->registry->getRepository('OroUserBundle:User')
->createQueryBuilder('u')
->select('DISTINCT(u.id)')
->join('u.userRoles', 'r');
QueryBuilderUtil::applyOptimizedIn($qb, 'r.id', $roleIds);
$result = array_map('current', $qb->getQuery()->getResult());
if (empty($result)) {
$result = [0];
}
return $result;
}
/**
* Builds filtering expression by tags ids and entity class name
*
*
*
* @SuppressWarnings(PHPMD.CyclomaticComplexity)
*/
protected function buildFilterExpr(OrmFilterDatasourceAdapter $ds, array $data, string $entityClassParam, string $comparisonType): mixed
{
QueryBuilderUtil::checkIdentifier($entityClassParam);
$expr = false;
$qb = $ds->getQueryBuilder();
$entityIdAlias = $this->getDataFieldName();
$taggingAlias = QueryBuilderUtil::generateParameterName('tagging');
$tagAlias = QueryBuilderUtil::generateParameterName('tag');
$taggingRepository = $qb->getEntityManager()->getRepository(Tagging::class);
if (!$this->isValueRequired($data['type'])) {
$subQueryDQL = $taggingRepository->createQueryBuilder($taggingAlias)
->select(QueryBuilderUtil::getField($taggingAlias, 'id'))
->where(QueryBuilderUtil::sprintf('%s.entityName = :%s', $taggingAlias, $entityClassParam))
->andWhere(QueryBuilderUtil::sprintf('%s.recordId = %s', $taggingAlias, $entityIdAlias))
->getDQL();
} elseif (isset($data['value']) && '' !== $data['value']) {
$subQueryDQL = $taggingRepository->createQueryBuilder($taggingAlias)
->select(QueryBuilderUtil::getField($taggingAlias, 'recordId'))
->join(QueryBuilderUtil::getField($taggingAlias, 'tag'), $tagAlias)
->where(QueryBuilderUtil::sprintf('%s.entityName = :%s', $taggingAlias, $entityClassParam))
->andWhere($qb->expr()->in(QueryBuilderUtil::getField($tagAlias, 'id'), $data['value']))
->getDQL();
} else {
return $expr;
}
switch ($comparisonType) {
case DictionaryFilterType::TYPE_IN:
case DictionaryFilterType::EQUAL:
$expr = $ds->expr()->in($entityIdAlias, $subQueryDQL);
break;
case DictionaryFilterType::TYPE_NOT_IN:
case DictionaryFilterType::NOT_EQUAL:
$expr = $ds->expr()->notIn($entityIdAlias, $subQueryDQL);
break;
case FilterUtility::TYPE_NOT_EMPTY:
$expr = $ds->expr()->exists($subQueryDQL);
break;
case FilterUtility::TYPE_EMPTY:
$expr = $ds->expr()->not($ds->expr()->exists($subQueryDQL));
break;
default:
break;
}
return $expr;
}
/**
* Calculates the page offset
*
* @param int $page The page number
* @param int $limit The maximum number of items per page
*/
protected function getOffset(int $page, int $limit): int
{
return QueryBuilderUtil::getPageOffset($page, $limit);
}
/**
* Applies the given joins for the query builder
*/
protected function applyJoins(QueryBuilder $qb, ?array $joins)
{
QueryBuilderUtil::applyJoins($qb, $joins);
}
/**
* Checks the given criteria and converts them to Criteria object if needed
*
* @param Criteria|array|null $criteria
*/
protected function normalizeCriteria($criteria): Criteria
{
return QueryBuilderUtil::normalizeCriteria($criteria);
}
private function getIdentityExpr(string $entityAlias, string $fieldName, string $fieldAlias): string
{
return QueryBuilderUtil::sprintf('IDENTITY(%s.%s) AS %s', $entityAlias, $fieldName, $fieldAlias);
}
/**
* @inheritdoc
*/
public function getEntitiesByIds($identifier, array $values)
{
QueryBuilderUtil::checkIdentifier($identifier);
$qb = clone $this->queryBuilder;
$alias = current($qb->getRootAliases());
$parameter = 'ORMQueryBuilderLoader_getEntitiesByIds_' . $identifier;
$where = $qb->expr()->in($alias . '.' . $identifier, ':' . $parameter);
// Guess type
$entity = current($qb->getRootEntities());
$metadata = $qb->getEntityManager()->getClassMetadata($entity);
if (in_array($metadata->getTypeOfField($identifier), ['integer', 'bigint', 'smallint'])) {
$parameterType = Connection::PARAM_INT_ARRAY;
// the same workaround as in Symfony:
// {@see \Symfony\Bridge\Doctrine\Form\ChoiceList\ORMQueryBuilderLoader::getEntitiesByIds }
// Filter out non-integer values (e.g. ""). If we don't, some
// databases such as PostgreSQL fail.
$values = array_values(array_filter($values, function ($v) {
return (string) $v === (string) (int) $v;
}));
} else {
$parameterType = Connection::PARAM_STR_ARRAY;
}
$qb->andWhere($where)->setParameter($parameter, $values, $parameterType);
$query = $qb->getQuery();
return $this->aclHelper->apply($query, $this->permission, $this->options)
->getResult();
}
/**
* Creates expression like (textField.value LIKE :value0_w0 OR textField.value LIKE :value0_w1)
* and adds parameters to $qb.
*/
protected function createLikeWordsExpr(
QueryBuilder $qb,
array $words,
string $index,
array $searchCondition
): string {
QueryBuilderUtil::checkIdentifier($index);
$joinAlias = $this->getJoinAlias($searchCondition['fieldType'], $index);
$fieldName = $searchCondition['fieldName'];
QueryBuilderUtil::checkIdentifier($joinAlias);
$result = $qb->expr()->orX();
foreach (array_values($words) as $key => $value) {
$valueParameter = 'value' . $index . '_w' . $key;
QueryBuilderUtil::checkIdentifier($valueParameter);
$result->add($qb->expr()->like($joinAlias . '.value', ':' . $valueParameter));
$qb->setParameter($valueParameter, "%$value%");
}
if ($this->isConcreteField($fieldName) && !$this->isAllDataField($fieldName)) {
$fieldParameter = 'field' . $index;
$result = $qb->expr()->andX($result, "$joinAlias.field = :$fieldParameter");
$qb->setParameter($fieldParameter, $fieldName);
}
return (string) $result;
}
/**
* Creates expression like MATCH_AGAINST(textField.value, :value0 'IN BOOLEAN MODE') and adds parameters
* to $qb.
*/
protected function createMatchAgainstWordsExpr(
QueryBuilder $qb,
array $words,
string $index,
array $searchCondition,
bool $setOrderBy = true
): string {
QueryBuilderUtil::checkIdentifier($index);
$joinAlias = $this->getJoinAlias($searchCondition['fieldType'], $index);
$fieldName = $searchCondition['fieldName'];
$fieldParameter = 'field' . $index;
$valueParameter = 'value' . $index;
QueryBuilderUtil::checkIdentifier($joinAlias);
$result = "MATCH_AGAINST($joinAlias.value, :$valueParameter 'IN BOOLEAN MODE') > 0";
if ($words) {
$qb->setParameter($valueParameter, implode('* ', $words) . '*');
} else {
$qb->setParameter($valueParameter, '');
}
if ($this->isConcreteField($fieldName)) {
$result = $qb->expr()->andX(
$result,
"$joinAlias.field = :$fieldParameter"
);
$qb->setParameter($fieldParameter, $fieldName);
}
if ($setOrderBy) {
$qb->addSelect(
"MATCH_AGAINST($joinAlias.value, :value$index 'IN BOOLEAN MODE') * search.weight as rankField$index"
)
->addOrderBy(sprintf('rankField%s', $index), Criteria::DESC);
}
return (string) $result;
}
/**
* @inheritdoc
*/
public function walkComparison(Comparison $comparison)
{
$value = $comparison->getValue()->getValue();
[$type, $field] = $this->explodeCombinedFieldString($comparison->getField());
QueryBuilderUtil::checkIdentifier($type);
$condition = Criteria::getSearchOperatorByComparisonOperator($comparison->getOperator());
[$joinAlias, $index] = $this->driver->getJoinAttributes($field, $type, $this->qb->getAllAliases());
QueryBuilderUtil::checkIdentifier($joinAlias);
QueryBuilderUtil::checkIdentifier($index);
$joinField = $this->driver->getJoinField($type);
$searchCondition = [
'fieldName' => $field,
'condition' => $condition,
'fieldValue' => $value,
'fieldType' => $type,
];
$fieldConditionParam = 'field' . $index;
if (in_array($comparison->getOperator(), SearchComparison::$filteringOperators, true)) {
$fieldCondition = is_array($searchCondition['fieldName'])
? $joinAlias . '.field IN (:field' . $index . ')'
: $joinAlias . '.field = :field' . $index;
$this->qb->leftJoin($joinField, $joinAlias, Join::WITH, $fieldCondition);
$this->qb->setParameter($fieldConditionParam, $searchCondition['fieldName']);
return $this->driver->addFilteringField($index, $searchCondition);
}
if (is_string($searchCondition['fieldName'])) {
$this->qb->leftJoin($joinField, $joinAlias, Join::WITH, $joinAlias . '.field = :field' . $index);
$this->qb->setParameter($fieldConditionParam, $searchCondition['fieldName']);
} else {
$this->qb->innerJoin($joinField, $joinAlias);
}
if ($type === Query::TYPE_TEXT && !in_array($condition, [Query::OPERATOR_IN, Query::OPERATOR_NOT_IN], true)) {
if ($searchCondition['fieldValue'] === '') {
$this->qb->setParameter($fieldConditionParam, $searchCondition['fieldName']);
return $joinAlias . '.field = :field' . $index;
} else {
return $this->driver->addTextField($this->qb, $index, $searchCondition, $this->setOrderBy);
}
}
return $this->driver->addNonTextField($this->qb, $index, $searchCondition);
}
/**
* @param string|string[]|null $from
*/
public function getSimpleSearchQuery(
?string $searchString,
?int $offset = 0,
?int $maxResults = 0,
$from = null,
?int $page = 0
): Query {
$query = $this->select();
$criteria = $query->getCriteria();
$nameField = Criteria::implodeFieldTypeName(Query::TYPE_TEXT, self::NAME_FIELD);
QueryBuilderUtil::checkField($nameField);
$query->addSelect($nameField . ' as name');
$query->from($from ?: '*');
$searchString = trim($searchString);
if ($searchString) {
$criteria->where(Criteria::expr()->contains(
Criteria::implodeFieldTypeName(Query::TYPE_TEXT, self::TEXT_ALL_DATA_FIELD),
$searchString
));
}
$criteria->setMaxResults($maxResults > 0 ? $maxResults : Query::INFINITY);
if ($page > 0) {
$offset = $maxResults * ($page - 1);
}
if ($offset > 0) {
$criteria->setFirstResult($offset);
}
return $query;
}
private function resolveBasicCondition(
QueryBuilder $qb,
string $alias,
string $field,
$value,
bool $withPriority
): mixed {
QueryBuilderUtil::checkIdentifier($alias);
QueryBuilderUtil::checkIdentifier($field);
$aliasedField = $alias . '.' . $field;
if ($value === null) {
$part = $qb->expr()->isNull($aliasedField);
} elseif ($value === self::IS_NOT_NULL) {
$part = $qb->expr()->isNotNull($aliasedField);
} else {
$paramName = $alias . '_param_' . $field;
if (\is_array($value)) {
$comparisonCondition = $qb->expr()->in($aliasedField, ':' . $paramName);
} else {
$comparisonCondition = $qb->expr()->eq($aliasedField, ':' . $paramName);
}
if ($withPriority) {
$part = $qb->expr()->orX(
$comparisonCondition,
$qb->expr()->isNull($aliasedField)
);
} else {
$part = $comparisonCondition;
}
$qb->setParameter($paramName, $value);
if ($withPriority) {
$qb->addOrderBy($aliasedField, Criteria::DESC);
}
}
return $part;
}
/**
* @param Join[] $joins
* @param string[] $ignoreFields
*
* @SuppressWarnings(PHPMD.NPathComplexity)
* @SuppressWarnings(PHPMD.CyclomaticComplexity)
*/
private function reapplyJoins(
QueryBuilder $qb,
array $joins,
string $alias,
array $ignoreFields,
bool $withPriority
): void {
$scopeClassMetadata = $this->getClassMetadata(Scope::class);
QueryBuilderUtil::checkIdentifier($alias);
foreach ($joins as $join) {
if (\is_array($join)) {
$this->reapplyJoins($qb, $join, $alias, $ignoreFields, $withPriority);
continue;
}
$parts = [];
$additionalJoins = [];
$joinCondition = $join->getCondition();
if ($joinCondition) {
$parts[] = $joinCondition;
}
if ($join->getAlias() === $alias) {
$usedFields = [];
if ($joinCondition) {
$usedFields = $this->getUsedFields($joinCondition, $alias);
}
foreach ($this->parameters as $field => $value) {
if (\in_array($field, $ignoreFields, true) || \in_array($field, $usedFields, true)) {
continue;
}
if ($this->isCollectionValuedAssociation($scopeClassMetadata, $field)) {
$additionalJoins[$field] = $this->resolveBasicCondition(
$qb,
$alias . '_' . $field,
'id',
$value,
$withPriority
);
} else {
$parts[] = $this->resolveBasicCondition($qb, $alias, $field, $value, $withPriority);
}
}
}
$condition = $this->getConditionFromParts($parts, $withPriority);
$this->applyJoinWithModifiedCondition($qb, $condition, $join);
if (!empty($additionalJoins)) {
$additionalJoins = array_filter($additionalJoins);
foreach ($additionalJoins as $field => $condition) {
QueryBuilderUtil::checkIdentifier($field);
$qb->leftJoin($alias . '.' . $field, $alias . '_' . $field, Join::WITH, $condition);
if (!$withPriority) {
$qb->andWhere($condition);
}
}
}
}
}
/**
* @param string[] $ignoreFields
*/
private function doApplyWhere(
QueryBuilder $qb,
string $alias,
array $ignoreFields,
bool $withPriority
): void {
$scopeClassMetadata = $this->getClassMetadata(Scope::class);
QueryBuilderUtil::checkIdentifier($alias);
foreach ($this->parameters as $field => $value) {
QueryBuilderUtil::checkIdentifier($field);
if (\in_array($field, $ignoreFields, true)) {
continue;
}
$condition = null;
if ($this->isCollectionValuedAssociation($scopeClassMetadata, $field)) {
$localAlias = $alias . '_' . $field;
$condition = $this->resolveBasicCondition($qb, $localAlias, 'id', $value, $withPriority);
$qb->leftJoin($alias . '.' . $field, $localAlias, Join::WITH, $condition);
} else {
$condition = $this->resolveBasicCondition($qb, $alias, $field, $value, $withPriority);
}
$qb->andWhere($condition);
}
}
public function getList(): array
{
$businessUnitRepo = $this->getBusinessUnitRepo();
$qb = $businessUnitRepo->getQueryBuilder();
$qb
->select('businessUnit.id')
->addSelect('o.id AS owner_id')
->leftJoin('businessUnit.owner', 'o')
->orderBy('businessUnit.id', 'ASC');
$this->addBusinessUnitName($qb);
QueryBuilderUtil::applyOptimizedIn($qb, 'businessUnit.id', $this->getBusinessUnitIds());
return $this->aclHelper->apply($qb)->getArrayResult();
}
/**
* Update all records in given table with organization id
*
* @param string $tableName table name to update, example: OroAccountBundle:Account or OroUserBundle:Group
* @param int $id Organization id
* @param string $relationName relation name to update. By default 'organization'
* @param bool $onlyEmpty Update data only for the records with empty relation
*
* @return int Number of rows affected
*/
public function updateWithOrganization(string $tableName, int $id, string $relationName = 'organization', bool $onlyEmpty = false): int
{
QueryBuilderUtil::checkIdentifier($relationName);
$qb = $this->getEntityManager()
->createQueryBuilder()
->update($tableName, 't')
->set('t.' . $relationName, ':id')
->setParameter('id', $id);
if ($onlyEmpty) {
$qb->where('t.' . $relationName . ' IS NULL ');
}
return $qb->getQuery()
->execute();
}
public function getGridFilterChoices(string $field, string $entity, string $alias = 'bu'): array
{
QueryBuilderUtil::checkIdentifier($alias);
QueryBuilderUtil::checkIdentifier($field);
$options = [];
$result = $this->_em->createQueryBuilder()
->select($alias)
->from($entity, $alias)
->addSelect($alias . '.' . $field)
->distinct()
->getQuery()
->getArrayResult();
foreach ((array) $result as $value) {
$options[$value[$field]] = current(
array_reverse(
explode('\\', $value[$field])
)
);
}
return $options;
}
public static function isToOneColumn(OrmFilterDatasourceAdapter $ds, string $column): bool
{
[$joinAlias] = explode('.', $column);
return QueryBuilderUtil::isToOne($ds->getQueryBuilder(), $joinAlias);
}
public static function findRelatedJoinByColumn(
OrmFilterDatasourceAdapter $ds,
string $column
): ?Expr\Join {
if (self::isToOneColumn($ds, $column)) {
return null;
}
[$alias] = explode('.', $column);
return QueryBuilderUtil::findJoinByAlias($ds->getQueryBuilder(), $alias);
}
/**
* @return array [DQL, parameters]
*/
public static function getSubQueryExpressionWithParameters(
OrmFilterDatasourceAdapter $ds,
QueryBuilder $subQuery,
string $fieldExpr,
string $filterName
): array {
QueryBuilderUtil::checkField($fieldExpr);
$subQuery
->resetDQLPart('orderBy')
->resetDQLPart('groupBy')
->select($fieldExpr)
->andWhere(sprintf('%1$s = %1$s', $fieldExpr));
self::processSubQueryExpressionGroupBy($ds, $subQuery, $fieldExpr);
[$dql, $replacements] = self::createDqlWithReplacedAliases($ds, $subQuery, $filterName);
[$fieldAlias, $field] = explode('.', $fieldExpr);
$replacedFieldExpr = sprintf('%s.%s', $replacements[$fieldAlias], $field);
$oldExpr = sprintf('%1$s = %1$s', $replacedFieldExpr);
$newExpr = sprintf('%s = %s', $replacedFieldExpr, $fieldExpr);
$dql = strtr($dql, [$oldExpr => $newExpr]);
return [$dql, $subQuery->getParameters()];
}
/**
* @inheritdoc
*/
protected function buildComparisonExpr(
FilterDatasourceAdapterInterface $ds,
$comparisonType,
$fieldName,
$parameterName
) {
QueryBuilderUtil::checkField($fieldName);
switch ($comparisonType) {
case FilterUtility::TYPE_NOT_EMPTY:
return $ds->expr()->isNotNull($fieldName);
case FilterUtility::TYPE_EMPTY:
return $ds->expr()->isNull($fieldName);
default:
return parent::buildComparisonExpr($ds, $comparisonType, $fieldName, $parameterName);
}
}
/**
* @param $extraWhereClauses
* @param $extraWhereParameters
* @return array|bool
*/
private function getUsedDates(
string $filterType,
string $calendarTableForGrouping,
string $calendarColumnForGrouping,
string $joinedTable,
string $joinedColumn,
$extraWhereClauses,
$extraWhereParameters
) {
QueryBuilderUtil::checkIdentifier($calendarTableForGrouping);
QueryBuilderUtil::checkIdentifier($calendarColumnForGrouping);
QueryBuilderUtil::checkIdentifier($joinedTable);
QueryBuilderUtil::checkIdentifier($joinedColumn);
/** @var EntityManagerInterface $manager */
$manager = $this->doctrine->getManagerForClass(CalendarDate::class);
$subQueryBuilder = $manager->createQueryBuilder();
$extraWhereClauses = str_replace(
$this->getDataFieldName(),
sprintf('%s.%s', $calendarTableForGrouping, $calendarColumnForGrouping),
$extraWhereClauses
);
$subQueryBuilder
->select(
sprintf(
"DISTINCT CONCAT(%s(%s.%s), '-', %s(%s.%s))",
$filterType,
$calendarTableForGrouping,
$calendarColumnForGrouping,
self::TYPE_YEAR,
$calendarTableForGrouping,
$calendarColumnForGrouping
)
)
->from($this->getCalendarEntity(), $calendarTableForGrouping)
->innerJoin(
$this->getTargetEntity(),
$joinedTable,
Join::WITH,
sprintf(
'(CAST(%s.%s as %s) = CAST(%s.%s as %s) %s)',
$calendarTableForGrouping,
$calendarColumnForGrouping,
'date',
$joinedTable,
$joinedColumn,
'date',
$extraWhereClauses
)
);
if ($extraWhereClauses != '') {
$subQueryBuilder->setParameters($extraWhereParameters);
}
$datesArray = $subQueryBuilder->getQuery()->getArrayResult();
return $this->arrayFlatten($datesArray);
}
protected function addWhereClause(QueryBuilder $qb, string $filterType)
{
$extraWhereClauses = !$qb->getDQLPart('where')
? null :
$this->getExtraWhereClauses($qb->getDQLPart('where')->getParts());
$whereClauseParameters = $this->getExtraWhereParameters($qb->getParameters(), $extraWhereClauses);
$usedDates = $this->getUsedDates(
$filterType,
'calendarDateTableForGrouping',
'date',
$this->get(self::JOINED_TABLE),
$this->get(self::JOINED_COLUMN),
$extraWhereClauses,
$whereClauseParameters
);
if (!$usedDates) {
return;
}
$dataFieldName = $this->getDataFieldName();
$notNullableField = $this->get(self::NOT_NULLABLE_FIELD);
QueryBuilderUtil::checkField($notNullableField);
$qb->andWhere(
$qb->expr()->orX(
$qb->expr()->andX(
$qb->expr()->notIn(
sprintf(
"CONCAT(%s(%s), '-', %s(%s))",
$filterType,
$dataFieldName,
self::TYPE_YEAR,
$dataFieldName
),
$usedDates
)
),
$qb->expr()->andX(
$qb->expr()->in(
sprintf(
"CONCAT(%s(%s), '-', %s(%s))",
$filterType,
$dataFieldName,
self::TYPE_YEAR,
$dataFieldName
),
$usedDates
),
$qb->expr()->isNotNull($notNullableField)
)
)
);
}
protected function addSelect(QueryBuilder $qb, array $parts, string $columnName)
{
QueryBuilderUtil::checkIdentifier($columnName);
$select = implode(', \'-\', ', $parts);
$qb->addSelect(sprintf('CONCAT(%s) as %s', $select, $columnName));
}
private function getSelectAlias(string $postfix): string
{
$selectAlias = $this->get(self::COLUMN_NAME) . ucfirst($postfix);
QueryBuilderUtil::checkField($selectAlias);
return $selectAlias;
}
/**
* If grouping by Day or Month make sure Year order is in same direction and keep multisort.
*/
public function applyOrderBy(OrmDatasource $datasource, String $sortKey, String $direction)
{
QueryBuilderUtil::checkField($sortKey);
$direction = QueryBuilderUtil::getSortOrder($direction);
$qb = $datasource->getQueryBuilder();
$added = false;
foreach ([self::TYPE_YEAR, self::TYPE_QUARTER, self::TYPE_MONTH, self::TYPE_DAY] as $groupBy) {
$columnName = $this->getSelectAlias($groupBy);
$groupingName = $this->getSelectClause($groupBy);
/** @var Select $select */
foreach ($qb->getDQLPart('select') as $select) {
foreach ($select->getParts() as $part) {
if (\in_array($part, [$groupingName, sprintf('%s as %s', $groupingName, $columnName)], true)) {
$qb->addOrderBy($columnName, $direction);
$added = true;
}
}
}
}
if (!$added) {
$qb->addOrderBy($sortKey, $direction);
}
}
/**
* @inheritDoc
*/
public function apply(FilterDatasourceAdapterInterface $ds, $data)
{
$data = $this->parseData($data);
if (!$data) {
return false;
}
/** @var OrmFilterDatasourceAdapter $ds */
/** @var QueryBuilder $qb */
$qb = $ds->getQueryBuilder();
$columnName = $this->get(self::COLUMN_NAME);
QueryBuilderUtil::checkField($columnName);
switch ($data['value']) {
case self::TYPE_DAY:
$dayGroupingName = $this->addFilter(self::TYPE_DAY, $qb);
$monthGroupingName = $this->addFilter(self::TYPE_MONTH, $qb);
$yearGroupingName = $this->addFilter(self::TYPE_YEAR, $qb);
$this->addSelect(
$qb,
[
$dayGroupingName,
$monthGroupingName,
$yearGroupingName,
],
$columnName
);
break;
case self::TYPE_MONTH:
$monthGroupingName = $this->addFilter(self::TYPE_MONTH, $qb);
$yearGroupingName = $this->addFilter(self::TYPE_YEAR, $qb);
$this->addSelect(
$qb,
[
$monthGroupingName,
$yearGroupingName,
],
$columnName
);
$this->addWhereClause($qb, self::TYPE_MONTH);
break;
case self::TYPE_QUARTER:
$quarterGroupingName = $this->addFilter(self::TYPE_QUARTER, $qb);
$yearGroupingName = $this->addFilter(self::TYPE_YEAR, $qb);
$this->addSelect(
$qb,
[
$quarterGroupingName,
$yearGroupingName,
],
$columnName
);
$this->addWhereClause($qb, self::TYPE_QUARTER);
break;
default:
$yearGroupingName = $this->addFilter(self::TYPE_YEAR, $qb);
$qb->addSelect(sprintf('%s as %s', $yearGroupingName, $columnName));
$this->addWhereClause($qb, self::TYPE_YEAR);
break;
}
$qb->addGroupBy($columnName);
return true;
}
/**
* Validates the filter field name.
*/
protected function validateFieldName()
{
QueryBuilderUtil::checkField($this->get(FilterUtility::DATA_NAME_KEY));
}
protected function createSubQueryBuilder(
OrmFilterDatasourceAdapter $ds,
string $rootEntity,
string $rootAlias,
string $rootField,
string $relAlias,
string $relJoinType
): QueryBuilder {
QueryBuilderUtil::checkIdentifier($relAlias);
QueryBuilderUtil::checkIdentifier($rootAlias);
$qb = $ds->createQueryBuilder()
->select($rootAlias)
->from($rootEntity, $rootAlias);
if ($relJoinType === Join::LEFT_JOIN) {
$qb->leftJoin(QueryBuilderUtil::getField($rootAlias, $rootField), $relAlias);
} else {
$qb->innerJoin(QueryBuilderUtil::getField($rootAlias, $rootField), $relAlias);
}
return $qb;
}
/**
* @inheritdoc
*/
public function buildNullValueExpr(
FilterDatasourceAdapterInterface $ds,
$fieldName,
$filterName,
$inverse = false
) {
QueryBuilderUtil::checkIdentifier($filterName);
[$entity, $alias, $field] = $this->getFilterParts($ds, $fieldName);
$rootAlias = sprintf('null_filter_%s', $filterName);
$relAlias = sprintf('null_filter_%s_rel', $filterName);
$qb = $this->createSubQueryBuilder($ds, $entity, $rootAlias, $field, $relAlias, 'LEFT');
$qb->where($inverse ? $ds->expr()->isNotNull($relAlias) : $ds->expr()->isNull($relAlias));
return $ds->expr()->in($alias, $qb->getDQL());
}
/**
* @inheritdoc
*/
public function buildComparisonExpr(
FilterDatasourceAdapterInterface $ds,
$fieldName,
$parameterName,
$filterName,
$inverse = false
) {
QueryBuilderUtil::checkIdentifier($parameterName);
[$entity, $alias, $field] = $this->getFilterParts($ds, $fieldName);
$rootAlias = sprintf('filter_%s', $ds->generateParameterName($filterName));
$relAlias = sprintf('filter_%s_rel', $ds->generateParameterName($filterName));
$qb = $this->createSubQueryBuilder($ds, $entity, $rootAlias, $field, $relAlias, 'INNER');
$qb->where($ds->expr()->in($relAlias, $parameterName, true));
return $inverse
? $ds->expr()->notIn($alias, $qb->getDQL())
: $ds->expr()->in($alias, $qb->getDQL());
}
/**
* @return $this
*/
protected function prepareQuery(QueryBuilder $query, string $rootAlias, string $joinAlias, string $itemsType)
{
QueryBuilderUtil::checkIdentifier($rootAlias);
QueryBuilderUtil::checkIdentifier($joinAlias);
$providers = $this->configManager->getProviders();
foreach ($providers as $provider) {
$configItems = $provider->getPropertyConfig()->getItems($itemsType);
foreach ($configItems as $code => $item) {
QueryBuilderUtil::checkIdentifier($code);
if (!isset($item['grid'])) {
continue;
}
if (!isset($item['options']['indexed']) || !$item['options']['indexed']) {
continue;
}
$alias = $joinAlias . $provider->getScope() . '_' . $code;
$fieldName = $provider->getScope() . '_' . $code;
if (isset($item['grid']['query'])) {
$query->andWhere($alias . '.value ' . $item['grid']['query']['operator'] . ' :' . $alias);
$query->setParameter($alias, (string) $item['grid']['query']['value']);
}
$query->leftJoin(
$rootAlias . '.indexedValues',
$alias,
'WITH',
$alias . ".code='" . $code . "' AND " . $alias . ".scope='" . $provider->getScope() . "'"
);
$query->addSelect($alias . '.value as ' . $fieldName);
}
}
return $this;
}
/**
* Returns a DQL expression that can be used to get a text representation of the given type of entities.
*
* @param string $className The FQCN of the entity
* @param string $alias The alias in SELECT or JOIN statement
* @param string|null $format The representation format, for example full, short, etc.
* If not specified a default representation is used
* @param string|null|Localization $locale The representation locale.
* If not specified a default locale is used
*
* @return string A DQL expression or NULL if the name cannot be resolved
*/
public function getNameDQL(string $className, string $alias, ?string $format = null, $locale = null): string
{
QueryBuilderUtil::checkIdentifier($alias);
$formats = $this->getFormatConfig($format ?: $this->defaultFormat);
foreach ($formats as $currentFormat) {
foreach ($this->providers as $provider) {
$val = $provider->getNameDQL($currentFormat['name'], $locale, $className, $alias);
if (false !== $val) {
return $val;
}
}
}
return null;
}
private function getManyToManyQueryBuilder(
ClassMetadata $mainEntityMetadata,
array $mainEntities,
string $fieldName
): QueryBuilder {
$mainEntityClass = $mainEntityMetadata->getName();
$mainEntityIdField = $this->getEntityIdField($mainEntityClass);
$assocMapping = $mainEntityMetadata->getAssociationMapping($fieldName);
$targetEntityClass = $mainEntityMetadata->getAssociationTargetClass($fieldName);
$targetEntityRepository = $this->doctrineHelper->getEntityRepositoryForClass($targetEntityClass);
$qbToMany = $targetEntityRepository->createQueryBuilder('collection_item');
QueryBuilderUtil::checkParameter($mainEntityClass);
QueryBuilderUtil::checkParameter($mainEntityIdField);
$qbToMany
->addSelect('entity.' . $mainEntityIdField)
->innerJoin($mainEntityClass, 'entity', Query\Expr\Join::WITH, $qbToMany->expr()->eq(1, 1))
->innerJoin('entity.' . $fieldName, 'entity_' . $fieldName)
->andWhere($qbToMany->expr()->eq('entity_' . $fieldName, 'collection_item'))
->andWhere($qbToMany->expr()->in('entity', ':entities'));
if (!empty($assocMapping['orderBy'])) {
foreach ($assocMapping['orderBy'] as $sort => $order) {
QueryBuilderUtil::checkParameter($sort);
QueryBuilderUtil::checkParameter($order);
$qbToMany->addOrderBy('collection_item.' . $sort, $order);
}
}
$qbToMany->setParameter(':entities', array_keys($mainEntities));
return $qbToMany;
}
private function getOneToManyQueryBuilder(
ClassMetadata $mainEntityMetadata,
array $mainEntities,
string $fieldName
): QueryBuilder {
$mainEntityClass = $mainEntityMetadata->getName();
$mainEntityIdField = $this->getEntityIdField($mainEntityClass);
$assocMapping = $mainEntityMetadata->getAssociationMapping($fieldName);
$targetEntityClass = $mainEntityMetadata->getAssociationTargetClass($fieldName);
$targetEntityRepository = $this->doctrineHelper->getEntityRepositoryForClass($targetEntityClass);
$qbToMany = $targetEntityRepository->createQueryBuilder('collection_item');
$mappedBy = $mainEntityMetadata->getAssociationMappedByTargetField($fieldName);
QueryBuilderUtil::checkParameter($mappedBy);
QueryBuilderUtil::checkParameter($mainEntityIdField);
$qbToMany
->addSelect('collection_item_' . $mappedBy . '.' . $mainEntityIdField)
->innerJoin('collection_item.' . $mappedBy, 'collection_item_' . $mappedBy)
->andWhere($qbToMany->expr()->in('collection_item_' . $mappedBy, ':entities'));
if (!empty($assocMapping['orderBy'])) {
foreach ($assocMapping['orderBy'] as $sort => $order) {
QueryBuilderUtil::checkParameter($sort);
QueryBuilderUtil::checkParameter($order);
$qbToMany->addOrderBy('collection_item.' . $sort, $order);
}
}
$qbToMany->setParameter(':entities', array_keys($mainEntities));
return $qbToMany;
}
/**
* @return Recipient[]
*/
public function getRecipients(
EmailRecipientsProviderArgs $args,
EmailAwareRepository $repository,
string $alias,
string $entityClass
): array {
QueryBuilderUtil::checkIdentifier($alias);
$searchRecipients = $this->search->simpleSearch(
$args->getQuery(),
0,
$args->getLimit(),
$this->search->getEntityAlias($entityClass)
);
$recipients = [];
if (!$searchRecipients->isEmpty()) {
$fullNameQueryPart = $this->dqlNameFormatter->getFormattedNameDQL($alias, $entityClass);
$excludedEmailNames = $args->getExcludedEmailNamesForEntity($entityClass);
$primaryEmailsQb = $repository
->getPrimaryEmailsQb($fullNameQueryPart, $excludedEmailNames)
->setMaxResults($args->getLimit());
$primaryEmailsQb->andWhere($primaryEmailsQb->expr()->in(sprintf('%s.id', $alias), ':entity_id_list'));
$primaryEmailsQb->setParameter(
'entity_id_list',
array_map(function (Result\Item $searchRecipient) {
return $searchRecipient->getRecordId();
}, $searchRecipients->getElements())
);
$primaryEmailsResult = $this->getRestrictedResult($primaryEmailsQb, $args);
$recipients = $this->recipientsFromResult($primaryEmailsResult, $entityClass);
}
return $recipients;
}
/**
* Adds "from" email address related columns to a query builder.
* The following columns are added:
* * fromEmailAddress - The text representation of email address
* * fromEmailAddressOwnerClass - The class name of email address owner
* * fromEmailAddressOwnerId - The id of email address owner
*
* @param QueryBuilder $qb The query builder to update
* @param string $emailAddressTableAlias The alias of the email address table
*/
public function addFromEmailAddress(QueryBuilder $qb, string $emailAddressTableAlias = 'a')
{
/**
* Doctrine does not support NULL as a scalar expression
* see https://github.com/doctrine/doctrine2/issues/5801
* as result we have to use NULLIF(0, 0) and NULLIF('', '') instead of NULL
*/
QueryBuilderUtil::checkIdentifier($emailAddressTableAlias);
$providers = $this->emailOwnerProviderStorage->getProviders();
if (empty($providers)) {
$qb->addSelect('NULLIF(\'\', \'\') AS fromEmailAddressOwnerClass');
$qb->addSelect('NULLIF(0, 0) AS fromEmailAddressOwnerId');
$qb->addSelect(sprintf('%s.email AS fromEmailAddress', $emailAddressTableAlias));
} else {
$emailAddressExpression = '';
$ownerClassExpression = '';
$ownerIdExpression = '';
foreach ($providers as $provider) {
$ownerFieldName = $this->emailOwnerProviderStorage->getEmailOwnerFieldName($provider);
$ownerClass = $provider->getEmailOwnerClass();
$ownerClassExpression .= sprintf(
'WHEN %s.%s IS NOT NULL THEN \'%s\' ',
$emailAddressTableAlias,
$ownerFieldName,
$ownerClass
);
if ($ownerIdExpression) {
$ownerIdExpression .= ', ';
}
$ownerIdExpression .= sprintf('IDENTITY(%s.%s) ', $emailAddressTableAlias, $ownerFieldName);
$emailAddressExpression .= sprintf(
'WHEN %s.%s IS NOT NULL THEN %s ',
$emailAddressTableAlias,
$ownerFieldName,
$this->entityNameResolver->getNameDQL($ownerClass, $ownerFieldName)
);
$qb->leftJoin(sprintf('%s.%s', $emailAddressTableAlias, $ownerFieldName), $ownerFieldName);
}
$ownerClassExpression = sprintf(
'(CASE %sELSE NULLIF(\'\', \'\') END) AS fromEmailAddressOwnerClass',
$ownerClassExpression
);
$ownerIdExpression = sprintf(
'COALESCE(%s) AS fromEmailAddressOwnerId',
$ownerIdExpression
);
$emailAddressExpression = sprintf(
'CONCAT(\'\', CASE WHEN %1$s.hasOwner = true THEN (%2$s) ELSE %1$s.email END) AS fromEmailAddress',
$emailAddressTableAlias,
sprintf('CASE %sELSE \'\' END', $emailAddressExpression)
);
$qb->addSelect($ownerClassExpression);
$qb->addSelect($ownerIdExpression);
$qb->addSelect($emailAddressExpression);
}
}
/**
* Get total row data from database
*
* @param ResultsObject $pageData Grid page data
* @param array $columnsConfig Total row columns config
* @param bool $perPage Get data only for page data or for all data
* @param bool $skipAclWalkerCheck Check Acl with acl helper or not
*/
protected function getData(ResultsObject $pageData, array $columnsConfig, bool $perPage = false, bool $skipAclWalkerCheck = false): array
{
// this method requires refactoring, see BAP-17427 for details
$totalQueries = [];
foreach ($columnsConfig as $field => $totalData) {
if (isset($totalData[Configuration::TOTALS_SQL_EXPRESSION_KEY])
&& $totalData[Configuration::TOTALS_SQL_EXPRESSION_KEY]
) {
$totalQueries[] = $totalData[Configuration::TOTALS_SQL_EXPRESSION_KEY] . ' AS ' . $field;
}
}
$queryBuilder = clone $this->masterQB;
$queryBuilder
->select($totalQueries)
->resetDQLParts(['groupBy', 'having']);
$this->addPageLimits($queryBuilder, $pageData, $perPage);
QueryBuilderUtil::removeUnusedParameters($queryBuilder);
$query = $queryBuilder->getQuery();
if (!$skipAclWalkerCheck) {
$query = $this->aclHelper->apply($query);
}
$resultData = $query
->setFirstResult(null)
->setMaxResults(1)
->getScalarResult();
return array_shift($resultData);
}
private function getObjectIdentifier(ActionConfiguration $actionConfiguration): ?string
{
$identifier = $actionConfiguration->offsetGetOr('object_identifier');
QueryBuilderUtil::checkIdentifier($identifier);
return $identifier;
}
private function getIdentifierField(ActionConfiguration $actionConfiguration): string
{
$identifier = $actionConfiguration->offsetGetOr('data_identifier');
if (!$identifier) {
throw new LogicException('Mass action must define identifier name');
}
QueryBuilderUtil::checkField($identifier);
return $identifier;
}
/**
* @inheritdoc
*/
public function visitDatasource(DatagridConfiguration $config, DatasourceInterface $datasource)
{
/** @var OrmDatasource $datasource */
$dataName = $config->offsetGetByPath('[features][entity_class_name_path]');
if (!$dataName) {
return;
}
/** @var QueryBuilder $qb */
$qb = $datasource->getQueryBuilder();
$excludedEntities = $this->featureChecker->getDisabledResourcesByType('entities');
if (!$excludedEntities) {
return;
}
QueryBuilderUtil::checkPath($dataName);
$excludedEntitiesParam = QueryBuilderUtil::generateParameterName('excluded_entities');
$qb
->andWhere($qb->expr()->notIn($dataName, sprintf(':%s', $excludedEntitiesParam)))
->setParameter($excludedEntitiesParam, $excludedEntities);
}
public function addDatePartsSelect(
\DateTime $start,
\DateTime $end,
QueryBuilder $qb,
string $entityField
) {
QueryBuilderUtil::checkField($entityField);
switch ($this->getFormatStrings($start, $end)['viewType']) {
case 'year':
$qb->addSelect(sprintf(
'%s as yearCreated',
$this->getEnforcedTimezoneFunction('YEAR', $entityField)
));
$qb->addGroupBy('yearCreated');
break;
case 'month':
$qb->addSelect(sprintf(
'%s as yearCreated',
$this->getEnforcedTimezoneFunction('YEAR', $entityField)
));
$qb->addSelect(
sprintf(
'%s as monthCreated',
$this->getEnforcedTimezoneFunction('MONTH', $entityField)
)
);
$qb->addGroupBy('yearCreated');
$qb->addGroupBy('monthCreated');
break;
case 'date':
$qb->addSelect(sprintf(
'%s as yearCreated',
$this->getEnforcedTimezoneFunction('YEAR', $entityField)
));
$qb->addSelect(sprintf(
'%s as weekCreated',
$this->getEnforcedTimezoneFunction('WEEK', $entityField)
));
$qb->addGroupBy('yearCreated');
$qb->addGroupBy('weekCreated');
break;
case 'day':
$qb->addSelect(sprintf(
'%s as yearCreated',
$this->getEnforcedTimezoneFunction('YEAR', $entityField)
));
$qb->addSelect(
sprintf(
'%s as monthCreated',
$this->getEnforcedTimezoneFunction('MONTH', $entityField)
)
);
$qb->addSelect(sprintf(
'%s as dayCreated',
$this->getEnforcedTimezoneFunction('DAY', $entityField)
));
$qb->addGroupBy('yearCreated');
$qb->addGroupBy('monthCreated');
$qb->addGroupBy('dayCreated');
break;
case 'time':
$qb->addSelect(sprintf(
'%s as dateCreated',
$this->getEnforcedTimezoneFunction('DATE', $entityField)
));
$qb->addSelect(sprintf(
'%s as hourCreated',
$this->getEnforcedTimezoneFunction('HOUR', $entityField)
));
$qb->addGroupBy('dateCreated');
$qb->addGroupBy('hourCreated');
break;
}
}
/**
* @inheritdoc
*/
public function getTransformSelectQuery(
$originalFieldName,
QueryBuilder $qb = null,
$rootAlias = null,
$newFieldName = null
) {
if (!$originalFieldName) {
throw new \InvalidArgumentException('You must specify original field name for base currency query');
}
if (!$qb && !$rootAlias) {
throw new \InvalidArgumentException('You must specify query builder or rootAlias for base currency query');
}
if (!$rootAlias) {
$rootAliases = $qb->getRootAliases();
$rootAlias = array_shift($rootAliases);
}
return QueryBuilderUtil::sprintf('%s.%sValue', $rootAlias, $originalFieldName);
}
/**
* Removes unused parameters from query builder
*/
public function fixUnusedParameters(QueryBuilder $qb)
{
QueryBuilderUtil::removeUnusedParameters($qb);
}
/**
* @inheritdoc
*/
public function walkComparison(Comparison $comparison)
{
if (!isset($this->queryAliases[0])) {
throw new QueryException('No aliases are set before invoking walkComparison().');
}
[$operator, $modifier] = array_pad(explode('/', $comparison->getOperator(), 2), 2, null);
if (!isset($this->comparisonExpressions[$operator])) {
throw new QueryException(sprintf('Unknown comparison operator "%s".', $operator));
}
$field = $this->getField($comparison->getField());
QueryBuilderUtil::checkPath($field);
$expression = $field;
if ('i' === $modifier) {
$expression = sprintf('LOWER(%s)', $expression);
} elseif ($modifier) {
throw new QueryException(sprintf(
'Unknown modifier "%s" for comparison operator "%s".',
$modifier,
$operator
));
}
return $this->comparisonExpressions[$operator]
->walkComparisonExpression(
$this,
$field,
$expression,
$this->getParameterName($comparison->getField()),
$this->walkValue($comparison->getValue())
);
}
private function getSubQuery(
string $target,
array $path,
string $entityIdExpr,
string $uniqueKey
): QueryBuilder {
QueryBuilderUtil::checkIdentifier($uniqueKey);
$alias = 'inherit_' . $uniqueKey;
/** @var QueryBuilder $subQueryBuilder */
$subQueryBuilder = $this->doctrine->getManagerForClass($target)->createQueryBuilder();
$subQueryBuilder->select($alias . '.id')->from($target, $alias);
foreach ($path as $key => $field) {
QueryBuilderUtil::checkIdentifier($key);
$newAlias = 't_' . $uniqueKey . '_' . $key;
if (\is_array($field)) {
$subQueryBuilder->join(
$field['join'],
$newAlias,
$field['conditionType'],
$subQueryBuilder->expr()->eq(QueryBuilderUtil::getField($newAlias, $field['field']), $alias)
);
} else {
$subQueryBuilder->join(QueryBuilderUtil::getField($alias, $field), $newAlias);
}
$alias = $newAlias;
}
$subQueryBuilder->where($subQueryBuilder->expr()->eq(QueryBuilderUtil::getField($alias, 'id'), $entityIdExpr));
$this->limitHelper->setLimit(
$subQueryBuilder,
static::MAX_INHERITANCE_TARGETS,
'id'
);
return $subQueryBuilder;
}
/**
* Apply to given query builder object additional conditions
* for integrate activity lists from inheritance target
*/
public function applyInheritanceActivity(
QueryBuilder $qb,
array $inheritanceTarget,
string $aliasSuffix,
string $entityIdExpr
): void {
QueryBuilderUtil::checkIdentifier($aliasSuffix);
$alias = 'ta_' . $aliasSuffix;
$qb->leftJoin(QueryBuilderUtil::getField('activity', $inheritanceTarget['targetClassAlias']), $alias);
$qb->andWhere($qb->expr()->in(
$alias . '.id',
$this->getSubQuery(
$inheritanceTarget['targetClass'],
$inheritanceTarget['path'],
$entityIdExpr,
$aliasSuffix
)->getDQL()
));
}
protected function getEntityAlias(): string
{
[$alias] = explode('.', $this->getOr(FilterUtility::DATA_NAME_KEY));
QueryBuilderUtil::checkIdentifier($alias);
return $alias;
}
protected function createActivityQueryBuilder(
EntityManager $em,
array $data,
string $entityIdField
): QueryBuilder {
QueryBuilderUtil::checkIdentifier($entityIdField);
$entityClass = $data['entityClassName'];
$joinField = sprintf(
'%s.%s',
$this->activityListAlias,
ExtendHelper::buildAssociationName($entityClass, ActivityListEntityConfigDumperExtension::ASSOCIATION_KIND)
);
$activityListRepository = $em->getRepository('OroActivityListBundle:ActivityList');
$activityQb = $activityListRepository
->createQueryBuilder($this->activityListAlias)
->select('1')
->setMaxResults(1);
if (!$this->activityAssociationHelper->hasActivityAssociations($entityClass)
&& !$activityListRepository->getRecordsCountForTargetClass($entityClass)
) {
$activityQb->andWhere('1 = 0');
return $activityQb;
}
$activityQb
->join($joinField, $this->activityAlias)
->andWhere(sprintf('%s.id = %s.%s', $this->activityAlias, $this->getEntityAlias(), $entityIdField));
$entityField = $this->getField($data);
$dateRangeField = str_starts_with($entityField, '$') ? substr($entityField, 1) : null;
if ($dateRangeField) {
$data['dateRange'] = $data['filter']['data'];
unset($data['filter']);
}
$this->activityListFilterHelper->addFiltersToQuery(
$activityQb,
$data,
$dateRangeField,
$this->activityListAlias
);
if (isset($data['filter'])) {
$activityDs = new OrmFilterDatasourceAdapter($activityQb);
$expr = $activityDs->expr()->exists($this->createRelatedActivityDql($activityDs, $data));
$this->applyFilterToClause($activityDs, $expr);
}
return $activityQb;
}
public function getForecastQB(
CurrencyQueryBuilderTransformerInterface $qbTransformer,
string $alias = 'o',
array $excludedStatuses = ['lost', 'won']
): QueryBuilder {
QueryBuilderUtil::checkIdentifier($alias);
$qb = $this->createQueryBuilder($alias);
$baBaseCurrencyQuery = $qbTransformer->getTransformSelectQuery('budgetAmount', $qb, $alias);
$qb->select(
QueryBuilderUtil::sprintf('COUNT(%s.id) as inProgressCount', $alias),
sprintf('SUM(%s) as budgetAmount', $baBaseCurrencyQuery),
sprintf('SUM((%s) * %s.probability) as weightedForecast', $baBaseCurrencyQuery, $alias)
);
if ($excludedStatuses) {
$qb->andWhere($qb->expr()->notIn(QueryBuilderUtil::getField($alias, 'status'), $excludedStatuses));
}
return $qb;
}
public function getFieldExpr(string $entityClass, QueryBuilder $qb, string $fieldName): string
{
if ($this->virtualFieldProvider->isVirtualField($entityClass, $fieldName)) {
return $this->getVirtualFieldExpression($qb, $entityClass, $fieldName);
}
return QueryBuilderUtil::getField($this->getRootTableAlias($qb), $fieldName);
}
protected function getAddressMatchExpr(
QueryBuilder $qb,
object $addressEntity,
array $fields,
string $alias,
int $idx,
array $configuration
): Andx {
$andExpr = $qb->expr()->andX();
QueryBuilderUtil::checkIdentifier($alias);
foreach ($fields as $field) {
QueryBuilderUtil::checkIdentifier($field);
$qbFieldName = $alias . '.' . $field;
$qbParameterName = ':' . $field . (int) $idx;
$andExpr->add($this->getFieldExpr($qb, $qbFieldName, $qbParameterName, $configuration));
$fieldValue = $this->propertyAccessor->getValue($addressEntity, $field);
$qb->setParameter($qbParameterName, $fieldValue);
}
return $andExpr;
}
protected function getFieldExpr(QueryBuilder $qb, string $qbFieldName, string $qbParameterName, array $configuration): \Doctrine\ORM\Query\Expr\Base
{
QueryBuilderUtil::checkParameter($qbParameterName);
$fieldExpr = $qb->expr()->eq($qbFieldName, $qbParameterName);
$options = $configuration[Configuration::DISCOVERY_OPTIONS_KEY];
if (!empty($options[Configuration::DISCOVERY_EMPTY_KEY])) {
$fieldExpr = $qb->expr()->orX(
$fieldExpr,
$qb->expr()->eq($qbFieldName, ':emptyValue'),
$qb->expr()->isNull($qbFieldName)
);
$qb->setParameter('emptyValue', '');
}
return $fieldExpr;
}
public function getCampaignsByCloseRevenueQB(
string $opportunitiesAlias,
CurrencyQueryBuilderTransformerInterface $qbTransformer
): QueryBuilder {
QueryBuilderUtil::checkIdentifier($opportunitiesAlias);
$qb = $this->getEntityManager()->createQueryBuilder();
$crSelect = $qbTransformer->getTransformSelectQuery('closeRevenue', $qb, $opportunitiesAlias);
$qb
->select(
'campaign.name as label',
sprintf(
'SUM(%s) as closeRevenue',
$crSelect
)
)
->from('OroCampaignBundle:Campaign', 'campaign')
->join('OroSalesBundle:Lead', 'lead', 'WITH', 'lead.campaign = campaign')
->join('lead.opportunities', $opportunitiesAlias)
->where(sprintf('%s.status=\'won\'', $opportunitiesAlias))
->andWhere(sprintf('%s.closeRevenueValue>0', $opportunitiesAlias))
->orderBy('closeRevenue', 'DESC')
->groupBy('campaign.name');
return $qb;
}
public function getCampaignsOpportunitiesQB(string $opportunitiesAlias): QueryBuilder
{
QueryBuilderUtil::checkIdentifier($opportunitiesAlias);
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('campaign.name as label', sprintf('COUNT(%s.id) as number', $opportunitiesAlias))
->from('OroCampaignBundle:Campaign', 'campaign')
->join('OroSalesBundle:Lead', 'lead', 'WITH', 'lead.campaign = campaign')
->join('lead.opportunities', $opportunitiesAlias)
->orderBy('number', 'DESC')
->groupBy('campaign.name');
return $qb;
}
public function getCampaignsLeadsQB(string $leadAlias): QueryBuilder
{
QueryBuilderUtil::checkIdentifier($leadAlias);
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select(
'campaign.name as label',
sprintf('COUNT(%s.id) as number', $leadAlias),
'MAX(campaign.createdAt) as maxCreated'
)
->from('OroCampaignBundle:Campaign', 'campaign')
->leftJoin('OroSalesBundle:Lead', $leadAlias, 'WITH', sprintf('%s.campaign = campaign', $leadAlias))
->orderBy('maxCreated', 'DESC')
->groupBy('campaign.name');
return $qb;
}
public function getPossibleValues($fieldName)
{
if (array_key_exists($fieldName, $this->cache)) {
return $this->cache[$fieldName];
}
QueryBuilderUtil::checkIdentifier($fieldName);
$qb = $this->doctrineHelper->getEntityRepository(Product::class)->createQueryBuilder('p');
$field = sprintf('p.%s', $fieldName);
$values = $qb
->select($field)
->distinct()
->where($qb->expr()->isNotNull($field))
->orderBy($qb->expr()->asc($field))
->getQuery()
->getScalarResult();
$values = array_column($values, $fieldName);
$values = array_combine($values, $values);
$this->cache[$fieldName] = $values;
return $values;
}