disco / app / Analytics / Queries / BaseQuery.php

BaseQuery.php in Discount Rules for WooCommerce – Disco | Dynamic Pricing, Conditions, Bulk, Bundle, BOGO 1.4.14, at app/Analytics/Queries/BaseQuery.php

390 lines 13.6 KB

Raw Download Zip

1	<?php
2
3	/**
4	* BaseQuery — shared table helpers for all Analytics query classes.
5	*
6	* @package Disco
7	* @subpackage Disco\App\Analytics\Queries
8	* @since 1.3.37
9	*/
10
11	namespace Disco\App\Analytics\Queries;
12
13	/**
14	* Abstract base for all analytics query classes.
15	*
16	* Provides HPOS-aware table name resolution and common SQL fragment builders
17	* so every concrete query class stays DRY.
18	*/
19	abstract class BaseQuery {
20
21	/**
22	* Resolves table names and the HPOS flag for the current site.
23	*
24	* 'order_meta' — where disco_campaign (and _discount_total) live.
25	* 'order_id_col' — FK column name in order_meta ('post_id' \| 'order_id').
26	* 'postmeta' — always wp_postmeta, used for product meta only.
27	* 'posts' — always wp_posts, used for product rows only.
28	*/
29	protected function get_tables(): array {
30	global $wpdb;
31
32	return array(
33	'orders' => $wpdb->prefix . 'wc_orders',
34	'order_meta' => $wpdb->prefix . 'wc_orders_meta',
35	'order_id_col' => 'order_id',
36	'posts' => $wpdb->posts,
37	'postmeta' => $wpdb->postmeta,
38	'users' => $wpdb->users,
39	'terms' => $wpdb->terms,
40	'term_taxonomy' => $wpdb->term_taxonomy,
41	'term_relationships' => $wpdb->term_relationships,
42	'order_items' => $wpdb->prefix . 'woocommerce_order_items',
43	'order_itemmeta' => $wpdb->prefix . 'woocommerce_order_itemmeta',
44	'order_addresses' => $wpdb->prefix . 'wc_order_addresses',
45	'product_lookup' => $wpdb->prefix . 'wc_order_product_lookup',
46	'campaigns' => $wpdb->prefix . 'disco_campaigns',
47	);
48	}
49
50	/**
51	* Returns SQL fragments for HPOS order columns.
52	*
53	* The wp_wc_orders table stores total_amount and customer_id as direct columns.
54	* _discount_total is still stored in wp_wc_orders_meta.
55	* total_join and customer_join are kept as empty strings so existing query
56	* interpolations remain valid without any changes.
57	*
58	* @param array $tables Tables from get_tables().
59	* @param string $order_alias SQL alias for the orders table (default 'o').
60	* @param string $meta_alias SQL alias for the order_meta anchor (default 'order_meta').
61	*/
62	protected function get_order_clauses(
63	array $tables,
64	string $order_alias = 'o',
65	string $meta_alias = 'order_meta'
66	): array {
67	$order_meta_table = $tables['order_meta'];
68	$order_id_column = $tables['order_id_col'];
69
70	return array(
71	'date_col' => "{$order_alias}.date_created_gmt",
72	'status_where' => "{$order_alias}.type = 'shop_order'",
73	'status_col' => "{$order_alias}.status",
74	'total_expr' => "{$order_alias}.total_amount",
75	'customer_expr' => "{$order_alias}.customer_id",
76	'discount_expr' => 'COALESCE(discount_meta.meta_value, 0)',
77	'total_join' => '',
78	'customer_join' => '',
79	'discount_join' => "LEFT JOIN {$order_meta_table} discount_meta ON discount_meta.{$order_id_column} = {$meta_alias}.{$order_id_column} AND discount_meta.meta_key = '_discount_total'",
80	);
81	}
82
83	/**
84	* Returns a SQL condition that restricts order_meta to the last disco_campaign row per order.
85	*
86	* Prevents revenue / discount SUM inflation when an order has more than one
87	* disco_campaign meta entry — only the row with the highest id is used.
88	*
89	* @param array $tables Tables from get_tables().
90	* @param string $meta_alias SQL alias for the order_meta table (default 'order_meta').
91	* @return string Raw SQL fragment (no leading AND).
92	*/
93	protected function get_campaign_dedup_condition( array $tables, string $meta_alias = 'order_meta' ): string {
94	$meta_table = $tables['order_meta'];
95	$order_id_col = $tables['order_id_col'];
96
97	return "{$meta_alias}.id = (
98	SELECT MAX(dedup_meta.id) FROM {$meta_table} dedup_meta
99	WHERE dedup_meta.meta_key = 'disco_campaign'
100	AND dedup_meta.{$order_id_col} = {$meta_alias}.{$order_id_col}
101	)";
102	}
103
104	/**
105	* Builds shared WHERE conditions and params for common filter args.
106	*
107	* Handles: date_from, date_to, campaign_id, customer_id (or user_id), order_id, status.
108	* Special filters (product_id, search) must be added by the caller.
109	*
110	* @param array $args Filter args from the request.
111	* @param array $clauses From get_order_clauses().
112	* @param array $tables From get_tables().
113	* @return array { conditions: string[], params: array }
114	*/
115	protected function build_common_conditions( array $args, array $clauses, array $tables ): array {
116	$conditions = array();
117	$params = array();
118
119	if ( ! empty( $args['date_from'] ) ) {
120	$conditions[] = "{$clauses['date_col']} >= %s";
121	$params[] = $args['date_from'] . ' 00:00:00';
122	}
123
124	if ( ! empty( $args['date_to'] ) ) {
125	$conditions[] = "{$clauses['date_col']} <= %s";
126	$params[] = $args['date_to'] . ' 23:59:59';
127	}
128
129	if ( ! empty( $args['campaign_id'] ) ) {
130	// Sargable string compare so the (meta_key, meta_value) index is used.
131	// meta_value stores campaign IDs as bare digit strings; CAST would force
132	// a full scan of all disco_campaign rows.
133	$conditions[] = 'order_meta.meta_value = %s';
134	$params[] = (string) (int) $args['campaign_id'];
135	}
136
137	$customer_id = $args['customer_id'] ?? $args['user_id'] ?? '';
138
139	if ( ! empty( $customer_id ) ) {
140	$conditions[] = "{$clauses['customer_expr']} = %s";
141	$params[] = (string) $customer_id;
142	}
143
144	if ( ! empty( $args['order_id'] ) ) {
145	$conditions[] = "order_meta.{$tables['order_id_col']} = %d";
146	$params[] = (int) $args['order_id'];
147	}
148
149	if ( ! empty( $args['status'] ) ) {
150	$conditions[] = 'o.status = %s';
151	$params[] = sanitize_text_field( $args['status'] );
152	}
153
154	return array( 'conditions' => $conditions, 'params' => $params );
155	}
156
157	/**
158	* Fetches name and intent for a set of campaign IDs in a single query.
159	*
160	* Used by resolve_campaign_names() implementations in concrete query classes
161	* to replace per-row JSON_EXTRACT calls inside GROUP_CONCAT.
162	*
163	* @param array<int> $ids Campaign IDs to fetch.
164	* @param array $tables From get_tables().
165	* @return array Map of campaign_id => { name: string, intent: string }
166	*/
167	protected function batch_campaign_meta( array $ids, array $tables ): array {
168	if ( empty( $ids ) ) {
169	return array();
170	}
171
172	$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
173
174	$rows = $this->run_rows(
175	"SELECT id, intent, data FROM {$tables['campaigns']} WHERE id IN ({$placeholders})",
176	array_map( 'intval', $ids )
177	);
178
179	if ( ! is_array( $rows ) ) {
180	$rows = array();
181	}
182
183	$map = array();
184
185	foreach ( $rows as $row ) {
186	$data = json_decode( $row->data, true );
187	$map[ (int) $row->id ] = array(
188	'name' => is_array( $data ) ? ( $data['name'] ?? 'Unknown' ) : 'Unknown',
189	'intent' => $row->intent ?? 'Unknown',
190	);
191	}
192
193	return $map;
194	}
195
196	/**
197	* Fetches display_name / user_email / user_login for a set of user IDs in one query.
198	*
199	* @param array<int> $user_ids WP user IDs.
200	* @param array $tables Table name map from get_tables().
201	* @return array Map of user_id => user row object.
202	*/
203	protected function fetch_users( array $user_ids, array $tables ): array {
204	if ( empty( $user_ids ) ) {
205	return array();
206	}
207
208	$placeholders = implode( ',', array_fill( 0, count( $user_ids ), '%d' ) );
209
210	$user_rows = $this->run_rows(
211	"SELECT ID, display_name, user_email, user_login
212	FROM {$tables['users']}
213	WHERE ID IN ({$placeholders})",
214	$user_ids
215	);
216
217	$user_by_id = array();
218
219	foreach ( $user_rows as $user_row ) {
220	$user_by_id[ (int) $user_row->ID ] = $user_row;
221	}
222
223	return $user_by_id;
224	}
225
226	/**
227	* Fetches the billing address rows for a set of order IDs in one query.
228	*
229	* @param array<int> $order_ids Order IDs.
230	* @param array $tables Table name map from get_tables().
231	* @return array Map of order_id => address row object.
232	*/
233	protected function fetch_billing_addresses( array $order_ids, array $tables ): array {
234	if ( empty( $order_ids ) ) {
235	return array();
236	}
237
238	$placeholders = implode( ',', array_fill( 0, count( $order_ids ), '%d' ) );
239
240	$address_rows = $this->run_rows(
241	"SELECT order_id, first_name, last_name, email, state
242	FROM {$tables['order_addresses']}
243	WHERE order_id IN ({$placeholders})
244	AND address_type = 'billing'",
245	$order_ids
246	);
247
248	$address_by_oid = array();
249
250	foreach ( $address_rows as $address_row ) {
251	$address_by_oid[ (int) $address_row->order_id ] = $address_row;
252	}
253
254	return $address_by_oid;
255	}
256
257	// =========================================================================
258	// List-query plumbing shared by all paginated table queries
259	// =========================================================================
260
261	/**
262	* Resolves per_page / page / offset from request args.
263	*
264	* @param array $args Request args (per_page, page).
265	* @param int $default_per_page Default page size.
266	* @param int $max_per_page Hard cap for page size.
267	* @return array { per_page: int, page: int, offset: int }
268	*/
269	protected function resolve_pagination( array $args, int $default_per_page = 10, int $max_per_page = 100 ): array {
270	$per_page = min( absint( $args['per_page'] ?? $default_per_page ), $max_per_page );
271	$page = max( 1, absint( $args['page'] ?? 1 ) );
272
273	return array(
274	'per_page' => $per_page,
275	'page' => $page,
276	'offset' => ( $page - 1 ) * $per_page,
277	);
278	}
279
280	/**
281	* Resolves a whitelisted ORDER BY field and direction from request args.
282	*
283	* Falls back to $default_orderby when args['orderby'] is not whitelisted.
284	* Direction defaults to DESC; only an explicit 'asc'/'ASC' flips it.
285	*
286	* @param array $args Request args (orderby, order).
287	* @param array<string> $allowed_orderby Whitelist of sortable fields.
288	* @param string $default_orderby Fallback field.
289	* @return array { orderby: string, direction: 'ASC'\|'DESC' }
290	*/
291	protected function resolve_sort( array $args, array $allowed_orderby, string $default_orderby ): array {
292	$orderby = $default_orderby;
293
294	if ( in_array( $args['orderby'] ?? '', $allowed_orderby, true ) ) {
295	$orderby = $args['orderby'];
296	}
297
298	$direction = 'DESC';
299
300	if ( strtoupper( $args['order'] ?? 'DESC' ) === 'ASC' ) {
301	$direction = 'ASC';
302	}
303
304	return array(
305	'orderby' => $orderby,
306	'direction' => $direction,
307	);
308	}
309
310	/**
311	* Runs a COUNT query through $wpdb->prepare().
312	*
313	* $sql must contain at least one placeholder matching $params; callers
314	* build it from trusted fragments (table names, whitelisted columns) only.
315	*
316	* @param string $sql COUNT SQL with %d/%s placeholders.
317	* @param array $params Values for the placeholders (must not be empty).
318	* @return int The count.
319	*/
320	protected function run_count( string $sql, array $params ): int {
321	global $wpdb;
322
323	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql contains only trusted fragments (table names from $wpdb->prefix, whitelisted columns); every value is bound here via prepare(). Real-time analytics aggregates must not be cached.
324	return (int) $wpdb->get_var( $wpdb->prepare( $sql, ...$params ) );
325	}
326
327	/**
328	* Runs a single-row SELECT through $wpdb->prepare().
329	*
330	* $sql must contain at least one placeholder matching $params; callers
331	* build it from trusted fragments (table names, whitelisted columns) only.
332	*
333	* @param string $sql SELECT SQL with %d/%s placeholders.
334	* @param array $params Values for the placeholders (must not be empty).
335	* @return object\|null Row object, or null when no row matches.
336	*/
337	protected function run_row( string $sql, array $params ) {
338	global $wpdb;
339
340	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql contains only trusted fragments (table names from $wpdb->prefix, whitelisted columns); every value is bound here via prepare(). Real-time analytics aggregates must not be cached.
341	return $wpdb->get_row( $wpdb->prepare( $sql, ...$params ) );
342	}
343
344	/**
345	* Runs a SELECT query through $wpdb->prepare() and always returns an array of row objects.
346	*
347	* $sql must contain at least one placeholder matching $params; callers
348	* build it from trusted fragments only.
349	*
350	* @param string $sql SELECT SQL with %d/%s placeholders.
351	* @param array $params Values for the placeholders (must not be empty).
352	* @return array Row objects (empty array on no result / error).
353	*/
354	protected function run_rows( string $sql, array $params ): array {
355	global $wpdb;
356
357	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $sql contains only trusted fragments (table names from $wpdb->prefix, whitelisted columns); every value is bound here via prepare(). Real-time analytics aggregates must not be cached.
358	$rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );
359
360	if ( is_array( $rows ) ) {
361	return $rows;
362	}
363
364	return array();
365	}
366
367	/**
368	* Formats the standard { total, pages, rows } result for list queries.
369	*
370	* @param int $total Total matching rows.
371	* @param int $per_page Page size used for the pages calculation.
372	* @param array $rows Result rows for the current page.
373	* @return array { total: int, pages: int, rows: array }
374	*/
375	protected function format_list_result( int $total, int $per_page, array $rows ): array {
376	$pages = 0;
377
378	if ( $total > 0 && $per_page > 0 ) {
379	$pages = (int) ceil( $total / $per_page );
380	}
381
382	return array(
383	'total' => $total,
384	'pages' => $pages,
385	'rows' => $rows,
386	);
387	}
388
389	}
390