Symfony Profiler

<?php
namespace Whater\Infrastructure\ZonesBundle\Repository;
use Whater\Domain\Zones\Model\Town;
use Whater\Domain\Zones\Repository\TownRepositoryInterface;
use Doctrine\ORM\EntityRepository;
use Pagerfanta\Doctrine\ORM\QueryAdapter;
use Pagerfanta\Pagerfanta;
/**
* Class TownRepository
*
* @package Whater\Infrastructure\ZonesBundle\Repository
*/
class TownRepository extends EntityRepository implements TownRepositoryInterface
{
public function findOneById(string $townId): ?Town
{
return $this->createQueryBuilder('town')
->where('town.uuid = :id')
->setParameter('id', $townId)
->getQuery()
->getOneOrNullResult();
}
public function findOneBySlug(string $townSlug): ?Town
{
return $this->createQueryBuilder('town')
->where('town.slug = :townSlug')
->setParameter('townSlug', $townSlug)
->getQuery()
->getOneOrNullResult();
}
public function findOneByExternalId(string $externalIdName, string $externalIdValue): ?Town
{
return $this->createQueryBuilder('town')
->where('town.externalIds like :externalIdRegex')
->setParameter('externalIdRegex', '%"' . $externalIdName . '":"' . $externalIdValue . '"%')
->getQuery()
->getOneOrNullResult();
}
public function findAll()
{
$queryBuilder = $this->createQueryBuilder($alias = 'town');
return $queryBuilder->getQuery()->getResult();
}
public function findAllDuplicatedSlug()
{
$queryBuilder = $this->createQueryBuilder($alias = 'town');
$queryBuilder = $queryBuilder->andWhere('town.slug in (SELECT tw.slug FROM Whater\Domain\Zones\Model\Town tw GROUP BY tw.slug HAVING count(tw.slug)>1)');
return $queryBuilder->getQuery()->getResult();
}
public function findAllByCountry(string $countryIsoCode)
{
$queryBuilder = $this->createQueryBuilder($alias = 'town');
$queryBuilder = $queryBuilder->leftJoin('town.countryArea', 'countryArea');
$queryBuilder = $queryBuilder->leftJoin('town.country', 'country');
$queryBuilder = $queryBuilder->andWhere('country.isoCode = :countryIsoCode');
$queryBuilder = $queryBuilder->setParameter('countryIsoCode', $countryIsoCode);
return $queryBuilder->getQuery()->getResult();
}
public function findAllByCountryArea(string $countryAreaIsoCode)
{
$queryBuilder = $this->createQueryBuilder($alias = 'town');
$queryBuilder = $queryBuilder->leftJoin('town.countryArea', 'countryArea');
$queryBuilder = $queryBuilder->andWhere('countryArea.isoCode = :countryAreaIsoCode');
$queryBuilder = $queryBuilder->setParameter('countryAreaIsoCode', $countryAreaIsoCode);
return $queryBuilder->getQuery()->getResult();
}
public function calculateStCenterId(
string $townId
): ?array {
// calculate centroid
$query = $this->createQueryBuilder('town')
->select('st_as_text(st_centroid(town.geometryPolygon))')
->andWhere('town.uuid = :townId')
->setParameter('townId', $townId);
$centroId = $query->getQuery()->getResult();
$lng = null;
$lat = null;
sscanf($centroId[0]["1"], 'POINT(%f %f)', $lng, $lat);
return [$lat, $lng];
}
public function findAllPaginated(
array $sort = [],
$limit = 500,
$page = 1,
?string $globalFilter = null,
?string $countryiso3 = null,
$nameFilter = null,
$slugFilter = null,
$countryAreaNameFilter = null,
$countryNameFilter = null,
?bool $hasPolygonFilter = null,
$isVerifiedFilter = null,
?string $whaterOrganizationFilter = null,
) {
$queryBuilder = $this->createQueryBuilder($alias = 'town');
$joinCountry = false;
$joinCountryArea = false;
$queryBuilder = $queryBuilder->setMaxResults($limit);
$queryBuilder = $queryBuilder->setFirstResult(($page - 1) * $limit);
if (!empty($globalFilter)) {
//Reemplazamos la N, n, C y c
$globalFilter = str_replace(
array('Ñ', 'ñ', 'Ç', 'ç'),
array('N', 'n', 'C', 'c'),
$globalFilter
);
// Agrega '+' al inicio y '.*' al final de cada palabra
$againsWords1 = preg_replace('/\b(\w+)\b/', '+$1.*', $globalFilter);
$queryBuilder = $queryBuilder->andWhere("MATCH(town.name) AGAINST (:againsWords1 boolean) >0");
$queryBuilder = $queryBuilder->setParameter('againsWords1', $againsWords1);
}
if (!empty($countryiso3)) {
$joinCountry = true;
$queryBuilder = $queryBuilder->andWhere('country.isoCode3 = :countryiso3');
$queryBuilder = $queryBuilder->setParameter('countryiso3', $countryiso3);
}
if (!empty($nameFilter)) {
//Reemplazamos la N, n, C y c
$nameFilter = str_replace(
array('Ñ', 'ñ', 'Ç', 'ç'),
array('N', 'n', 'C', 'c'),
$nameFilter
);
// Agrega '+' al inicio y '.*' al final de cada palabra
$againsWords2 = preg_replace('/\b(\w+)\b/', '+$1.*', $nameFilter);
$queryBuilder = $queryBuilder->andWhere("MATCH(town.name) AGAINST (:againsWords2 boolean) >0");
$queryBuilder = $queryBuilder->setParameter('againsWords2', $againsWords2);
}
if (!empty($slugFilter)) {
$queryBuilder = $queryBuilder->andWhere('town.slug = :slugFilter');
$queryBuilder = $queryBuilder->setParameter('slugFilter', $slugFilter);
}
if (!empty($countryAreaNameFilter)) {
$joinCountryArea = true;
$queryBuilder = $queryBuilder->andWhere('countryArea.name like :countryAreaNameFilter');
$queryBuilder = $queryBuilder->setParameter('countryAreaNameFilter', '%' . $countryAreaNameFilter . '%');
}
if (!empty($countryNameFilter)) {
$joinCountry = true;
$queryBuilder = $queryBuilder->andWhere('country.name like :countryNameFilter');
$queryBuilder = $queryBuilder->setParameter('countryNameFilter', '%' . $countryNameFilter . '%');
}
if (!empty($hasPolygonFilter)) {
if ($hasPolygonFilter) {
$queryBuilder = $queryBuilder->andWhere('town.geometryPolygon IS NOT NULL');
} else {
$queryBuilder = $queryBuilder->andWhere('town.geometryPolygon IS NULL');
}
}
if (!empty($isVerifiedFilter)) {
if ($isVerifiedFilter == "Si") {
$queryBuilder = $queryBuilder->andWhere('town.whaterOrganization IS NOT NULL');
} elseif ($isVerifiedFilter == "No") {
$queryBuilder = $queryBuilder->andWhere('town.whaterOrganization IS NULL');
}
}
if (!empty($whaterOrganizationFilter)) {
$queryBuilder = $queryBuilder->leftJoin('town.whaterOrganization', 'whaterOrganization');
$queryBuilder = $queryBuilder->andWhere('whaterOrganization.uuid = :whaterOrganizationFilter');
$queryBuilder = $queryBuilder->setParameter('whaterOrganizationFilter', $whaterOrganizationFilter);
}
foreach ($sort as $property => $order) {
if (!empty($order)) {
if (strcmp($property, 'name') == 0) {
$queryBuilder->addOrderBy('town.name', $order);
} elseif (strcmp($property, 'town_name') == 0) {
$queryBuilder->addOrderBy('town.name', $order);
} elseif (strcmp($property, 'country_area_name') == 0) {
$queryBuilder->addOrderBy('countryArea.name', $order);
$joinCountryArea = true;
} elseif (strcmp($property, 'town_slug') == 0) {
$queryBuilder->addOrderBy('town.slug', $order);
} elseif (strcmp($property, 'country_name') == 0) {
$joinCountry = true;
$queryBuilder->addOrderBy('country.name', $order);
} elseif (strcmp($property, 'created_at') == 0) {
$queryBuilder->addOrderBy('town.createdAt', $order);
} elseif (strcmp($property, 'population') == 0) {
$queryBuilder->addOrderBy('town.population', $order);
} elseif (strcmp($property, 'updated_at') == 0) {
$queryBuilder->addOrderBy('town.updatedAt', $order);
} elseif (strcmp($property, 'sinac_synchonization') == 0) {
$queryBuilder->addOrderBy('town.sinacSynchonizationAt', $order);
}
}
}
if ($joinCountry) {
$queryBuilder = $queryBuilder->leftJoin('town.countryArea', 'countryArea');
$queryBuilder = $queryBuilder->leftJoin('town.country', 'country');
} else if ($joinCountryArea) {
$queryBuilder = $queryBuilder->leftJoin('town.countryArea', 'countryArea');
}
return new Pagerfanta(new QueryAdapter($queryBuilder, false, false));
}
public function countTowns(\DateTime $beforeAt = null): ?int
{
$qb = $this->createQueryBuilder('town')
->select('count(DISTINCT(town.uuid))');
if (!empty($beforeAt)) {
$qb = $qb->andWhere('town.createdAt <= :beforeAt')
->setParameter('beforeAt', $beforeAt);
}
$qb = $qb->distinct(true)
->getQuery();
return $qb->getSingleScalarResult();
}
/**
* @param Town $town
*/
public function add(Town $town): void
{
$this->_em->persist($town);
}
public function findOneByNearestLatLong(
float $latitude,
float $longitude,
int $radioInKiloMeters,
string $searchName = null,
?string $countryCode = null
): ?Town {
$query = $this->createQueryBuilder('town')
->andWhere('town.latitude IS NOT NULL')
->andWhere('town.longitude IS NOT NULL')
->andWhere('GEO_DISTANCE(town.latitude, town.longitude, :originLatitude, :originLongitude) < :radioInMeters')
->addOrderBy('GEO_DISTANCE(town.latitude, town.longitude, :originLatitude, :originLongitude )', 'ASC')
->setParameter('originLatitude', $latitude)
->setParameter('originLongitude', $longitude)
->setParameter('radioInMeters', $radioInKiloMeters)
->setMaxResults(1);
if ($searchName != null) {
$query = $query->andWhere('town.name like :searchName')->setParameter('searchName', $searchName);
}
if ($countryCode != null) {
$query = $query->leftJoin('town.countryArea', 'countryArea');
$query = $query->leftJoin('town.country', 'country');
$query = $query->andWhere('country.isoCode = :countryIsoCode');
$query = $query->setParameter('countryIsoCode', $countryCode);
}
return $query->getQuery()
->getOneOrNullResult();
}
}

src/Infrastructure/ZonesBundle/Repository/TownRepository.php line 266