← All changes | app/Repositories/DestinationRepository.php +144 -8 3.0.4 → trunk View file →

		@@ -8,8 +8,9 @@
8	8	use Yatra\Database\Tables\ClassificationsTable;
9	9	use Yatra\Database\Tables\TripClassificationsTable;
10	10	use Yatra\Database\Tables\TripsTable;
11	11	use Yatra\Database\Tables\ReviewsTable;
	12	+use Yatra\Utils\Cache;
12	13
13	14	/**
14	15	* Destination Repository
15	16	* Handles database operations for destinations using the new ClassificationsTable
		@@ -76,8 +77,24 @@
76	77	return $this->all($args);
77	78	}
78	79
79	80	/**
	81	+ * Wipe listing caches whenever a destination row is created /
	82	+ * updated / deleted so {@see self::getPublishedWithTripCounts()}
	83	+ * (and any other `destination_listing_*` / `trip_listing_*` keys)
	84	+ * never serve stale aggregates after admin edits. The matching
	85	+ * {@see \Yatra\Hooks\CacheHooks::onDestinationUpdated()} listeners
	86	+ * only fire when a `yatra_destination_*` action is dispatched
	87	+ * elsewhere, which today nothing does — so without this override an
	88	+ * admin edit could keep showing old `trips_count` / `starting_price`
	89	+ * for up to {@see Cache::DURATION_DESTINATION_DATA} seconds.
	90	+ */
	91	+ protected function afterWrite(string $operation, int $id, array $context = []): void
	92	+ {
	93	+ Cache::invalidateListingCaches();
	94	+ }
	95	+
	96	+ /**
80	97	* Get published destinations with attached trip counts.
81	98	*
82	99	* This uses the new TripClassificationsTable relation table to count how many
83	100	* trips are linked to each destination. It returns each destination row
		@@ -84,10 +101,34 @@
84	101	* plus a numeric trips_count property.
85	102	*/
86	103	public function getPublishedWithTripCounts(): array
87	104	{
88		- global $wpdb;
	105	+ // The underlying SQL + price walk has been made O(1) in the
	106	+ // number of trips per destination (see
	107	+ // {@see self::computeStartingPriceForTripIds()}), so the
	108	+ // uncached path is now fast on its own. We still cache the full
	109	+ // result for repeat visits, but the cache key sits behind
	110	+ // {@see \Yatra\Utils\Cache::invalidateListingCaches()} (which
	111	+ // matches the `destination_listing_` prefix) and gets wiped
	112	+ // automatically whenever a trip/destination row is written via
	113	+ // {@see \Yatra\Hooks\CacheHooks}. Users always see real data
	114	+ // immediately after admin edits — no manual cache-bust needed.
	115	+ return $this->cacheQueryResult(
	116	+ 'destination_listing_with_trip_counts_v2',
	117	+ function (): array {
	118	+ return $this->fetchPublishedWithTripCounts();
	119	+ },
	120	+ Cache::DURATION_DESTINATION_DATA
	121	+ );
	122	+ }
