Programming Language PHP
Namespace Oro\Component\DoctrineUtils\ORM
Class QueryBuilderUtil
Method/Function checkIdentifier
Total Examples 31
31 code examples of PHP Oro\Component\DoctrineUtils\ORM\QueryBuilderUtil::checkIdentifier extracted from open source projects
/**
* @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;
}
/**
* 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;
}
/**
* @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);
}
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);
}
}
/**
* 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;
}
/**
* @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 addSelect(QueryBuilder $qb, array $parts, string $columnName)
{
QueryBuilderUtil::checkIdentifier($columnName);
$select = implode(', \'-\', ', $parts);
$qb->addSelect(sprintf('CONCAT(%s) as %s', $select, $columnName));
}
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;
}
/**
* @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);
}
}
private function getObjectIdentifier(ActionConfiguration $actionConfiguration): ?string
{
$identifier = $actionConfiguration->offsetGetOr('object_identifier');
QueryBuilderUtil::checkIdentifier($identifier);
return $identifier;
}
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;
}
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;
}
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;
}