Symfony Profiler

<?php
namespace Products\NotificationsBundle\Service;
use App\Service\Query\ConditionQueryTransformer;
use Cms\CoreBundle\Util\Doctrine\EntityManager;
use Doctrine\ORM\QueryBuilder;
use Products\NotificationsBundle\Entity\AbstractList;
use Products\NotificationsBundle\Entity\AbstractRecipient;
use Products\NotificationsBundle\Entity\Profile;
use Products\NotificationsBundle\Entity\Student;
use Products\NotificationsBundle\Service\ChannelHandlers\AbstractChannelHandler;
use Products\NotificationsBundle\Service\Query\NotificationsConditionQueryBuilder;
use Products\NotificationsBundle\Util\ListBuilder\AbstractListBuilder;
use Products\NotificationsBundle\Util\ListBuilder\ConditionListBuilder;
use Products\NotificationsBundle\Util\ListBuilder\DistrictListBuilder;
use Products\NotificationsBundle\Util\ListBuilder\SchoolListBuilder;
use Products\NotificationsBundle\Util\ListBuilder\StaticListBuilder;
/**
* Common methods for dealing with AbstractLists and their complexities when it comes to querying against them.
*
* The query builders that this service returns follow this general format:
*
* SELECT the "root" entity
* FROM the "root" entity's table
* LEFT JOIN to get to the associated entity (if recipients, join in profiles; if profiles, join in recipients)
* WHERE
* (
* (
* "root" entity id is IN (subquery to grab ids based on list 0's configuration)
* )
* ...
* OR
* ...
* (
* "root" entity id is IN (subquery to grab ids based on list N's configuration)
* )
* )
* AND... (any other filters needed at the "top" layer of the query to limit the final results)
*
* Basically:
* 1. There is an "outer" query that wraps the whole thing; this is what is selected against.
* 2. The outer query will always have the "profiles" and the "recipients" available to it.
* 3. However, the "root" of the entire query (what entity is actually being selected from the database) changes.
* 4. There is a grouped (OR'd) set of subqueries that perform the list checks (can pass one or multiple lists; work in the query is the same).
* 5. The subqueries are done so that the all the entities and the params contained within them are unique and don't affect the "outer" query.
* 6. Once all the subqueris are done, in the "outer" query after the grouped subqueries, more specific filtering logic can be applied to the results of the "outer" query.
*
* In essence, this subgroup of queries determines which IDs of the "root" entity fall within the list's configuration.
* Then, more work can be done to filter the resultset afterwards.
*/
final class ListBuilderService
{
const BUILDERS = [
DistrictListBuilder::class,
SchoolListBuilder::class,
ConditionListBuilder::class,
StaticListBuilder::class,
];
private EntityManager $em;
private NotificationsConditionQueryBuilder $notificationsConditionQueryBuilder;
private ConditionQueryTransformer $conditionQueryTransformer;
/**
* @param EntityManager $em
* @param NotificationsConditionQueryBuilder $notificationsConditionQueryBuilder
* @param ConditionQueryTransformer $conditionQueryTransformer
*/
public function __construct(EntityManager $em, NotificationsConditionQueryBuilder $notificationsConditionQueryBuilder, ConditionQueryTransformer $conditionQueryTransformer)
{
$this->em = $em;
$this->notificationsConditionQueryBuilder = $notificationsConditionQueryBuilder;
$this->conditionQueryTransformer = $conditionQueryTransformer;
}
/**
* Returns a query builder that has already been configured to return the results of a list.
* The query builder can then have more criteria applied to it to limit search results.
*
* @param array<AbstractList>|AbstractList $lists
* @param string|null $entity
* @return QueryBuilder
*/
public function search(
$lists,
?string $entity = null
): QueryBuilder
{
$qb = $this->build(
$lists = !is_array($lists) ? [$lists] : $lists,
$entity = $entity ?: $lists[0]->getEntityClass(),
);
switch (true) {
case $entity === Profile::class:
$qb
->addSelect(AbstractListBuilder::ENTITIES__PROFILE_CONTACTS)
->addSelect(AbstractListBuilder::ENTITIES__RECIPIENTS);
break;
case $entity === AbstractRecipient::class || in_array($entity, AbstractRecipient::DISCRS):
$qb
->addSelect(AbstractListBuilder::ENTITIES__PROFILE_CONTACTS)
->addSelect(AbstractListBuilder::ENTITIES__PROFILES);
break;
case $entity === Student::class:
$qb
->addSelect(AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS)
->addSelect(AbstractListBuilder::ENTITIES__PROFILES);
break;
default:
throw new \Exception();
}
return $qb;
}
/**
* Builds out a query for a list that can then be used to get the "sendable" IDs.
* Secondary classes can be given to return tuples of IDs for extra information in the sending process.
*
* @param array<AbstractList>|AbstractList $lists
* @param string|null $entity
* @return QueryBuilder
*/
public function identify(
$lists,
?string $entity = null
): QueryBuilder
{
return ($qb = $this->build($lists, $entity))
// reset the select because we are only getting the ids
// no need for ordering as the ids are only ever used in like a technical sense
->resetDQLParts(['select', 'orderBy'])
// just get the id of the root alias
->select(
sprintf(
'%s.id AS %s',
$qb->getRootAliases()[0],
AbstractChannelHandler::ITEMS__TARGET,
)
);
}
/**
* Generates a count query builder that will count the total items in a given list.
*
* @param array<AbstractList>|AbstractList $lists
* @param string|null $entity
* @return QueryBuilder
*/
public function count(
$lists,
?string $entity = null
): QueryBuilder
{
return ($qb = $this->build($lists, $entity))
// clear what we are overriding
// clearing ordering as it will speed up the count
->resetDQLParts(['select', 'orderBy'])
// select only a scalar value, count by the root entity
->select(
sprintf(
'COUNT(DISTINCT %s)',
// there should only ever be one root alias
$qb->getRootAliases()[0],
)
);
}
/**
* @param array<AbstractList>|AbstractList $lists
* @param string|null $entity
* @return QueryBuilder
*/
public function build(
$lists,
?string $entity = null
): QueryBuilder
{
// if we don't have an array, normalize it
$lists = !is_array($lists) ? [$lists] : $lists;
// default entity if not given
$entity = $entity ?: $lists[0]->getEntityClass();
// create the base query builder, this depends on what type of target entity we want
switch (true) {
case $entity === Profile::class:
$qb = $this->em->getRepository($entity)
->createQueryBuilder(AbstractListBuilder::ENTITIES__PROFILES)
->leftJoin(
AbstractListBuilder::ENTITIES__PROFILES.'.contacts',
AbstractListBuilder::ENTITIES__PROFILE_CONTACTS,
)
->leftJoin(
AbstractListBuilder::ENTITIES__PROFILE_CONTACTS.'.recipient',
AbstractListBuilder::ENTITIES__RECIPIENTS,
);
break;
case $entity === AbstractRecipient::class || in_array($entity, AbstractRecipient::DISCRS):
$qb = $this->em->getRepository($entity)
->createQueryBuilder(AbstractListBuilder::ENTITIES__RECIPIENTS)
->leftJoin(
AbstractListBuilder::ENTITIES__RECIPIENTS.'.contacts',
AbstractListBuilder::ENTITIES__PROFILE_CONTACTS,
)
->leftJoin(
AbstractListBuilder::ENTITIES__PROFILE_CONTACTS.'.profile',
AbstractListBuilder::ENTITIES__PROFILES,
);
break;
// mainly used when doing stuff with invocations...
case $entity === Student::class:
$qb = $this->em->getRepository($entity)
->createQueryBuilder(AbstractListBuilder::ENTITIES__STUDENTS)
->leftJoin(
AbstractListBuilder::ENTITIES__STUDENTS.'.relationships',
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS,
)
->leftJoin(
AbstractListBuilder::ENTITIES__PROFILE_RELATIONSHIPS.'.profile',
AbstractListBuilder::ENTITIES__PROFILES,
);
break;
default:
throw new \Exception();
}
// if lists are empty, we need to ensure that nothing goes out
// we should never have an id on the root entity that is 0...
if (empty($lists)) {
return $qb->andWhere('0 = 1');
}
// NOTE: orm/dbal does not support unions, so have to workaround using subqueries...
// sets of lists are essentially unioned or "or'd" together
// meaning the id should match in any of the lists given, not all (that would be an intersect operation)
// loop over the lists and add in the proper subqueries
$clauses = [];
foreach ($lists as $list) {
// get the builder for the given list
$builder = $this->getBuilder($list);
// generate a subquery qb using the lists specific builder
$subquery = $builder->subquery();
// add to list of clauses that we need to eventually group into the query
$clauses[] = $qb->expr()->in(
$qb->getRootAliases()[0].'.id',
$subquery->getDQL(),
);
// loop through and attach the subqueries parameters to our own
// this is because the subquery ends up being our query, so the params need carried over
foreach ($subquery->getParameters() as $parameter) {
$qb->setParameter(
$parameter->getName(),
$parameter->getValue(),
);
}
}
// if we have clauses, we need to attach them into the query
$qb->andWhere(
$qb->expr()->orX(...$clauses),
);
// if we have a recipient entity, we want to filter by a few more things
// this ensures that recipients are only chosen that are "good" in the system
if ($entity === AbstractRecipient::class || in_array($entity, AbstractRecipient::DISCRS)) {
$qb
// the recipient itself should be active
->andWhere(AbstractListBuilder::ENTITIES__RECIPIENTS.'.active = :active')
->setParameter('active', true)
// the assocation between the profile and the recipient should be enabled
->andWhere(AbstractListBuilder::ENTITIES__PROFILE_CONTACTS.'.enabled = :enabled')
->setParameter('enabled', true);
}
return $qb;
}
/**
* @param AbstractList $list
* @return array
*/
public function serialize(AbstractList $list): array
{
return $this->getBuilder($list)->serialize();
}
/**
* @param string $class
* @param array $serialized
* @return AbstractList
*/
public function unserialize(string $class, array $serialized): AbstractList
{
return $this->getBuilder($class)->unserialize($serialized);
}
/**
* @param AbstractList|string $list
* @return AbstractListBuilder
*/
private function getBuilder($list): AbstractListBuilder
{
foreach (self::BUILDERS as $builder) {
if (call_user_func([$builder, 'supports'], $list)) {
switch ($builder) {
case StaticListBuilder::class:
return new StaticListBuilder(
$this->em,
($list instanceof AbstractList) ? $list : null
);
case SchoolListBuilder::class:
return new SchoolListBuilder(
$this->em,
$this->notificationsConditionQueryBuilder,
($list instanceof AbstractList) ? $list : null
);
case DistrictListBuilder::class:
return new DistrictListBuilder(
$this->em,
$this->notificationsConditionQueryBuilder,
($list instanceof AbstractList) ? $list : null
);
case ConditionListBuilder::class:
return new ConditionListBuilder(
$this->em,
$this->notificationsConditionQueryBuilder,
$this->conditionQueryTransformer,
($list instanceof AbstractList) ? $list : null
);
}
}
}
throw new \RuntimeException(
sprintf(
'Could not load builder for "%s".',
$list,
),
);
}
}

src/Products/NotificationsBundle/Service/ListBuilderService.php line 248