89	123
	124	+ /**
	125	+ * Uncached worker for {@see self::getPublishedWithTripCounts()}.
	126	+ *
	127	+ * @return array<int, \stdClass>
	128	+ */
	129	+ private function fetchPublishedWithTripCounts(): array
	130	+ {
90	131	$destTable = esc_sql($this->table);
91	132	$relTable = TripClassificationsTable::getTableName();
92	133	$tripsTable = TripsTable::getTableName();
93	134	$reviewsTable = ReviewsTable::getTableName();
		@@ -95,15 +136,15 @@
95	136	// COUNT(DISTINCT tc.trip_id) gives real number of trips per destination.
96	137	// avg_rating is computed from approved reviews across all those trips.
97	138	// starting_price is computed in PHP using both regular trip prices and
98	139	// traveler-based pricing from recurring availability rules.
99		- $sql = "SELECT d.*,
	140	+ $sql = "SELECT d.*,
100	141	COUNT(DISTINCT tc.trip_id) AS trips_count,
101	142	COALESCE(AVG(r.rating), 0) AS avg_rating,
102	143	GROUP_CONCAT(DISTINCT tc.trip_id) AS trip_ids
103	144	FROM `{$destTable}` d
104	145	LEFT JOIN `{$relTable}` tc
105		- ON tc.classification_id = d.id
	146	+ ON tc.classification_id = d.id
106	147	AND tc.classification_type = %s
107	148	LEFT JOIN `{$tripsTable}` t
108	149	ON t.id = tc.trip_id
109	150	LEFT JOIN `{$reviewsTable}` r
		@@ -135,22 +176,117 @@
135	176	if (trim($tripIdsCsv) === '') {
136	177	return 0.0;
137	178	}
138	179
139		- $tripIds = array_filter(array_map('intval', explode(',', $tripIdsCsv)));
	180	+ $tripIds = array_values(array_unique(array_filter(array_map('intval', explode(',', $tripIdsCsv)))));
140	181	if (empty($tripIds)) {
141	182	return 0.0;
142	183	}
143	184
	185	+ // Batch-load all trip rows in ONE query (was: SELECT-per-trip,
	186	+ // which made every /destinations request issue O(trips_per_dest)
	187	+ // queries — multiplied across all destinations on the page).
	188	+ $tripsTable = TripsTable::getTableName();
	189	+ $placeholders = implode(',', array_fill(0, count($tripIds), '%d'));
	190	+ $rows = $this->wpdb->get_results(
	191	+ $this->wpdb->prepare(
	192	+ "SELECT id, sale_price, discounted_price, original_price FROM `{$tripsTable}` WHERE id IN ({$placeholders})",
	193	+ ...$tripIds
	194	+ )
	195	+ ) ?: [];
	196	+
144	197	$minPrice = null;
145		- foreach ($tripIds as $tripId) {
146		- $price = $this->getEffectiveTripBasePrice($tripId);
147		- if ($price > 0 && ($minPrice === null || $price < $minPrice)) {
148		- $minPrice = $price;
	198	+ $unpricedIds = [];
	199	+
	200	+ foreach ($rows as $row) {
	201	+ $tripEffective = \Yatra\Services\TripPricingService::getEffectivePrice($row);
	202	+ if ($tripEffective > 0) {
	203	+ if ($minPrice === null || $tripEffective < $minPrice) {
	204	+ $minPrice = $tripEffective;
	205	+ }
	206	+ } else {
	207	+ $unpricedIds[] = (int) $row->id;
149	208	}
150	209	}
151	210
	211	+ // Only fall back to RecurringAvailabilityRepository for trips
	212	+ // that have no trip-level price set. Even there, batch the rule
	213	+ // query across all such trip IDs instead of N separate calls.
	214	+ if (!empty($unpricedIds)) {
	215	+ $fallbackMin = $this->minPriceFromRecurringRulesForTripIds($unpricedIds);
	216	+ if ($fallbackMin > 0 && ($minPrice === null || $fallbackMin < $minPrice)) {
	217	+ $minPrice = $fallbackMin;
	218	+ }
	219	+ }
	220	+
152	221	return $minPrice ?? 0.0;
	222	+ }
	223	+
	224	+ /**
	225	+ * Batched version of the traveler-pricing fallback that used to run
	226	+ * once per trip. Pulls every active rule for the given trip IDs in a
	227	+ * single query and scans them in PHP.
	228	+ *
	229	+ * @param list<int> $tripIds
	230	+ */
	231	+ private function minPriceFromRecurringRulesForTripIds(array $tripIds): float
	232	+ {
	233	+ if ($tripIds === []) {
	234	+ return 0.0;
	235	+ }
	236	+
	237	+ $rulesRepo = new RecurringAvailabilityRepository();
	238	+ if (!method_exists($rulesRepo, 'findByTripIds')) {
	239	+ // Older repository — fall back to per-trip lookup but only
	240	+ // for the (small) set of trips with no trip-level price.
	241	+ $candidates = [];
	242	+ foreach ($tripIds as $tid) {
	243	+ $rules = $rulesRepo->findByTripId($tid, ['status' => 'active']);
	244	+ foreach ($rules as $rule) {
	245	+ $this->collectRulePriceCandidates($rule, $candidates);
	246	+ }
	247	+ }
	248	+ return $candidates === [] ? 0.0 : (float) min($candidates);
	249	+ }
	250	+
	251	+ $rules = $rulesRepo->findByTripIds($tripIds, ['status' => 'active']);
	252	+ $candidates = [];
	253	+ foreach ($rules as $rule) {
	254	+ $this->collectRulePriceCandidates($rule, $candidates);
	255	+ }
	256	+ return $candidates === [] ? 0.0 : (float) min($candidates);
	257	+ }
	258	+
	259	+ /**
	260	+ * @param list<float> $candidates Accumulator passed by reference.
	261	+ */
	262	+ private function collectRulePriceCandidates(object $rule, array &$candidates): void
	263	+ {
	264	+ if (!empty($rule->sale_price) && (float) $rule->sale_price > 0) {
	265	+ $candidates[] = (float) $rule->sale_price;
	266	+ }
	267	+ if (!empty($rule->original_price) && (float) $rule->original_price > 0) {
	268	+ $candidates[] = (float) $rule->original_price;
	269	+ }
	270	+ if (!empty($rule->traveler_pricing) && is_array($rule->traveler_pricing)) {
	271	+ foreach ($rule->traveler_pricing as $pricing) {
	272	+ if (!empty($pricing['effective_price']) && (float) $pricing['effective_price'] > 0) {
	273	+ $candidates[] = (float) $pricing['effective_price'];
	274	+ }
	275	+ }
	276	+ }
	277	+ if (!empty($rule->time_slots) && is_array($rule->time_slots)) {
	278	+ foreach ($rule->time_slots as $slot) {
	279	+ if (empty($slot['traveler_pricing']) || !is_array($slot['traveler_pricing'])) {
	280	+ continue;
	281	+ }
	282	+ foreach ($slot['traveler_pricing'] as $pricing) {
	283	+ if (!empty($pricing['effective_price']) && (float) $pricing['effective_price'] > 0) {
	284	+ $candidates[] = (float) $pricing['effective_price'];
	285	+ }
	286	+ }
	287	+ }
	288	+ }
153	289	}
154	290
155	291	/**
156	292	* Get an effective base price for a single trip.