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

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(
  858.             array_reverse(
  859.                 explode($separator, $locationFullPath)
  860.             ),
  861.             $separator
  862.         );
  863.     }
  865.     /**
  866.      * Get Path of Location.
  867.      *
  868.      * @param string $locale
  869.      *
  870.      * @return array
  871.      */
  872.     public function getLocationPathNodes(Location $location, $locale = null)
  873.     {
  874.         return $this->getPathQueryBuilder($location)
  875.             ->getQuery()
  876.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  877.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  878.             ->getArrayResult();
  879.     }
  881.     /**
  882.      * @param null $locale
  883.      */
  884.     public function getLocationPathNodesResult(Location $location, $locale = null)
  885.     {
  886.         return $this
  887.             ->getPathQueryBuilder($location)
  888.             ->getQuery()
  889.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  890.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  891.             ->getResult();
  892.     }
  894.     public function getSameLevelLocations(Location $location, ?int $limit = null, array $excluded = [])
  895.     {
  896.         $queryBuilder = $this->createQueryBuilder('lo');
  898.         $queryBuilder
  899.             ->where($queryBuilder->expr()->eq('lo.level', ':level'))
  900.             ->setParameter('level', $location->getLevel())
  901.             ->andWhere($queryBuilder->expr()->notIn('lo.id', ':excluded'))
  902.             ->setParameter('excluded', array_merge($excluded, [$location]));
  904.         if ($limit) {
  905.             $queryBuilder->setMaxResults($limit);
  906.         }
  908.         return $queryBuilder->getQuery()->getResult();
  909.     }
  911.     /**
  912.      * Find locations by slug indexed by id.
  913.      *
  914.      * @return array
  915.      */
  916.     public function getBySlugs(array $slugs = [])
  917.     {
  918.         if (empty($slugs)) {
  919.             return [];
  920.         }
  922.         $queryBuilder = $this->createQueryBuilder('location', 'location.id');
  924.         return $queryBuilder
  925.             ->andWhere($queryBuilder->expr()->in('location.slug', $slugs))
  926.             ->getQuery()
  927.             ->getResult();
  928.     }
  930.     /**
  931.      * @param string $locale
  932.      *
  933.      * @return string
  934.      */
  935.     public function getRootLocation(Location $location, $locale = 'en')
  936.     {
  937.         $queryBuilder = $this->getPathQueryBuilder($location);
  938.         $queryBuilder = $queryBuilder->setMaxResults(self::ONE_RESULT_NUMBER);
  940.         $rootLocationQuery = $queryBuilder->getQuery()->setHint(
  941.             Query::HINT_CUSTOM_OUTPUT_WALKER,
  942.             TranslationWalker::class
  943.         )->setHint(
  944.             TranslatableListener::HINT_TRANSLATABLE_LOCALE,
  945.             $locale
  946.         );
  948.         return $rootLocationQuery->getOneOrNullResult();
  949.     }
  951.     /**
  952.      * @param int|string $idOrSlug
  953.      *
  954.      * @throws NonUniqueResultException
  955.      */
  956.     public function findOneByIdOrSlug($idOrSlug)
  957.     {
  958.         $queryBuilder = $this->createQueryBuilder('l');
  960.         return $queryBuilder
  961.             ->andWhere($queryBuilder->expr()->eq('l.slug', ':slug'))
  962.             ->setParameter('slug', (string) $idOrSlug)
  963.             ->orWhere($queryBuilder->expr()->eq('l.id', ':id'))
  964.             ->setParameter('id', $idOrSlug)
  965.             ->getQuery()
  966.             ->getOneOrNullResult();
  967.     }
  969.     /**
  970.      * @param int $limit
  971.      *
  972.      * @return array
  973.      */
  974.     public function getMostSearched($limit = self::RECENT_SEARCHED_LOCATIONS_COUNT)
  975.     {
  976.         $queryBuilder = $this->createQueryBuilder('l');
  978.         return $queryBuilder
  979.             ->addSelect('l')
  980.             ->groupBy('l.id')
  981.             ->orderBy('l.listingsCounter', 'desc')
  982.             ->setMaxResults($limit)
  983.             ->getQuery()
  984.             ->getResult();
  985.     }
  987.     /**
  988.      * Get locations count by keyword.
  989.      *
  990.      * @return Query
  991.      */
  992.     public function getLocationCountByKeyword(?string $keyword = null)
  993.     {
  994.         return $this->createQueryBuilder('l')
  995.             ->select('COUNT(l.id)')
  996.             ->where('FIND_IN_SET(:keyword, l.keyword) > 0')
  997.             ->setParameter('keyword', $keyword)
  998.             ->getQuery()
  999.             ->setMaxResults(1);
  1000.     }
  1002.     /**
  1003.      * Get locations title by ids.
  1004.      *
  1005.      * @return Query
  1006.      */
  1007.     public function findTitleById(array $ids, string $locale = 'en')
  1008.     {
  1009.         $queryBuilder = $this->createQueryBuilder('l');
  1011.         return $queryBuilder->select('l.id, l.title')
  1012.             ->where($queryBuilder->expr()->in('l.id', ':ids'))
  1013.             ->setParameter('ids', $ids)
  1014.             ->getQuery()
  1015.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  1016.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  1017.     }
  1019.     public function getParentLocationByLevel(Location $location, int $level = 1, string $locale = 'en')
  1020.     {
  1021.         return $this
  1022.             ->getPathQueryBuilder($location)
  1023.             ->setFirstResult($level)
  1024.             ->setMaxResults(1)
  1025.             ->getQuery()
  1026.             ->setHint(
  1027.                 Query::HINT_CUSTOM_OUTPUT_WALKER,
  1028.                 TranslationWalker::class
  1029.             )
  1030.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  1031.             ->getArrayResult();
  1032.     }
  1034.     /**
  1035.      * @return Query
  1036.      */
  1037.     public function getParentsIds(Location $location)
  1038.     {
  1039.         $queryBuilder = $this->createQueryBuilder('location');
  1040.         $queryBuilder->select('location.id');
  1042.         $queryBuilder->where($queryBuilder->expr()->lt('location.left', $location->getLeft()));
  1043.         $queryBuilder->andWhere($queryBuilder->expr()->gt('location.right', $location->getRight()));
  1045.         $queryBuilder->andWhere($queryBuilder->expr()->eq('location.root', ':rid'));
  1046.         $queryBuilder->setParameter('rid', $location->getRoot());
  1048.         return $queryBuilder->getQuery();
  1049.     }
  1051.     /**
  1052.      * @return Location|null
  1053.      *
  1054.      * @throws NonUniqueResultException
  1055.      */
  1056.     public function getFirstRoot()
  1057.     {
  1058.         $queryBuilder = $this->createQueryBuilder('location');
  1060.         return $queryBuilder
  1061.             ->setMaxResults(1)
  1062.             ->getQuery()
  1063.             ->getOneOrNullResult();
  1064.     }
  1066.     /**
  1067.      * @return int|mixed|string
  1068.      */
  1069.     public function getPathNodeByLevel(Location $location, int $level = 1)
  1070.     {
  1071.         $queryBuilder = $this->getPathQueryBuilder($location);
  1073.         $queryBuilder->andWhere($queryBuilder->expr()->eq('node.level', ':level'))
  1074.             ->setParameter('level', $level);
  1076.         return $queryBuilder->getQuery()->getResult();
  1077.     }
  1079.     /**
  1080.      * @return array
  1081.      */
  1082.     public function getLevelOneLocations()
  1083.     {
  1084.         $queryBuilder = $this->getEntityManager()->createQueryBuilder();
  1086.         $queryBuilder->addSelect('l.id');
  1087.         $queryBuilder->addSelect('l.title');
  1088.         $queryBuilder->from('AqarmapListingBundle:Location', 'l');
  1090.         $queryBuilder->andWhere($queryBuilder->expr()->eq('l.level', ':level'))
  1091.             ->setParameter('level', 1);
  1093.         $queryBuilder->andWhere($queryBuilder->expr()->eq('l.disabled', ':disabled'))
  1094.             ->setParameter('disabled', 0);
  1096.         $query = $queryBuilder
  1097.             ->getQuery()
  1098.             ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  1099.             ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, 'en');
  1101.         return $query->getArrayResult();
  1102.     }
  1104.     /**
  1105.      * @return QueryBuilder
  1106.      */
  1107.     public function getLevelOneLocationsQuery()
  1108.     {
  1109.         $locationQueryBuilder = $this->getEntityManager()->createQueryBuilder();
  1111.         $locationQueryBuilder
  1112.             ->addSelect('locations.title AS locationTitle')
  1113.             ->from('AqarmapListingBundle:Location', 'locations');
  1115.         $locationQueryBuilder
  1116.             ->andWhere($locationQueryBuilder->expr()->eq('locations.disabled', ':disabled'))
  1117.             ->setParameter('disabled', false);
  1119.         $locationQueryBuilder
  1120.             ->andWhere($locationQueryBuilder->expr()->eq('locations.level', ':level'))
  1121.             ->setParameter('level', 1);
  1123.         return $locationQueryBuilder;
  1124.     }
  1126.     /**
  1127.      * @return array
  1128.      */
  1129.     public function getLocationChildrenDQL(array $aliases)
  1130.     {
  1131.         $leftAlias = $aliases['leftAlias'];
  1132.         $rightAlias = $aliases['rightAlias'];
  1133.         $rootAlias = $aliases['rootAlias'];
  1135.         $leftLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1136.             ->select("$leftAlias.left")
  1137.             ->from('AqarmapListingBundle:Location', "$leftAlias");
  1138.         $leftLocationDQL
  1139.             ->where($leftLocationDQL->expr()->eq("$leftAlias.id", 'locations.id'))
  1140.             ->getDQL();
  1142.         $rightLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1143.             ->select("$rightAlias.right")
  1144.             ->from('AqarmapListingBundle:Location', "$rightAlias");
  1145.         $rightLocationDQL
  1146.             ->where($rightLocationDQL->expr()->eq("$rightAlias.id", 'locations.id'))
  1147.             ->getDQL();
  1149.         $rootLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1150.             ->select("$rootAlias.root")
  1151.             ->from('AqarmapListingBundle:Location', "$rootAlias");
  1152.         $rootLocationDQL
  1153.             ->where($rootLocationDQL->expr()->eq("$rootAlias.id", 'locations.id'))
  1154.             ->getDQL();
  1156.         return [
  1157.             'leftLocationDQL' => $leftLocationDQL,
  1158.             'rightLocationDQL' => $rightLocationDQL,
  1159.             'rootLocationDQL' => $rootLocationDQL,
  1160.         ];
  1161.     }
  1163.     public function getIterableLocations(): IterableResult
  1164.     {
  1165.         $qb = $this->createQueryBuilder('location');
  1167.         return $qb->select('location')
  1168.             ->where($qb->expr()->isNotNull('location.parent'))
  1169.             ->getQuery()->iterate();
  1170.     }
  1172.     /**
  1173.      * @return mixed[]
  1174.      *
  1175.      * @throws \Doctrine\DBAL\DBALException
  1176.      */
  1177.     public function getLocationParentsAndChildrenIds(Location $location)
  1178.     {
  1179.         $statement = "
  1180.         SELECT
  1181.             CASE WHEN locations.root = {$location->getRoot()} AND
  1182.             locations._right > {$location->getRight()} AND
  1183.             locations._left < {$location->getLeft()}
  1184.             THEN locations.id
  1185.             ELSE NULL
  1186.             END AS parents,
  1187.             CASE WHEN locations.root = {$location->getRoot()} AND
  1188.             locations._right < {$location->getRight()} AND
  1189.             locations._left > {$location->getLeft()}
  1190.             THEN locations.id
  1191.             ELSE NULL
  1192.             END AS children
  1193.         FROM locations
  1194.             WHERE (locations.root = {$location->getRoot()} AND
  1195.             locations._right >= {$location->getRight()} AND
  1196.             locations._left <= {$location->getLeft()}) OR
  1197.             (locations.root = {$location->getRoot()} AND
  1198.             locations._right < {$location->getRight()} AND
  1199.             locations._left > {$location->getLeft()});";
  1201.         $statement = $this->getEntityManager()->getConnection()->prepare($statement);
  1203.         $statement->execute();
  1205.         return $statement->fetchAll();
  1206.     }
  1208.     /**
  1209.      * @return Query
  1210.      */
  1211.     public function getLocationsData(array $locations = [], array $selections = [], bool $translations = false)
  1212.     {
  1213.         $queryBuilder = $this->createQueryBuilder('location');
  1214.         $selectStatement = '';
  1216.         foreach ($selections as $index => $selection) {
  1217.             $selectStatement .= "location.{$selection}";
  1218.             if ($index !== \count($selections) - 1) {
  1219.                 $selectStatement .= ', ';
  1220.             }
  1221.         }
  1223.         if ($translations) {
  1224.             $queryBuilder
  1225.                 ->join('location.translations', 'translations');
  1226.             $queryBuilder
  1227.                 ->andWhere($queryBuilder->expr()->eq('translations.field', ':field'))
  1228.                 ->setParameter(':field', 'title')
  1229.                 ->andWhere($queryBuilder->expr()->eq('translations.locale', ':locale'))
  1230.                 ->setParameter(':locale', Locales::EN);
  1231.             $selectStatement .= ', translations.content, translations.locale';
  1232.         }
  1234.         $queryBuilder->select($selectStatement);
  1236.         if (!empty($locations)) {
  1237.             $queryBuilder->andWhere($queryBuilder->expr()->in('location.id', ':id'))
  1238.                 ->setParameter('id', $locations);
  1239.         }
  1241.         return $queryBuilder->getQuery();
  1242.     }
  1244.     /**
  1245.      * @param array $selections = []
  1246.      *
  1247.      * @return Query
  1248.      */
  1249.     public function getParent(int $locationId, array $selections = [], ?string $locale = null)
  1250.     {
  1251.         $queryBuilder = $this->createQueryBuilder('location');
  1252.         $selectStatement = '';
  1254.         foreach ($selections as $index => $selection) {
  1255.             $selectStatement .= "location.{$selection}";
  1256.             if ($index !== \count($selections) - 1) {
  1257.                 $selectStatement .= ', ';
  1258.             }
  1259.         }
  1261.         $queryBuilder->select($selectStatement)
  1262.             ->andWhere($queryBuilder->expr()->eq('location.parent', ':parent'))
  1263.             ->setParameter('id', $locationId);
  1265.         $query = $queryBuilder->getQuery();
  1267.         if ($locale) {
  1268.             $query->setHint(
  1269.                 Query::HINT_CUSTOM_OUTPUT_WALKER,
  1270.                 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker'
  1271.             );
  1273.             $query->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  1274.         }
  1276.         return $queryBuilder->getQuery();
  1277.     }
  1279.     public function getUserLiveLocationsWithParents(int $user)
  1280.     {
  1281.         $queryBuilder = $this->createQueryBuilder('location');
  1282.         $queryBuilder->select('location.id as id, IDENTITY(location.parent) as parent, location.level as level');
  1283.         $queryBuilder->join('location.listings', 'listings', Expr\Join::WITH, 'location.id=listings.location');
  1284.         $queryBuilder->andWhere($queryBuilder->expr()->eq('listings.user', ':user'))
  1285.             ->setParameter(':user', $user)
  1286.             ->andWhere($queryBuilder->expr()->eq('listings.status', ':status'))
  1287.             ->setParameter(':status', ListingStatus::LIVE);
  1289.         return $queryBuilder->getQuery()->getResult();
  1290.     }
  1292.     /**
  1293.      * @return array
  1294.      */
  1295.     public function getChildrenIds($right, $left, $root)
  1296.     {
  1297.         $queryBuilder = $this->createQueryBuilder('location');
  1298.         $queryBuilder->select('location.id');
  1300.         $queryBuilder->where($queryBuilder->expr()->lt('location.right', $right));
  1301.         $queryBuilder->andWhere($queryBuilder->expr()->gt('location.left', $left));
  1303.         $queryBuilder->andWhere($queryBuilder->expr()->eq('location.root', ':rid'));
  1304.         $queryBuilder->setParameter('rid', $root);
  1306.         return $queryBuilder->getQuery()->getResult();
  1307.     }
  1309.     /**
  1310.      * @return Query
  1311.      */
  1312.     public function getChildrenObjects(Location $location, $excludedLocations = null)
  1313.     {
  1314.         $queryBuilder = $this->createQueryBuilder('location');
  1316.         $queryBuilder->where($queryBuilder->expr()->lt('location.right', $location->getRight()));
  1317.         $queryBuilder->andWhere($queryBuilder->expr()->gt('location.left', $location->getLeft()));
  1319.         $queryBuilder->andWhere($queryBuilder->expr()->eq('location.root', ':rid'));
  1320.         $queryBuilder->setParameter('rid', $location->getRoot());
  1322.         $queryBuilder->andWhere($queryBuilder->expr()->neq('location', ':location'));
  1323.         $queryBuilder->setParameter('location', $location);
  1325.         $queryBuilder->andWhere($queryBuilder->expr()->notIn('location', $excludedLocations));
  1327.         return $queryBuilder->getQuery();
  1328.     }
  1330.     /**
  1331.      * @return QueryBuilder
  1332.      */
  1333.     public function getLocationsByLevel($criteria)
  1334.     {
  1335.         [
  1336.             'selectType' => $selectType,
  1337.             'lessThanOrEqualLevel' => $lessThanOrEqualLevel,
  1338.             'moreThanLevel' => $moreThanLevel,
  1339.         ] = array_merge([
  1340.             'selectType' => null,
  1341.             'lessThanOrEqualLevel' => null,
  1342.             'moreThanLevel' => null,
  1343.         ], $criteria);
  1345.         $queryBuilder = $this->createQueryBuilder('locations');
  1347.         if ('count' === $selectType) {
  1348.             $queryBuilder->select('COUNT(locations.id)');
  1349.         }
  1351.         if ($lessThanOrEqualLevel) {
  1352.             $queryBuilder
  1353.                 ->andWhere($queryBuilder->expr()->lte('locations.level', ':level'))
  1354.                 ->setParameter('level', $lessThanOrEqualLevel);
  1355.         } elseif ($moreThanLevel) {
  1356.             $queryBuilder
  1357.                 ->andWhere($queryBuilder->expr()->gt('locations.level', ':level'))
  1358.                 ->setParameter('level', $moreThanLevel);
  1359.         }
  1361.         return $queryBuilder;
  1362.     }
  1364.     /**
  1365.      * Get all parents ids for location.
  1366.      *
  1367.      * @param int|Location $location
  1368.      */
  1369.     public function getAllLocationParentsIds($location, bool $withRoot = true): array
  1370.     {
  1371.         $location = \is_int($location) ? $this->find($location) : $location;
  1373.         $queryBuilder = $this->getPathQueryBuilder($location);
  1374.         $queryBuilder->select('node.id');
  1376.         $queryBuilder->andWhere($queryBuilder->expr()->neq('node.id', ':location'));
  1377.         $queryBuilder->setParameter('location', $location->getId());
  1379.         if (false == $withRoot) {
  1380.             $queryBuilder->andWhere($queryBuilder->expr()->isNotNull('node.parent'));
  1381.         }
  1383.         return $queryBuilder->getQuery()->getResult();
  1384.     }
  1386.     /**
  1387.      * @return array
  1388.      */
  1389.     public function getMostLocationHasListingsForUser(User $user)
  1390.     {
  1391.         $queryBuilder = $this->createQueryBuilder('location');
  1392.         $queryBuilder->join('location.listings', 'listing')->addSelect('listing');
  1394.         $queryBuilder->andWhere($queryBuilder->expr()->eq('listing.status', ':status'));
  1395.         $queryBuilder->andWhere($queryBuilder->expr()->eq('listing.user', ':user'));
  1397.         $queryBuilder->setParameter('status', ListingStatus::LIVE);
  1398.         $queryBuilder->setParameter('user', $user);
  1400.         $queryBuilder->addSelect('COUNT(listing.id) AS listing_count');
  1401.         $queryBuilder->groupBy('location.id');
  1403.         $queryBuilder->orderBy('listing_count', 'DESC');
  1405.         return $queryBuilder->setMaxResults(self::ONE_RESULT_NUMBER)->getQuery()->getOneOrNullResult();
  1406.     }
  1408.     /**
  1409.      * @return array
  1410.      */
  1411.     public function getLocationTranslations($entity)
  1412.     {
  1413.         $queryBuilder = $this->createQueryBuilder('location');
  1414.         $queryBuilder->select('location.alias as aliasAr', 'location.title as titleAr', 'location.description as descriptionAr', 'translation.field', 'translation.content');
  1416.         $queryBuilder->join('location.translations', 'translation', Expr\Join::WITH, 'translation.object = location.id');
  1418.         $queryBuilder->andWhere($queryBuilder->expr()->eq('translation.locale', ':locale'))->setParameter('locale', 'en');
  1419.         $queryBuilder->andWhere($queryBuilder->expr()->eq('location.id', ':id'))->setParameter('id', $entity->getId());
  1421.         $results = $queryBuilder->getQuery()->getArrayResult();
  1423.         $data = [];
  1425.         foreach ($results as $result) {
  1426.             $data['titleAr'] = $result['titleAr'];
  1427.             $data['aliasAr'] = $result['aliasAr'];
  1428.             $data['descriptionAr'] = $result['descriptionAr'];
  1429.             if ('title' == $result['field']) {
  1430.                 $data['titleEn'] = $result['content'];
  1431.             }
  1432.             if ('alias' == $result['field']) {
  1433.                 $data['aliasEn'] = $result['content'];
  1434.             }
  1436.             if ('description' == $result['field']) {
  1437.                 $data['descriptionEn'] = $result['content'];
  1438.             }
  1439.         }
  1441.         $data['titleAr'] ??= '';
  1442.         $data['titleEn'] ??= '';
  1443.         $data['aliasAr'] ??= '';
  1444.         $data['aliasEn'] ??= '';
  1445.         $data['descriptionAr'] ??= '';
  1446.         $data['descriptionEn'] ??= '';
  1448.         return $data;
  1449.     }
  1451.     /**
  1452.      * @return array
  1453.      */
  1454.     public function getCompoundLocations(array $criteria)
  1455.     {
  1456.         $queryBuilder = $this->createQueryBuilder('location');
  1457.         $queryBuilder->select('location.id');
  1459.         if (isset($criteria['title'])) {
  1460.             $queryBuilder->andWhere($queryBuilder->expr()->like('location.title', ':title'))
  1461.                 ->setParameter('title', "%{$criteria['title']}%");
  1462.         }
  1464.         return $queryBuilder->getQuery()->getResult();
  1465.     }
  1467.     /**
  1468.      * @return void
  1469.      */
  1470.     public function updateIsCompoundLocation(array $locationIDs)
  1471.     {
  1472.         $queryBuilder = $this->createQueryBuilder('location');
  1474.         return $queryBuilder->update()
  1475.             ->set('location.isCompoundLocation', ':status')
  1476.             ->setParameter('status', 1)
  1477.             ->where($queryBuilder->expr()->in('location', ':locations'))
  1478.             ->setParameter('locations', $locationIDs)
  1479.             ->getQuery()
  1480.             ->execute();
  1481.     }
  1483.     public function getLocationsBySlugs(array $locationSlugs): array
  1484.     {
  1485.         $queryBuilder = $this->createQueryBuilder('l');
  1487.         $query = $queryBuilder->select('l')
  1488.             ->where($queryBuilder->expr()->in('l.slug', ':locationSlugs'))
  1489.             ->setParameter('locationSlugs', $locationSlugs)
  1490.             ->getQuery();
  1492.         return $query->getResult();
  1493.     }
  1495.     public function getHomeCompoundLocations($limit): array
  1496.     {
  1497.         $queryBuilder = $this->createQueryBuilder('location');
  1499.         $query = $queryBuilder
  1500.             ->where($queryBuilder->expr()->in('location.homeFilter', ':homeFilter'))
  1501.             ->setParameter('homeFilter', true, \PDO::PARAM_BOOL)
  1502.             ->andWhere($queryBuilder->expr()->in('location.isCompoundLocation', ':isCompoundLocation'))
  1503.             ->setParameter('isCompoundLocation', true, \PDO::PARAM_BOOL)
  1504.             ->andWhere($queryBuilder->expr()->isNotNull('location.listing'))
  1505.         ;
  1507.         if ($limit) {
  1508.             $queryBuilder->setMaxResults($limit);
  1509.         }
  1511.         return $query->getQuery()->getResult();
  1512.     }
  1514.     /**
  1515.      * @throws NonUniqueResultException
  1516.      * @throws NoResultException
  1517.      */
  1518.     public function getListingsCount(Location $location): int
  1519.     {
  1520.         $queryBuilder = $this->createQueryBuilder('location');
  1521.         $children = $this->getLocationChildrenIds($location);
  1522.         $queryBuilder->select('COUNT(listing.id) as listingsCount')
  1523.             ->join('location.listings', 'listing')
  1524.             ->andWhere($queryBuilder->expr()->in('location.id', ':locationId'))
  1525.             ->andWhere($queryBuilder->expr()->eq('listing.status', ':status'))
  1526.             ->setParameters([
  1527.                 'locationId' => $children,
  1528.                 'status' => ListingStatus::LIVE,
  1529.             ]);
  1531.         return $queryBuilder->getQuery()->getSingleScalarResult();
  1532.     }
  1533. }