src/Aqarmap/Bundle/ListingBundle/Repository/LocationRepository.php line 30

Open in your IDE?
  1. <?php
  3. namespace Aqarmap\Bundle\ListingBundle\Repository;
  5. use Aqarmap\Bundle\ListingBundle\Constant\ListingCategories;
  6. use Aqarmap\Bundle\ListingBundle\Constant\ListingStatus;
  7. use Aqarmap\Bundle\ListingBundle\Entity\Location;
  8. use Aqarmap\Bundle\ListingBundle\Entity\PropertyType;
  9. use Aqarmap\Bundle\MainBundle\Constant\Locales;
  10. use Aqarmap\Bundle\SearchBundle\Entity\SearchResultCombinations;
  11. use Aqarmap\Bundle\UserBundle\Entity\User;
  12. use Doctrine\ORM\EntityManagerInterface;
  13. use Doctrine\ORM\Event\LifecycleEventArgs;
  14. use Doctrine\ORM\Events;
  15. use Doctrine\ORM\Internal\Hydration\IterableResult;
  16. use Doctrine\ORM\NonUniqueResultException;
  17. use Doctrine\ORM\NoResultException;
  18. use Doctrine\ORM\Query;
  19. use Doctrine\ORM\Query\Expr;
  20. use Doctrine\ORM\QueryBuilder;
  21. use Gedmo\Translatable\Query\TreeWalker\TranslationWalker;
  22. use Gedmo\Translatable\TranslatableListener;
  23. use Gedmo\Tree\Entity\Repository\NestedTreeRepository;
  25. class LocationRepository extends NestedTreeRepository
  26. {
  27.     public const RECENT_SEARCHED_LOCATIONS_COUNT = 16;
  28.     public const ONE_RESULT_NUMBER = 1;
  30.     public function __construct(EntityManagerInterface $manager)
  31.     {
  32.         parent::__construct($manager, $manager->getClassMetadata(Location::class));
  33.     }
  35.     public function search(array $criteria)
  36.     {
  37.         $QueryBuilder = $this->createQueryBuilder('lo');
  39.         if (isset($criteria['is_disabled'])) {
  40.             $QueryBuilder
  41.                 ->andWhere($QueryBuilder->expr()
  42.                     ->eq('lo.disabled', ':disabled'))
  43.                 ->setParameter('disabled', $criteria['is_disabled']);
  44.         }
  46.         if (isset($criteria['level'])) {
  47.             $QueryBuilder
  48.                 ->andWhere($QueryBuilder->expr()->eq('lo.level', ':level'))
  49.                 ->setParameter('level', $criteria['level']);
  50.         }
  52.         // Search
  53.         if (!empty($criteria['search']) && !empty($criteria['q'])) {
  54.             $criteria['q'] = trim($criteria['q']);
  56.             switch ($criteria['search']) {
  57.                 case 'location_id':
  58.                     $QueryBuilder
  59.                         ->andWhere($QueryBuilder->expr()->in('lo.id', ':location_id'))
  60.                         ->setParameter('location_id', $criteria['q']);
  61.                     break;
  63.                 case 'parent_id':
  64.                     $QueryBuilder
  65.                         ->andWhere($QueryBuilder->expr()->in('lo.parent', ':parent_id'))
  66.                         ->setParameter('parent_id', $criteria['q']);
  67.                     break;
  69.                 case 'title':
  70.                     $QueryBuilder
  71.                         ->join('lo.translations', 'lot', Expr\Join::WITH, 'lot.field = :title_field')
  72.                         ->andWhere($QueryBuilder->expr()->orX(
  73.                             $QueryBuilder->expr()->like('lot.content', ':title'),
  74.                             $QueryBuilder->expr()->like('lo.title', ':title')
  75.                         ))
  76.                         ->setParameter('title_field', 'title')
  77.                         ->setParameter('title', "{$criteria['q']}%");
  78.                     break;
  79.             }
  80.         }
  82.         return $QueryBuilder;
  83.     }
  85.     /**
  86.      * @param bool $searchableOnly
  87.      *
  88.      * @internal param null $locale
  89.      *
  90.      * @return array
  91.      */
  92.     public function getLocationsList($searchableOnly = false, $estimatable = false)
  93.     {
  94.         $QueryBuilder = $this->createQueryBuilder('lo');
  96.         $QueryBuilder
  97.             ->andWhere($QueryBuilder->expr()->isNull('lo.parent'));
  99.         $QueryBuilder
  100.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  101.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  103.         if ($searchableOnly) {
  104.             $QueryBuilder
  105.                 ->andWhere($QueryBuilder->expr()->eq('lo.searchable', ':is_searchable'))
  106.                 ->setParameter('is_searchable', true, \PDO::PARAM_BOOL);
  107.         }
  109.         if ($estimatable) {
  110.             $QueryBuilder
  111.                 ->andWhere($QueryBuilder->expr()->eq('lo.estimate', ':is_estimatable'))
  112.                 ->setParameter('is_estimatable', true, \PDO::PARAM_BOOL);
  113.         }
  115.         $query = $QueryBuilder->getQuery();
  116.         $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker');
  118.         return $query;
  119.     }
  121.     public function getLocationFlat($criteria)
  122.     {
  123.         $criteria = array_merge([
  124.             'searchable' => null,
  125.             'estimatable' => null,
  126.             'level' => null,
  127.         ], $criteria);
  129.         $QueryBuilder = $this->createQueryBuilder('lo');
  131.         $QueryBuilder
  132.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  133.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  135.         if ((int) $criteria['searchable']) {
  136.             $QueryBuilder
  137.                 ->andWhere($QueryBuilder->expr()->eq('lo.searchable', ':is_searchable'))
  138.                 ->setParameter('is_searchable', true, \PDO::PARAM_BOOL);
  139.         }
  141.         if ((int) $criteria['estimatable']) {
  142.             $QueryBuilder
  143.                 ->andWhere($QueryBuilder->expr()->eq('lo.estimate', ':is_estimatable'))
  144.                 ->setParameter('is_estimatable', true, \PDO::PARAM_BOOL);
  145.         }
  147.         if (null !== $criteria['level']) {
  148.             $QueryBuilder
  149.                 ->andWhere($QueryBuilder->expr()->eq('lo.level', ':level'))
  150.                 ->setParameter('level', $criteria['level']);
  151.         }
  153.         $query = $QueryBuilder->getQuery();
  154.         $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker');
  156.         return $query;
  157.     }
  159.     /**
  160.      * Get location children flat.
  161.      *
  162.      * @return Location|array
  163.      */
  164.     public function getLocationChildrenFlat(Location $location)
  165.     {
  166.         $QueryBuilder = $this->createQueryBuilder('lo');
  167.         $QueryBuilder
  168.             ->andWhere($QueryBuilder->expr()->eq('lo.parent', ':parent_id'))
  169.             ->setParameter('parent_id', $location);
  171.         return $QueryBuilder->getQuery();
  172.     }
  174.     /**
  175.      * Get location children flat Array of Results.
  176.      *
  177.      * @param string $locale
  178.      *
  179.      * @return Location|array
  180.      */
  181.     public function getLocationChildrenFlatResults(Location $location, $locale = null)
  182.     {
  183.         $QueryBuilder = $this->createQueryBuilder('lo');
  184.         $QueryBuilder
  185.             ->andWhere($QueryBuilder->expr()->eq('lo.parent', ':parent_id'))
  186.             ->setParameter('parent_id', $location);
  187.         $query = $QueryBuilder
  188.             ->getQuery()
  189.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class);
  190.         if ($locale) {
  191.             $query->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  192.         }
  194.         return $query->getArrayResult();
  195.     }
  197.     /**
  198.      * @internal param null $locale
  199.      *
  200.      * @return Query
  201.      *
  202.      * @internal param User $user
  203.      * @internal param bool $iteration
  204.      */
  205.     public function getSectionLocationsWithLiveListings(Section $section)
  206.     {
  207.         $QueryBuilder = $this->createQueryBuilder('lo');
  209.         $QueryBuilder
  210.             ->leftJoin('lo.listings', 'lol');
  212.         $QueryBuilder
  213.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  214.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  216.         if ($section) {
  217.             $QueryBuilder
  218.                 ->andWhere($QueryBuilder->expr()->eq('lol.section', ':section'))
  219.                 ->setParameter('section', $section);
  220.         }
  222.         $QueryBuilder
  223.             ->andWhere($QueryBuilder->expr()->eq('lol.status', ':status'))
  224.             ->setParameter('status', ListingStatus::LIVE);
  226.         $query = $QueryBuilder->getQuery();
  227.         $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker');
  229.         return $query->getResult();
  230.     }
  232.     /**
  233.      * @return array
  234.      *
  235.      * @internal param int $level
  236.      * @internal param bool $searchableOnly
  237.      * @internal param null $locale
  238.      */
  239.     public function getLeveledLocations(array $criteria)
  240.     {
  241.         $QueryBuilder = $this->createQueryBuilder('lo');
  243.         $QueryBuilder
  244.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  245.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  247.         if (!empty($criteria['parent'])) {
  248.             if ('no-parent' == $criteria['parent']) {
  249.                 $QueryBuilder
  250.                     ->andWhere($QueryBuilder->expr()->isNull('lo.parent'));
  251.             } else {
  252.                 $QueryBuilder
  253.                     ->andWhere($QueryBuilder->expr()->eq('lo.parent', ':parent'))
  254.                     ->setParameter('parent', $criteria['parent']);
  255.             }
  256.         }
  258.         if (isset($criteria['level'])) {
  259.             $QueryBuilder
  260.                 ->andWhere($QueryBuilder->expr()->lte('lo.level', ':level'))
  261.                 ->setParameter('level', $criteria['level']);
  262.         }
  264.         $QueryBuilder->orderBy('lo.listingsCounter', 'desc');
  266.         $query = $QueryBuilder->getQuery();
  267.         $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker');
  269.         return $query->getResult();
  270.     }
  272.     public function getLeveledLocationsArray(array $criteria)
  273.     {
  274.         $queryBuilder = $this->createQueryBuilder('location');
  276.         $queryBuilder
  277.             ->select('location.id as id, COALESCE(translation.content, location.title) as title, location.slug as slug')
  278.             ->leftJoin(
  279.                 'location.translations',
  280.                 'translation',
  281.                 Expr\Join::WITH,
  282.                 'translation.locale = :locale and translation.field = :title and translation.object = location.id'
  283.             )
  284.             ->andWhere($queryBuilder->expr()->eq('location.disabled', ':disabled'))
  285.             ->setParameter('locale', $criteria['locale'])
  286.             ->setParameter('title', 'title')
  287.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  289.         if (!empty($criteria['parent'])) {
  290.             if ('no-parent' == $criteria['parent']) {
  291.                 $queryBuilder->andWhere($queryBuilder->expr()->isNull('location.parent'));
  292.             } else {
  293.                 $queryBuilder
  294.                     ->andWhere($queryBuilder->expr()->eq('location.parent', ':parent'))
  295.                     ->setParameter('parent', $criteria['parent']);
  296.             }
  297.         }
  299.         if (!empty($criteria['level'])) {
  300.             $queryBuilder
  301.                 ->andWhere($queryBuilder->expr()->lte('location.level', ':level'))
  302.                 ->setParameter('level', $criteria['level']);
  303.         }
  305.         if (!empty($criteria['searchResultsCombinationMinimumCount'])) {
  306.             $queryBuilder->join(
  307.                 SearchResultCombinations::class,
  308.                 'searchResultCombinations',
  309.                 Expr\Join::WITH,
  310.                 'searchResultCombinations.location = location.id'
  311.             )
  312.                 ->andWhere($queryBuilder->expr()->gte('searchResultCombinations.searchResultCount', ':searchResultCount'))
  313.                 ->setParameter('searchResultCount', $criteria['searchResultsCombinationMinimumCount']);
  314.             if (isset($criteria['propertyType'])) {
  315.                 $queryBuilder->andWhere($queryBuilder->expr()->eq('searchResultCombinations.propertyType', ':propertyType'))
  316.                     ->setParameter('propertyType', $criteria['propertyType']);
  317.             }
  318.             if (isset($criteria['section'])) {
  319.                 $queryBuilder->andWhere($queryBuilder->expr()->eq('searchResultCombinations.section', ':section'))
  320.                     ->setParameter('section', $criteria['section']);
  321.             }
  322.             if (!empty($criteria['searchResultsCombinationMax'])) {
  323.                 $queryBuilder->addSelect('MAX(searchResultCombinations.searchResultCount) as searchResultCount');
  324.             } else {
  325.                 $queryBuilder->addSelect('searchResultCombinations.searchResultCount as searchResultCount');
  326.             }
  327.             $queryBuilder->orderBy('searchResultCombinations.searchResultCount', 'desc');
  328.         } else {
  329.             $queryBuilder->orderBy('location.listingsCounter', 'desc');
  330.         }
  332.         return $queryBuilder;
  333.     }
  335.     /**
  336.      * @return Location
  337.      */
  338.     public function getSearchableOrNeighbourhoodNearestParent(Location $location)
  339.     {
  340.         $QueryBuilder = $this->createQueryBuilder('lo');
  342.         $QueryBuilder
  343.             ->andWhere('lo.root = :root')
  344.             ->andWhere('lo.left <= :left')
  345.             ->andWhere('lo.right >= :right')
  346.             ->setParameter('left', $location->getLeft())
  347.             ->setParameter('right', $location->getRight())
  348.             ->setParameter('root', $location->getRoot());
  349.         $QueryBuilder
  350.             ->andWhere('lo.neighborhoodFilter = :is_neighbourhood OR lo.searchable  = :is_searchable')
  351.             ->setParameter('is_neighbourhood', true, \PDO::PARAM_BOOL)
  352.             ->setParameter('is_searchable', true, \PDO::PARAM_BOOL);
  354.         $QueryBuilder
  355.             ->orderBy('lo.level', 'DESC')
  356.             ->setMaxResults(1);
  358.         $query = $QueryBuilder->getQuery();
  359.         $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker');
  361.         return $query->getOneOrNullResult();
  362.     }
  364.     // --------------------------------------------------------------------
  366.     /**
  367.      * Get location children.
  368.      *
  369.      * @return Location|array
  370.      */
  371.     public function getLocationChildren(Location $location)
  372.     {
  373.         $locationChildren = $this->children($location);
  374.         $location = array_merge([$location], $locationChildren);
  376.         return $location;
  377.     }
  379.     /**
  380.      * @return array
  381.      */
  382.     public function getLocationChildrenIds(Location $location)
  383.     {
  384.         $locationChildrenIds = $this->childrenIds($location);
  385.         $location = array_merge([$location->getId()], array_column(array_values($locationChildrenIds), 'id'));
  387.         return $location;
  388.     }
  390.     /**
  391.      * @return array
  392.      */
  393.     public function childrenIds(Location $location)
  394.     {
  395.         $queryBuilder = $this->createQueryBuilder('location');
  396.         $queryBuilder->select('location.id');
  398.         $queryBuilder->where($queryBuilder->expr()->lt('location.right', $location->getRight()));
  399.         $queryBuilder->andWhere($queryBuilder->expr()->gt('location.left', $location->getLeft()));
  401.         $queryBuilder->andWhere($queryBuilder->expr()->eq('location.root', ':rid'));
  402.         $queryBuilder->setParameter('rid', $location->getRoot());
  404.         return $queryBuilder
  405.             ->getQuery()
  406.             ->enableResultCache(1800, sprintf('location_children_ids_%d_%d', $location->getRight(), $location->getLeft()))
  407.             ->getResult();
  408.     }
  410.     // --------------------------------------------------------------------
  412.     /**
  413.      * Get neighbourhood locations.
  414.      *
  415.      * @return Location|array
  416.      */
  417.     public function getNeighbourhoodLocations($criteria = [])
  418.     {
  419.         $QueryBuilder = $this->createQueryBuilder('lo');
  421.         $QueryBuilder
  422.             ->andWhere($QueryBuilder->expr()->eq('lo.neighborhoodFilter', ':is_true'))
  423.             ->setParameter('is_true', true, \PDO::PARAM_BOOL);
  425.         $QueryBuilder
  426.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  427.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  429.         if (isset($criteria['level'])) {
  430.             $QueryBuilder
  431.                 ->andWhere($QueryBuilder->expr()->eq('lo.level', ':level'))
  432.                 ->setParameter('level', $criteria['level']);
  433.         }
  435.         if (isset($criteria['maxLevel'])) {
  436.             $QueryBuilder
  437.                 ->andWhere($QueryBuilder->expr()->lte('lo.level', ':maxLevel'))
  438.                 ->setParameter('maxLevel', $criteria['maxLevel']);
  439.         }
  441.         $query = $QueryBuilder->getQuery();
  443.         return $query->getResult();
  444.     }
  446.     /**
  447.      * Get neighbourhood  sub-locations.
  448.      *
  449.      * @return Location|array
  450.      */
  451.     public function getNeighbourhoodSubLocations(Location $location, PropertyType $propertyType)
  452.     {
  453.         $QueryBuilder = $this->createQueryBuilder('lo')
  454.             ->join('lo.locationStatistics', 'location_statistics', Expr\Join::LEFT_JOIN);
  456.         $QueryBuilder
  457.             ->andWhere($QueryBuilder->expr()->eq('lo.parent', ':location'))
  458.             ->setParameter('location', $location);
  460.         $QueryBuilder
  461.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  462.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  464.         $QueryBuilder
  465.             ->andWhere($QueryBuilder->expr()->eq('location_statistics.propertyType', ':property_type'))
  466.             ->setParameter('property_type', $propertyType);
  468.         $QueryBuilder
  469.             ->andWhere($QueryBuilder->expr()->eq('lo.neighborhoodFilter', ':is_true'))
  470.             ->setParameter('is_true', true, \PDO::PARAM_BOOL);
  472.         $QueryBuilder
  473.             ->andWhere($QueryBuilder->expr()->isNotNull('location_statistics.avgPrice'));
  474.         $QueryBuilder
  475.             ->andWhere($QueryBuilder->expr()->neq('location_statistics.avgPrice', 0));
  477.         $QueryBuilder
  478.             ->orderBy('location_statistics.avgPrice', 'DESC');
  480.         $query = $QueryBuilder->getQuery();
  482.         return $query->getResult();
  483.     }
  485.     /**
  486.      * Get neighbourhood Compounds.
  487.      *
  488.      * @param null $state
  489.      *
  490.      * @return Location|array
  491.      */
  492.     public function getNeighbourhoodCompounds($locations = null, $state = null)
  493.     {
  494.         $QueryBuilder = $this->createQueryBuilder('lo')
  495.             ->join('lo.locationCompound', 'lc', Expr\Join::INNER_JOIN);
  497.         $QueryBuilder
  498.             ->andWhere($QueryBuilder->expr()->eq('lo.compoundFilter', ':is_true'))
  499.             ->setParameter('is_true', true, \PDO::PARAM_BOOL);
  500.         if ($locations) {
  501.             $QueryBuilder
  502.                 ->andWhere($QueryBuilder->expr()->orX(
  503.                     $QueryBuilder->expr()->in('lo.parent', ':locations')
  504.                 ))
  505.                 ->setParameter('locations', $locations);
  506.         }
  507.         if ($state) {
  508.             $QueryBuilder
  509.                 ->andWhere($QueryBuilder->expr()->eq('lc.state', ':state'))
  510.                 ->setParameter('state', $state);
  511.         }
  512.         $query = $QueryBuilder->getQuery();
  514.         return $query->getResult();
  515.     }
  517.     /**
  518.      * @return array
  519.      */
  520.     public function generateNearestLocations(Location $location, $distance)
  521.     {
  522.         $QueryBuilder = $this->createQueryBuilder('lo');
  523.         $QueryBuilder
  524.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  525.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  526.         $QueryBuilder
  527.             ->andWhere($QueryBuilder->expr()->eq('lo.level', ':level'))
  528.             ->setParameter('level', $location->getLevel());
  529.         $QueryBuilder
  530.             ->andWhere($QueryBuilder->expr()->neq('lo', ':location'))
  531.             ->setParameter('location', $location);
  532.         if ($location->getParent()) {
  533.             $QueryBuilder
  534.                 ->andWhere($QueryBuilder->expr()->eq('lo.parent', ':parent'))
  535.                 ->setParameter('parent', $location->getParent());
  536.         }
  537.         $QueryBuilder = $this->getNearestLocationsFormulaQuery($QueryBuilder, $location, $distance);
  538.         $query = $QueryBuilder->getQuery();
  540.         return $query->getResult();
  541.     }
  543.     /**
  544.      * @return Query
  545.      */
  546.     public function generateNearestLocationsQuery(array $criteria)
  547.     {
  548.         /** @var Location $location */
  549.         $location = $criteria['location'];
  551.         $listingQueryBuilder = $this->_em->createQueryBuilder();
  552.         $listingQueryBuilder = $listingQueryBuilder
  553.             ->select('MAX(li.id)')
  554.             ->from(Listing::class, 'li')
  555.             ->andWhere($listingQueryBuilder->expr()->eq('li.status', ListingStatus::LIVE))
  556.             ->andWhere($listingQueryBuilder->expr()->neq('li.category', ListingCategories::PROJECTS));
  558.         if (isset($criteria['propertyType']) && $criteria['propertyType']) {
  559.             $listingQueryBuilder->andWhere($listingQueryBuilder->expr()->in('li.propertyType', $criteria['propertyType']));
  560.         }
  562.         if (isset($criteria['section']) && $criteria['section']) {
  563.             $listingQueryBuilder->andWhere($listingQueryBuilder->expr()->eq('li.section', $criteria['section']));
  564.         }
  566.         $queryBuilder = $this->createQueryBuilder('lo');
  567.         $queryBuilder->addSelect('ST_Distance_Sphere( Point(:longitude, :latitude), Point(lo.lon, lo.lat)) AS distance');
  568.         $queryBuilder
  569.             ->andWhere($queryBuilder->expr()->eq('lo.disabled', ':disabled'))
  570.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  571.         $queryBuilder->innerJoin(
  572.             'lo.listings',
  573.             'listings',
  574.             Expr\Join::WITH,
  575.             $queryBuilder->expr()->eq(
  576.                 'listings.id',
  577.                 sprintf('(%s)', $listingQueryBuilder
  578.                     ->andWhere($listingQueryBuilder->expr()->eq('li.location', 'lo.id'))
  579.                     ->setMaxResults(1)->getQuery()->getDQL())
  580.             )
  581.         );
  583.         $queryBuilder
  584.             ->andWhere($queryBuilder->expr()->neq('lo', ':location'))
  585.             ->setParameter('location', $location);
  587.         if (isset($criteria['excludedSubLocations']) && $criteria['excludedSubLocations']) {
  588.             $queryBuilder
  589.                 ->andWhere($queryBuilder->expr()->notIn('lo', ':subLocations'))
  590.                 ->setParameter('subLocations', $this->getLocationChildrenIds($location));
  591.         }
  593.         $queryBuilder->andWhere($queryBuilder->expr()->neq('lo.listingsCounter', 0));
  595.         $queryBuilder->andWhere($queryBuilder->expr()->lt('ABS(lo.lat)', 90));
  596.         $queryBuilder->andWhere($queryBuilder->expr()->lt('ABS(lo.lon)', 180));
  598.         $queryBuilder->andWhere($queryBuilder->expr()->isNotNull('lo.lat'));
  599.         $queryBuilder->andWhere($queryBuilder->expr()->isNotNull('lo.lon'));
  601.         $queryBuilder
  602.             ->having('distance  <= :maxDistance')
  603.             ->setParameter('maxDistance', $criteria['maxDistance'] * 1000)
  604.             ->setParameter('latitude', $location->getCenterLat())
  605.             ->setParameter('longitude', $location->getCenterLng());
  607.         $queryBuilder->orderBy('distance');
  609.         return $queryBuilder->getQuery();
  610.     }
  612.     public function getNearestLocationsFormulaQuery(QueryBuilder $queryBuilder, Location $location, $distance): QueryBuilder
  613.     {
  614.         return $queryBuilder
  615.             ->having('( 3959 * acos(cos(radians(:latitude))'.
  616.                 '* cos( radians( lo.lat ) )'.
  617.                 '* cos( radians( lo.lon )'.
  618.                 '- radians(:longitude) )'.
  619.                 '+ sin( radians(:latitude) )'.
  620.                 '* sin( radians( lo.lat ) ) ) )  < :distance')
  621.             ->setParameter('distance', $distance)
  622.             ->setParameter('latitude', $location->getCenterLat())
  623.             ->setParameter('longitude', $location->getCenterLng());
  624.     }
  626.     /**
  627.      * Get Nearest Compounds.
  628.      *
  629.      * @return Location|array
  630.      */
  631.     public function getNearestLocations(array $locations = [], bool $limit = true, int $max = 3)
  632.     {
  633.         $QueryBuilder = $this->createQueryBuilder('lo')
  634.             ->join('lo.nearestLocations', 'nearest_locations', Expr\Join::INNER_JOIN);
  636.         $QueryBuilder
  637.             ->andWhere($QueryBuilder->expr()->eq('lo.searchable', ':is_searchable'))
  638.             ->setParameter('is_searchable', true, \PDO::PARAM_BOOL);
  639.         $QueryBuilder
  640.             ->andWhere($QueryBuilder->expr()->in('nearest_locations.id', ':locations'))
  641.             ->setParameter('locations', $locations);
  643.         if ($limit) {
  644.             $QueryBuilder->setMaxResults($max);
  645.         }
  647.         return $QueryBuilder->getQuery()->enableResultCache()->getResult();
  648.     }
  650.     /**
  651.      * @internal param null $locale
  652.      *
  653.      * @param string $locale
  654.      * @param null $levelLimit
  655.      *
  656.      * @return array
  657.      */
  658.     public function getAllSearchableLocationsList($levelLimit = null, $locale = null)
  659.     {
  660.         $QueryBuilder = $this->createQueryBuilder('lo');
  662.         $QueryBuilder
  663.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  664.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  666.         if ($levelLimit) {
  667.             $QueryBuilder
  668.                 ->andWhere($QueryBuilder->expr()->lt('lo.level', ':level'))
  669.                 ->setParameter('level', $levelLimit);
  670.         }
  672.         $query = $QueryBuilder
  673.             ->getQuery()
  674.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class);
  675.         if ($locale) {
  676.             $query->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  677.         }
  679.         return $query->getResult();
  680.     }
  682.     /**
  683.      * @return array
  684.      */
  685.     public function getAllLocationsCount()
  686.     {
  687.         $QueryBuilder = $this->createQueryBuilder('lo')->select('count(lo.id)');
  689.         $QueryBuilder
  690.             ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  691.             ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  693.         return $QueryBuilder->getQuery()->getSingleScalarResult();
  694.     }
  696.     /**
  697.      * Get Nearest Neighbourhoods or  Compounds.
  698.      *
  699.      * @return Location|array
  700.      */
  701.     public function getNearestNeighbourhoods(Location $location)
  702.     {
  703.         $QueryBuilder = $this->createQueryBuilder('lo')
  704.             ->join('lo.nearestLocations', 'nearest_locations', Expr\Join::INNER_JOIN);
  706.         if ($location->getCompoundFilter()) {
  707.             $QueryBuilder
  708.                 ->andWhere($QueryBuilder->expr()->eq('lo.compoundFilter', ':is_true'))
  709.                 ->setParameter('is_true', true, \PDO::PARAM_BOOL);
  710.         }
  711.         if ($location->getNeighborhoodFilter()) {
  712.             $QueryBuilder
  713.                 ->andWhere($QueryBuilder->expr()->eq('lo.neighborhoodFilter', ':is_true'))
  714.                 ->setParameter('is_true', true, \PDO::PARAM_BOOL);
  715.         }
  716.         $QueryBuilder
  717.             ->andWhere($QueryBuilder->expr()->eq('nearest_locations.id', ':location'))
  718.             ->setParameter('location', $location);
  719.         $QueryBuilder->setMaxResults(3);
  721.         $query = $QueryBuilder->getQuery();
  723.         return $query->getResult();
  724.     }
  726.     /**
  727.      * @param bool $forceLocale
  728.      *
  729.      * @return Location
  730.      */
  731.     public function getLocationLike($location, $forceLocale = true)
  732.     {
  733.         $queryBuilder = $this->createQueryBuilder('p')
  734.             ->where('p.title LIKE :location')
  735.             ->setParameter('location', '%'.$location.'%')
  736.             ->getQuery()
  737.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker');
  739.         if ($forceLocale) {
  740.             $queryBuilder->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, 'en');
  741.         }
  743.         return $queryBuilder->setMaxResults(1)
  744.             ->getOneOrNullResult();
  745.     }
  747.     /**
  748.      * Get locations by keyword.
  749.      *
  750.      * @return Query
  751.      */
  752.     public function getLocationByKeyword(?string $keyword = null)
  753.     {
  754.         return $this->createQueryBuilder('l')
  755.             ->select('l')
  756.             ->where('FIND_IN_SET(:keyword, l.keyword) > 0')
  757.             ->setParameter('keyword', $keyword)
  758.             ->getQuery();
  759.     }
  761.     public function getWhereHasBumpableListings()
  762.     {
  763.         $queryBuilder = $this->createQueryBuilder('l');
  765.         return $queryBuilder
  766.             ->select('l, li, lf')
  767.             ->join('l.listings', 'li', Expr\Join::WITH, 'l.id=li.location')
  768.             ->join('li.listingFeatures', 'lf', Expr\Join::WITH, 'li.id=lf.listing')
  769.             ->where($queryBuilder->expr()->eq('li.isBumped', ':isBumped'))
  770.             ->setParameter(':isBumped', true, \PDO::PARAM_BOOL)
  771.             ->andWhere($queryBuilder->expr()->isNull('lf.expiresAt'))
  772.             ->andWhere('lf.bumpsCount < lf.bumpUpOccurrences')
  773.             ->orWhere($queryBuilder->expr()->isNull('lf.bumpsCount'))
  774.             ->andWhere($queryBuilder->expr()->lte('lf.nextBumpDate', ':nextBumpDate'))
  775.             ->setParameter(':nextBumpDate', (new \DateTime())->setTime(0, 0, 0))
  776.             ->orderBy('lf.nextBumpDate', 'DESC')
  777.             ->addOrderBy('lf.id', 'DESC')
  778.             ->addOrderBy('lf.listing', 'DESC')
  779.             ->setMaxResults(100)
  780.             ->getQuery()
  781.             ->getResult();
  782.     }
  784.     /**
  785.      * @param bool $status
  786.      *
  787.      * @throws \Doctrine\ORM\OptimisticLockException
  788.      */
  789.     public function changeStatus(Location $location, $status = true)
  790.     {
  791.         $queryBuilder = $this->createQueryBuilder('location');
  793.         $locations = $this->getChildren($location);
  794.         $locations[] = $location;
  796.         $locationsIds = [];
  797.         foreach ($locations as $childLocation) {
  798.             $locationsIds[] = $childLocation->getId();
  799.         }
  801.         $disableStatus = $queryBuilder
  802.             ->set('location.disabled', (int) $status)
  803.             ->update()
  804.             ->where($queryBuilder->expr()->in('location.id', $locationsIds))
  805.             ->getQuery()
  806.             ->execute();
  808.         $em = $this->getEntityManager();
  809.         foreach ($locations as $childLocation) {
  810.             $childLocation->setDisabled((bool) $status);
  812.             $em->getEventManager()->dispatchEvent(
  813.                 Events::postUpdate,
  814.                 new LifecycleEventArgs($childLocation, $em)
  815.             );
  816.         }
  817.         $em->flush();
  819.         return $disableStatus;
  820.     }
  822.     /**
  823.      * Get Location full path.
  824.      *
  825.      * @param string $locale
  826.      * @param string $separator
  827.      *
  828.      * @return string
  829.      */
  830.     public function getLocationFullPath(Location $location, $locale, $separator = ' / ')
  831.     {
  832.         $nodes = $this->getPathQueryBuilder($location)
  833.             ->getQuery()
  834.             ->setHint(
  835.                 Query::HINT_CUSTOM_OUTPUT_WALKER,
  836.                 TranslationWalker::class
  837.             )->setHint(
  838.                 TranslatableListener::HINT_TRANSLATABLE_LOCALE,
  839.                 $locale
  840.             )
  841.             ->getArrayResult();
  843.         $locationFullPath = $this->buildTree($nodes, [
  844.             'decorate' => true,
  845.             'rootOpen' => '',
  846.             'rootClose' => '',
  847.             'childOpen' => function ($node) use ($nodes, $separator) {
  848.                 if ($node['id'] === $nodes[0]['id']) {
  849.                     return '';
  850.                 }
  852.                 return $separator;
  853.             },
  854.             'childClose' => '',
  855.         ]);
  857.         return implode($separator, array_reverse(explode($separator, $locationFullPath)));
  858.     }
  860.     /**
  861.      * Get Path of Location.
  862.      *
  863.      * @param string $locale
  864.      *
  865.      * @return array
  866.      */
  867.     public function getLocationPathNodes(Location $location, $locale = null)
  868.     {
  869.         return $this->getPathQueryBuilder($location)
  870.             ->getQuery()
  871.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  872.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  873.             ->getArrayResult();
  874.     }
  876.     /**
  877.      * @param null $locale
  878.      */
  879.     public function getLocationPathNodesResult(Location $location, $locale = null)
  880.     {
  881.         return $this
  882.             ->getPathQueryBuilder($location)
  883.             ->getQuery()
  884.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  885.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  886.             ->getResult();
  887.     }
  889.     public function getSameLevelLocations(Location $location, ?int $limit = null, array $excluded = [])
  890.     {
  891.         $queryBuilder = $this->createQueryBuilder('lo');
  893.         $queryBuilder
  894.             ->where($queryBuilder->expr()->eq('lo.level', ':level'))
  895.             ->setParameter('level', $location->getLevel())
  896.             ->andWhere($queryBuilder->expr()->notIn('lo.id', ':excluded'))
  897.             ->setParameter('excluded', array_merge($excluded, [$location]));
  899.         if ($limit) {
  900.             $queryBuilder->setMaxResults($limit);
  901.         }
  903.         return $queryBuilder->getQuery()->getResult();
  904.     }
  906.     /**
  907.      * Find locations by slug indexed by id.
  908.      *
  909.      * @return array
  910.      */
  911.     public function getBySlugs(array $slugs = [])
  912.     {
  913.         if (empty($slugs)) {
  914.             return [];
  915.         }
  917.         $queryBuilder = $this->createQueryBuilder('location', 'location.id');
  919.         return $queryBuilder
  920.             ->andWhere($queryBuilder->expr()->in('location.slug', $slugs))
  921.             ->getQuery()
  922.             ->getResult();
  923.     }
  925.     /**
  926.      * @param string $locale
  927.      *
  928.      * @return string
  929.      */
  930.     public function getRootLocation(Location $location, $locale = 'en')
  931.     {
  932.         $queryBuilder = $this->getPathQueryBuilder($location);
  933.         $queryBuilder = $queryBuilder->setMaxResults(self::ONE_RESULT_NUMBER);
  935.         $rootLocationQuery = $queryBuilder->getQuery()->setHint(
  936.             Query::HINT_CUSTOM_OUTPUT_WALKER,
  937.             TranslationWalker::class
  938.         )->setHint(
  939.             TranslatableListener::HINT_TRANSLATABLE_LOCALE,
  940.             $locale
  941.         );
  943.         return $rootLocationQuery->getOneOrNullResult();
  944.     }
  946.     /**
  947.      * @param int|string $idOrSlug
  948.      *
  949.      * @throws NonUniqueResultException
  950.      */
  951.     public function findOneByIdOrSlug($idOrSlug)
  952.     {
  953.         $queryBuilder = $this->createQueryBuilder('l');
  955.         return $queryBuilder
  956.             ->andWhere($queryBuilder->expr()->eq('l.slug', ':slug'))
  957.             ->setParameter('slug', (string) $idOrSlug)
  958.             ->orWhere($queryBuilder->expr()->eq('l.id', ':id'))
  959.             ->setParameter('id', $idOrSlug)
  960.             ->getQuery()
  961.             ->getOneOrNullResult();
  962.     }
  964.     /**
  965.      * @param int $limit
  966.      *
  967.      * @return array
  968.      */
  969.     public function getMostSearched($limit = self::RECENT_SEARCHED_LOCATIONS_COUNT)
  970.     {
  971.         $queryBuilder = $this->createQueryBuilder('l');
  973.         return $queryBuilder
  974.             ->addSelect('l')
  975.             ->groupBy('l.id')
  976.             ->orderBy('l.listingsCounter', 'desc')
  977.             ->setMaxResults($limit)
  978.             ->getQuery()
  979.             ->getResult();
  980.     }
  982.     /**
  983.      * Get locations count by keyword.
  984.      *
  985.      * @return Query
  986.      */
  987.     public function getLocationCountByKeyword(?string $keyword = null)
  988.     {
  989.         return $this->createQueryBuilder('l')
  990.             ->select('COUNT(l.id)')
  991.             ->where('FIND_IN_SET(:keyword, l.keyword) > 0')
  992.             ->setParameter('keyword', $keyword)
  993.             ->getQuery()
  994.             ->setMaxResults(1);
  995.     }
  997.     /**
  998.      * Get locations title by ids.
  999.      *
  1000.      * @return Query
  1001.      */
  1002.     public function findTitleById(array $ids, string $locale = 'en')
  1003.     {
  1004.         $queryBuilder = $this->createQueryBuilder('l');
  1006.         return $queryBuilder->select('l.id, l.title')
  1007.             ->where($queryBuilder->expr()->in('l.id', ':ids'))
  1008.             ->setParameter('ids', $ids)
  1009.             ->getQuery()
  1010.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  1011.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  1012.     }
  1014.     public function getParentLocationByLevel(Location $location, int $level = 1, string $locale = 'en')
  1015.     {
  1016.         return $this
  1017.             ->getPathQueryBuilder($location)
  1018.             ->setFirstResult($level)
  1019.             ->setMaxResults(1)
  1020.             ->getQuery()
  1021.             ->setHint(
  1022.                 Query::HINT_CUSTOM_OUTPUT_WALKER,
  1023.                 TranslationWalker::class
  1024.             )
  1025.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  1026.             ->getArrayResult();
  1027.     }
  1029.     /**
  1030.      * @return Query
  1031.      */
  1032.     public function getParentsIds(Location $location)
  1033.     {
  1034.         $queryBuilder = $this->getParents($location);
  1035.         $queryBuilder->select('location.id');
  1037.         return $queryBuilder->getQuery();
  1038.     }
  1040.     /**
  1041.      * @return Query
  1042.      */
  1043.     public function getParentsBreadCrumbData(Location $location, string $locale = Locales::AR)
  1044.     {
  1045.         $queryBuilder = $this->getParents($location);
  1046.         $queryBuilder->select('location.id, location.title, location.slug');
  1048.         return $queryBuilder->getQuery()
  1049.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  1050.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  1051.         ;
  1052.     }
  1054.     /**
  1055.      * @return Location|null
  1056.      *
  1057.      * @throws NonUniqueResultException
  1058.      */
  1059.     public function getFirstRoot()
  1060.     {
  1061.         $queryBuilder = $this->createQueryBuilder('location');
  1063.         return $queryBuilder
  1064.             ->setMaxResults(1)
  1065.             ->getQuery()
  1066.             ->getOneOrNullResult();
  1067.     }
  1069.     /**
  1070.      * @return int|mixed|string
  1071.      */
  1072.     public function getPathNodeByLevel(Location $location, int $level = 1)
  1073.     {
  1074.         $queryBuilder = $this->getPathQueryBuilder($location);
  1076.         $queryBuilder->andWhere($queryBuilder->expr()->eq('node.level', ':level'))
  1077.             ->setParameter('level', $level);
  1079.         return $queryBuilder->getQuery()->getResult();
  1080.     }
  1082.     /**
  1083.      * @return array
  1084.      */
  1085.     public function getLevelOneLocations()
  1086.     {
  1087.         $queryBuilder = $this->getEntityManager()->createQueryBuilder();
  1089.         $queryBuilder->addSelect('l.id');
  1090.         $queryBuilder->addSelect('l.title');
  1091.         $queryBuilder->from('AqarmapListingBundle:Location', 'l');
  1093.         $queryBuilder->andWhere($queryBuilder->expr()->eq('l.level', ':level'))
  1094.             ->setParameter('level', 1);
  1096.         $queryBuilder->andWhere($queryBuilder->expr()->eq('l.disabled', ':disabled'))
  1097.             ->setParameter('disabled', 0);
  1099.         $query = $queryBuilder
  1100.             ->getQuery()
  1101.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  1102.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, 'en');
  1104.         return $query->getArrayResult();
  1105.     }
  1107.     /**
  1108.      * @return QueryBuilder
  1109.      */
  1110.     public function getLevelOneLocationsQuery()
  1111.     {
  1112.         $locationQueryBuilder = $this->getEntityManager()->createQueryBuilder();
  1114.         $locationQueryBuilder
  1115.             ->addSelect('locations.title AS locationTitle')
  1116.             ->from('AqarmapListingBundle:Location', 'locations');
  1118.         $locationQueryBuilder
  1119.             ->andWhere($locationQueryBuilder->expr()->eq('locations.disabled', ':disabled'))
  1120.             ->setParameter('disabled', false);
  1122.         $locationQueryBuilder
  1123.             ->andWhere($locationQueryBuilder->expr()->eq('locations.level', ':level'))
  1124.             ->setParameter('level', 1);
  1126.         return $locationQueryBuilder;
  1127.     }
  1129.     /**
  1130.      * @return array
  1131.      */
  1132.     public function getLocationChildrenDQL(array $aliases)
  1133.     {
  1134.         $leftAlias = $aliases['leftAlias'];
  1135.         $rightAlias = $aliases['rightAlias'];
  1136.         $rootAlias = $aliases['rootAlias'];
  1138.         $leftLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1139.             ->select("$leftAlias.left")
  1140.             ->from('AqarmapListingBundle:Location', "$leftAlias");
  1141.         $leftLocationDQL
  1142.             ->where($leftLocationDQL->expr()->eq("$leftAlias.id", 'locations.id'))
  1143.             ->getDQL();
  1145.         $rightLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1146.             ->select("$rightAlias.right")
  1147.             ->from('AqarmapListingBundle:Location', "$rightAlias");
  1148.         $rightLocationDQL
  1149.             ->where($rightLocationDQL->expr()->eq("$rightAlias.id", 'locations.id'))
  1150.             ->getDQL();
  1152.         $rootLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1153.             ->select("$rootAlias.root")
  1154.             ->from('AqarmapListingBundle:Location', "$rootAlias");
  1155.         $rootLocationDQL
  1156.             ->where($rootLocationDQL->expr()->eq("$rootAlias.id", 'locations.id'))
  1157.             ->getDQL();
  1159.         return [
  1160.             'leftLocationDQL' => $leftLocationDQL,
  1161.             'rightLocationDQL' => $rightLocationDQL,
  1162.             'rootLocationDQL' => $rootLocationDQL,
  1163.         ];
  1164.     }
  1166.     public function getIterableLocations(): IterableResult
  1167.     {
  1168.         $qb = $this->createQueryBuilder('location');
  1170.         return $qb->select('location')
  1171.             ->where($qb->expr()->isNotNull('location.parent'))
  1172.             ->getQuery()->iterate();
  1173.     }
  1175.     /**
  1176.      * @return mixed[]
  1177.      *
  1178.      * @throws \Doctrine\DBAL\DBALException
  1179.      */
  1180.     public function getLocationParentsAndChildrenIds(Location $location)
  1181.     {
  1182.         $statement = "
  1183.         SELECT
  1184.             CASE WHEN locations.root = {$location->getRoot()} AND
  1185.             locations._right > {$location->getRight()} AND
  1186.             locations._left < {$location->getLeft()}
  1187.             THEN locations.id
  1188.             ELSE NULL
  1189.             END AS parents,
  1190.             CASE WHEN locations.root = {$location->getRoot()} AND
  1191.             locations._right < {$location->getRight()} AND
  1192.             locations._left > {$location->getLeft()}
  1193.             THEN locations.id
  1194.             ELSE NULL
  1195.             END AS children
  1196.         FROM locations
  1197.             WHERE (locations.root = {$location->getRoot()} AND
  1198.             locations._right >= {$location->getRight()} AND
  1199.             locations._left <= {$location->getLeft()}) OR
  1200.             (locations.root = {$location->getRoot()} AND
  1201.             locations._right < {$location->getRight()} AND
  1202.             locations._left > {$location->getLeft()});";
  1204.         $statement = $this->getEntityManager()->getConnection()->prepare($statement);
  1206.         $statement->execute();
  1208.         return $statement->fetchAll();
  1209.     }
  1211.     /**
  1212.      * @return Query
  1213.      */
  1214.     public function getLocationsData(array $locations = [], array $selections = [], bool $translations = false)
  1215.     {
  1216.         $queryBuilder = $this->createQueryBuilder('location');
  1217.         $selectStatement = '';
  1219.         foreach ($selections as $index => $selection) {
  1220.             $selectStatement .= "location.{$selection}";
  1221.             if ($index !== \count($selections) - 1) {
  1222.                 $selectStatement .= ', ';
  1223.             }
  1224.         }
  1226.         if ($translations) {
  1227.             $queryBuilder
  1228.                 ->join('location.translations', 'translations');
  1229.             $queryBuilder
  1230.                 ->andWhere($queryBuilder->expr()->eq('translations.field', ':field'))
  1231.                 ->setParameter(':field', 'title')
  1232.                 ->andWhere($queryBuilder->expr()->eq('translations.locale', ':locale'))
  1233.                 ->setParameter(':locale', Locales::EN);
  1234.             $selectStatement .= ', translations.content, translations.locale';
  1235.         }
  1237.         $queryBuilder->select($selectStatement);
  1239.         if (!empty($locations)) {
  1240.             $queryBuilder->andWhere($queryBuilder->expr()->in('location.id', ':id'))
  1241.                 ->setParameter('id', $locations);
  1242.         }
  1244.         return $queryBuilder->getQuery();
  1245.     }
  1247.     /**
  1248.      * @param array $selections = []
  1249.      *
  1250.      * @return Query
  1251.      */
  1252.     public function getParent(int $locationId, array $selections = [], ?string $locale = null)
  1253.     {
  1254.         $queryBuilder = $this->createQueryBuilder('location');
  1255.         $selectStatement = '';
  1257.         foreach ($selections as $index => $selection) {
  1258.             $selectStatement .= "location.{$selection}";
  1259.             if ($index !== \count($selections) - 1) {
  1260.                 $selectStatement .= ', ';
  1261.             }
  1262.         }
  1264.         $queryBuilder->select($selectStatement)
  1265.             ->andWhere($queryBuilder->expr()->eq('location.parent', ':parent'))
  1266.             ->setParameter('id', $locationId);
  1268.         $query = $queryBuilder->getQuery();
  1270.         if ($locale) {
  1271.             $query->setHint(
  1272.                 Query::HINT_CUSTOM_OUTPUT_WALKER,
  1273.                 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker'
  1274.             );
  1276.             $query->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  1277.         }
  1279.         return $queryBuilder->getQuery();
  1280.     }
  1282.     public function getUserLiveLocationsWithParents(int $user)
  1283.     {
  1284.         $queryBuilder = $this->createQueryBuilder('location');
  1285.         $queryBuilder->select('location.id as id, IDENTITY(location.parent) as parent, location.level as level');
  1286.         $queryBuilder->join('location.listings', 'listings', Expr\Join::WITH, 'location.id=listings.location');
  1287.         $queryBuilder->andWhere($queryBuilder->expr()->eq('listings.user', ':user'))
  1288.             ->setParameter(':user', $user)
  1289.             ->andWhere($queryBuilder->expr()->eq('listings.status', ':status'))
  1290.             ->setParameter(':status', ListingStatus::LIVE);
  1292.         return $queryBuilder->getQuery()->getResult();
  1293.     }
  1295.     /**
  1296.      * @return array
  1297.      */
  1298.     public function getChildrenIds($right, $left, $root)
  1299.     {
  1300.         $queryBuilder = $this->createQueryBuilder('location');
  1301.         $queryBuilder->select('location.id');
  1303.         $queryBuilder->where($queryBuilder->expr()->lt('location.right', $right));
  1304.         $queryBuilder->andWhere($queryBuilder->expr()->gt('location.left', $left));
  1306.         $queryBuilder->andWhere($queryBuilder->expr()->eq('location.root', ':rid'));
  1307.         $queryBuilder->setParameter('rid', $root);
  1309.         return $queryBuilder->getQuery()->getResult();
  1310.     }
  1312.     /**
  1313.      * @return Query
  1314.      */
  1315.     public function getChildrenObjects(Location $location, $excludedLocations = null)
  1316.     {
  1317.         $queryBuilder = $this->createQueryBuilder('location');
  1319.         $queryBuilder->where($queryBuilder->expr()->lt('location.right', $location->getRight()));
  1320.         $queryBuilder->andWhere($queryBuilder->expr()->gt('location.left', $location->getLeft()));
  1322.         $queryBuilder->andWhere($queryBuilder->expr()->eq('location.root', ':rid'));
  1323.         $queryBuilder->setParameter('rid', $location->getRoot());
  1325.         $queryBuilder->andWhere($queryBuilder->expr()->neq('location', ':location'));
  1326.         $queryBuilder->setParameter('location', $location);
  1328.         $queryBuilder->andWhere($queryBuilder->expr()->notIn('location', $excludedLocations));
  1330.         return $queryBuilder->getQuery();
  1331.     }
  1333.     /**
  1334.      * @return QueryBuilder
  1335.      */
  1336.     public function getLocationsByLevel($criteria)
  1337.     {
  1338.         [
  1339.             'selectType' => $selectType,
  1340.             'lessThanOrEqualLevel' => $lessThanOrEqualLevel,
  1341.             'moreThanLevel' => $moreThanLevel,
  1342.         ] = array_merge([
  1343.             'selectType' => null,
  1344.             'lessThanOrEqualLevel' => null,
  1345.             'moreThanLevel' => null,
  1346.         ], $criteria);
  1348.         $queryBuilder = $this->createQueryBuilder('locations');
  1350.         if ('count' === $selectType) {
  1351.             $queryBuilder->select('COUNT(locations.id)');
  1352.         }
  1354.         if ($lessThanOrEqualLevel) {
  1355.             $queryBuilder
  1356.                 ->andWhere($queryBuilder->expr()->lte('locations.level', ':level'))
  1357.                 ->setParameter('level', $lessThanOrEqualLevel);
  1358.         } elseif ($moreThanLevel) {
  1359.             $queryBuilder
  1360.                 ->andWhere($queryBuilder->expr()->gt('locations.level', ':level'))
  1361.                 ->setParameter('level', $moreThanLevel);
  1362.         }
  1364.         return $queryBuilder;
  1365.     }
  1367.     /**
  1368.      * Get all parents ids for location.
  1369.      *
  1370.      * @param int|Location $location
  1371.      */
  1372.     public function getAllLocationParentsIds($location, bool $withRoot = true): array
  1373.     {
  1374.         $location = \is_int($location) ? $this->find($location) : $location;
  1376.         $queryBuilder = $this->getPathQueryBuilder($location);
  1377.         $queryBuilder->select('node.id');
  1379.         $queryBuilder->andWhere($queryBuilder->expr()->neq('node.id', ':location'));
  1380.         $queryBuilder->setParameter('location', $location->getId());
  1382.         if (false == $withRoot) {
  1383.             $queryBuilder->andWhere($queryBuilder->expr()->isNotNull('node.parent'));
  1384.         }
  1386.         return $queryBuilder->getQuery()->getResult();
  1387.     }
  1389.     /**
  1390.      * @return array
  1391.      */
  1392.     public function getMostLocationHasListingsForUser(User $user)
  1393.     {
  1394.         $queryBuilder = $this->createQueryBuilder('location');
  1395.         $queryBuilder->join('location.listings', 'listing')->addSelect('listing');
  1397.         $queryBuilder->andWhere($queryBuilder->expr()->eq('listing.status', ':status'));
  1398.         $queryBuilder->andWhere($queryBuilder->expr()->eq('listing.user', ':user'));
  1400.         $queryBuilder->setParameter('status', ListingStatus::LIVE);
  1401.         $queryBuilder->setParameter('user', $user);
  1403.         $queryBuilder->addSelect('COUNT(listing.id) AS listing_count');
  1404.         $queryBuilder->groupBy('location.id');
  1406.         $queryBuilder->orderBy('listing_count', 'DESC');
  1408.         return $queryBuilder->setMaxResults(self::ONE_RESULT_NUMBER)->getQuery()->getOneOrNullResult();
  1409.     }
  1411.     /**
  1412.      * @return array
  1413.      */
  1414.     public function getLocationTranslations($entity)
  1415.     {
  1416.         $queryBuilder = $this->createQueryBuilder('location');
  1417.         $queryBuilder->select('location.alias as aliasAr', 'location.title as titleAr', 'location.description as descriptionAr', 'translation.field', 'translation.content');
  1419.         $queryBuilder->join('location.translations', 'translation', Expr\Join::WITH, 'translation.object = location.id');
  1421.         $queryBuilder->andWhere($queryBuilder->expr()->eq('translation.locale', ':locale'))->setParameter('locale', 'en');
  1422.         $queryBuilder->andWhere($queryBuilder->expr()->eq('location.id', ':id'))->setParameter('id', $entity->getId());
  1424.         $results = $queryBuilder->getQuery()->getArrayResult();
  1426.         $data = [];
  1428.         foreach ($results as $result) {
  1429.             $data['titleAr'] = $result['titleAr'];
  1430.             $data['aliasAr'] = $result['aliasAr'];
  1431.             $data['descriptionAr'] = $result['descriptionAr'];
  1432.             if ('title' == $result['field']) {
  1433.                 $data['titleEn'] = $result['content'];
  1434.             }
  1435.             if ('alias' == $result['field']) {
  1436.                 $data['aliasEn'] = $result['content'];
  1437.             }
  1439.             if ('description' == $result['field']) {
  1440.                 $data['descriptionEn'] = $result['content'];
  1441.             }
  1442.         }
  1444.         $data['titleAr'] ??= '';
  1445.         $data['titleEn'] ??= '';
  1446.         $data['aliasAr'] ??= '';
  1447.         $data['aliasEn'] ??= '';
  1448.         $data['descriptionAr'] ??= '';
  1449.         $data['descriptionEn'] ??= '';
  1451.         return $data;
  1452.     }
  1454.     /**
  1455.      * @return array
  1456.      */
  1457.     public function getCompoundLocations(array $criteria)
  1458.     {
  1459.         $queryBuilder = $this->createQueryBuilder('location');
  1460.         $queryBuilder->select('location.id');
  1462.         if (isset($criteria['title'])) {
  1463.             $queryBuilder->andWhere($queryBuilder->expr()->like('location.title', ':title'))
  1464.                 ->setParameter('title', "%{$criteria['title']}%");
  1465.         }
  1467.         return $queryBuilder->getQuery()->getResult();
  1468.     }
  1470.     /**
  1471.      * @return void
  1472.      */
  1473.     public function updateIsCompoundLocation(array $locationIDs)
  1474.     {
  1475.         $queryBuilder = $this->createQueryBuilder('location');
  1477.         return $queryBuilder->update()
  1478.             ->set('location.isCompoundLocation', ':status')
  1479.             ->setParameter('status', 1)
  1480.             ->where($queryBuilder->expr()->in('location', ':locations'))
  1481.             ->setParameter('locations', $locationIDs)
  1482.             ->getQuery()
  1483.             ->execute();
  1484.     }
  1486.     public function getLocationsBySlugs(array $locationSlugs): array
  1487.     {
  1488.         $queryBuilder = $this->createQueryBuilder('l');
  1490.         $query = $queryBuilder->select('l')
  1491.             ->where($queryBuilder->expr()->in('l.slug', ':locationSlugs'))
  1492.             ->setParameter('locationSlugs', $locationSlugs)
  1493.             ->getQuery();
  1495.         return $query->getResult();
  1496.     }
  1498.     public function getHomeCompoundLocations(int $limit): array
  1499.     {
  1500.         $queryBuilder = $this->createQueryBuilder('location');
  1502.         $query = $queryBuilder
  1503.             ->where($queryBuilder->expr()->in('location.homeFilter', ':homeFilter'))
  1504.             ->setParameter('homeFilter', true, \PDO::PARAM_BOOL)
  1505.             ->andWhere($queryBuilder->expr()->in('location.isCompoundLocation', ':isCompoundLocation'))
  1506.             ->setParameter('isCompoundLocation', true, \PDO::PARAM_BOOL)
  1507.             ->andWhere($queryBuilder->expr()->isNotNull('location.listing'))
  1508.         ;
  1510.         $queryBuilder->setMaxResults($limit);
  1512.         return $query->getQuery()->enableResultCache(3600)->getResult();
  1513.     }
  1515.     /**
  1516.      * @throws NonUniqueResultException
  1517.      * @throws NoResultException
  1518.      */
  1519.     public function getListingsCount(Location $location): int
  1520.     {
  1521.         $queryBuilder = $this->createQueryBuilder('location');
  1522.         $children = $this->getLocationChildrenIds($location);
  1523.         $queryBuilder->select('COUNT(listing.id) as listingsCount')
  1524.             ->join('location.listings', 'listing')
  1525.             ->andWhere($queryBuilder->expr()->in('location.id', ':locationId'))
  1526.             ->andWhere($queryBuilder->expr()->eq('listing.status', ':status'))
  1527.             ->setParameters([
  1528.                 'locationId' => $children,
  1529.                 'status' => ListingStatus::LIVE,
  1530.             ]);
  1532.         return $queryBuilder->getQuery()->getSingleScalarResult();
  1533.     }
  1535.     /**
  1536.      * @return QueryBuilder
  1537.      */
  1538.     private function getParents(Location $location)
  1539.     {
  1540.         $queryBuilder = $this->createQueryBuilder('location');
  1542.         $queryBuilder->where($queryBuilder->expr()->lt('location.left', ':left'))
  1543.             ->andWhere($queryBuilder->expr()->gt('location.right', ':right'))
  1544.             ->andWhere($queryBuilder->expr()->eq('location.root', ':root'))
  1545.             ->setParameter('left', $location->getLeft())
  1546.             ->setParameter('right', $location->getRight())
  1547.             ->setParameter('root', $location->getRoot());
  1549.         return $queryBuilder;
  1550.     }
  1551. }