← All changes | src/Donations/Endpoints/ListDonations.php +107 -44 4.15.4 → 4.18.0 View file →

		@@ -1,8 +1,9 @@
1	1	<?php
2	2
3	3	namespace Give\Donations\Endpoints;
4	4
	5	+use Closure;
5	6	use Give\Donations\ListTable\DonationsListTable;
6	7	use Give\Donations\ValueObjects\DonationMetaKeys;
7	8	use Give\Donations\ValueObjects\DonationMode;
8	9	use Give\Donations\ValueObjects\DonationStatus;
		@@ -178,8 +179,9 @@
178	179	);
179	180	}
180	181
181	182	/**
	183	+ * @since 4.18.0 Select the page of IDs first, then hydrate, so deep pages do not join every row
182	184	* @since 2.24.0 Replace Query Builder with Donations model
183	185	* @since 2.21.0
184	186	*
185	187	* @return array
		@@ -190,28 +192,67 @@
190	192	$perPage = $this->request->get_param('perPage');
191	193	$sortColumns = $this->listTable->getSortColumnById($this->request->get_param('sortColumn') ?: 'id');
192	194	$sortDirection = $this->request->get_param('sortDirection') ?: 'desc';
193	195
194		- $query = give()->donations->prepareQuery();
195		- list($query) = $this->getWhereConditions($query);
	196	+ // Resolve the page of IDs against the posts table with only the meta the filters and sort
	197	+ // need, then hydrate those rows. Paging the fully joined model query scans every row.
	198	+ $idQuery = DB::table('posts')->select(
	199	+ ['ID', 'id'],
	200	+ ['post_date', 'createdAt'],
	201	+ ['post_modified', 'updatedAt'],
	202	+ ['post_status', 'status']
	203	+ );
	204	+ list($idQuery, $dependencies) = $this->getWhereConditions($idQuery);
	205	+ $dependencies = array_merge($dependencies, $this->getSortDependencies($sortColumns));
196	206
	207	+ if ($dependencies) {
	208	+ $idQuery->attachMeta(
	209	+ 'give_donationmeta',
	210	+ 'ID',
	211	+ 'donation_id',
	212	+ ...DonationMetaKeys::getColumnsForAttachMetaQueryFromArray($dependencies)
	213	+ );
	214	+ }
	215	+
197	216	foreach ($sortColumns as $sortColumn) {
198		- $query->orderBy($sortColumn, $sortDirection);
	217	+ $idQuery->orderBy($sortColumn, $sortDirection);
199	218	}
200	219
201		- $query->limit($perPage)
202		- ->offset(($page - 1) * $perPage);
	220	+ $ids = array_column($idQuery->limit($perPage)->offset(($page - 1) * $perPage)->getAll() ?: [], 'id');
203	221
204		- $donations = $query->getAll();
	222	+ if (!$ids) {
	223	+ return [];
	224	+ }
205	225
206		- if (!$donations) {
207		- return [];
	226	+ $query = give()->donations->prepareQuery()->whereIn('ID', $ids);
	227	+
	228	+ foreach ($sortColumns as $sortColumn) {
	229	+ $query->orderBy($sortColumn, $sortDirection);
208	230	}
209	231
210		- return $donations;
	232	+ return $query->getAll() ?: [];
211	233	}
212	234
213	235	/**
	236	+ * Meta keys a sort expression references, so the ID query can attach them.
	237	+ *
	238	+ * @since 4.18.0
	239	+ *
	240	+ * @param string[] $sortColumns
	241	+ *
	242	+ * @return DonationMetaKeys[]
	243	+ */
	244	+ private function getSortDependencies(array $sortColumns): array
	245	+ {
	246	+ $sortSql = implode(' ', $sortColumns);
	247	+
	248	+ return array_values(array_filter(DonationMetaKeys::values(), static function (DonationMetaKeys $key) use ($sortSql) {
	249	+ return strpos($sortSql, $key->getKeyAsCamelCase()) !== false;
	250	+ }));
	251	+ }
	252	+
	253	+ /**
	254	+ * @since 4.18.0 Drop the GROUP BY on mode, which made count() return the size of one mode group
214	255	* @since 2.24.0 Replace Query Builder with Donations model
215	256	* @since 2.21.0
216	257	*
217	258	* @return int
		@@ -217,25 +258,54 @@
217	258	* @return int
218	259	*/
219	260	public function getTotalDonationsCount(): int
220	261	{
221		- $query = DB::table('posts')
222		- ->where('post_type', 'give_payment')
223		- ->groupBy('mode');
	262	+ list($query, $dependencies) = $this->getWhereConditions(DB::table('posts'));
224	263
225		- list($query, $dependencies) = $this->getWhereConditions($query);
	264	+ if ($dependencies) {
	265	+ $query->attachMeta(
	266	+ 'give_donationmeta',
	267	+ 'ID',
	268	+ 'donation_id',
	269	+ ...DonationMetaKeys::getColumnsForAttachMetaQueryFromArray($dependencies)
	270	+ );
	271	+ }
226	272
227		- $query->attachMeta(
228		- 'give_donationmeta',
229		- 'ID',
230		- 'donation_id',
231		- ...DonationMetaKeys::getColumnsForAttachMetaQueryFromArray($dependencies)
232		- );
	273	+ return $query->count();
	274	+ }
