Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\BlogPost;
use App\Entity\User;
use DateTimeImmutable;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\Security\Core\Exception\UnsupportedUserException;
use Symfony\Component\Security\Core\User\PasswordAuthenticatedUserInterface;
use Symfony\Component\Security\Core\User\PasswordUpgraderInterface;
use Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql as DqlFunctions;
use App\Repository\SiteConfigRepository;
/**
* @method User|null find($id, $lockMode = null, $lockVersion = null)
* @method User|null findOneBy(array $criteria, array $orderBy = null)
* @method User[] findAll()
* @method User[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class UserRepository extends ServiceEntityRepository implements PasswordUpgraderInterface
{
public function __construct(
ManagerRegistry $registry,
private EntityManagerInterface $em,
private PreferenceRepository $preferenceRepository,
SiteConfigRepository $siteConfigRepository
) {
parent::__construct($registry, User::class);
$this->siteConfigRepository = $siteConfigRepository;
}
public function deleteOldFirebaseToken(): int
{
$lastMounth = (new DateTimeImmutable())->modify("-30 day");
return $this->createQueryBuilder("u")
->update()
->set('u.tokenFirebase', ":null")
->where('u.firebaseTokenGeneratedAt < :val')
->orWhere('u.firebaseTokenGeneratedAt is null')
->setParameter('val', $lastMounth)
->setParameter('null', null)
->getQuery()
->execute();
}
/**
* Used to upgrade (rehash) the user's password automatically over time.
*/
public function upgradePassword(PasswordAuthenticatedUserInterface $user, string $newHashedPassword): void
{
if (!$user instanceof User) {
throw new UnsupportedUserException(sprintf('Instances of "%s" are not supported.', \get_class($user)));
}
$user->setPassword($newHashedPassword);
$this->_em->persist($user);
$this->_em->flush();
}
public function search(string $query)
{
return $this->createQueryBuilder('u')
->andWhere('u.email LIKE :query')
->setParameter('query', '%' . $query . '%')
->getQuery()
->getArrayResult();
}
public function allCategories($id)
{
return $this->createQueryBuilder('u')
->select('u, b')
->andWhere('u.id = :id')
->leftJoin('u.categories', 'b')
->setParameter('id', $id)
->addSelect('b as BlogPostCategory')
->getQuery()
->execute();
}
public function getId($email)
{
$query = $this
->createQueryBuilder('u')
->select('u.id')
->andWhere('u.email IN (:email)')
->setParameter(':email', $email);
return $query->getQuery()->getResult();
}
public function getTotalUsers(User $currentUser = null)
{
$qb = $this->createQueryBuilder('u')
->select('count(DISTINCT u.id)');
$this->addInstanceFilter($qb, $currentUser);
return $qb->getQuery()->getSingleScalarResult();
}
public function getTotalUsersByMonth(User $currentUser = null)
{
$qb = $this->createQueryBuilder('u')
->select('count(DISTINCT u.id)')
->where('MONTH(u.createdAt) = MONTH(CURRENT_DATE())')
->andWhere('YEAR(u.createdAt) = YEAR(CURRENT_DATE())');
$this->addInstanceFilter($qb, $currentUser);
return $qb->getQuery()->getSingleScalarResult();
}
public function getTotalUsersByWeek(User $currentUser = null)
{
$qb = $this->createQueryBuilder('u')
->select('count(DISTINCT u.id)')
->where('WEEK(u.createdAt) = WEEK(CURRENT_DATE())')
->andWhere('YEAR(u.createdAt) = YEAR(CURRENT_DATE())');
$this->addInstanceFilter($qb, $currentUser);
return $qb->getQuery()->getSingleScalarResult();
}
public function getTotalUsersByDay(User $currentUser = null)
{
$qb = $this->createQueryBuilder('u')
->select('count(DISTINCT u.id)')
->where('DAY(u.createdAt) = DAY(CURRENT_DATE())')
->andWhere('MONTH(u.createdAt) = MONTH(CURRENT_DATE())')
->andWhere('YEAR(u.createdAt) = YEAR(CURRENT_DATE())');
$this->addInstanceFilter($qb, $currentUser);
return $qb->getQuery()->getSingleScalarResult();
}
private function addInstanceFilter($qb, ?User $currentUser): void
{
$generalSiteConfig = $this->siteConfigRepository->findOneBy([]);
$isInstanceActive = $generalSiteConfig ? $generalSiteConfig->isInstanceActive() : true;
if ($isInstanceActive && $currentUser) {
$userInstances = $currentUser->getInstances();
if ($userInstances->count() > 0) {
$instanceIds = $userInstances->map(function($instance) {
return $instance->getId();
})->toArray();
$qb->leftJoin('u.instances', 'i')
->andWhere('i.id IN (:userInstanceIds)')
->setParameter('userInstanceIds', $instanceIds);
} else {
$qb->andWhere('1 = 0');
}
}
}
public function getUsersByRolesAndCriterias(BlogPost $post): mixed
{
$postRoles = $post->getRoles();
$postCriteria1Items = $post->getCriteria1Items()->map(fn($c) => $c->getId())->toArray() + [-1]; // adding -1 value for sql query "in" that requires at least one value
$postCriteria2Items = $post->getCriteria2Items()->map(fn($c) => $c->getId())->toArray() + [-1];
$postCriteria3Items = $post->getCriteria3Items()->map(fn($c) => $c->getId())->toArray() + [-1];
$postIsPublic = $post->getIsPublic();
$postHasAllCriterias1 = $post->isHasAllCriteria1();
$postHasAllCriterias2 = $post->isHasAllCriteria2();
$postHasAllCriterias3 = $post->isHasAllCriteria3();
// create the custom query to filter by user role
$roleSqlArray = [];
foreach ($postRoles as $role) {
$roleSqlArray[] = 'JSON_CONTAINS(u.roles, \'["' . $role->getCode() . '"]\', \'$\') = true';
}
if (count($roleSqlArray))
$roleSqlQuery = implode(" OR ", $roleSqlArray) . " OR ";
else
$roleSqlQuery = "";
$query = $this->createQueryBuilder('u')
->leftJoin('u.criteria1Items', 'c1items')
->leftJoin('u.criteria2Items', 'c2items')
->leftJoin('u.criteria3Items', 'c3items')
->where($roleSqlQuery . ":postIsPublic = true")
->andWhere("c1items IN (:postCriteria1Items) OR :postHasAllCriterias1 = true")
->andWhere("c2items IN (:postCriteria2Items) OR :postHasAllCriterias2 = true")
->andWhere("c3items IN (:postCriteria3Items) OR :postHasAllCriterias3 = true")
->setParameter('postCriteria1Items', $postCriteria1Items)
->setParameter('postCriteria2Items', $postCriteria2Items)
->setParameter('postCriteria3Items', $postCriteria3Items)
->setParameter('postIsPublic', $postIsPublic)
->setParameter('postHasAllCriterias1', $postHasAllCriterias1)
->setParameter('postHasAllCriterias2', $postHasAllCriterias2)
->setParameter('postHasAllCriterias3', $postHasAllCriterias3)
->distinct();
return $query->getQuery()->getResult();
}
/**
* Add all the existing preferences of the application to the user
*/
public function addAllPreferences(User $user): void
{
$preferences = $this->preferenceRepository->findAll();
foreach ($preferences as $preference) {
$user->addPreference($preference);
}
}
public function getUsersByRolesAndCriteriasAndInstances(BlogPost $post): array
{
// 1) récupération de ce qu’on avait déjà
$postRoles = $post->getRoles();
$postCriteria1Items = $post->getCriteria1Items()->map(fn($c) => $c->getId())->toArray() + [-1];
$postCriteria2Items = $post->getCriteria2Items()->map(fn($c) => $c->getId())->toArray() + [-1];
$postCriteria3Items = $post->getCriteria3Items()->map(fn($c) => $c->getId())->toArray() + [-1];
$postIsPublic = $post->getIsPublic();
$postHasAllCriterias1 = $post->isHasAllCriteria1();
$postHasAllCriterias2 = $post->isHasAllCriteria2();
$postHasAllCriterias3 = $post->isHasAllCriteria3();
// 2) on récupère les instances du post
// (dans ton back tu as un champ "Instances *", donc on part du principe que c’est un ManyToMany)
$postInstanceIds = [];
if (method_exists($post, 'getInstances')) {
$postInstanceIds = $post->getInstances()
->map(fn($i) => $i->getId())
->toArray();
}
// create the custom query to filter by user role
$roleSqlArray = [];
foreach ($postRoles as $role) {
$roleSqlArray[] = 'JSON_CONTAINS(u.roles, \'["' . $role->getCode() . '"]\', \'$\') = true';
}
$roleSqlQuery = count($roleSqlArray)
? implode(" OR ", $roleSqlArray) . " OR "
: "";
$qb = $this->createQueryBuilder('u')
->leftJoin('u.criteria1Items', 'c1items')
->leftJoin('u.criteria2Items', 'c2items')
->leftJoin('u.criteria3Items', 'c3items')
->where($roleSqlQuery . ":postIsPublic = true")
->andWhere("c1items IN (:postCriteria1Items) OR :postHasAllCriterias1 = true")
->andWhere("c2items IN (:postCriteria2Items) OR :postHasAllCriterias2 = true")
->andWhere("c3items IN (:postCriteria3Items) OR :postHasAllCriterias3 = true")
->setParameter('postCriteria1Items', $postCriteria1Items)
->setParameter('postCriteria2Items', $postCriteria2Items)
->setParameter('postCriteria3Items', $postCriteria3Items)
->setParameter('postIsPublic', $postIsPublic)
->setParameter('postHasAllCriterias1', $postHasAllCriterias1)
->setParameter('postHasAllCriterias2', $postHasAllCriterias2)
->setParameter('postHasAllCriterias3', $postHasAllCriterias3)
->distinct();
/**
* Ici on ajoute le filtre “instance”
* On ne le fait que si le site a la gestion d’instances activée
* ET que le contenu a bien au moins 1 instance.
*/
$generalSiteConfig = $this->siteConfigRepository->findOneBy([]);
$isInstanceActive = $generalSiteConfig ? $generalSiteConfig->isInstanceActive() : true;
if ($isInstanceActive && !empty($postInstanceIds)) {
// user.instances est un ManyToMany → on join
$qb
->leftJoin('u.instances', 'ui')
->andWhere('ui.id IN (:postInstanceIds)')
->setParameter('postInstanceIds', $postInstanceIds);
}
return $qb->getQuery()->getResult();
}
}

src/Repository/UserRepository.php line 31