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

  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((string) $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, TranslationWalker::class);
  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, TranslationWalker::class);
  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, TranslationWalker::class);
  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, TranslationWalker::class);
  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, TranslationWalker::class);
  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. * @return Location|array
  489. */
  490. public function getNeighbourhoodCompounds($locations = null, $state = null)
  491. {
  492. $QueryBuilder = $this->createQueryBuilder('lo')
  493. ->join('lo.locationCompound', 'lc', Expr\Join::INNER_JOIN);
  495. $QueryBuilder
  496. ->andWhere($QueryBuilder->expr()->eq('lo.compoundFilter', ':is_true'))
  497. ->setParameter('is_true', true, \PDO::PARAM_BOOL);
  498. if ($locations) {
  499. $QueryBuilder
  500. ->andWhere($QueryBuilder->expr()->orX(
  501. $QueryBuilder->expr()->in('lo.parent', ':locations')
  502. ))
  503. ->setParameter('locations', $locations);
  504. }
  505. if ($state) {
  506. $QueryBuilder
  507. ->andWhere($QueryBuilder->expr()->eq('lc.state', ':state'))
  508. ->setParameter('state', $state);
  509. }
  510. $query = $QueryBuilder->getQuery();
  512. return $query->getResult();
  513. }
  515. /**
  516. * @return array
  517. */
  518. public function generateNearestLocations(Location $location, $distance)
  519. {
  520. $QueryBuilder = $this->createQueryBuilder('lo');
  521. $QueryBuilder
  522. ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  523. ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  524. $QueryBuilder
  525. ->andWhere($QueryBuilder->expr()->eq('lo.level', ':level'))
  526. ->setParameter('level', $location->getLevel());
  527. $QueryBuilder
  528. ->andWhere($QueryBuilder->expr()->neq('lo', ':location'))
  529. ->setParameter('location', $location);
  530. if ($location->getParent()) {
  531. $QueryBuilder
  532. ->andWhere($QueryBuilder->expr()->eq('lo.parent', ':parent'))
  533. ->setParameter('parent', $location->getParent());
  534. }
  535. $QueryBuilder = $this->getNearestLocationsFormulaQuery($QueryBuilder, $location, $distance);
  536. $query = $QueryBuilder->getQuery();
  538. return $query->getResult();
  539. }
  541. /**
  542. * @return Query
  543. */
  544. public function generateNearestLocationsQuery(array $criteria)
  545. {
  546. /** @var Location $location */
  547. $location = $criteria['location'];
  549. $listingQueryBuilder = $this->_em->createQueryBuilder();
  550. $listingQueryBuilder = $listingQueryBuilder
  551. ->select('MAX(li.id)')
  552. ->from(Listing::class, 'li')
  553. ->andWhere($listingQueryBuilder->expr()->eq('li.status', ListingStatus::LIVE))
  554. ->andWhere($listingQueryBuilder->expr()->neq('li.category', ListingCategories::PROJECTS));
  556. if (isset($criteria['propertyType']) && $criteria['propertyType']) {
  557. $listingQueryBuilder->andWhere($listingQueryBuilder->expr()->in('li.propertyType', $criteria['propertyType']));
  558. }
  560. if (isset($criteria['section']) && $criteria['section']) {
  561. $listingQueryBuilder->andWhere($listingQueryBuilder->expr()->eq('li.section', $criteria['section']));
  562. }
  564. $queryBuilder = $this->createQueryBuilder('lo');
  565. $queryBuilder->addSelect('ST_Distance_Sphere( Point(:longitude, :latitude), Point(lo.lon, lo.lat)) AS distance');
  566. $queryBuilder
  567. ->andWhere($queryBuilder->expr()->eq('lo.disabled', ':disabled'))
  568. ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  569. $queryBuilder->innerJoin(
  570. 'lo.listings',
  571. 'listings',
  572. Expr\Join::WITH,
  573. $queryBuilder->expr()->eq(
  574. 'listings.id',
  575. sprintf('(%s)', $listingQueryBuilder
  576. ->andWhere($listingQueryBuilder->expr()->eq('li.location', 'lo.id'))
  577. ->setMaxResults(1)->getQuery()->getDQL())
  578. )
  579. );
  581. $queryBuilder
  582. ->andWhere($queryBuilder->expr()->neq('lo', ':location'))
  583. ->setParameter('location', $location);
  585. if (isset($criteria['excludedSubLocations']) && $criteria['excludedSubLocations']) {
  586. $queryBuilder
  587. ->andWhere($queryBuilder->expr()->notIn('lo', ':subLocations'))
  588. ->setParameter('subLocations', $this->getLocationChildrenIds($location));
  589. }
  591. $queryBuilder->andWhere($queryBuilder->expr()->neq('lo.listingsCounter', 0));
  593. $queryBuilder->andWhere($queryBuilder->expr()->lt('ABS(lo.lat)', 90));
  594. $queryBuilder->andWhere($queryBuilder->expr()->lt('ABS(lo.lon)', 180));
  596. $queryBuilder->andWhere($queryBuilder->expr()->isNotNull('lo.lat'));
  597. $queryBuilder->andWhere($queryBuilder->expr()->isNotNull('lo.lon'));
  599. $queryBuilder
  600. ->having('distance <= :maxDistance')
  601. ->setParameter('maxDistance', $criteria['maxDistance'] * 1000)
  602. ->setParameter('latitude', $location->getCenterLat())
  603. ->setParameter('longitude', $location->getCenterLng());
  605. $queryBuilder->orderBy('distance');
  607. return $queryBuilder->getQuery();
  608. }
  610. public function getNearestLocationsFormulaQuery(QueryBuilder $queryBuilder, Location $location, $distance): QueryBuilder
  611. {
  612. return $queryBuilder
  613. ->having('( 3959 * acos(cos(radians(:latitude))'.
  614. '* cos( radians( lo.lat ) )'.
  615. '* cos( radians( lo.lon )'.
  616. '- radians(:longitude) )'.
  617. '+ sin( radians(:latitude) )'.
  618. '* sin( radians( lo.lat ) ) ) ) < :distance')
  619. ->setParameter('distance', $distance)
  620. ->setParameter('latitude', $location->getCenterLat())
  621. ->setParameter('longitude', $location->getCenterLng());
  622. }
  624. /**
  625. * Get Nearest Compounds.
  626. *
  627. * @return Location|array
  628. */
  629. public function getNearestLocations(array $locations = [], bool $limit = true, int $max = 3)
  630. {
  631. $QueryBuilder = $this->createQueryBuilder('lo')
  632. ->join('lo.nearestLocations', 'nearest_locations', Expr\Join::INNER_JOIN);
  634. $QueryBuilder
  635. ->andWhere($QueryBuilder->expr()->eq('lo.searchable', ':is_searchable'))
  636. ->setParameter('is_searchable', true, \PDO::PARAM_BOOL);
  637. $QueryBuilder
  638. ->andWhere($QueryBuilder->expr()->in('nearest_locations.id', ':locations'))
  639. ->setParameter('locations', $locations);
  641. if ($limit) {
  642. $QueryBuilder->setMaxResults($max);
  643. }
  645. return $QueryBuilder->getQuery()->enableResultCache()->getResult();
  646. }
  648. /**
  649. * @internal param null $locale
  650. *
  651. * @param string $locale
  652. *
  653. * @return array
  654. */
  655. public function getAllSearchableLocationsList($levelLimit = null, $locale = null)
  656. {
  657. $QueryBuilder = $this->createQueryBuilder('lo');
  659. $QueryBuilder
  660. ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  661. ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  663. if ($levelLimit) {
  664. $QueryBuilder
  665. ->andWhere($QueryBuilder->expr()->lt('lo.level', ':level'))
  666. ->setParameter('level', $levelLimit);
  667. }
  669. $query = $QueryBuilder
  670. ->getQuery()
  671. ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class);
  672. if ($locale) {
  673. $query->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  674. }
  676. return $query->getResult();
  677. }
  679. /**
  680. * @return array
  681. */
  682. public function getAllLocationsCount()
  683. {
  684. $QueryBuilder = $this->createQueryBuilder('lo')->select('count(lo.id)');
  686. $QueryBuilder
  687. ->andWhere($QueryBuilder->expr()->eq('lo.disabled', ':disabled'))
  688. ->setParameter('disabled', false, \PDO::PARAM_BOOL);
  690. return $QueryBuilder->getQuery()->getSingleScalarResult();
  691. }
  693. /**
  694. * Get Nearest Neighbourhoods or Compounds.
  695. *
  696. * @return Location|array
  697. */
  698. public function getNearestNeighbourhoods(Location $location)
  699. {
  700. $QueryBuilder = $this->createQueryBuilder('lo')
  701. ->join('lo.nearestLocations', 'nearest_locations', Expr\Join::INNER_JOIN);
  703. if ($location->getCompoundFilter()) {
  704. $QueryBuilder
  705. ->andWhere($QueryBuilder->expr()->eq('lo.compoundFilter', ':is_true'))
  706. ->setParameter('is_true', true, \PDO::PARAM_BOOL);
  707. }
  708. if ($location->getNeighborhoodFilter()) {
  709. $QueryBuilder
  710. ->andWhere($QueryBuilder->expr()->eq('lo.neighborhoodFilter', ':is_true'))
  711. ->setParameter('is_true', true, \PDO::PARAM_BOOL);
  712. }
  713. $QueryBuilder
  714. ->andWhere($QueryBuilder->expr()->eq('nearest_locations.id', ':location'))
  715. ->setParameter('location', $location);
  716. $QueryBuilder->setMaxResults(3);
  718. $query = $QueryBuilder->getQuery();
  720. return $query->getResult();
  721. }
  723. /**
  724. * @param bool $forceLocale
  725. *
  726. * @return Location
  727. */
  728. public function getLocationLike($location, $forceLocale = true)
  729. {
  730. $queryBuilder = $this->createQueryBuilder('p')
  731. ->where('p.title LIKE :location')
  732. ->setParameter('location', '%'.$location.'%')
  733. ->getQuery()
  734. ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class);
  736. if ($forceLocale) {
  737. $queryBuilder->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, 'en');
  738. }
  740. return $queryBuilder->setMaxResults(1)
  741. ->getOneOrNullResult();
  742. }
  744. /**
  745. * Get locations by keyword.
  746. *
  747. * @return Query
  748. */
  749. public function getLocationByKeyword(?string $keyword = null)
  750. {
  751. return $this->createQueryBuilder('l')
  752. ->select('l')
  753. ->where('FIND_IN_SET(:keyword, l.keyword) > 0')
  754. ->setParameter('keyword', $keyword)
  755. ->getQuery();
  756. }
  758. public function getWhereHasBumpableListings()
  759. {
  760. $queryBuilder = $this->createQueryBuilder('l');
  762. return $queryBuilder
  763. ->select('l, li, lf')
  764. ->join('l.listings', 'li', Expr\Join::WITH, 'l.id=li.location')
  765. ->join('li.listingFeatures', 'lf', Expr\Join::WITH, 'li.id=lf.listing')
  766. ->where($queryBuilder->expr()->eq('li.isBumped', ':isBumped'))
  767. ->setParameter(':isBumped', true, \PDO::PARAM_BOOL)
  768. ->andWhere($queryBuilder->expr()->isNull('lf.expiresAt'))
  769. ->andWhere('lf.bumpsCount < lf.bumpUpOccurrences')
  770. ->orWhere($queryBuilder->expr()->isNull('lf.bumpsCount'))
  771. ->andWhere($queryBuilder->expr()->lte('lf.nextBumpDate', ':nextBumpDate'))
  772. ->setParameter(':nextBumpDate', (new \DateTime())->setTime(0, 0, 0))
  773. ->orderBy('lf.nextBumpDate', 'DESC')
  774. ->addOrderBy('lf.id', 'DESC')
  775. ->addOrderBy('lf.listing', 'DESC')
  776. ->setMaxResults(100)
  777. ->getQuery()
  778. ->getResult();
  779. }
  781. /**
  782. * @param bool $status
  783. *
  784. * @throws \Doctrine\ORM\OptimisticLockException
  785. */
  786. public function changeStatus(Location $location, $status = true)
  787. {
  788. $queryBuilder = $this->createQueryBuilder('location');
  790. $locations = $this->getChildren($location);
  791. $locations[] = $location;
  793. $locationsIds = [];
  794. foreach ($locations as $childLocation) {
  795. $locationsIds[] = $childLocation->getId();
  796. }
  798. $disableStatus = $queryBuilder
  799. ->set('location.disabled', (int) $status)
  800. ->update()
  801. ->where($queryBuilder->expr()->in('location.id', $locationsIds))
  802. ->getQuery()
  803. ->execute();
  805. $em = $this->getEntityManager();
  806. foreach ($locations as $childLocation) {
  807. $childLocation->setDisabled((bool) $status);
  809. $em->getEventManager()->dispatchEvent(
  810. Events::postUpdate,
  811. new LifecycleEventArgs($childLocation, $em)
  812. );
  813. }
  814. $em->flush();
  816. return $disableStatus;
  817. }
  819. /**
  820. * Get Location full path.
  821. *
  822. * @param string $locale
  823. * @param string $separator
  824. *
  825. * @return string
  826. */
  827. public function getLocationFullPath(Location $location, $locale, $separator = ' / ')
  828. {
  829. $nodes = $this->getPathQueryBuilder($location)
  830. ->getQuery()
  831. ->setHint(
  832. Query::HINT_CUSTOM_OUTPUT_WALKER,
  833. TranslationWalker::class
  834. )->setHint(
  835. TranslatableListener::HINT_TRANSLATABLE_LOCALE,
  836. $locale
  837. )
  838. ->getArrayResult();
  840. $locationFullPath = $this->buildTree($nodes, [
  841. 'decorate' => true,
  842. 'rootOpen' => '',
  843. 'rootClose' => '',
  844. 'childOpen' => function($node) use ($nodes, $separator) {
  845. if ($node['id'] === $nodes[0]['id']) {
  846. return '';
  847. }
  849. return $separator;
  850. },
  851. 'childClose' => '',
  852. ]);
  854. return implode($separator, array_reverse(explode($separator, $locationFullPath)));
  855. }
  857. /**
  858. * Get Path of Location.
  859. *
  860. * @param string $locale
  861. *
  862. * @return array
  863. */
  864. public function getLocationPathNodes(Location $location, $locale = null)
  865. {
  866. return $this->getPathQueryBuilder($location)
  867. ->getQuery()
  868. ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  869. ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  870. ->getArrayResult();
  871. }
  873. public function getLocationPathNodesResult(Location $location, $locale = null)
  874. {
  875. return $this
  876. ->getPathQueryBuilder($location)
  877. ->getQuery()
  878. ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  879. ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  880. ->getResult();
  881. }
  883. public function getSameLevelLocations(Location $location, ?int $limit = null, array $excluded = [])
  884. {
  885. $queryBuilder = $this->createQueryBuilder('lo');
  887. $queryBuilder
  888. ->where($queryBuilder->expr()->eq('lo.level', ':level'))
  889. ->setParameter('level', $location->getLevel())
  890. ->andWhere($queryBuilder->expr()->notIn('lo.id', ':excluded'))
  891. ->setParameter('excluded', array_merge($excluded, [$location]));
  893. if ($limit) {
  894. $queryBuilder->setMaxResults($limit);
  895. }
  897. return $queryBuilder->getQuery()->getResult();
  898. }
  900. /**
  901. * Find locations by slug indexed by id.
  902. *
  903. * @return array
  904. */
  905. public function getBySlugs(array $slugs = [])
  906. {
  907. if (empty($slugs)) {
  908. return [];
  909. }
  911. $queryBuilder = $this->createQueryBuilder('location', 'location.id');
  913. return $queryBuilder
  914. ->andWhere($queryBuilder->expr()->in('location.slug', $slugs))
  915. ->getQuery()
  916. ->getResult();
  917. }
  919. /**
  920. * @param string $locale
  921. *
  922. * @return string
  923. */
  924. public function getRootLocation(Location $location, $locale = 'en')
  925. {
  926. $queryBuilder = $this->getPathQueryBuilder($location);
  927. $queryBuilder = $queryBuilder->setMaxResults(self::ONE_RESULT_NUMBER);
  929. $rootLocationQuery = $queryBuilder->getQuery()->setHint(
  930. Query::HINT_CUSTOM_OUTPUT_WALKER,
  931. TranslationWalker::class
  932. )->setHint(
  933. TranslatableListener::HINT_TRANSLATABLE_LOCALE,
  934. $locale
  935. );
  937. return $rootLocationQuery->getOneOrNullResult();
  938. }
  940. /**
  941. * @param int|string $idOrSlug
  942. *
  943. * @throws NonUniqueResultException
  944. */
  945. public function findOneByIdOrSlug($idOrSlug)
  946. {
  947. $queryBuilder = $this->createQueryBuilder('l');
  949. return $queryBuilder
  950. ->andWhere($queryBuilder->expr()->eq('l.slug', ':slug'))
  951. ->setParameter('slug', (string) $idOrSlug)
  952. ->orWhere($queryBuilder->expr()->eq('l.id', ':id'))
  953. ->setParameter('id', $idOrSlug)
  954. ->getQuery()
  955. ->getOneOrNullResult();
  956. }
  958. /**
  959. * @param int $limit
  960. *
  961. * @return array
  962. */
  963. public function getMostSearched($limit = self::RECENT_SEARCHED_LOCATIONS_COUNT)
  964. {
  965. $queryBuilder = $this->createQueryBuilder('l');
  967. return $queryBuilder
  968. ->addSelect('l')
  969. ->groupBy('l.id')
  970. ->orderBy('l.listingsCounter', 'desc')
  971. ->setMaxResults($limit)
  972. ->getQuery()
  973. ->getResult();
  974. }
  976. /**
  977. * Get locations count by keyword.
  978. *
  979. * @return Query
  980. */
  981. public function getLocationCountByKeyword(?string $keyword = null)
  982. {
  983. return $this->createQueryBuilder('l')
  984. ->select('COUNT(l.id)')
  985. ->where('FIND_IN_SET(:keyword, l.keyword) > 0')
  986. ->setParameter('keyword', $keyword)
  987. ->getQuery()
  988. ->setMaxResults(1);
  989. }
  991. /**
  992. * Get locations title by ids.
  993. *
  994. * @return Query
  995. */
  996. public function findTitleById(array $ids, string $locale = 'en')
  997. {
  998. $queryBuilder = $this->createQueryBuilder('l');
  1000. return $queryBuilder->select('l.id, l.title')
  1001. ->where($queryBuilder->expr()->in('l.id', ':ids'))
  1002. ->setParameter('ids', $ids)
  1003. ->getQuery()
  1004. ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  1005. ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  1006. }
  1008. public function getParentLocationByLevel(Location $location, int $level = 1, string $locale = 'en')
  1009. {
  1010. return $this
  1011. ->getPathQueryBuilder($location)
  1012. ->setFirstResult($level)
  1013. ->setMaxResults(1)
  1014. ->getQuery()
  1015. ->setHint(
  1016. Query::HINT_CUSTOM_OUTPUT_WALKER,
  1017. TranslationWalker::class
  1018. )
  1019. ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  1020. ->getArrayResult();
  1021. }
  1023. /**
  1024. * @return Query
  1025. */
  1026. public function getParentsIds(Location $location)
  1027. {
  1028. $queryBuilder = $this->getParents($location);
  1029. $queryBuilder->select('location.id');
  1031. return $queryBuilder->getQuery();
  1032. }
  1034. /**
  1035. * @return Query
  1036. */
  1037. public function getParentsBreadCrumbData(Location $location, string $locale = Locales::AR)
  1038. {
  1039. $queryBuilder = $this->getParents($location);
  1040. $queryBuilder->select('location.id, location.title, location.slug');
  1042. return $queryBuilder->getQuery()
  1043. ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  1044. ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale)
  1045. ;
  1046. }
  1048. /**
  1049. * @return Location|null
  1050. *
  1051. * @throws NonUniqueResultException
  1052. */
  1053. public function getFirstRoot()
  1054. {
  1055. $queryBuilder = $this->createQueryBuilder('location');
  1057. return $queryBuilder
  1058. ->setMaxResults(1)
  1059. ->getQuery()
  1060. ->getOneOrNullResult();
  1061. }
  1063. /**
  1064. * @return int|mixed|string
  1065. */
  1066. public function getPathNodeByLevel(Location $location, int $level = 1)
  1067. {
  1068. $queryBuilder = $this->getPathQueryBuilder($location);
  1070. $queryBuilder->andWhere($queryBuilder->expr()->eq('node.level', ':level'))
  1071. ->setParameter('level', $level);
  1073. return $queryBuilder->getQuery()->getResult();
  1074. }
  1076. /**
  1077. * @return array
  1078. */
  1079. public function getLevelOneLocations()
  1080. {
  1081. $queryBuilder = $this->getEntityManager()->createQueryBuilder();
  1083. $queryBuilder->addSelect('l.id');
  1084. $queryBuilder->addSelect('l.title');
  1085. $queryBuilder->from(Location::class, 'l');
  1087. $queryBuilder->andWhere($queryBuilder->expr()->eq('l.level', ':level'))
  1088. ->setParameter('level', 1);
  1090. $queryBuilder->andWhere($queryBuilder->expr()->eq('l.disabled', ':disabled'))
  1091. ->setParameter('disabled', 0);
  1093. $query = $queryBuilder
  1094. ->getQuery()
  1095. ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, TranslationWalker::class)
  1096. ->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, 'en');
  1098. return $query->getArrayResult();
  1099. }
  1101. /**
  1102. * @return QueryBuilder
  1103. */
  1104. public function getLevelOneLocationsQuery()
  1105. {
  1106. $locationQueryBuilder = $this->getEntityManager()->createQueryBuilder();
  1108. $locationQueryBuilder
  1109. ->addSelect('locations.title AS locationTitle')
  1110. ->from(Location::class, 'locations');
  1112. $locationQueryBuilder
  1113. ->andWhere($locationQueryBuilder->expr()->eq('locations.disabled', ':disabled'))
  1114. ->setParameter('disabled', false);
  1116. $locationQueryBuilder
  1117. ->andWhere($locationQueryBuilder->expr()->eq('locations.level', ':level'))
  1118. ->setParameter('level', 1);
  1120. return $locationQueryBuilder;
  1121. }
  1123. /**
  1124. * @return array
  1125. */
  1126. public function getLocationChildrenDQL(array $aliases)
  1127. {
  1128. $leftAlias = $aliases['leftAlias'];
  1129. $rightAlias = $aliases['rightAlias'];
  1130. $rootAlias = $aliases['rootAlias'];
  1132. $leftLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1133. ->select("$leftAlias.left")
  1134. ->from(Location::class, "$leftAlias");
  1135. $leftLocationDQL
  1136. ->where($leftLocationDQL->expr()->eq("$leftAlias.id", 'locations.id'))
  1137. ->getDQL();
  1139. $rightLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1140. ->select("$rightAlias.right")
  1141. ->from(Location::class, "$rightAlias");
  1142. $rightLocationDQL
  1143. ->where($rightLocationDQL->expr()->eq("$rightAlias.id", 'locations.id'))
  1144. ->getDQL();
  1146. $rootLocationDQL = $this->getEntityManager()->createQueryBuilder()
  1147. ->select("$rootAlias.root")
  1148. ->from(Location::class, "$rootAlias");
  1149. $rootLocationDQL
  1150. ->where($rootLocationDQL->expr()->eq("$rootAlias.id", 'locations.id'))
  1151. ->getDQL();
  1153. return [
  1154. 'leftLocationDQL' => $leftLocationDQL,
  1155. 'rightLocationDQL' => $rightLocationDQL,
  1156. 'rootLocationDQL' => $rootLocationDQL,
  1157. ];
  1158. }
  1160. public function getIterableLocations(): IterableResult
  1161. {
  1162. $qb = $this->createQueryBuilder('location');
  1164. return $qb->select('location')
  1165. ->where($qb->expr()->isNotNull('location.parent'))
  1166. ->getQuery()->iterate();
  1167. }
  1169. /**
  1170. * @return mixed[]
  1171. *
  1172. * @throws \Doctrine\DBAL\DBALException
  1173. */
  1174. public function getLocationParentsAndChildrenIds(Location $location)
  1175. {
  1176. $statement = "
  1177. SELECT
  1178. CASE WHEN locations.root = {$location->getRoot()} AND
  1179. locations._right > {$location->getRight()} AND
  1180. locations._left < {$location->getLeft()}
  1181. THEN locations.id
  1182. ELSE NULL
  1183. END AS parents,
  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 children
  1190. FROM locations
  1191. WHERE (locations.root = {$location->getRoot()} AND
  1192. locations._right >= {$location->getRight()} AND
  1193. locations._left <= {$location->getLeft()}) OR
  1194. (locations.root = {$location->getRoot()} AND
  1195. locations._right < {$location->getRight()} AND
  1196. locations._left > {$location->getLeft()});";
  1198. $statement = $this->getEntityManager()->getConnection()->prepare($statement);
  1200. $statement->execute();
  1202. return $statement->fetchAll();
  1203. }
  1205. /**
  1206. * @return Query
  1207. */
  1208. public function getLocationsData(array $locations = [], array $selections = [], bool $translations = false)
  1209. {
  1210. $queryBuilder = $this->createQueryBuilder('location');
  1211. $selectStatement = '';
  1213. foreach ($selections as $index => $selection) {
  1214. $selectStatement .= "location.{$selection}";
  1215. if ($index !== \count($selections) - 1) {
  1216. $selectStatement .= ', ';
  1217. }
  1218. }
  1220. if ($translations) {
  1221. $queryBuilder
  1222. ->join('location.translations', 'translations');
  1223. $queryBuilder
  1224. ->andWhere($queryBuilder->expr()->eq('translations.field', ':field'))
  1225. ->setParameter(':field', 'title')
  1226. ->andWhere($queryBuilder->expr()->eq('translations.locale', ':locale'))
  1227. ->setParameter(':locale', Locales::EN);
  1228. $selectStatement .= ', translations.content, translations.locale';
  1229. }
  1231. $queryBuilder->select($selectStatement);
  1233. if (!empty($locations)) {
  1234. $queryBuilder->andWhere($queryBuilder->expr()->in('location.id', ':id'))
  1235. ->setParameter('id', $locations);
  1236. }
  1238. return $queryBuilder->getQuery();
  1239. }
  1241. /**
  1242. * @param array $selections = []
  1243. *
  1244. * @return Query
  1245. */
  1246. public function getParent(int $locationId, array $selections = [], ?string $locale = null)
  1247. {
  1248. $queryBuilder = $this->createQueryBuilder('location');
  1249. $selectStatement = '';
  1251. foreach ($selections as $index => $selection) {
  1252. $selectStatement .= "location.{$selection}";
  1253. if ($index !== \count($selections) - 1) {
  1254. $selectStatement .= ', ';
  1255. }
  1256. }
  1258. $queryBuilder->select($selectStatement)
  1259. ->andWhere($queryBuilder->expr()->eq('location.parent', ':parent'))
  1260. ->setParameter('id', $locationId);
  1262. $query = $queryBuilder->getQuery();
  1264. if ($locale) {
  1265. $query->setHint(
  1266. Query::HINT_CUSTOM_OUTPUT_WALKER,
  1267. TranslationWalker::class
  1268. );
  1270. $query->setHint(TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
  1271. }
  1273. return $queryBuilder->getQuery();
  1274. }
  1276. public function getUserLiveLocationsWithParents(int $user)
  1277. {
  1278. $queryBuilder = $this->createQueryBuilder('location');
  1279. $queryBuilder->select('location.id as id, IDENTITY(location.parent) as parent, location.level as level');
  1280. $queryBuilder->join('location.listings', 'listings', Expr\Join::WITH, 'location.id=listings.location');
  1281. $queryBuilder->andWhere($queryBuilder->expr()->eq('listings.user', ':user'))
  1282. ->setParameter(':user', $user)
  1283. ->andWhere($queryBuilder->expr()->eq('listings.status', ':status'))
  1284. ->setParameter(':status', ListingStatus::LIVE);
  1286. return $queryBuilder->getQuery()->getResult();
  1287. }
  1289. /**
  1290. * @return array
  1291. */
  1292. public function getChildrenIds($right, $left, $root)
  1293. {
  1294. $queryBuilder = $this->createQueryBuilder('location');
  1295. $queryBuilder->select('location.id');
  1297. $queryBuilder->where($queryBuilder->expr()->lt('location.right', $right));
  1298. $queryBuilder->andWhere($queryBuilder->expr()->gt('location.left', $left));
  1300. $queryBuilder->andWhere($queryBuilder->expr()->eq('location.root', ':rid'));
  1301. $queryBuilder->setParameter('rid', $root);
  1303. return $queryBuilder->getQuery()->getResult();
  1304. }
  1306. /**
  1307. * @return Query
  1308. */
  1309. public function getChildrenObjects(Location $location, $excludedLocations = null)
  1310. {
  1311. $queryBuilder = $this->createQueryBuilder('location');
  1313. $queryBuilder->where($queryBuilder->expr()->lt('location.right', $location->getRight()));
  1314. $queryBuilder->andWhere($queryBuilder->expr()->gt('location.left', $location->getLeft()));
  1316. $queryBuilder->andWhere($queryBuilder->expr()->eq('location.root', ':rid'));
  1317. $queryBuilder->setParameter('rid', $location->getRoot());
  1319. $queryBuilder->andWhere($queryBuilder->expr()->neq('location', ':location'));
  1320. $queryBuilder->setParameter('location', $location);
  1322. $queryBuilder->andWhere($queryBuilder->expr()->notIn('location', $excludedLocations));
  1324. return $queryBuilder->getQuery();
  1325. }
  1327. /**
  1328. * @return QueryBuilder
  1329. */
  1330. public function getLocationsByLevel($criteria)
  1331. {
  1332. [
  1333. 'selectType' => $selectType,
  1334. 'lessThanOrEqualLevel' => $lessThanOrEqualLevel,
  1335. 'moreThanLevel' => $moreThanLevel,
  1336. ] = array_merge([
  1337. 'selectType' => null,
  1338. 'lessThanOrEqualLevel' => null,
  1339. 'moreThanLevel' => null,
  1340. ], $criteria);
  1342. $queryBuilder = $this->createQueryBuilder('locations');
  1344. if ('count' === $selectType) {
  1345. $queryBuilder->select('COUNT(locations.id)');
  1346. }
  1348. if ($lessThanOrEqualLevel) {
  1349. $queryBuilder
  1350. ->andWhere($queryBuilder->expr()->lte('locations.level', ':level'))
  1351. ->setParameter('level', $lessThanOrEqualLevel);
  1352. } elseif ($moreThanLevel) {
  1353. $queryBuilder
  1354. ->andWhere($queryBuilder->expr()->gt('locations.level', ':level'))
  1355. ->setParameter('level', $moreThanLevel);
  1356. }
  1358. return $queryBuilder;
  1359. }
  1361. /**
  1362. * Get all parents ids for location.
  1363. *
  1364. * @param int|Location $location
  1365. */
  1366. public function getAllLocationParentsIds($location, bool $withRoot = true): array
  1367. {
  1368. $location = \is_int($location) ? $this->find($location) : $location;
  1370. $queryBuilder = $this->getPathQueryBuilder($location);
  1371. $queryBuilder->select('node.id');
  1373. $queryBuilder->andWhere($queryBuilder->expr()->neq('node.id', ':location'));
  1374. $queryBuilder->setParameter('location', $location->getId());
  1376. if (false == $withRoot) {
  1377. $queryBuilder->andWhere($queryBuilder->expr()->isNotNull('node.parent'));
  1378. }
  1380. return $queryBuilder->getQuery()->getResult();
  1381. }
  1383. /**
  1384. * @return array
  1385. */
  1386. public function getMostLocationHasListingsForUser(User $user)
  1387. {
  1388. $queryBuilder = $this->createQueryBuilder('location');
  1389. $queryBuilder->join('location.listings', 'listing')->addSelect('listing');
  1391. $queryBuilder->andWhere($queryBuilder->expr()->eq('listing.status', ':status'));
  1392. $queryBuilder->andWhere($queryBuilder->expr()->eq('listing.user', ':user'));
  1394. $queryBuilder->setParameter('status', ListingStatus::LIVE);
  1395. $queryBuilder->setParameter('user', $user);
  1397. $queryBuilder->addSelect('COUNT(listing.id) AS listing_count');
  1398. $queryBuilder->groupBy('location.id');
  1400. $queryBuilder->orderBy('listing_count', 'DESC');
  1402. return $queryBuilder->setMaxResults(self::ONE_RESULT_NUMBER)->getQuery()->getOneOrNullResult();
  1403. }
  1405. /**
  1406. * @return array
  1407. */
  1408. public function getLocationTranslations($entity)
  1409. {
  1410. $queryBuilder = $this->createQueryBuilder('location');
  1411. $queryBuilder->select('location.alias as aliasAr', 'location.title as titleAr', 'location.description as descriptionAr', 'translation.field', 'translation.content');
  1413. $queryBuilder->join('location.translations', 'translation', Expr\Join::WITH, 'translation.object = location.id');
  1415. $queryBuilder->andWhere($queryBuilder->expr()->eq('translation.locale', ':locale'))->setParameter('locale', 'en');
  1416. $queryBuilder->andWhere($queryBuilder->expr()->eq('location.id', ':id'))->setParameter('id', $entity->getId());
  1418. $results = $queryBuilder->getQuery()->getArrayResult();
  1420. $data = [];
  1422. foreach ($results as $result) {
  1423. $data['titleAr'] = $result['titleAr'];
  1424. $data['aliasAr'] = $result['aliasAr'];
  1425. $data['descriptionAr'] = $result['descriptionAr'];
  1426. if ('title' == $result['field']) {
  1427. $data['titleEn'] = $result['content'];
  1428. }
  1429. if ('alias' == $result['field']) {
  1430. $data['aliasEn'] = $result['content'];
  1431. }
  1433. if ('description' == $result['field']) {
  1434. $data['descriptionEn'] = $result['content'];
  1435. }
  1436. }
  1438. $data['titleAr'] ??= '';
  1439. $data['titleEn'] ??= '';
  1440. $data['aliasAr'] ??= '';
  1441. $data['aliasEn'] ??= '';
  1442. $data['descriptionAr'] ??= '';
  1443. $data['descriptionEn'] ??= '';
  1445. return $data;
  1446. }
  1448. /**
  1449. * @return array
  1450. */
  1451. public function getCompoundLocations(array $criteria)
  1452. {
  1453. $queryBuilder = $this->createQueryBuilder('location');
  1454. $queryBuilder->select('location.id');
  1456. if (isset($criteria['title'])) {
  1457. $queryBuilder->andWhere($queryBuilder->expr()->like('location.title', ':title'))
  1458. ->setParameter('title', "%{$criteria['title']}%");
  1459. }
  1461. return $queryBuilder->getQuery()->getResult();
  1462. }
  1464. /**
  1465. * @return void
  1466. */
  1467. public function updateIsCompoundLocation(array $locationIDs)
  1468. {
  1469. $queryBuilder = $this->createQueryBuilder('location');
  1471. return $queryBuilder->update()
  1472. ->set('location.isCompoundLocation', ':status')
  1473. ->setParameter('status', 1)
  1474. ->where($queryBuilder->expr()->in('location', ':locations'))
  1475. ->setParameter('locations', $locationIDs)
  1476. ->getQuery()
  1477. ->execute();
  1478. }
  1480. public function getLocationsBySlugs(array $locationSlugs): array
  1481. {
  1482. $queryBuilder = $this->createQueryBuilder('l');
  1484. $query = $queryBuilder->select('l')
  1485. ->where($queryBuilder->expr()->in('l.slug', ':locationSlugs'))
  1486. ->setParameter('locationSlugs', $locationSlugs)
  1487. ->getQuery();
  1489. return $query->getResult();
  1490. }
  1492. public function getHomeCompoundLocations(int $limit): array
  1493. {
  1494. $queryBuilder = $this->createQueryBuilder('location');
  1496. $query = $queryBuilder
  1497. ->where($queryBuilder->expr()->in('location.homeFilter', ':homeFilter'))
  1498. ->setParameter('homeFilter', true, \PDO::PARAM_BOOL)
  1499. ->andWhere($queryBuilder->expr()->in('location.isCompoundLocation', ':isCompoundLocation'))
  1500. ->setParameter('isCompoundLocation', true, \PDO::PARAM_BOOL)
  1501. ->andWhere($queryBuilder->expr()->isNotNull('location.listing'))
  1502. ;
  1504. $queryBuilder->setMaxResults($limit);
  1506. return $query->getQuery()->enableResultCache(3600)->getResult();
  1507. }
  1509. /**
  1510. * @throws NonUniqueResultException
  1511. * @throws NoResultException
  1512. */
  1513. public function getListingsCount(Location $location): int
  1514. {
  1515. $queryBuilder = $this->createQueryBuilder('location');
  1516. $children = $this->getLocationChildrenIds($location);
  1517. $queryBuilder->select('COUNT(listing.id) as listingsCount')
  1518. ->join('location.listings', 'listing')
  1519. ->andWhere($queryBuilder->expr()->in('location.id', ':locationId'))
  1520. ->andWhere($queryBuilder->expr()->eq('listing.status', ':status'))
  1521. ->setParameters([
  1522. 'locationId' => $children,
  1523. 'status' => ListingStatus::LIVE,
  1524. ]);
  1526. return $queryBuilder->getQuery()->getSingleScalarResult();
  1527. }
  1529. /**
  1530. * @return QueryBuilder
  1531. */
  1532. private function getParents(Location $location)
  1533. {
  1534. $queryBuilder = $this->createQueryBuilder('location');
  1536. $queryBuilder->where($queryBuilder->expr()->lt('location.left', ':left'))
  1537. ->andWhere($queryBuilder->expr()->gt('location.right', ':right'))
  1538. ->andWhere($queryBuilder->expr()->eq('location.root', ':root'))
  1539. ->setParameter('left', $location->getLeft())
  1540. ->setParameter('right', $location->getRight())
  1541. ->setParameter('root', $location->getRoot());
  1543. return $queryBuilder;
  1544. }
  1546. /**
  1547. * Get seller listing locations filtered by nearest parent type.
  1548. */
  1549. public function getSellerLocationsIDsByType(User $user, string $locationType): array
  1550. {
  1551. $queryBuilder = $this->createQueryBuilder('location');
  1552. $queryBuilder
  1553. ->distinct()
  1554. ->join('location.listings', 'l')
  1555. ->where('l.user = :user')
  1556. ->setParameter('user', $user);
  1558. $locations = $queryBuilder->getQuery()->getResult();
  1560. $results = [];
  1561. foreach ($locations as $location) {
  1562. if (!$location instanceof Location) {
  1563. continue;
  1564. }
  1565. $parent = $location->getParentByType($locationType);
  1566. if ($parent) {
  1567. $results[$parent->getId()] = $parent;
  1568. }
  1569. }
  1571. return array_values($results);
  1572. }
  1573. }