233	275
234		- return $query->count();
	276	+ /**
	277	+ * @since 4.18.0
	278	+ */
	279	+ private function donationIdsWithNamePrefix(string $value): Closure
	280	+ {
	281	+ return $this->donationIdsWithMetaPrefix([DonationMetaKeys::FIRST_NAME, DonationMetaKeys::LAST_NAME], $value);
235	282	}
236	283
237	284	/**
	285	+ * Subquery for donation IDs whose meta value starts with the search term. A prefix match on
	286	+ * the (meta_key, meta_value) index replaces a leading-wildcard LIKE across joined meta tables,
	287	+ * which had to scan every row for the key.
	288	+ *
	289	+ * @since 4.18.0
	290	+ *
	291	+ * @param string[] $metaKeys
	292	+ */
	293	+ private function donationIdsWithMetaPrefix(array $metaKeys, string $value): Closure
	294	+ {
	295	+ $prefix = DB::esc_like($value) . '%';
	296	+
	297	+ return static function (QueryBuilder $builder) use ($metaKeys, $prefix) {
	298	+ $builder
	299	+ ->select('donation_id')
	300	+ ->from('give_donationmeta')
	301	+ ->whereIn('meta_key', $metaKeys)
	302	+ ->where('meta_value', $prefix, 'LIKE');
	303	+ };
	304	+ }
	305	+
	306	+ /**
	307	+ * @since 4.18.0 Match name and email searches by prefix through indexed subqueries, and filter test mode the same way instead of HAVING
238	308	* @since 4.12.0 Updated status filtering to accept multiple comma-separated values
239	309	* @since 4.8.0 Added support for subscriptionId parameter to filter donations
240	310	* @since 4.6.0 add status status condition to filter donations
241	311	* @since 3.4.0 Make this method protected so it can be extended
		@@ -256,14 +326,10 @@
256	326	$testMode = $this->request->get_param('testMode');
257	327	$campaignId = $this->request->get_param('campaignId');
258	328	$subscriptionId = $this->request->get_param('subscriptionId');
259	329	$status = $this->request->get_param('status');
260		- $dependencies = [
261		- DonationMetaKeys::MODE(),
262		- ];
	330	+ $dependencies = [];
263	331
264		- $hasWhereConditions = $search || $start || $end || $campaignId || $subscriptionId || $donor || $status;
265		-
266	332	$query->where('post_type', 'give_payment');
267	333
268	334	if (!empty($status)) {
269	335	$query->whereIn('post_status', $status);
		@@ -275,17 +341,11 @@
275	341	if ($search) {
276	342	if (ctype_digit($search)) {
277	343	$query->where('id', $search);
278	344	} elseif (strpos($search, '@') !== false) {
279		- $query
280		- ->whereLike('give_donationmeta_attach_meta_email.meta_value', $search);
281		- $dependencies[] = DonationMetaKeys::EMAIL();
	345	+ $query->whereIn('ID', $this->donationIdsWithMetaPrefix([DonationMetaKeys::EMAIL], $search));
282	346	} else {
283		- $query
284		- ->whereLike('give_donationmeta_attach_meta_firstName.meta_value', $search)
285		- ->orWhereLike('give_donationmeta_attach_meta_lastName.meta_value', $search);
286		- $dependencies[] = DonationMetaKeys::FIRST_NAME();
287		- $dependencies[] = DonationMetaKeys::LAST_NAME();
	347	+ $query->whereIn('ID', $this->donationIdsWithNamePrefix($search));
288	348	}
289	349	}
290	350
291	351	if ($donor) {
		@@ -293,13 +353,9 @@
293	353	$query
294	354	->where('give_donationmeta_attach_meta_donorId.meta_value', $donor);
295	355	$dependencies[] = DonationMetaKeys::DONOR_ID();
296	356	} else {
297		- $query
298		- ->whereLike('give_donationmeta_attach_meta_firstName.meta_value', $donor)
299		- ->orWhereLike('give_donationmeta_attach_meta_lastName.meta_value', $donor);
300		- $dependencies[] = DonationMetaKeys::FIRST_NAME();
301		- $dependencies[] = DonationMetaKeys::LAST_NAME();
	357	+ $query->whereIn('ID', $this->donationIdsWithNamePrefix($donor));
302	358	}
303	359	}
304	360
305	361	if ($campaignId) {
		@@ -321,15 +377,22 @@
321	377	} elseif ($end) {
322	378	$query->where('post_date', $end, '<=');
323	379	}
324	380
325		- if ($hasWhereConditions) {
326		- $query->havingRaw('HAVING COALESCE(give_donationmeta_attach_meta_mode.meta_value, %s) = %s', DonationMode::LIVE, $testMode ? DonationMode::TEST : DonationMode::LIVE);
327		- } elseif ($testMode) {
328		- $query->where('give_donationmeta_attach_meta_mode.meta_value', DonationMode::TEST);
	381	+ // Test-mode donations carry a mode meta row; live donations may have none. A subquery on
	382	+ // (meta_key, meta_value) is index-friendly, unlike a LEFT JOIN with an IS NULL OR condition.
	383	+ $testModeDonationIds = static function (QueryBuilder $builder) {
	384	+ $builder
	385	+ ->select('donation_id')
	386	+ ->from('give_donationmeta')
	387	+ ->where('meta_key', DonationMetaKeys::MODE)
	388	+ ->where('meta_value', DonationMode::TEST);
	389	+ };
	390	+
	391	+ if ($testMode) {
	392	+ $query->whereIn('ID', $testModeDonationIds);
329	393	} else {
330		- $query->whereIsNull('give_donationmeta_attach_meta_mode.meta_value')
331		- ->orWhere('give_donationmeta_attach_meta_mode.meta_value', DonationMode::TEST, '<>');
	394	+ $query->whereNotIn('ID', $testModeDonationIds);
332	395	}
333	396
334	397	return [
335	398	$query,