Symfony Profiler

<?php
namespace Products\NotificationsBundle\Service\Query;
use App\Model\Query\ConditionQuery\AbstractOperatorCondition;
use App\Model\Query\ConditionQuery\Condition\ContainsCondition;
use App\Model\Query\ConditionQuery\Condition\EmptyCondition;
use App\Model\Query\ConditionQuery\Condition\EqualCondition;
use App\Model\Query\ConditionQuery\Condition\GreaterThanCondition;
use App\Model\Query\ConditionQuery\Condition\GreaterThanEqualCondition;
use App\Model\Query\ConditionQuery\Condition\InCondition;
use App\Model\Query\ConditionQuery\Condition\LessThanCondition;
use App\Model\Query\ConditionQuery\Condition\LessThanEqualCondition;
use App\Model\Query\ConditionQuery\Condition\NotContainsCondition;
use App\Model\Query\ConditionQuery\Condition\NotEmptyCondition;
use App\Model\Query\ConditionQuery\Condition\NotEqualCondition;
use App\Model\Query\ConditionQuery\Condition\NotInCondition;
use App\Model\Query\ConditionQuery\Condition\NotNullCondition;
use App\Model\Query\ConditionQuery\Condition\NotOverlapsCondition;
use App\Model\Query\ConditionQuery\Condition\NullCondition;
use App\Model\Query\ConditionQuery\Condition\OverlapsCondition;
use App\Model\Query\ConditionQuery\ConditionGroupInterface;
use App\Model\Query\ConditionQuery\ConditionQuery;
use App\Model\Query\ConditionQuery\Type\BooleanType;
use App\Model\Query\ConditionQuery\Type\DateType;
use App\Model\Query\ConditionQuery\Type\FloatType;
use Cms\CoreBundle\Util\Doctrine\EntityManager;
use Doctrine\ORM\Query\Expr\Comparison;
use Doctrine\ORM\Query\Expr\Func;
use Doctrine\ORM\QueryBuilder;
use Exception;
use LogicException;
use Products\NotificationsBundle\Util\ListBuilder\AbstractListBuilder;
final class NotificationsConditionQueryBuilder
{
private const JSON_LENGTH = 'JSON_LENGTH';
private const JSON_FILTER_FUNCTION_MAP = [
'count' => self::JSON_LENGTH,
];
/**
* @var EntityManager
*/
protected EntityManager $em;
/**
* @param EntityManager $em
*/
public function __construct(EntityManager $em)
{
$this->em = $em;
}
/**
* @param ConditionQuery $query
* @param string|null $qid
* @return QueryBuilder
* @throws Exception
*/
public function build(ConditionQuery $query, ?string $qid = null): QueryBuilder
{
// check for config
if ( ! $query->getConditionConfig()) {
throw new Exception();
}
$class = $query->getEntityClass();
$alias = $query->getEntityAlias() . $qid;
$qb = $this->em->createQueryBuilder()
->select($alias)
->from($class, $alias);
// if no conditions, force some kind of condition that will always return zero results
if ( ! $query->isUsable()) {
$qb->andWhere(
sprintf(
'%s.id = 0',
$alias
)
);
} else {
[$conditions, $parameters, $entities] = $this->buildConditions($query, $qid);
$qb->andWhere($conditions);
foreach ($parameters as $name => $value) {
$qb->setParameter($name, $value);
}
foreach ($entities as $entity) {
if ( ! in_array($entity . $qid, $qb->getAllAliases())) {
switch ($entity) {
// if we need to add a profile, probably means that we are starting with students
case AbstractListBuilder::ENTITIES__PROFILES:
if ( ! in_array(
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS . $qid,
$qb->getAllAliases()
)) {
$qb->leftJoin(
AbstractListBuilder::ENTITIES__STUDENTS . $qid . '.relationships',
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS . $qid,
);
}
$qb->leftJoin(
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS . $qid . '.profile',
AbstractListBuilder::ENTITIES__PROFILES . $qid,
);
break;
// if we need to add a student, probably means that we are starting with profiles
case AbstractListBuilder::ENTITIES__STUDENTS:
if ( ! in_array(
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS . $qid,
$qb->getAllAliases()
)) {
$qb->leftJoin(
AbstractListBuilder::ENTITIES__PROFILES . $qid . '.relationships',
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS . $qid,
);
}
$qb->leftJoin(
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS . $qid . '.student',
AbstractListBuilder::ENTITIES__STUDENTS . $qid,
);
break;
// handle relationships
case AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS:
switch ($query->getEntity(true)) {
case ConditionQuery::STUDENT_ENTITY:
$qb->leftJoin(
AbstractListBuilder::ENTITIES__STUDENTS . $qid . '.relationships',
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS . $qid,
);
break;
case ConditionQuery::PROFILE_ENTITY:
$qb->leftJoin(
AbstractListBuilder::ENTITIES__PROFILES . $qid . '.relationships',
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS . $qid,
);
break;
default:
throw new Exception();
}
break;
case AbstractListBuilder::ENTITIES__PROFILE_AUTOMATION_RECORDS:
$qb->leftJoin(
AbstractListBuilder::ENTITIES__PROFILES . $qid . '.automationRecords',
AbstractListBuilder::ENTITIES__PROFILE_AUTOMATION_RECORDS . $qid,
);
break;
default:
throw new Exception();
}
}
}
}
return $qb;
}
/**
* @param ConditionGroupInterface $query
* @param string|null $qid
* @return array
*/
private function buildConditions(ConditionGroupInterface $query, ?string $qid = null): array
{
$expressionBuilder = $this->em->createQueryBuilder()->expr();
if ($query->getMode() === ConditionGroupInterface::MODES__OR) {
$rootExpression = $expressionBuilder->orX();
} else {
$rootExpression = $expressionBuilder->andX();
}
$parameters = [];
$entities = [];
foreach ($query->getConditions() as $condition) {
if ($condition instanceof ConditionGroupInterface) {
$newConditions = $this->buildConditions($condition, $qid);
$rootExpression->add($newConditions[0]);
$parameters = array_merge($parameters, $newConditions[1]);
$entities = array_merge($entities, $newConditions[2]);
} elseif ($condition instanceof AbstractOperatorCondition) {
// handle special "today" value for date type here
// use the customer timezone, as these are just strings and not like normalized date/time types in the database...
if ($condition->getType() instanceof DateType && $condition->getValue() === 'today') {
$condition->setValue(
$query->getConditionContext()->getToday()->format('Y-m-d')
);
}
// get the name of the entity this condition is trying to use and track it
$entities[] = $condition->getFilterEntity();
$paramName = $this->buildParamName($condition, $qid);
$rootExpression->add($this->buildExpression($condition, $paramName, $qid));
if (($param = $this->buildParameter($condition)) !== null) {
$parameters[$paramName] = $param;
}
}
}
return [$rootExpression, $parameters, array_values(array_filter(array_unique($entities)))];
}
/**
* @param AbstractOperatorCondition $operatorCondition
* @param string|null $paramName
* @param string|null $qid
* @return string
*/
private function buildFieldName(
AbstractOperatorCondition $operatorCondition,
?string $paramName,
?string $qid = null
): string {
if (empty($operatorCondition->getFilterJsonKey())) {
return sprintf(
'%s%s.%s',
$operatorCondition->getFilterEntity(),
$qid,
$operatorCondition->getFilterEntityProperty()
);
}
$prefix = $suffix = '';
switch (true) {
case $operatorCondition::NAME === ContainsCondition::NAME:
case $operatorCondition::NAME === NotContainsCondition::NAME:
$prefix = sprintf('%s(', 'JSON_CONTAINS');
$suffix = sprintf(', :%s)', $paramName);
break;
case $operatorCondition::NAME === OverlapsCondition::NAME:
case $operatorCondition::NAME === NotOverlapsCondition::NAME:
$prefix = sprintf('%s(', 'JSON_OVERLAPS');
$suffix = sprintf(', :%s)', $paramName);
break;
case is_array($operatorCondition->getValue()) && $operatorCondition::NAME === EmptyCondition::NAME:
case is_array($operatorCondition->getValue()) && $operatorCondition::NAME === NotEmptyCondition::NAME:
$prefix = 'JSON_LENGTH(';
$suffix = sprintf(')',);
break;
case $operatorCondition->getType() instanceof BooleanType:
$prefix = 'CAST(';
$suffix = ' AS UNSIGNED)';
break;
case $operatorCondition->getType() instanceof FloatType:
case is_float($operatorCondition->getValue()):
$prefix = 'CAST(';
$suffix = ' AS DECIMAL(10,3))';
break;
}
$field = sprintf(
'%sJSON_EXTRACT(%s%s.%s, \'$.%s\')%s',
$prefix,
$operatorCondition->getFilterEntity(),
$qid,
$operatorCondition->getFilterEntityProperty(),
implode(
'.',
array_map(
function (string $key) {
if ($key === '*') {
return $key;
}
return '"' . $key . '"';
},
explode('.', $operatorCondition->getFilterJsonKey()),
),
),
$suffix,
);
$filterFunction = $operatorCondition->getFilterFunction();
if ($filterFunction === null) {
return $field;
}
switch (self::JSON_FILTER_FUNCTION_MAP[$filterFunction]) {
case self::JSON_LENGTH:
return sprintf('JSON_LENGTH(%s)', $field);
default:
throw new LogicException('Unsupported filter function');
}
}
/**
* @param AbstractOperatorCondition $operatorCondition
* @param string|null $qid
* @return string
*/
private function buildParamName(AbstractOperatorCondition $operatorCondition, ?string $qid = null): ?string
{
switch ($operatorCondition::NAME) {
case NullCondition::NAME:
case NotNullCondition::NAME:
return null;
default:
return str_replace(
'-',
'_',
sprintf(
'p%s_%s%s%s%s',
$qid ?: uniqid(),
spl_object_id($operatorCondition),
$operatorCondition->getFilterEntity(),
$operatorCondition->getFilterEntityProperty(),
crc32($operatorCondition->getFilterJsonKey()),
)
);
}
}
/**
* @param AbstractOperatorCondition $operatorCondition
* @param string|null $paramName
* @param string|null $qid
* @return Comparison|Func|string
*/
private function buildExpression(
AbstractOperatorCondition $operatorCondition,
?string $paramName,
?string $qid = null
) {
$expressionBuilder = $this->em->createQueryBuilder()->expr();
$fieldName = $this->buildFieldName($operatorCondition, $paramName, $qid);
switch ($operatorCondition::NAME) {
case EqualCondition::NAME:
case EmptyCondition::NAME:
return $expressionBuilder->eq($fieldName, ':' . $paramName);
case NotEqualCondition::NAME:
case NotEmptyCondition::NAME:
return $expressionBuilder->neq($fieldName, ':' . $paramName);
case GreaterThanCondition::NAME:
return $expressionBuilder->gt($fieldName, ':' . $paramName);
case GreaterThanEqualCondition::NAME:
return $expressionBuilder->gte($fieldName, ':' . $paramName);
case LessThanCondition::NAME:
return $expressionBuilder->lt($fieldName, ':' . $paramName);
case LessThanEqualCondition::NAME:
return $expressionBuilder->lte($fieldName, ':' . $paramName);
case InCondition::NAME:
return $expressionBuilder->in($fieldName, ':' . $paramName);
case NotInCondition::NAME:
return $expressionBuilder->notIn($fieldName, ':' . $paramName);
case ContainsCondition::NAME:
// when array is empty JSON_CONTAINS returns unexpected result (returns 1 but we really want a "falsey" result)
$value = $operatorCondition->getValue();
if (is_array($value) && empty($value)) {
return $expressionBuilder->eq($fieldName, 0);
}
return $expressionBuilder->eq($fieldName, 1);
case NotContainsCondition::NAME:
// when array is empty JSON_CONTAINS returns unexpected result (returns 1 but we really want a "falsey" result)
$value = $operatorCondition->getValue();
if (is_array($value) && empty($value)) {
return $expressionBuilder->neq($fieldName, 0);
}
return $expressionBuilder->neq($fieldName, 1);
case OverlapsCondition::NAME:
return $expressionBuilder->eq($fieldName, 1);
case NotOverlapsCondition::NAME:
return $expressionBuilder->neq($fieldName, 1);
case NullCondition::NAME:
return $expressionBuilder->isNull($fieldName);
case NotNullCondition::NAME:
return $expressionBuilder->isNotNull($fieldName);
default:
throw new LogicException("Unsupported operator");
}
}
/**
* @param AbstractOperatorCondition $operatorCondition
* @return mixed|string|null
*/
private function buildParameter(AbstractOperatorCondition $operatorCondition)
{
switch ($operatorCondition::NAME) {
case EqualCondition::NAME:
case NotEqualCondition::NAME:
case GreaterThanCondition::NAME:
case GreaterThanEqualCondition::NAME:
case LessThanCondition::NAME:
case LessThanEqualCondition::NAME:
case InCondition::NAME:
case NotInCondition::NAME:
return $operatorCondition->getValue();
case EmptyCondition::NAME:
case NotEmptyCondition::NAME:
return is_array($operatorCondition->getValue()) ? 0 : '';
case ContainsCondition::NAME:
case NotContainsCondition::NAME:
case OverlapsCondition::NAME:
case NotOverlapsCondition::NAME:
if (is_array($operatorCondition->getValue())) {
return json_encode($operatorCondition->getValue());
}
return '"' . $operatorCondition->getValue() . '"';
case NullCondition::NAME:
case NotNullCondition::NAME:
return null;
default:
throw new LogicException("Unsupported operator");
}
}
}

src/Products/NotificationsBundle/Service/Query/NotificationsConditionQueryBuilder.php line 195