Symfony Profiler

<?php
/**
* Created by simpson <simpsonwork@gmail.com>
* Date: 2019-04-15
* Time: 20:20
*/
namespace App\Repository;
use App\Entity\Location\City;
use App\Entity\Saloon\Saloon;
use App\Entity\User;
use App\Service\Features;
use App\Service\Map\MapClusterMinPriceDql;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\AbstractQuery;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\ORM\QueryBuilder;
use Happyr\DoctrineSpecification\Filter\Filter;
use Happyr\DoctrineSpecification\Query\QueryModifier;
use Porpaginas\Doctrine\ORM\ORMQueryResult;
class SaloonRepository extends ServiceEntityRepository
{
use SpecificationTrait;
use EntityIteratorTrait;
private Features $features;
public function __construct(ManagerRegistry $registry, Features $features)
{
parent::__construct($registry, Saloon::class);
$this->features = $features;
}
/**
* Возвращает итератор по данным, необходимым для генерации файлов sitemap, в виде массивов с
* следующими ключами:
* - id
* - uri
* - updatedAt
* - city_uri
*
* @return iterable<array{id: int, uri: string, updatedAt: \DateTimeImmutable, city_uri: string}>
*/
public function sitemapItemsIterator(): iterable
{
$qb = $this->createQueryBuilder('saloon')
->select('saloon.id, saloon.uriIdentity AS uri, saloon.updatedAt, city.uriIdentity AS city_uri')
->join('saloon.city', 'city')
->andWhere('saloon.deletedAt IS NULL');
return $qb->getQuery()->toIterable([], AbstractQuery::HYDRATE_ARRAY);
}
protected function modifyListingQueryBuilder(QueryBuilder $qb, string $alias): void
{
$qb
->addSelect('city')
->addSelect('station')
->addSelect('thumbnail')
->join(sprintf('%s.city', $alias), 'city')
->leftJoin(sprintf('%s.stations', $alias), 'station')
->leftJoin(sprintf('%s.thumbnail', $alias), 'thumbnail')
;
$this->excludeHavingPlacementHiding($qb, $alias);
if (!in_array('saloon_adboard_placements', $qb->getAllAliases())) {
$qb
->leftJoin(sprintf('%s.adBoardPlacement', $alias), 'saloon_adboard_placements')
;
}
$qb->addSelect('saloon_adboard_placements');
//if($this->features->free_profiles()) {
if (!in_array('placement_hiding', $qb->getAllAliases())) {
$qb
->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding');
}
$qb->addSelect('placement_hiding');
//}
}
public function ofUriIdentityWithinCity(string $uriIdentity, City $city): ?Saloon
{
return $this->findOneBy([
'uriIdentity' => $uriIdentity,
'city' => $city,
]);
}
/**
* Метод проверки уникальности анкет по URI не должен использовать никаких фильтров, кроме URI и города,
* поэтому QueryBuilder не используется
*/
public function isUniqueUriIdentityExistWithinCity(string $uriIdentity, City $city): bool
{
$connection = $this->_em->getConnection();
$stmt = $connection->executeQuery('SELECT COUNT(id) FROM saloons WHERE uri_identity = ? AND city_id = ?', [$uriIdentity, $city->getId()]);
$count = $stmt->fetchOne();
return $count > 0;
}
/**
* @return Saloon[]
*/
public function ofOwner(User $owner): array
{
return $this->findBy([
'owner' => $owner,
]);
}
public function searchLinkableToProfileByOwner(User $owner, ?string $query, int $limit = 20): array
{
$qb = $this->createQueryBuilder('saloon')
->leftJoin('saloon.adBoardPlacement', 'placement')
->leftJoin('saloon.thumbnail', 'thumbnail')
->addSelect('placement')
->addSelect('thumbnail')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
->orderBy('saloon.id', 'DESC')
->setMaxResults($limit)
;
if ($query) {
$qb
->andWhere('LOWER(JSON_UNQUOTE(JSON_EXTRACT(saloon.name, :json_path))) LIKE :query')
->setParameter('json_path', '$.ru')
->setParameter('query', '%' . addcslashes(mb_strtolower(trim($query)), '%_') . '%')
;
}
return $qb->getQuery()->getResult();
}
public function ofOwnerPaged(User $owner): ORMQueryResult
{
$qb = $this->createQueryBuilder('saloon')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
;
return new ORMQueryResult($qb);
}
public function idsOfOwner(User $owner): array
{
$qb = $this->createQueryBuilder('saloon')
->select('saloon.id')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
;
return $qb->getQuery()->getResult('column_hydrator');
}
public function countActiveOfOwner(User $owner): int
{
$qb = $this->createQueryBuilder('saloon')
->select('COUNT(saloon.id)')
->join('saloon.adBoardPlacement', 'saloon_adboard_placement')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
;
return (int)$qb->getQuery()->getSingleScalarResult();
}
/**
* Список активных салонов, привязанных к аккаунту
*
* @return Saloon[]
*/
public function activeAndOwnedBy(User $owner): ORMQueryResult
{
$qb = $this->createQueryBuilder('saloon')
->join('saloon.adBoardPlacement', 'saloon_adboard_placement')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
;
return new ORMQueryResult($qb);
}
/**
* Список активных или скрытых салонов, привязанных к аккаунту
*
* @return Saloon[]
*/
public function activeOrHiddenAndOwnedBy(User $owner): ORMQueryResult
{
$qb = $this->createQueryBuilder('saloon')
->leftJoin('saloon.adBoardPlacement', 'saloon_adboard_placement')
->leftJoin('saloon.placementHiding', 'placement_hiding')
->andWhere('saloon_adboard_placement IS NOT NULL OR placement_hiding IS NOT NULL')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
;
return new ORMQueryResult($qb);
}
/**
* Число всех салонов, привязанных к аккаунту
*/
public function countAllOfOwner(User $owner): int
{
$qb = $this->createQueryBuilder('saloon')
->select('COUNT(saloon.id)')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
;
return (int)$qb->getQuery()->getSingleScalarResult();
}
public function getTimezonesListByUser(User $owner): array
{
$q = $this->_em->createQuery(sprintf("
SELECT c
FROM %s c
WHERE c.id IN (
SELECT DISTINCT(c2.id)
FROM %s saloon
JOIN saloon.city c2
WHERE saloon.owner = :user
)
", $this->_em->getClassMetadata(City::class)->name, $this->_em->getClassMetadata(Saloon::class)->name))
->setParameter('user', $owner);
return $q->getResult();
}
private function excludeHavingPlacementHiding(QueryBuilder $qb, string $alias): void
{
if($this->features->free_profiles()) {
if (!in_array('placement_hiding', $qb->getAllAliases())) {
$qb
->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding')
->andWhere(sprintf('placement_hiding IS NULL'))
;
}
}
}
public function deletedByPeriod(\DateTimeInterface $start, \DateTimeInterface $end): array
{
$qb = $this->createQueryBuilder('saloon')
->join('saloon.city', 'city')
->select('saloon.uriIdentity _saloon')
->addSelect('city.uriIdentity _city')
->andWhere('saloon.deletedAt >= :start')
->andWhere('saloon.deletedAt <= :end')
->setParameter('start', $start)
->setParameter('end', $end)
;
return $qb->getQuery()->getResult();
}
public function listForMapMatchingSpec(Filter|QueryModifier $specification, int $coordinatesRoundPrecision = 3): array
{
/** @var QueryBuilder $qb */
$qb = $this->createQueryBuilder($dqlAlias = 's');
$qb->select(sprintf('GROUP_CONCAT(s.id), CONCAT(ROUND(MIN(s.mapCoordinate.latitude),5),\',\',ROUND(MIN(s.mapCoordinate.longitude),5)), count(s.id), CONCAT(ROUND(s.mapCoordinate.latitude,%1$s),\',\',ROUND(s.mapCoordinate.longitude,%1$s)) as coords', $coordinatesRoundPrecision));
$qb->groupBy('coords');
$specification->modify($qb, $dqlAlias);
$qb->andWhere($specification->getFilter($qb, $dqlAlias));
return $qb->getQuery()->getResult();
}
/**
* Clustered map points for JSON API mode=map.
* Representative point is the centroid (AVG), not MIN as in listForMapMatchingSpec().
*/
public function listMapClustersMatchingSpec(Filter|QueryModifier $specification, int $coordinatesRoundPrecision): array
{
$this->getEntityManager()->getConnection()->executeQuery("
SET SESSION group_concat_max_len = 100000;
");
$precision = (int) $coordinatesRoundPrecision;
/** @var QueryBuilder $qb */
$qb = $this->createQueryBuilder($dqlAlias = 's');
$qb->select(sprintf(
'%s, '
. 'GROUP_CONCAT(s.id ORDER BY s.id) AS ids, '
. 'COUNT(s.id) AS cnt, '
. 'ROUND(AVG(s.mapCoordinate.latitude), 5) AS lat, '
. 'ROUND(AVG(s.mapCoordinate.longitude), 5) AS lng, '
. 'CONCAT(ROUND(s.mapCoordinate.latitude, %2$d), \',\', ROUND(s.mapCoordinate.longitude, %2$d)) AS coords',
MapClusterMinPriceDql::clusterMinPriceSelect($dqlAlias),
$precision
));
$qb->groupBy('coords');
$specification->modify($qb, $dqlAlias);
$qb->andWhere($specification->getFilter($qb, $dqlAlias));
return $qb->getQuery()->getResult();
}
public function getCommentedSaloonsPaged(User $owner): ORMQueryResult
{
$qb = $this->createQueryBuilder('saloon')
->join('saloon.comments', 'comment')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
->orderBy('comment.createdAt', 'DESC')
;
return new ORMQueryResult($qb);
}
public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, ?string $nameFilter): array
{
$qb = $this->queryBuilderOfOwnerAndNameFilter($owner, $nameFilter);
return $qb->getQuery()->getResult();
}
private function queryBuilderOfOwnerAndNameFilter(User $owner, ?string $nameFilter): QueryBuilder
{
$qb = $this->createQueryBuilder('saloon')
->andWhere('saloon.owner = :owner')
->setParameter('owner', $owner)
;
if($nameFilter) {
$nameExpr = $qb->expr()->orX(
'LOWER(JSON_UNQUOTE(JSON_EXTRACT(saloon.name, :jsonPath))) LIKE :name_filter',
\sprintf("REGEXP_REPLACE(saloon.phoneNumber, '-| ', '') LIKE :name_filter"),
'LOWER(profile.phoneNumber) LIKE :name_filter',
\sprintf("REGEXP_REPLACE(saloon.phoneNumber, '\+7', '8') LIKE :name_filter"),
);
$qb->setParameter('jsonPath', '$.ru');
$qb->setParameter('name_filter', '%'.addcslashes(mb_strtolower(str_replace(['(', ')', ' ', '-'], '', $nameFilter)), '%_').'%');
$qb->andWhere($nameExpr);
}
return $qb;
}
}

src/Repository/SaloonRepository.php line 87