Symfony Profiler

<?php
/**
* Created by simpson <simpsonwork@gmail.com>
* Date: 2019-03-19
* Time: 22:23
*/
namespace App\Repository;
use App\Entity\Location\City;
use App\Entity\Location\MapCoordinate;
use App\Entity\Profile\Genders;
use App\Entity\Profile\Photo;
use App\Entity\Profile\Profile;
use App\Entity\Sales\Profile\AdBoardPlacement;
use App\Entity\Sales\Profile\AdBoardPlacementType;
use App\Entity\Sales\Profile\PlacementHiding;
use App\Entity\User;
use App\Repository\ReadModel\CityReadModel;
use App\Repository\ReadModel\ProfileApartmentPricingReadModel;
use App\Repository\ReadModel\ProfileListingReadModel;
use App\Repository\ReadModel\ProfileMapReadModel;
use App\Repository\ReadModel\ProfilePersonParametersReadModel;
use App\Repository\ReadModel\ProfilePlacementHidingDetailReadModel;
use App\Repository\ReadModel\ProfilePlacementPriceDetailReadModel;
use App\Repository\ReadModel\ProfileTakeOutPricingReadModel;
use App\Repository\ReadModel\ProvidedServiceReadModel;
use App\Repository\ReadModel\StationLineReadModel;
use App\Repository\ReadModel\StationReadModel;
use App\Service\Features;
use App\Specification\Profile\ProfileIdINOrderedByINValues;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\AbstractQuery;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\DBAL\Statement;
use Doctrine\ORM\QueryBuilder;
use Happyr\DoctrineSpecification\Filter\Filter;
use Happyr\DoctrineSpecification\Query\QueryModifier;
use Porpaginas\Doctrine\ORM\ORMQueryResult;
class ProfileRepository extends ServiceEntityRepository
{
use SpecificationTrait;
use EntityIteratorTrait;
private Features $features;
public function __construct(ManagerRegistry $registry, Features $features)
{
parent::__construct($registry, Profile::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('profile')
->select('profile.id, profile.uriIdentity AS uri, profile.updatedAt, city.uriIdentity AS city_uri')
->join('profile.city', 'city')
->andWhere('profile.deletedAt IS NULL');
$this->addModerationFilterToQb($qb, 'profile');
return $qb->getQuery()->toIterable([], AbstractQuery::HYDRATE_ARRAY);
}
protected function addModerationFilterToQb(QueryBuilder $qb, string $dqlAlias): void
{
if ($this->features->hard_moderation()) {
$qb->leftJoin(sprintf('%s.owner', $dqlAlias), 'owner');
$qb->andWhere(
$qb->expr()->orX(
sprintf('%s.moderationStatus = :status_passed', $dqlAlias),
$qb->expr()->andX(
sprintf('%s.moderationStatus = :status_waiting', $dqlAlias),
'owner.trusted = true'
)
)
);
$qb->setParameter('status_passed', Profile::MODERATION_STATUS_APPROVED);
$qb->setParameter('status_waiting', Profile::MODERATION_STATUS_WAITING);
} else {
$qb->andWhere(sprintf('%s.moderationStatus IN (:statuses)', $dqlAlias));
$qb->setParameter('statuses', [Profile::MODERATION_STATUS_NOT_PASSED, Profile::MODERATION_STATUS_WAITING, Profile::MODERATION_STATUS_APPROVED]);
}
}
public function ofUriIdentityWithinCity(string $uriIdentity, City $city): ?Profile
{
return $this->findOneBy([
'uriIdentity' => $uriIdentity,
'city' => $city,
]);
}
/**
* Метод проверки уникальности анкет по URI не должен использовать никаких фильтров, кроме URI и города,
* поэтому QueryBuilder не используется
* @see https://redminez.net/issues/27310
*/
public function isUniqueUriIdentityExistWithinCity(string $uriIdentity, City $city): bool
{
$connection = $this->_em->getConnection();
$stmt = $connection->executeQuery('SELECT COUNT(id) FROM profiles WHERE uri_identity = ? AND city_id = ?', [$uriIdentity, $city->getId()]);
$count = $stmt->fetchOne();
return $count > 0;
}
public function countByCity(): array
{
$qb = $this->createQueryBuilder('profile')
->select('IDENTITY(profile.city), COUNT(profile.id)')
->groupBy('profile.city');
$this->addFemaleGenderFilterToQb($qb, 'profile');
$this->addModerationFilterToQb($qb, 'profile');
//$this->excludeHavingPlacementHiding($qb, 'profile');
$this->havingAdBoardPlacement($qb, 'profile');
$query = $qb->getQuery()
->useResultCache(true)
->setResultCacheLifetime(120);
$rawResult = $query->getScalarResult();
$indexedResult = [];
foreach ($rawResult as $row) {
$indexedResult[$row[1]] = $row[2];
}
return $indexedResult;
}
protected function addFemaleGenderFilterToQb(QueryBuilder $qb, string $alias): void
{
$this->addGenderFilterToQb($qb, $alias, [Genders::FEMALE]);
}
protected function addGenderFilterToQb(QueryBuilder $qb, string $alias, array $genders = [Genders::FEMALE]): void
{
$qb->andWhere(sprintf('%s.personParameters.gender IN (:genders)', $alias));
$qb->setParameter('genders', $genders);
}
private function havingAdBoardPlacement(QueryBuilder $qb, string $alias): void
{
$qb->join(sprintf('%s.adBoardPlacement', $alias), 'adboard_placement');
}
public function countByStations(): array
{
$qb = $this->createQueryBuilder('profiles')
->select('stations.id, COUNT(profiles.id) as cnt')
->join('profiles.stations', 'stations')
//это условие сильно затормжаживает запрос, но оно и не нужно при условии, что чужих(от других городов) станций у анкеты нет
//->where('profiles.city = stations.city')
->groupBy('stations.id');
$this->addFemaleGenderFilterToQb($qb, 'profiles');
$this->addModerationFilterToQb($qb, 'profiles');
//$this->excludeHavingPlacementHiding($qb, 'profiles');
$this->havingAdBoardPlacement($qb, 'profiles');
$query = $qb->getQuery()
->useResultCache(true)
->setResultCacheLifetime(120);
$rawResult = $query->getScalarResult();
$indexedResult = [];
foreach ($rawResult as $row) {
$indexedResult[$row['id']] = $row['cnt'];
}
return $indexedResult;
}
public function countByDistricts(): array
{
$qb = $this->createQueryBuilder('profiles')
->select('districts.id, COUNT(profiles.id) as cnt')
->join('profiles.stations', 'stations')
->join('stations.district', 'districts')
->groupBy('districts.id');
$this->addFemaleGenderFilterToQb($qb, 'profiles');
$this->addModerationFilterToQb($qb, 'profiles');
//$this->excludeHavingPlacementHiding($qb, 'profiles');
$this->havingAdBoardPlacement($qb, 'profiles');
$query = $qb->getQuery()
->useResultCache(true)
->setResultCacheLifetime(120);
$rawResult = $query->getScalarResult();
$indexedResult = [];
foreach ($rawResult as $row) {
$indexedResult[$row['id']] = $row['cnt'];
}
return $indexedResult;
}
public function countByCounties(): array
{
$qb = $this->createQueryBuilder('profiles')
->select('counties.id, COUNT(profiles.id) as cnt')
->join('profiles.stations', 'stations')
->join('stations.district', 'districts')
->join('districts.county', 'counties')
->groupBy('counties.id');
$this->addFemaleGenderFilterToQb($qb, 'profiles');
$this->addModerationFilterToQb($qb, 'profiles');
//$this->excludeHavingPlacementHiding($qb, 'profiles');
$this->havingAdBoardPlacement($qb, 'profiles');
$query = $qb->getQuery()
->useResultCache(true)
->setResultCacheLifetime(120);
$rawResult = $query->getScalarResult();
$indexedResult = [];
foreach ($rawResult as $row) {
$indexedResult[$row['id']] = $row['cnt'];
}
return $indexedResult;
}
/**
* @param array|int[] $ids
* @return Profile[]
*/
public function findByIds(array $ids): array
{
return $this->createQueryBuilder('profile')
->andWhere('profile.id IN (:ids)')
->setParameter('ids', $ids)
->orderBy('FIELD(profile.id,:ids2)')
->setParameter('ids2', $ids)
->getQuery()
->getResult();
}
public function findByIdsIterate(array $ids): iterable
{
$qb = $this->createQueryBuilder('profile')
->andWhere('profile.id IN (:ids)')
->setParameter('ids', $ids)
->orderBy('FIELD(profile.id,:ids2)')
->setParameter('ids2', $ids);
return $this->iterateQueryBuilder($qb);
}
/**
* Список анкет указанного типа (массажистки или нет), привязанных к аккаунту
*/
public function ofOwnerAndTypePaged(User $owner, bool $masseurs): ORMQueryResult
{
$qb = $this->createQueryBuilder('profile')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner)
->andWhere('profile.masseur = :is_masseur')
->setParameter('is_masseur', $masseurs);
return new ORMQueryResult($qb);
}
/**
* Список активных анкет, привязанных к аккаунту
*/
public function activeAndOwnedBy(User $owner): ORMQueryResult
{
$qb = $this->createQueryBuilder('profile')
->join('profile.adBoardPlacement', 'profile_adboard_placement')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner);
return new ORMQueryResult($qb);
}
/**
* Список активных или скрытых анкет, привязанных к аккаунту
*
* @return Profile[]|ORMQueryResult
*/
public function activeOrHiddenAndOwnedBy(User $owner): ORMQueryResult
{
$qb = $this->createQueryBuilder('profile')
->join('profile.adBoardPlacement', 'profile_adboard_placement')
// ->leftJoin('profile.placementHiding', 'placement_hiding')
// ->andWhere('profile_adboard_placement IS NOT NULL OR placement_hiding IS NOT NULL')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner);
// return $this->iterateQueryBuilder($qb);
return new ORMQueryResult($qb);
}
public function countFreeUnapprovedLimited(): int
{
$qb = $this->createQueryBuilder('profile')
->select('count(profile)')
->join('profile.adBoardPlacement', 'placement')
->andWhere('placement.type = :placement_type')
->setParameter('placement_type', AdBoardPlacementType::FREE)
->leftJoin('profile.placementHiding', 'hiding')
->andWhere('hiding IS NULL')
->andWhere('profile.approved = false');
return (int)$qb->getQuery()->getSingleScalarResult();
}
public function iterateFreeUnapprovedLimited(int $limit): iterable
{
$qb = $this->createQueryBuilder('profile')
->join('profile.adBoardPlacement', 'placement')
->andWhere('placement.type = :placement_type')
->setParameter('placement_type', AdBoardPlacementType::FREE)
->leftJoin('profile.placementHiding', 'hiding')
->andWhere('hiding IS NULL')
->andWhere('profile.approved = false')
->setMaxResults($limit);
return $this->iterateQueryBuilder($qb);
}
/**
* Число активных анкет, привязанных к аккаунту
*/
public function countActiveOfOwner(User $owner, ?bool $isMasseur = false): int
{
$qb = $this->createQueryBuilder('profile')
->select('COUNT(profile.id)')
->join('profile.adBoardPlacement', 'profile_adboard_placement')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner);
if ($this->features->hard_moderation()) {
$qb->leftJoin('profile.owner', 'owner');
$qb->andWhere(
$qb->expr()->orX(
'profile.moderationStatus = :status_passed',
$qb->expr()->andX(
'profile.moderationStatus = :status_waiting',
'owner.trusted = true'
)
)
);
$qb->setParameter('status_passed', Profile::MODERATION_STATUS_APPROVED);
$qb->setParameter('status_waiting', Profile::MODERATION_STATUS_WAITING);
} else {
$qb->andWhere('profile.moderationStatus IN (:statuses)')
->setParameter('statuses', [Profile::MODERATION_STATUS_NOT_PASSED, Profile::MODERATION_STATUS_WAITING, Profile::MODERATION_STATUS_APPROVED]);
}
if (null !== $isMasseur) {
$qb->andWhere('profile.masseur = :is_masseur')
->setParameter('is_masseur', $isMasseur);
}
return (int)$qb->getQuery()->getSingleScalarResult();
}
/**
* Число всех анкет, привязанных к аккаунту
*/
public function countAllOfOwnerNotDeleted(User $owner, ?bool $isMasseur = false): int
{
$qb = $this->createQueryBuilder('profile')
->select('COUNT(profile.id)')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner)
//потому что используется в т.ч. на тех страницах, где отключен фильтр вывода "только неудаленных"
->andWhere('profile.deletedAt IS NULL');
if (null !== $isMasseur) {
$qb->andWhere('profile.masseur = :is_masseur')
->setParameter('is_masseur', $isMasseur);
}
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 p
JOIN p.city c2
WHERE p.owner = :user
)
", $this->_em->getClassMetadata(City::class)->name, $this->_em->getClassMetadata(Profile::class)->name))
->setParameter('user', $owner);
return $q->getResult();
}
/**
* Список анкет, привязанных к аккаунту
*
* @return Profile[]
*/
public function ofOwner(User $owner): array
{
$qb = $this->createQueryBuilder('profile')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner);
return $qb->getQuery()->getResult();
}
public function ofOwnerPaged(User $owner, array $genders = [Genders::FEMALE]): ORMQueryResult
{
$qb = $this->createQueryBuilder('profile')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner)
->andWhere('profile.personParameters.gender IN (:genders)')
->setParameter('genders', $genders);
return new ORMQueryResult($qb);
}
public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilterIterateAll(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): \Generator
{
$query = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur)->getQuery();
foreach ($query->iterate() as $row) {
yield $row[0];
}
}
private function queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): QueryBuilder
{
$qb = $this->createQueryBuilder('profile')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner);
switch ($placementTypeFilter) {
case 'paid':
$qb->join('profile.adBoardPlacement', 'placement')
->andWhere('placement.type != :placement_type')
->setParameter('placement_type', AdBoardPlacementType::FREE);
break;
case 'free':
$qb->join('profile.adBoardPlacement', 'placement')
->andWhere('placement.type = :placement_type')
->setParameter('placement_type', AdBoardPlacementType::FREE);
break;
case 'ultra-vip':
$qb->join('profile.adBoardPlacement', 'placement')
->andWhere('placement.type = :placement_type')
->setParameter('placement_type', AdBoardPlacementType::ULTRA_VIP);
break;
case 'vip':
$qb->join('profile.adBoardPlacement', 'placement')
->andWhere('placement.type = :placement_type')
->setParameter('placement_type', AdBoardPlacementType::VIP);
break;
case 'standard':
$qb->join('profile.adBoardPlacement', 'placement')
->andWhere('placement.type = :placement_type')
->setParameter('placement_type', AdBoardPlacementType::STANDARD);
break;
case 'hidden':
$qb->join('profile.placementHiding', 'placement_hiding');
break;
case 'all':
default:
break;
}
if ($nameFilter) {
$nameExpr = $qb->expr()->orX(
'LOWER(JSON_UNQUOTE(JSON_EXTRACT(profile.name, :jsonPath))) LIKE :name_filter',
\sprintf("REGEXP_REPLACE(profile.phoneNumber, '-| ', '') LIKE :name_filter"),
'LOWER(profile.phoneNumber) LIKE :name_filter',
\sprintf("REGEXP_REPLACE(profile.phoneNumber, '\+7', '8') LIKE :name_filter"),
);
$qb->setParameter('jsonPath', '$.ru');
$qb->setParameter('name_filter', '%' . addcslashes(mb_strtolower(str_replace(['(', ')', ' ', '-'], '', $nameFilter)), '%_') . '%');
$qb->andWhere($nameExpr);
}
if (null !== $isMasseur) {
$qb->andWhere('profile.masseur = :is_masseur')
->setParameter('is_masseur', $isMasseur);
}
return $qb;
}
public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilterPaged(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): ORMQueryResult
{
$qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
//сортируем анкеты по статусу UltraVip->Vip->Standard->Free->Hidden
$aliases = $qb->getAllAliases();
if (false == in_array('placement', $aliases))
$qb->leftJoin('profile.adBoardPlacement', 'placement');
if (false == in_array('placement_hiding', $aliases))
$qb->leftJoin('profile.placementHiding', 'placement_hiding');
$qb->addSelect('IF(placement_hiding.id IS NULL, 0, 1) as HIDDEN is_hidden');
$qb->addOrderBy('placement.type', 'DESC');
$qb->addOrderBy('placement.placedAt', 'DESC');
$qb->addOrderBy('is_hidden', 'ASC');
return new ORMQueryResult($qb);
}
public function idsOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): array
{
$qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
$qb->select('profile.id');
return $qb->getQuery()->getResult('column_hydrator');
}
public function countOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): int
{
$qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
$qb->select('count(profile.id)')
->setMaxResults(1);
return (int)$qb->getQuery()->getSingleScalarResult();
}
/**
* @deprecated
*/
public function hydrateProfileRow(array $row): ProfileListingReadModel
{
$profile = new ProfileListingReadModel();
$profile->id = $row['id'];
$profile->city = $row['city'];
$profile->uriIdentity = $row['uriIdentity'];
$profile->name = $row['name'];
$profile->description = $row['description'];
$profile->phoneNumber = $row['phoneNumber'];
$profile->approved = $row['approved'];
$now = new \DateTimeImmutable('now');
$hasRunningTopPlacement = false;
foreach ($row['topPlacements'] as $topPlacement) {
if ($topPlacement['placedAt'] <= $now && $now <= $topPlacement['expiresAt'])
$hasRunningTopPlacement = true;
}
$profile->active = null !== $row['adBoardPlacement'] || $hasRunningTopPlacement;
$profile->hidden = null != $row['placementHiding'];
$profile->personParameters = new ProfilePersonParametersReadModel();
$profile->personParameters->age = $row['personParameters.age'];
$profile->personParameters->height = $row['personParameters.height'];
$profile->personParameters->weight = $row['personParameters.weight'];
$profile->personParameters->breastSize = $row['personParameters.breastSize'];
$profile->personParameters->bodyType = $row['personParameters.bodyType'];
$profile->personParameters->hairColor = $row['personParameters.hairColor'];
$profile->personParameters->privateHaircut = $row['personParameters.privateHaircut'];
$profile->personParameters->nationality = $row['personParameters.nationality'];
$profile->personParameters->hasTattoo = $row['personParameters.hasTattoo'];
$profile->personParameters->hasPiercing = $row['personParameters.hasPiercing'];
$profile->stations = $row['stations'];
$profile->avatar = $row['avatar'];
foreach ($row['photos'] as $photo)
if ($photo['main'])
$profile->mainPhoto = $photo;
$profile->mainPhoto = null;
$profile->photos = [];
$profile->selfies = [];
foreach ($row['photos'] as $photo) {
if ($photo['main'])
$profile->mainPhoto = $photo;
if ($photo['type'] == Photo::TYPE_PHOTO)
$profile->photos[] = $photo;
if ($photo['type'] == Photo::TYPE_SELFIE)
$profile->selfies[] = $photo;
}
$profile->videos = $row['videos'];
$profile->comments = $row['comments'];
$profile->apartmentsPricing = new ProfileApartmentPricingReadModel();
$profile->apartmentsPricing->oneHourPrice = $row['apartmentsPricing.oneHourPrice'];
$profile->apartmentsPricing->twoHoursPrice = $row['apartmentsPricing.twoHoursPrice'];
$profile->apartmentsPricing->nightPrice = $row['apartmentsPricing.nightPrice'];
$profile->takeOutPricing = new ProfileTakeOutPricingReadModel();
$profile->takeOutPricing->oneHourPrice = $row['takeOutPricing.oneHourPrice'];
$profile->takeOutPricing->twoHoursPrice = $row['takeOutPricing.twoHoursPrice'];
$profile->takeOutPricing->nightPrice = $row['takeOutPricing.nightPrice'];
return $profile;
}
public function deletedByPeriod(\DateTimeInterface $start, \DateTimeInterface $end): array
{
$qb = $this->createQueryBuilder('profile')
->join('profile.city', 'city')
->select('profile.uriIdentity _profile')
->addSelect('city.uriIdentity _city')
->andWhere('profile.deletedAt >= :start')
->andWhere('profile.deletedAt <= :end')
->setParameter('start', $start)
->setParameter('end', $end);
return $qb->getQuery()->getResult();
}
public function listForMapMatchingSpec(Filter|QueryModifier $specification, int $coordinatesRoundPrecision = 3): array
{
$stmt = $this->getEntityManager()->getConnection()->prepare("
SET SESSION group_concat_max_len = 100000;
");
$stmt->executeStatement();
/** @var QueryBuilder $qb */
$qb = $this->createQueryBuilder($dqlAlias = 'p');
$qb->select(sprintf('GROUP_CONCAT(p.id), CONCAT(ROUND(MIN(p.mapCoordinate.latitude),5),\',\',ROUND(MIN(p.mapCoordinate.longitude),5)), count(p.id), CONCAT(ROUND(p.mapCoordinate.latitude,%1$s),\',\',ROUND(p.mapCoordinate.longitude,%1$s)) as coords, GROUP_CONCAT(p.masseur)', $coordinatesRoundPrecision));
$qb->groupBy('coords');
$specification->modify($qb, $dqlAlias);
$qb->andWhere($specification->getFilter($qb, $dqlAlias));
return $qb->getQuery()->getResult();
}
public function fetchListingByIds(ProfileIdINOrderedByINValues $specification): array
{
$ids = implode(',', $specification->getIds());
$mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
$mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
$sql = "
SELECT
p.*, JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru'))
as `name`,
JSON_UNQUOTE(JSON_EXTRACT(p.description, '$.ru'))
as `description`,
(SELECT path FROM profile_media_files pmf_avatar WHERE p.id = pmf_avatar.profile_id AND pmf_avatar.type = '{$mediaType}' AND pmf_avatar.is_main = {$mediaIsMain} LIMIT 1)
as `avatar_path`,
(SELECT type FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1)
as `adboard_placement_type`,
(SELECT position FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1)
as `adboard_placement_position`,
c.id
as `city_id`,
JSON_UNQUOTE(JSON_EXTRACT(c.name, '$.ru'))
as `city_name`,
c.uri_identity
as `city_uri_identity`,
c.country_code
as `city_country_code`,
EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
as `has_top_placement`,
EXISTS(SELECT * FROM placement_hidings ph WHERE p.id = ph.profile_id AND ph.entity_type = 'profile')
as `has_placement_hiding`,
EXISTS(SELECT * FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL)
as `has_comments`,
EXISTS(SELECT * FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video')
as `has_videos`,
EXISTS(SELECT * FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie')
as `has_selfies`
FROM profiles `p`
JOIN cities `c` ON c.id = p.city_id
WHERE p.id IN ($ids)
ORDER BY FIELD(p.id,$ids)";
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
/** @var Statement $stmt */
$profiles = $result->fetchAllAssociative();
$sql = "SELECT
cs.id
as `id`,
JSON_UNQUOTE(JSON_EXTRACT(cs.name, '$.ru'))
as `name`,
cs.uri_identity
as `uriIdentity`,
ps.profile_id
as `profile_id`,
csl.name
as `line_name`,
csl.color
as `line_color`
FROM profile_stations ps
JOIN city_stations cs ON ps.station_id = cs.id
LEFT JOIN city_subway_station_lines cssl ON cssl.station_id = cs.id
LEFT JOIN city_subway_lines csl ON csl.id = cssl.line_id
WHERE ps.profile_id IN ($ids)";
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
/** @var Statement $stmt */
$stations = $result->fetchAllAssociative();
$sql = "SELECT
s.id
as `id`,
JSON_UNQUOTE(JSON_EXTRACT(s.name, '$.ru'))
as `name`,
s.group
as `group`,
s.uri_identity
as `uriIdentity`,
pps.profile_id
as `profile_id`,
pps.service_condition
as `condition`,
pps.extra_charge
as `extra_charge`,
pps.comment
as `comment`
FROM profile_provided_services pps
JOIN services s ON pps.service_id = s.id
WHERE pps.profile_id IN ($ids)
ORDER BY s.group ASC, s.sort ASC, s.id ASC";
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
/** @var Statement $stmt */
$providedServices = $result->fetchAllAssociative();
$result = array_map(function ($profile) use ($stations, $providedServices): ProfileListingReadModel {
return $this->hydrateProfileRow2($profile, $stations, $providedServices);
}, $profiles);
return $result;
}
public function hydrateProfileRow2(array $row, array $stations, array $services): ProfileListingReadModel
{
$profile = new ProfileListingReadModel();
$profile->id = $row['id'];
$profile->moderationStatus = $row['moderation_status'];
$profile->city = new CityReadModel();
$profile->city->id = $row['city_id'];
$profile->city->name = $row['city_name'];
$profile->city->uriIdentity = $row['city_uri_identity'];
$profile->city->countryCode = $row['city_country_code'];
$profile->uriIdentity = $row['uri_identity'];
$profile->name = $row['name'];
$profile->description = $row['description'];
$profile->phoneNumber = $row['phone_number'];
$profile->approved = (bool)$row['is_approved'];
$profile->isUltraVip = $row['adboard_placement_type'] == AdBoardPlacement::POSITION_GROUP_ULTRA_VIP;
$profile->isVip = $row['adboard_placement_type'] == AdBoardPlacement::POSITION_GROUP_VIP;
$profile->isStandard = false !== array_search(
$row['adboard_placement_type'],
[
AdBoardPlacement::POSITION_GROUP_STANDARD_APPROVED, AdBoardPlacement::POSITION_GROUP_STANDARD,
AdBoardPlacement::POSITION_GROUP_WITHOUT_OWNER_APPROVED, AdBoardPlacement::POSITION_GROUP_WITHOUT_OWNER
]
);
$profile->position = $row['adboard_placement_position'];
$profile->active = null !== $row['adboard_placement_type'] || $row['has_top_placement'];
$profile->hidden = $row['has_placement_hiding'] == true;
$profile->personParameters = new ProfilePersonParametersReadModel();
$profile->personParameters->age = $row['person_age'];
$profile->personParameters->height = $row['person_height'];
$profile->personParameters->weight = $row['person_weight'];
$profile->personParameters->breastSize = $row['person_breast_size'];
$profile->personParameters->bodyType = $row['person_body_type'];
$profile->personParameters->hairColor = $row['person_hair_color'];
$profile->personParameters->privateHaircut = $row['person_private_haircut'];
$profile->personParameters->nationality = $row['person_nationality'];
$profile->personParameters->hasTattoo = $row['person_has_tattoo'];
$profile->personParameters->hasPiercing = $row['person_has_piercing'];
$profile->stations = [];
foreach ($stations as $station) {
if ($profile->id !== $station['profile_id'])
continue;
$profileStation = $profile->stations[$station['id']] ?? new StationReadModel($station['id'], $station['uriIdentity'], $station['name'], []);
if (null !== $station['line_name']) {
$profileStation->lines[] = new StationLineReadModel($station['line_name'], $station['line_color']);
}
$profile->stations[$station['id']] = $profileStation;
}
$primaryId = (int)$row['primary_station_id'];
if (!empty($profile->stations)) {
uasort($profile->stations, function (StationReadModel $a, StationReadModel $b) use ($primaryId) {
$aPrimary = $a->id === $primaryId;
$bPrimary = $b->id === $primaryId;
if ($aPrimary !== $bPrimary) {
return $aPrimary ? -1 : 1;
}
return strnatcasecmp($a->name, $b->name);
});
}
$profile->providedServices = [];
foreach ($services as $service) {
if ($profile->id !== $service['profile_id'])
continue;
$providedService = $profile->providedServices[$service['id']] ?? new ProvidedServiceReadModel(
$service['id'], $service['name'], $service['group'], $service['uriIdentity'],
$service['condition'], $service['extra_charge'], $service['comment']
);
$profile->providedServices[$service['id']] = $providedService;
}
$profile->selfies = $row['has_selfies'] ? [1] : [];
$profile->videos = $row['has_videos'] ? [1] : [];
$avatar = [
'path' => $row['avatar_path'] ?? '',
'type' => $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO
];
if ($this->features->crop_avatar()) {
$profile->avatar = $avatar;
} else {
$profile->mainPhoto = $avatar;
$profile->photos = [];
}
$profile->comments = $row['has_comments'] ? [1] : [];
$profile->apartmentsPricing = new ProfileApartmentPricingReadModel();
$profile->apartmentsPricing->oneHourPrice = $row['apartments_one_hour_price'];
$profile->apartmentsPricing->twoHoursPrice = $row['apartments_two_hours_price'];
$profile->apartmentsPricing->nightPrice = $row['apartments_night_price'];
$profile->takeOutPricing = new ProfileTakeOutPricingReadModel();
$profile->takeOutPricing->oneHourPrice = $row['take_out_one_hour_price'];
$profile->takeOutPricing->twoHoursPrice = $row['take_out_two_hours_price'];
$profile->takeOutPricing->nightPrice = $row['take_out_night_price'];
$profile->takeOutPricing->locations = $row['take_out_locations'] ? array_map('intval', explode(',', $row['take_out_locations'])) : [];
$profile->seo = $row['seo'] ? json_decode($row['seo'], true) : null;
return $profile;
}
public function fetchMapProfilesByIds(ProfileIdINOrderedByINValues $specification): array
{
$ids = implode(',', $specification->getIds());
$mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
$mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
$sql = "
SELECT
p.id, p.uri_identity, p.map_latitude, p.map_longitude, p.phone_number, p.is_masseur, p.is_approved,
p.person_age, p.person_breast_size, p.person_height, p.person_weight, pap.type as placement_type, ptp.id as top_placement, p.primary_station_id
JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru'))
as `name`,
(SELECT path FROM profile_media_files pmf_avatar WHERE p.id = pmf_avatar.profile_id AND pmf_avatar.type = '{$mediaType}' AND pmf_avatar.is_main = {$mediaIsMain} LIMIT 1)
as `avatar_path`,
p.apartments_one_hour_price, p.apartments_two_hours_price, p.apartments_night_price, p.take_out_one_hour_price, p.take_out_two_hours_price, p.take_out_night_price,
GROUP_CONCAT(ps.station_id) as `stations`,
GROUP_CONCAT(pps.service_id) as `services`,
EXISTS(SELECT * FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL)
as `has_comments`,
EXISTS(SELECT * FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video')
as `has_videos`,
EXISTS(SELECT * FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie')
as `has_selfies`
FROM profiles `p`
LEFT JOIN profile_stations ps ON ps.profile_id = p.id
LEFT JOIN profile_provided_services pps ON pps.profile_id = p.id
LEFT JOIN profile_adboard_placements pap ON pap.profile_id = p.id
LEFT JOIN profile_top_placements ptp ON ptp.profile_id = p.id
WHERE p.id IN ($ids)
GROUP BY p.id, ptp.id
"; // AND p.map_latitude IS NOT NULL AND p.map_longitude IS NOT NULL; ORDER BY FIELD(p.id,$ids)
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
/** @var Statement $stmt */
$profiles = $result->fetchAllAssociative();
$result = array_map(function ($profile): ProfileMapReadModel {
return $this->hydrateMapProfileRow($profile);
}, $profiles);
return $result;
}
public function hydrateMapProfileRow(array $row): ProfileMapReadModel
{
$profile = new ProfileMapReadModel();
$profile->id = $row['id'];
$profile->uriIdentity = $row['uri_identity'];
$profile->name = $row['name'];
$profile->phoneNumber = $row['phone_number'];
$profile->avatar = ['path' => $row['avatar_path'] ?? '', 'type' => $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO];
$profile->mapLatitude = $row['map_latitude'];
$profile->mapLongitude = $row['map_longitude'];
$profile->age = $row['person_age'];
$profile->breastSize = $row['person_breast_size'];
$profile->height = $row['person_height'];
$profile->weight = $row['person_weight'];
$profile->isMasseur = $row['is_masseur'];
$profile->isApproved = $row['is_approved'];
$profile->hasComments = $row['has_comments'];
$profile->hasSelfies = $row['has_selfies'];
$profile->hasVideos = $row['has_videos'];
$profile->apartmentOneHourPrice = $row['apartments_one_hour_price'];
$profile->apartmentTwoHoursPrice = $row['apartments_two_hours_price'];
$profile->apartmentNightPrice = $row['apartments_night_price'];
$profile->takeOutOneHourPrice = $row['take_out_one_hour_price'];
$profile->takeOutTwoHoursPrice = $row['take_out_two_hours_price'];
$profile->takeOutNightPrice = $row['take_out_night_price'];
$profile->station = $row['primary_station_id'] ?? ($row['stations'] ? explode(',', $row['stations'])[0] : null);
$profile->services = $row['services'] ? array_unique(explode(',', $row['services'])) : [];
$profile->isPaid = $row['placement_type'] >= AdBoardPlacement::POSITION_GROUP_STANDARD || $row['top_placement'] !== null;
// $prices = [ $row['apartments_one_hour_price'], $row['apartments_two_hours_price'], $row['apartments_night_price'],
// $row['take_out_one_hour_price'], $row['take_out_two_hours_price'], $row['take_out_night_price'] ];
// $prices = array_filter($prices, function($item) {
// return $item != null;
// });
// $profile->price = count($prices) ? min($prices) : null;
return $profile;
}
public function fetchAccountProfileListByIds(ProfileIdINOrderedByINValues $specification): array
{
$ids = implode(',', $specification->getIds());
$mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
$mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
$sql = "
SELECT
p.*, JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru'))
as `name`,
JSON_UNQUOTE(JSON_EXTRACT(p.description, '$.ru'))
as `description`,
(SELECT path FROM profile_media_files pmf_avatar WHERE p.id = pmf_avatar.profile_id AND pmf_avatar.type = '{$mediaType}' AND pmf_avatar.is_main = {$mediaIsMain} LIMIT 1)
as `avatar_path`,
(SELECT type FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1)
as `adboard_placement_type`,
c.id
as `city_id`,
JSON_UNQUOTE(JSON_EXTRACT(c.name, '$.ru'))
as `city_name`,
c.uri_identity
as `city_uri_identity`,
c.country_code
as `city_country_code`,
EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
as `has_top_placement`,
EXISTS(SELECT * FROM placement_hidings ph WHERE p.id = ph.profile_id AND ph.entity_type = 'profile')
as `has_placement_hiding`,
EXISTS(SELECT * FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL)
as `has_comments`,
EXISTS(SELECT * FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video')
as `has_videos`,
EXISTS(SELECT * FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie')
as `has_selfies`
FROM profiles `p`
JOIN cities `c` ON c.id = p.city_id
WHERE p.id IN ($ids)
ORDER BY FIELD(p.id,$ids)";
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
/** @var Statement $stmt */
$profiles = $result->fetchAllAssociative();
$sql = "SELECT
JSON_UNQUOTE(JSON_EXTRACT(cs.name, '$.ru'))
as `name`,
cs.uri_identity
as `uriIdentity`,
ps.profile_id
as `profile_id`
FROM profile_stations ps
JOIN city_stations cs ON ps.station_id = cs.id
WHERE ps.profile_id IN ($ids)";
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
/** @var Statement $stmt */
$stations = $result->fetchAllAssociative();
$sql = "SELECT
s.id
as `id`,
JSON_UNQUOTE(JSON_EXTRACT(s.name, '$.ru'))
as `name`,
s.group
as `group`,
s.uri_identity
as `uriIdentity`,
pps.profile_id
as `profile_id`,
pps.service_condition
as `condition`,
pps.extra_charge
as `extra_charge`,
pps.comment
as `comment`
FROM profile_provided_services pps
JOIN services s ON pps.service_id = s.id
WHERE pps.profile_id IN ($ids)
ORDER BY s.group ASC, s.sort ASC, s.id ASC";
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
/** @var Statement $stmt */
$providedServices = $result->fetchAllAssociative();
$result = array_map(function ($profile) use ($stations, $providedServices): ProfileListingReadModel {
return $this->hydrateProfileRow2($profile, $stations, $providedServices);
}, $profiles);
return $result;
}
public function getCommentedProfilesPaged(User $owner): ORMQueryResult
{
$qb = $this->createQueryBuilder('profile')
->join('profile.comments', 'comment')
->andWhere('profile.owner = :owner')
->setParameter('owner', $owner)
->orderBy('comment.createdAt', 'DESC');
return new ORMQueryResult($qb);
}
/**
* @return ProfilePlacementPriceDetailReadModel[]
*/
public function fetchOfOwnerPlacedPriceDetails(User $owner): array
{
$sql = "
SELECT
p.id, p.is_approved, psp.price_amount
FROM profiles `p`
JOIN profile_adboard_placements pap ON pap.profile_id = p.id AND pap.placement_price_id IS NOT NULL
JOIN paid_service_prices psp ON pap.placement_price_id = psp.id
WHERE p.user_id = {$owner->getId()}
";
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
$profiles = $result->fetchAllAssociative();
return array_map(function (array $row): ProfilePlacementPriceDetailReadModel {
return new ProfilePlacementPriceDetailReadModel(
$row['id'], $row['is_approved'], $row['price_amount'] / 24
);
}, $profiles);
}
/**
* @return ProfilePlacementHidingDetailReadModel[]
*/
public function fetchOfOwnerHiddenDetails(User $owner): array
{
$sql = "
SELECT
p.id, p.is_approved
FROM profiles `p`
JOIN placement_hidings ph ON ph.profile_id = p.id
WHERE p.user_id = {$owner->getId()}
";
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->prepare($sql);
$result = $stmt->executeQuery([]);
$profiles = $result->fetchAllAssociative();
return array_map(function (array $row): ProfilePlacementHidingDetailReadModel {
return new ProfilePlacementHidingDetailReadModel(
$row['id'], $row['is_approved'], true
);
}, $profiles);
}
protected function modifyListingQueryBuilder(QueryBuilder $qb, string $alias): void
{
$qb
->addSelect('city')
->addSelect('station')
->addSelect('photo')
->addSelect('video')
->addSelect('comment')
->addSelect('avatar')
->join(sprintf('%s.city', $alias), 'city');
if (!in_array('station', $qb->getAllAliases()))
$qb->leftJoin(sprintf('%s.stations', $alias), 'station');
if (!in_array('photo', $qb->getAllAliases()))
$qb->leftJoin(sprintf('%s.photos', $alias), 'photo');
if (!in_array('video', $qb->getAllAliases()))
$qb->leftJoin(sprintf('%s.videos', $alias), 'video');
if (!in_array('avatar', $qb->getAllAliases()))
$qb->leftJoin(sprintf('%s.avatar', $alias), 'avatar');
if (!in_array('comment', $qb->getAllAliases()))
$qb->leftJoin(sprintf('%s.comments', $alias), 'comment');
$this->addFemaleGenderFilterToQb($qb, $alias);
//TODO убрать, если все ок
//$this->excludeHavingPlacementHiding($qb, $alias);
if (!in_array('profile_adboard_placement', $qb->getAllAliases())) {
$qb
->leftJoin(sprintf('%s.adBoardPlacement', $alias), 'profile_adboard_placement');
}
$qb->addSelect('profile_adboard_placement');
if (!in_array('profile_top_placement', $qb->getAllAliases())) {
$qb
->leftJoin(sprintf('%s.topPlacements', $alias), 'profile_top_placement');
}
$qb->addSelect('profile_top_placement');
//if($this->features->free_profiles()) {
if (!in_array('placement_hiding', $qb->getAllAliases())) {
$qb
->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding');
}
$qb->addSelect('placement_hiding');
//}
}
protected function addActiveFilterToQb(QueryBuilder $qb, string $dqlAlias)
{
if (!in_array('profile_adboard_placement', $qb->getAllAliases())) {
$qb
->join(sprintf('%s.adBoardPlacement', $dqlAlias), 'profile_adboard_placement');
}
}
private function excludeHavingPlacementHiding(QueryBuilder $qb, $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'))
// ;
// }
$sub = new QueryBuilder($qb->getEntityManager());
$sub->select("exclude_hidden_placement_hiding");
$sub->from($qb->getEntityManager()->getClassMetadata(PlacementHiding::class)->name, "exclude_hidden_placement_hiding");
$sub->andWhere(sprintf('exclude_hidden_placement_hiding.profile = %s', $alias));
$qb->andWhere($qb->expr()->not($qb->expr()->exists($sub->getDQL())));
}
}
}

src/Repository/ProfileRepository.php line 118