Symfony Profiler

<?php
namespace App\Repository;
use App\Config;
use App\Entity\Page;
use App\Entity\Tag;
use DateInterval;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\NonUniqueResultException;
use Doctrine\ORM\Tools\Pagination\Paginator;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;
/**
* Class PageRepository.
*/
class PageRepository extends ServiceEntityRepository
{
/**
* PageRepository constructor.
*/
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Page::class);
}
public function paginate(string $type, array $filters, array $orderBy, int $limit, int $offset): mixed
{
$parameters = [];
$today = new DateTime();
$inTwoDays = new DateTime();
$inTwoDays->add(new DateInterval('P2D'));
$parameters['type'] = $type;
$parameters['status'] = Page::STATUS_TRASH;
$qb = $this->createQueryBuilder('p')
->select('p,pt,a')
->leftJoin('p.author', 'a')
->leftJoin('p.translations', 'pt')
->andWhere('p.type = :type')
->andWhere('p.status != :status');
foreach ($filters as $key => $filter) {
switch ($key) {
case 'locale':
$qb->andWhere('p.locale = :locale');
$parameters['locale'] = $filter;
break;
case 'author':
$qb->andWhere('a.id = :id');
$parameters['id'] = $filter;
break;
case 'status':
$condition = $qb->expr()->orX();
foreach ($filter as $k => $f) {
if ('soon_expired' == $f) {
$condition->add($qb->expr()->between('p.expired', ':todayExpiration', ':inTwoDaysExpiration'));
$parameters['todayExpiration'] = $today;
$parameters['inTwoDaysExpiration'] = $inTwoDays;
} elseif ('soon_published' == $f) {
$condition->add($qb->expr()->between('p.published', ':todayPublication', ':inTwoDaysPublication'));
$parameters['todayPublication'] = $today;
$parameters['inTwoDaysPublication'] = $inTwoDays;
} else {
$condition->add($qb->expr()->eq('p.status', ':status'.$f));
$parameters['status'.$f] = $f;
}
}
$qb->andWhere($condition);
break;
default:
$qb->andWhere('p.'.$key.'= :'.$filter);
$parameters[$filter] = $filter;
break;
}
}
foreach ($orderBy as $key => $value) {
$qb->addOrderBy('p.'.$key, $value);
}
$qb->setParameters($parameters);
$qb->setFirstResult($offset)
->setMaxResults($limit);
return $qb->getQuery()->getResult();
}
public function getTrash(): array
{
$qb = $this->createQueryBuilder('p')
->select('p')
->leftJoin('p.author', 'a')
->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_TRASH)
->orderBy('p.title');
return $this->groupByType($qb->getQuery()->getResult());
}
public function findForRedirection(array $filters): array
{
$qb = $this->createQueryBuilder('p')
->select('p, pa')
->leftJoin('p.paths', 'pa')
->orderBy('p.title');
foreach ($filters as $key => $filter) {
if ('' == $filter) {
continue;
}
$qb->andWhere('p.'.$key.'= :'.$filter)
->setParameter($filter, $filter);
}
return $this->groupByTypeForRedirection($qb->getQuery()->getResult());
}
public function findForSliceJsonUpdate(string $sliceName): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p')
->where("JSON_SEARCH(p.slices, 'all', :key) IS NOT NULL")
->setParameter('key', $sliceName);
return $qb->getQuery()->getResult();
}
public function findAllValueForJsonField(string $type, string $field, ?string $contentType = null): mixed
{
$qb = $this->createQueryBuilder('p')
->select('JSON_EXTRACT(p.'.$type.', :key)')
->setParameter('key', '$.'.$field);
if ($contentType) {
$qb->andWhere('p.type = :type')
->setParameter('type', $contentType);
}
return $qb->getQuery()->getResult();
}
public function findAll(?string $locale = null, bool $groupByType = false, bool $onlyMaster = false, $isPublished = false, $onlyVisible = false): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p');
if ($isPublished) {
$qb->where('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED);
}
$qb->orderBy('p.title');
if ($locale) {
$qb->andWhere('p.locale = :locale')
->setParameter('locale', $locale);
}
if ($onlyMaster) {
$qb->andWhere('p.master IS NULL');
}
if ($onlyVisible) {
$qb->andWhere('SIZE(p.paths) > :count')
->setParameter('count', 0);
}
return $groupByType ? $this->groupByType($qb->getQuery()->getResult()) : $qb->getQuery()->getResult();
}
public function findAllExpired(): mixed
{
$today = new DateTime();
$qb = $this->createQueryBuilder('p')
->select('p')
->orderBy('p.title')
->andWhere('p.expired < :today')
->setParameter('today', $today);
return $qb->getQuery()->getResult();
}
public function findAllToPublish(): mixed
{
$today = new DateTime();
$qb = $this->createQueryBuilder('p')
->select('p')
->orderBy('p.title')
->andWhere('p.published < :today')
->setParameter('today', $today);
return $qb->getQuery()->getResult();
}
public function findAllByType(string $type, ?string $locale = null, bool $onlyMaster = false): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p');
if (!Config::isContentMulti($type)) {
$where = '';
foreach (Config::getSingleContentTypes() as $k => $singleType) {
$where .= 'p.type = :type'.$k.' OR ';
$qb->setParameter('type'.$k, $singleType);
}
$qb->where(substr($where, 0, -4));
} else {
$qb->where('p.type = :type')
->setParameter('type', $type);
}
if ($locale) {
$qb->andWhere('p.locale = :locale')
->setParameter('locale', $locale);
}
if ($onlyMaster) {
$qb->andWhere('p.master IS NULL');
}
return $qb->getQuery()->getResult();
}
/**
* @throws NonUniqueResultException
*/
public function findOneSingleByType(string $type, ?string $lang = null, bool $front = false): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p')
->where('p.type = :type')
->setParameter('type', $type);
if ($front) {
$qb->andWhere('p.status = :status')
->andWhere('p.visibility = :visibility')
->setParameter('status', Page::STATUS_PUBLISHED)
->setParameter('visibility', 'public');
}
if ($lang) {
$qb->andWhere('p.locale = :lang')
->setParameter('lang', $lang);
} else {
$qb->andWhere('p.master IS NULL');
}
return $qb->getQuery()->getOneOrNullResult();
}
/**
* @throws NonUniqueResultException
*/
public function findOne(?int $id): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p')
->leftJoin('p.translations', 'pt')
->leftJoin('p.master', 'pm')
->where('p.id = :id')
->setParameter('id', $id);
return $qb->getQuery()->getOneOrNullResult();
}
/**
* @throws NonUniqueResultException
*/
public function findOneByPath(string $path, ?string $locale = null): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p')
->leftJoin('p.paths', 'pa')
->where('pa.path = :path')
->setParameter('path', $path);
if ($locale) {
$qb->andWhere('pa.locale = :locale')
->setParameter('locale', $locale);
}
return $qb->getQuery()->getOneOrNullResult();
}
/**
* @throws NonUniqueResultException
*/
public function getCachingLevelByPath(string $path, ?string $locale = null): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p.cachingLevel')
->leftJoin('p.paths', 'pa')
->where('pa.path = :path')
->setParameter('path', $path);
if ($locale) {
$qb->andWhere('pa.locale = :locale')
->setParameter('locale', $locale);
}
return $qb->getQuery()->getOneOrNullResult();
}
/**
* @throws NonUniqueResultException
*/
public function countPage(): mixed
{
$qb = $this->createQueryBuilder('p')
->select('count(p)');
return $qb->getQuery()->getOneOrNullResult();
}
private function groupByType(array $results): array
{
$data = [];
if (count($results)) {
/** @var Page $i */
foreach ($results as $i) {
if ($i->isMulti()) {
$data[$i->getType()][] = $i;
} else {
$data['main'][] = $i;
}
}
}
return $data;
}
private function groupByTypeForRedirection(array $results): array
{
$data = [];
if (count($results)) {
/** @var Page $i */
foreach ($results as $i) {
if (count($i->getRedirections())) {
if ($i->isMulti()) {
$data[$i->getType()][] = $i;
} else {
$data['main'][] = $i;
}
}
}
}
return $data;
}
/**
* @throws NonUniqueResultException
*/
public function findType(string $type): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p')
->where('p.type = :type')
->setParameter('type', $type)
->setMaxResults(1);
return $qb->getQuery()->getOneOrNullResult();
}
public function findExpertiseFaqPages(?int $tag = null): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p');
$qb->where('p.type IN (:type)')
->setParameter('type', ['faq'])
->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED)
->orderBy('JSON_EXTRACT(p.contentProperties, :key)', 'DESC')
->setParameter('key', '$.publicationDate.date');
if ($tag != null) {
$qb->leftJoin('p.tags', 'pt')
->andWhere('pt.id = :id')
->setParameter('id', $tag);
}
return $qb->getQuery()->getResult();
}
public function findPages(array $types, ?Tag $tag = null): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p');
$qb->where('p.type IN (:type)')
->setParameter('type', $types)
->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED)
->orderBy('JSON_EXTRACT(p.contentProperties, :key)', 'DESC')
->setParameter('key', '$.publicationDate.date');
if ($tag != null) {
$qb->leftJoin('p.tags', 'pt')
->andWhere('pt.id = :id')
->setParameter('id', $tag);
}
$qb->setMaxResults(6);
return $qb->getQuery()->getResult();
}
public function findThematic(?int $tag = null, ?int $limit = null): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p');
$qb->where('p.type IN (:type)')
->setParameter('type', ['thematic'])
->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED);
// ->orderBy('JSON_EXTRACT(p.contentProperties, :key)', 'DESC')
// ->setParameter('key', '$.publicationDate.date');
if ($tag != null) {
$qb->leftJoin('p.tags', 'pt')
->andWhere('pt.id = :id')
->setParameter('id', $tag);
}
$qb->orderBy('p.created', 'DESC');
if ($limit != null) {
$qb->setMaxResults($limit);
}
return $qb->getQuery()->getResult();
}
public function findQuestions(?array $thematic = null, ?int $limit = null): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p');
$qb->where('p.type IN (:type)')
->setParameter('type', ['questions'])
->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED);
// ->orderBy('JSON_EXTRACT(p.contentProperties, :key)', 'DESC')
// ->setParameter('key', '$.publicationDate.date');
if ($thematic != null) {
$qb->andWhere('JSON_EXTRACT(p.contentProperties, :key) IN (:id)')
->setParameter('key', '$.thematic')
->setParameter('id', $thematic);
}
$qb->orderBy('p.created', 'DESC');
if ($limit != null) {
$qb->setMaxResults($limit);
}
return $qb->getQuery()->getResult();
}
public function find2LastPortraits(): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p')
->where('p.type = :type')
->setParameter('type', 'portrait')
->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED);
$qb->orderBy('p.created', 'DESC');
$qb->setMaxResults(2);
return $qb->getQuery()->getResult();
}
/**
* @throws NonUniqueResultException
*/
public function findLastForHome(array $types, int $limit): mixed
{
$qb = $this->createQueryBuilder('p')
->select('p');
foreach ($types as $key => $type) {
if ($key == 0) {
$qb->where('p.type = :type' . $key)
->setParameter('type' . $key, $type);
} else {
$qb->orWhere('p.type = :type' . $key)
->setParameter('type' . $key, $type);
}
}
$qb->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED);
$qb->orderBy('JSON_EXTRACT(p.contentProperties, :key)', 'DESC')
->setParameter('key', '$.publicationDate.date');
$qb->setMaxResults($limit);
if ($limit == 1) {
return $qb->getQuery()->getOneOrNullResult();
}
return $qb->getQuery()->getResult();
}
public function paginateListPages(array $types, int $page, int $limit)
{
$qb = $this->createQueryBuilder('p')
->select('p')
->where('p.type IN (:types)')
->setParameter('types', $types)
->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED);
$query = $qb->getQuery();
$firstResult = ($page - 1) * $limit;
$query->setFirstResult($firstResult)->setMaxResults($limit);
$paginator = new Paginator($query);
if (($paginator->count() <= $firstResult) && $page != 1) {
throw new NotFoundHttpException('La page demandée n\'existe pas.');
}
return $paginator->getQuery()->getResult();
}
public function countPages(array $types)
{
$qb = $this->createQueryBuilder('p')
->select('COUNT(p)')
->where('p.type IN (:types)')
->setParameter('types', $types)
->andWhere('p.status = :status')
->setParameter('status', Page::STATUS_PUBLISHED);
return $qb->getQuery()->getResult();
}
}

src/Repository/PageRepository.php line 542