wp-slimstat / src / Utils / Query.php

Query.php in SlimStat Analytics trunk, at src/Utils/Query.php

1,581 lines 53.6 KB

Raw Download Zip

1	<?php
2
3	namespace SlimStat\Utils;
4
5	use InvalidArgumentException;
6
7	class Query
8	{
9	private $queries = [];
10
11	private $operation;
12
13	private $table;
14
15	private $fields = '*';
16
17	private $orderClause;
18
19	private $groupByClause;
20
21	private $havingClauses = [];
22
23	private $limitClause;
24
25	private $whereRelation = 'AND';
26
27	private $setClauses = [];
28
29	private $setValuesToPrepare = [];
30
31	private $joinClauses = [];
32
33	private $whereClauses = [];
34
35	private $rawWhereClause = [];
36
37	private $valuesToPrepare = [];
38
39	private $insertValues = [];
40
41	private $ignore = false;
42
43	private $allowCaching = false;
44
45	private $cacheExpiration = 3600;
46
47	protected $db;
48
49	private $_isLiveQuery = false;
50
51	private static $processingTimestamp = null;
52
53	/**
54	* Constructor.
55	*
56	* Initializes the query with the global $wpdb instance.
57	*/
58	public function __construct()
59	{
60	$this->db = \wp_slimstat::$wpdb ?? $GLOBALS['wpdb'];
61	}
62
63	/**
64	* Initializes a new query instance for a select operation on a table.
65	*
66	* @param string\|array $fields The fields to select. If an array is provided, the fields are
67	* concatenated with a comma separator and the resulting string
68	* is used as the SELECT clause.
69	*
70	* @return static A new Query instance configured for a select operation.
71	*/
72	public static function select($fields = '*')
73	{
74	$instance = new self();
75	$instance->operation = 'select';
76	$instance->fields = is_array($fields) ? implode(', ', $fields) : $fields;
77	return $instance;
78	}
79
80	/**
81	* Initializes a new query instance for an update operation on the specified table.
82	*
83	* @param string $table The name of the table to update.
84	*
85	* @return static A new Query instance configured for an update operation.
86	*/
87	public static function update($table)
88	{
89	$instance = new self();
90	$instance->operation = 'update';
91	$instance->table = $table;
92	return $instance;
93	}
94
95	/**
96	* Initializes a new query instance for a delete operation on the specified table.
97	*
98	* @param string $table The name of the table to delete from.
99	*
100	* @return static
101	*/
102	public static function delete($table)
103	{
104	$instance = new self();
105	$instance->operation = 'delete';
106	$instance->table = $table;
107	return $instance;
108	}
109
110	/**
111	* Initializes a new query instance for an insert operation on the specified table.
112	*
113	* @param string $table The name of the table to insert data into.
114	*
115	* @return self A new Query instance configured for an insert operation.
116	*/
117	public static function insert($table)
118	{
119	$instance = new self();
120	$instance->operation = 'insert';
121	$instance->table = $table;
122	return $instance;
123	}
124
125	/**
126	* Adds IGNORE to the query.
127	*
128	* @param bool $ignore
129	* @return $this
130	*/
131	public function ignore($ignore = true)
132	{
133	$this->ignore = $ignore;
134	return $this;
135	}
136
137	/**
138	* Combines multiple query instances into a single UNION query.
139	*
140	* @param array $queries An array of Query instances to be united.
141	*
142	* @return self A new Query instance representing the UNION of the provided queries.
143	*/
144	public static function union($queries)
145	{
146	$instance = new self();
147	$instance->operation = 'union';
148	$instance->queries = $queries;
149	return $instance;
150	}
151
152	/**
153	* Specifies the table to be used in the query.
154	*
155	* @param string $table The name of the table to use.
156	*
157	* @return $this
158	*/
159	public function from($table)
160	{
161	$this->table = $table;
162	return $this;
163	}
164
165	/**
166	* Sets the values for an insert operation.
167	*
168	* @return $this
169	*/
170	public function values(array $values)
171	{
172	if ($values === []) {
173	return $this;
174	}
175
176	// Check if it's an array of arrays for bulk insert
177	if (isset($values[0]) && is_array($values[0])) {
178	// Bulk insert
179	$this->insertValues = $values;
180	} else {
181	// Single row insert
182	$this->insertValues[] = $values;
183	}
184
185	return $this;
186	}
187
188	/**
189	* Sets the values for the columns in the current query.
190	*
191	* This function prepares the column assignments for an SQL update operation.
192	* It supports string, numeric, and null values, and automatically escapes
193	* field names to prevent SQL injection.
194	*
195	* @param array $values An associative array of column-value pairs to set.
196	* The array key is the column name, and the value is
197	* the value to assign to the column.
198	*
199	* @return $this
200	*/
201	public function set($values)
202	{
203	if (empty($values)) {
204	return $this;
205	}
206
207	foreach ($values as $field => $value) {
208	$column = '`' . str_replace('`', '``', $field) . '`';
209	if (is_string($value)) {
210	$this->setClauses[] = sprintf('%s = %%s', $column);
211	$this->setValuesToPrepare[] = $value;
212	} elseif (is_numeric($value)) {
213	$this->setClauses[] = sprintf('%s = %%s', $column);
214	$this->setValuesToPrepare[] = $value;
215	} elseif (is_null($value)) {
216	$this->setClauses[] = $column . ' = NULL';
217	}
218	}
219
220	return $this;
221	}
222
223	/**
224	* Sets a raw value for a column, allowing for SQL expressions.
225	*
226	* @param string $column
227	* @param string $expression
228	* @param array $params
229	* @return $this
230	*/
231	public function setRaw($column, $expression, $params = [])
232	{
233	$this->setClauses[] = sprintf('`%s` = %s', str_replace('`', '``', $column), $expression);
234	if (!empty($params)) {
235	$this->setValuesToPrepare = array_merge($this->setValuesToPrepare, $params);
236	}
237
238	return $this;
239	}
240
241	/**
242	* Add a WHERE clause to the query.
243	*
244	* @param string $field The field to filter on.
245	* @param string $operator The operator to use. Supported operators: =, !=, >, >=, <, <=, LIKE, NOT LIKE, IN, NOT IN, BETWEEN.
246	* @param mixed $value The value to filter on. Can be a string, int, array or null.
247	*
248	* @return $this
249	*
250	* @throws InvalidArgumentException If the operator is not supported.
251	*/
252	public function where($field, $operator, $value)
253	{
254	if ('BETWEEN' === strtoupper($operator) && is_array($value) && 2 === count($value) && (null !== $value[0] && null !== $value[1])) {
255	$condition = $this->generateCondition($field, $operator, $value);
256	if (!empty($condition)) {
257	$this->whereClauses[] = $condition['condition'];
258	$this->valuesToPrepare = array_merge($this->valuesToPrepare, $condition['values']);
259	}
260
261	return $this;
262	}
263
264	if (is_array($value)) {
265	$value = array_filter(array_values($value));
266	}
267
268	if (!is_numeric($value) && empty($value)) {
269	return $this;
270	}
271
272	$condition = $this->generateCondition($field, $operator, $value);
273	if (!empty($condition)) {
274	$this->whereClauses[] = $condition['condition'];
275	$this->valuesToPrepare = array_merge($this->valuesToPrepare, $condition['values']);
276	}
277
278	return $this;
279	}
280
281	/**
282	* Add a raw WHERE clause to the query. If values are provided, they will be
283	* escaped and inserted into the query.
284	*
285	* @param string $condition The raw WHERE condition.
286	* @param array $values Values to be inserted into the condition.
287	*
288	* @return $this
289	*/
290	public function whereRaw($condition, $values = [])
291	{
292	$this->rawWhereClause[] = empty($values) ? $condition : $this->prepareQuery($condition, $values);
293
294	return $this;
295	}
296
297	/**
298	* Add a raw HAVING clause to the query. If values are provided, they will be
299	* escaped and inserted into the clause. Any leading "HAVING" keyword is stripped
300	* to ensure correct placement in the final query.
301	*
302	* @param string $condition The raw HAVING condition.
303	* @param array $values Values to be inserted into the condition.
304	*
305	* @return $this
306	*/
307	public function havingRaw($condition, $values = [])
308	{
309	// Strip an optional leading HAVING keyword to avoid duplication
310	$condition = preg_replace('/^\s*HAVING\s+/i', '', $condition);
311	$this->havingClauses[] = empty($values) ? $condition : $this->prepareQuery($condition, $values);
312
313	return $this;
314	}
315
316	/**
317	* Sets the GROUP BY clause for the query.
318	*
319	* @param string\|array $fields The fields to group by. Can be a comma-separated string or an array of fields.
320	*
321	* @return $this
322	*/
323	public function groupBy($fields)
324	{
325	if (is_array($fields)) {
326	$fields = implode(', ', $fields);
327	}
328
329	if (!empty($fields)) {
330	$this->groupByClause = 'GROUP BY ' . $fields;
331	}
332
333	return $this;
334	}
335
336	/**
337	* Sets the ORDER BY clause for the query.
338	*
339	* @param string\|array $fields The fields to order by. Can be a comma-separated string or an array of fields.
340	* @param string $order The order direction, either 'ASC' or 'DESC'. Defaults to 'DESC'.
341	*
342	* @return $this
343	*/
344	public function orderBy($fields, $order = 'DESC')
345	{
346	if (empty($fields)) {
347	return $this;
348	}
349
350	if (is_string($fields)) {
351	if (preg_match('/\b(ASC\|DESC)\b/i', $fields)) {
352	$this->orderClause = 'ORDER BY ' . $fields;
353	return $this;
354	}
355
356	$fields = explode(',', $fields);
357	$fields = array_map('trim', $fields);
358	}
359
360	if (is_array($fields)) {
361	$order = strtoupper($order);
362	if (!in_array($order, ['ASC', 'DESC'])) {
363	$order = 'DESC';
364	}
365
366	$orderParts = [];
367	foreach ($fields as $field) {
368	$orderParts[] = sprintf('%s %s', $field, $order);
369	}
370
371	$this->orderClause = 'ORDER BY ' . implode(', ', $orderParts);
372	}
373
374	return $this;
375	}
376
377	/**
378	* Sets the LIMIT clause for the query, with an optional OFFSET.
379	*
380	* @param int $limit The maximum number of results to return.
381	* @param int $offset The number of rows to skip. Defaults to 0.
382	*
383	* @return $this
384	*/
385	public function limit($limit, $offset = 0)
386	{
387	$limit = intval($limit);
388	$offset = intval($offset);
389	if ($offset > 0) {
390	$this->limitClause = sprintf('LIMIT %d OFFSET %d', $limit, $offset);
391	} else {
392	$this->limitClause = 'LIMIT ' . $limit;
393	}
394	return $this;
395	}
396
397	/**
398	* Sets the LIMIT and OFFSET clauses for pagination.
399	*
400	* @param int $page The page number. Defaults to 1.
401	* @param int $perPage The number of results to show per page. Defaults to 10.
402	*
403	* @return $this
404	*/
405	public function perPage($page = 1, $perPage = 10)
406	{
407	$page = intval($page);
408	$perPage = intval($perPage);
409	if ($page > 0 && $perPage > 0) {
410	$offset = ($page - 1) * $perPage;
411	$this->limitClause = sprintf('LIMIT %d OFFSET %d', $perPage, $offset);
412	}
413
414	return $this;
415	}
416
417	/**
418	* Join another table.
419	*
420	* @param string $table The table to join.
421	* @param string\|array $on The join condition. Can be an array with two fields to join on, or a string with a condition.
422	* @param array $conditions An array of conditions to join on. Each condition is an array with three elements: field, operator, value.
423	* @param string $joinType The type of join. Can be INNER, LEFT, or RIGHT. Defaults to INNER.
424	*
425	* @return $this
426	*
427	* @throws InvalidArgumentException If the join condition is invalid.
428	*/
429	public function join($table, $on, $conditions = [], $joinType = 'INNER')
430	{
431	$joinType = strtoupper($joinType);
432	if (is_array($on) && 2 == count($on)) {
433	$joinClause = sprintf('%s JOIN %s ON %s = %s', $joinType, $table, $on[0], $on[1]);
434	if (!empty($conditions)) {
435	foreach ($conditions as $condition) {
436	$field = $condition[0];
437	$operator = $condition[1];
438	$value = $condition[2];
439	$cond = $this->generateCondition($field, $operator, $value);
440	if (!empty($cond)) {
441	$joinClause .= ' AND ' . $cond['condition'];
442	$this->valuesToPrepare = array_merge($this->valuesToPrepare, $cond['values']);
443	}
444	}
445	}
446
447	$this->joinClauses[] = $joinClause;
448	return $this;
449	}
450
451	// Backward compatibility: allow two string fields passed separately
452	if (is_string($on) && is_string($conditions) && '' !== $on && '' !== $conditions) {
453	$this->joinClauses[] = sprintf('%s JOIN %s ON %s = %s', $joinType, $table, $on, $conditions);
454	return $this;
455	}
456
457	// Allow raw ON condition string
458	if (is_string($on) && '' !== $on && (empty($conditions) \|\| (is_array($conditions) && empty($conditions)))) {
459	$this->joinClauses[] = sprintf('%s JOIN %s ON %s', $joinType, $table, $on);
460	return $this;
461	}
462
463	throw new InvalidArgumentException('Invalid join clause');
464	}
465
466	/**
467	* Set the caching flag and expiration time.
468	*
469	* @param bool $flag Whether to allow caching.
470	* @param int $expiration The cache expiration time in seconds.
471	*
472	* @return $this
473	*/
474	public function allowCaching($flag = true, $expiration = 3600)
475	{
476	$this->allowCaching = $flag;
477	$this->cacheExpiration = $expiration;
478	return $this;
479	}
480
481	/**
482	* Set the caching flag depending on whether the given date range overlaps with today.
483	*
484	* If the given date range is entirely in the past, caching is allowed. Otherwise, caching is disabled.
485	*
486	* @param int\|string $to The end date of the range (Y-m-d or Y-m-d H:i:s or timestamp)
487	*
488	* @return $this
489	*/
490	public function canUseCacheForDateRange($to)
491	{
492	$today = $this->getTodayDate();
493	$toTs = is_numeric($to) ? intval($to) : strtotime($to);
494
495	if ($toTs < $today) {
496	$this->allowCaching(true, $this->cacheExpiration);
497	} else {
498	$this->allowCaching(false);
499	}
500	}
501
502	/**
503	* Set the processing timestamp context for caching decisions.
504	* This should be set to the timestamp of the event being processed (e.g., $stat['dt'])
505	* to ensure caching decisions are based on the event time, not the current server time.
506	*
507	* @param int\|null $timestamp Unix timestamp of the event being processed, or null to use current time.
508	* @return void
509	*/
510	public static function setProcessingTimestamp($timestamp)
511	{
512	self::$processingTimestamp = $timestamp;
513	}
514
515	/**
516	* Get the timestamp for the start of today.
517	* If a processing timestamp has been set, calculates "today" based on that timestamp.
518	* Otherwise, uses the current server time.
519	*
520	* @return int The timestamp for the start of today (midnight).
521	*/
522	protected function getTodayDate()
523	{
524	if (null !== self::$processingTimestamp) {
525	return strtotime(date('Y-m-d 00:00:00', self::$processingTimestamp));
526	}
527	return strtotime(date('Y-m-d 00:00:00'));
528	}
529
530	protected function getCacheKey($input)
531	{
532	$normalized = $input;
533	if (preg_match('/BETWEEN\s+[\'\"]?(\d{4}-\d{2}-\d{2})[\s\d:][\'\"]?\s+AND\s+[\'\"]?(\d{4}-\d{2}-\d{2})[\s\d:][\'\"]?/i', $input, $matches)) {
534	$from = $matches[1];
535	$to = $matches[2];
536	$normalized = preg_replace('/BETWEEN\s+[\'\"]?(\d{4}-\d{2}-\d{2})[\s\d:][\'\"]?\s+AND\s+[\'\"]?(\d{4}-\d{2}-\d{2})[\s\d:][\'\"]?/i', sprintf("BETWEEN '%s' AND '%s'", $from, $to), $input);
537	}
538
539	$normalized = preg_replace_callback('/(\d{4}-\d{2}-\d{2})[\s\d:]{0,8}/', fn ($m) => $m[1], $normalized);
540	$hash = substr(md5($normalized), 0, 10);
541	return sprintf('wp_slimstat_cache_%s', $hash);
542	}
543
544	protected function getCachedResult($input)
545	{
546	$cacheKey = $this->getCacheKey($input);
547	return get_transient($cacheKey);
548	}
549
550	protected function setCachedResult($input, $result, $expiration = DAY_IN_SECONDS)
551	{
552	$cacheKey = $this->getCacheKey($input);
553	return set_transient($cacheKey, $result, $expiration);
554	}
555
556	/**
557	* Analyzes the WHERE clauses to detect date ranges that overlap with today.
558	*
559	* This function iterates through the WHERE clauses to find any clause that specifies
560	* a date range (using "BETWEEN %s AND %s") and determines if this range overlaps
561	* with today. It extracts the timestamps for the start and end of the historical
562	* period (up to the start of today) and the live period (starting today).
563	*
564	* @return array<int\|bool\|null> An array containing:
565	* - boolean: whether a split range was found
566	* - int\|null: historical start timestamp
567	* - int\|null: historical end timestamp (inclusive)
568	* - int\|null: live start timestamp (inclusive)
569	* - int\|null: live end timestamp
570	*/
571	protected function getSplitDateRanges2()
572	{
573	$dtField = 'dt';
574	$todayStart = $this->getTodayDate();
575	foreach ($this->whereClauses as $idx => $clause) {
576	if (preg_match('/' . $dtField . ' BETWEEN %s AND %s/', $clause)) {
577	$from = null;
578	$to = null;
579	$dtIdx = 0;
580	foreach ($this->whereClauses as $i => $c) {
581	if ($i == $idx) {
582	break;
583	}
584
585	if (preg_match('/%s/', $c)) {
586	$dtIdx += substr_count($c, '%s');
587	}
588	}
589
590	$from = $this->valuesToPrepare[$dtIdx] ?? null;
591	$to = $this->valuesToPrepare[$dtIdx + 1] ?? null;
592	$fromTs = is_numeric($from) ? intval($from) : strtotime($from);
593	$toTs = is_numeric($to) ? intval($to) : strtotime($to);
594	if (null !== $fromTs && null !== $toTs && $fromTs < $todayStart && $toTs >= $todayStart) {
595	return [true, $fromTs, $todayStart - 1, $todayStart, $toTs];
596	}
597	}
598	}
599
600	return [false, null, null, null, null];
601	}
602
603	/**
604	* Helper: Generate a SQL condition based on the given field, operator and value.
605	*
606	* Supported operators: =, !=, >, >=, <, <=, LIKE, NOT LIKE, IN, NOT IN, BETWEEN
607	*
608	* @param string $field Field name
609	* @param string $operator SQL operator
610	* @param mixed $value Value to be used in the condition. Can be a string, int, array or null.
611	*
612	* @return array\|false Array with keys 'condition' and 'values', or false if the condition could not be generated.
613	*
614	* @throws InvalidArgumentException If the operator is not supported.
615	*/
616	protected function generateCondition($field, $operator, $value)
617	{
618	$condition = '';
619	$values = [];
620	switch ($operator) {
621	case '=':
622	case '!=':
623	case '>':
624	case '>=':
625	case '<':
626	case '<=':
627	case 'LIKE':
628	case 'NOT LIKE':
629	$condition = sprintf('%s %s %%s', $field, $operator);
630	$values[] = $value;
631	break;
632	case 'IS':
633	case 'IS NOT':
634	if (is_null($value)) {
635	$condition = sprintf('%s %s NULL', $field, $operator);
636	}
637
638	break;
639	case 'IN':
640	case 'NOT IN':
641	if (is_string($value)) {
642	$value = explode(',', $value);
643	}
644
645	if (!empty($value) && is_array($value) && 1 == count($value)) {
646	$operator = ('IN' === $operator) ? '=' : '!=';
647	return $this->generateCondition($field, $operator, reset($value));
648	}
649
650	if (!empty($value) && is_array($value)) {
651	$placeholders = implode(', ', array_fill(0, count($value), '%s'));
652	$condition = sprintf('%s %s (%s)', $field, $operator, $placeholders);
653	$values = $value;
654	}
655
656	break;
657	case 'BETWEEN':
658	if (is_array($value) && 2 === count($value)) {
659	$condition = sprintf('%s BETWEEN %%s AND %%s', $field);
660	$values = $value;
661	}
662
663	break;
664	default:
665	throw new InvalidArgumentException('Unsupported operator: ' . $operator);
666	}
667
668	if ('' === $condition \|\| '0' === $condition) {
669	return null;
670	}
671
672	return [
673	'condition' => $condition,
674	'values' => $values,
675	];
676	}
677
678	/**
679	* Builds and returns an SQL query string based on the current operation and clauses.
680	*
681	* This function constructs a SQL query by assembling various parts such as the
682	* operation type (select, update, delete, insert, union), join clauses, where
683	* clauses, group by, order by, and limit clauses. It supports conditional logic
684	* to append appropriate SQL syntax based on the operation and provided clauses.
685	*
686	* @return string The constructed SQL query string.
687	*
688	* @throws InvalidArgumentException If the operation type is unknown.
689	*/
690	protected function buildQuery()
691	{
692	switch ($this->operation) {
693	case 'select':
694	$query = sprintf('SELECT %s FROM %s', $this->fields, $this->table);
695	break;
696	case 'update':
697	$operation = $this->ignore ? 'UPDATE IGNORE' : 'UPDATE';
698	$query = sprintf('%s %s SET ', $operation, $this->table) . implode(', ', $this->setClauses);
699	break;
700	case 'delete':
701	$query = 'DELETE FROM ' . $this->table;
702	break;
703	case 'insert':
704	if (empty($this->insertValues)) {
705	return '';
706	}
707
708	$operation = $this->ignore ? 'INSERT IGNORE INTO' : 'INSERT INTO';
709	$sampleRow = $this->insertValues[0];
710	$keys = array_keys($sampleRow);
711	$query = sprintf('%s %s (`%s`) VALUES ', $operation, $this->table, implode('`, `', $keys));
712
713	$valueSets = [];
714	foreach ($this->insertValues as $row) {
715	$placeholders = implode(', ', array_fill(0, count($row), '%s'));
716	$valueSets[] = '(' . $placeholders . ')';
717	foreach ($row as $value) {
718	$this->valuesToPrepare[] = $value;
719	}
720	}
721
722	$query .= implode(', ', $valueSets);
723	break;
724	case 'union':
725	$query = implode(' UNION ', $this->queries);
726	break;
727	default:
728	throw new InvalidArgumentException('Unknown operation');
729	}
730
731	if (!empty($this->joinClauses)) {
732	$query .= ' ' . implode(' ', $this->joinClauses);
733	}
734
735	if (!empty($this->whereClauses)) {
736	$query .= ' WHERE ' . implode(sprintf(' %s ', $this->whereRelation), $this->whereClauses);
737	}
738
739	if (!empty($this->rawWhereClause)) {
740	$wrappedClauses = array_map(fn($clause) => "($clause)", $this->rawWhereClause);
741	if (!empty($this->whereClauses)) {
742	$query .= ' AND ' . implode(' AND ', $wrappedClauses);
743	} else {
744	$query .= ' WHERE ' . implode(' AND ', $wrappedClauses);
745	}
746	}
747
748	if (!empty($this->groupByClause)) {
749	$query .= ' ' . $this->groupByClause;
750	}
751
752	if (!empty($this->havingClauses)) {
753	$query .= ' HAVING ' . implode(' AND ', $this->havingClauses);
754	}
755
756	if (!empty($this->orderClause)) {
757	$query .= ' ' . $this->orderClause;
758	}
759
760	if (!empty($this->limitClause)) {
761	$query .= ' ' . $this->limitClause;
762	}
763
764	return $query;
765	}
766
767	/**
768	* Prepares a query for execution by replacing placeholders with actual values.
769	* Supported placeholders are %i, %s, %f, and %d.
770	* If the query contains more than one placeholder, the $args parameter should be an array with the same number of elements.
771	* If the query contains only one placeholder, the $args parameter can be either an array or a single value.
772	* If the query contains no placeholders, the $args parameter is ignored.
773	*
774	* @param string $query
775	* @param array $args
776	*
777	* @return string The prepared query
778	*/
779	protected function prepareQuery($query, $args = [])
780	{
781	if (preg_match('/%[i\|s\|f\|d]/', $query)) {
782	$placeholder_count = preg_match_all('/%[i\|s\|f\|d]/', $query, $matches);
783	$args_count = is_array($args) ? count($args) : (empty($args) ? 0 : 1);
784	if (1 === $placeholder_count) {
785	$query = is_array($args) ? $this->db->prepare($query, reset($args)) : $this->db->prepare($query, $args);
786	} elseif (is_array($args) && $args_count === $placeholder_count) {
787	$query = $this->db->prepare($query, $args);
788	} else {
789	return $query;
790	}
791	}
792
793	return $query;
794	}
795
796	/**
797	* Generates a cache key for a given query and its arguments.
798	*
799	* This method serializes the query and arguments into a data array,
800	* creates an MD5 hash of the serialized data, and returns a truncated
801	* hash as a unique cache key prefixed with 'wp_slimstat_query_'.
802	*
803	* @param string $query The SQL query.
804	* @param array $args The query arguments.
805	*
806	* @return string The generated cache key.
807	*/
808	protected function getCacheKeyForQuery($query, $args = [])
809	{
810	$data = [
811	'query' => $query,
812	'args' => $args,
813	];
814	$hash = substr(md5(serialize($data)), 0, 16);
815	return 'wp_slimstat_query_' . $hash;
816	}
817
818	/**
819	* Retrieves the cached result for the given query and args
820	*
821	* @param string $query The SQL query
822	* @param array $args The query arguments
823	*
824	* @return mixed The query result, or false if there is no cached result
825	*/
826	protected function getCachedResultForQuery($query, $args = [])
827	{
828	$cacheKey = $this->getCacheKeyForQuery($query, $args);
829	$data = get_transient($cacheKey);
830	if (false === $data) {
831	return false;
832	}
833
834	if (is_array($data) && isset($data['chunks']) && isset($data['size'])) {
835	$chunks = [];
836	for ($i = 0; $i < $data['chunks']; $i++) {
837	$chunk = get_transient($cacheKey . '_' . $i);
838	if (false === $chunk) {
839	return false;
840	}
841
842	$chunks[] = $chunk;
843	}
844
845	$data = implode('', $chunks);
846	} elseif (is_array($data)) {
847	// Data is already an array (from transient), return directly
848	return $data;
849	}
850
851	if (function_exists('gzuncompress') && is_string($data)) {
852	$first2 = substr($data, 0, 2);
853	if ("\x1f\x8b" === $first2 \|\| "\x78\x9c" === $first2 \|\| "\x78\xda" === $first2) {
854	$data = @gzuncompress($data);
855	}
856	}
857
858	// Use JSON decode instead of unserialize for security
859	$decoded = json_decode($data, true);
860	if (json_last_error() === JSON_ERROR_NONE) {
861	return $decoded;
862	}
863
864	// Return false if JSON decode failed (corrupted or legacy data)
865	return false;
866	}
867
868	/**
869	* Sets the transient cache for the given query and args
870	*
871	* @param string $query The SQL query
872	* @param array $args The query arguments
873	* @param mixed $result The query result
874	* @param int $expiration The cache expiration time, in seconds
875	*
876	* @return bool True if cache was successfully set, false otherwise
877	*/
878	protected function setCachedResultForQuery($query, $args, $result, $expiration = 300)
879	{
880	$cacheKey = $this->getCacheKeyForQuery($query, $args);
881	// Use JSON encode instead of serialize for security
882	$data = wp_json_encode($result, JSON_UNESCAPED_UNICODE \| JSON_UNESCAPED_SLASHES);
883	if (false === $data) {
884	return false;
885	}
886
887	$max_chunk_size = 900 * 1024; // 900KB
888	$old_meta = get_transient($cacheKey);
889	if (is_array($old_meta) && isset($old_meta['chunks'])) {
890	for ($i = 0; $i < $old_meta['chunks']; $i++) {
891	delete_transient($cacheKey . '_' . $i);
892	}
893	}
894
895	if (strlen($data) > $max_chunk_size) {
896	$chunks = str_split($data, $max_chunk_size);
897	$meta = [
898	'chunks' => count($chunks),
899	'size' => strlen($data),
900	];
901	if (strlen(wp_json_encode($meta)) > $max_chunk_size) {
902	return false;
903	}
904
905	set_transient($cacheKey, $meta, $expiration);
906	foreach ($chunks as $i => $chunk) {
907	set_transient($cacheKey . '_' . $i, $chunk, $expiration);
908	}
909	} else {
910	set_transient($cacheKey, $data, $expiration);
911	}
912
913	return true;
914	}
915
916	/**
917	* Extracts a date range from the WHERE clause where the range overlaps with today.
918	* Returns an array with the following elements:
919	* - boolean: whether a split range was found
920	* - int: historical start timestamp
921	* - int: historical end timestamp (inclusive)
922	* - int: live start timestamp (inclusive)
923	* - int: live end timestamp
924	* - int: index of the date field in the WHERE clause
925	* - int: index of the WHERE clause with the date range
926	*
927	* @return array<int, int, int, int, int, int, int>
928	*/
929	protected function getSplitDateRanges()
930	{
931	$dtField = 'dt';
932	$todayStart = $this->getTodayDate();
933	foreach ($this->whereClauses as $idx => $clause) {
934	if (preg_match('/' . $dtField . ' BETWEEN %s AND %s/', $clause)) {
935	$dtIdx = 0;
936	foreach ($this->whereClauses as $i => $c) {
937	if ($i == $idx) {
938	break;
939	}
940
941	if (preg_match('/%s/', $c)) {
942	$dtIdx += substr_count($c, '%s');
943	}
944	}
945
946	$from = $this->valuesToPrepare[$dtIdx] ?? null;
947	$to = $this->valuesToPrepare[$dtIdx + 1] ?? null;
948	$fromTs = is_numeric($from) ? intval($from) : strtotime($from);
949	$toTs = is_numeric($to) ? intval($to) : strtotime($to);
950	if (null !== $fromTs && null !== $toTs && $fromTs < $todayStart && $toTs >= $todayStart) {
951	return [true, $fromTs, $todayStart - 1, $todayStart, $toTs, $dtIdx, $idx];
952	}
953	}
954	}
955
956	return [false, null, null, null, null, null, null];
957	}
958
959	/**
960	* Merges two arrays of result rows from the historical and live parts of a query.
961	* If $groupKey is set, the function will group the results by this key and sum the
962	* values in $sumFields for each group. Otherwise, the function will return the
963	* array merge of the two arrays.
964	*
965	* @param array $historical The result rows from the historical part of the query
966	* @param array $live The result rows from the live part of the query
967	* @param string $groupKey The key to group the results by
968	* @param array $sumFields The fields to sum for each group
969	*
970	* @return array The merged and grouped result rows
971	*/
972	protected function mergeGroupResults($historical, $live, $groupKey = null, $sumFields = ['counthits'])
973	{
974	$historical = is_array($historical) ? $historical : [];
975	$live = is_array($live) ? $live : [];
976
977	// If no group key provided, try to determine it from the data
978	if (!$groupKey) {
979	// Try to find a suitable group key from the first row
980	$firstRow = !empty($historical) ? $historical[0] : (!empty($live) ? $live[0] : null);
981	if ($firstRow && is_array($firstRow)) {
982	// Use the first column that's not a sum field
983	foreach (array_keys($firstRow) as $key) {
984	if (!in_array($key, $sumFields)) {
985	$groupKey = $key;
986	break;
987	}
988	}
989	}
990
991	// If still no group key, just merge without grouping
992	if (!$groupKey) {
993	return array_merge($historical, $live);
994	}
995	}
996
997	$result = [];
998	foreach ($historical as $row) {
999	if (isset($row[$groupKey])) {
1000	$key = $row[$groupKey];
1001	$result[$key] = $row;
1002	}
1003	}
1004
1005	foreach ($live as $row) {
1006	if (isset($row[$groupKey])) {
1007	$key = $row[$groupKey];
1008	if (isset($result[$key])) {
1009	foreach ($sumFields as $field) {
1010	if (isset($row[$field])) {
1011	$result[$key][$field] += $row[$field];
1012	}
1013	}
1014	} else {
1015	$result[$key] = $row;
1016	}
1017	}
1018	}
1019
1020	return array_values($result);
1021	}
1022
1023	/**
1024	* Re-sort merged results to honour the original ORDER BY clause.
1025	*
1026	* After mergeGroupResults() sums counts from historical + live partitions,
1027	* the relative ordering may no longer match the SQL ORDER BY (e.g. a row
1028	* whose counthits grew after summing should move up). This method parses
1029	* the stored orderClause and applies an equivalent PHP usort().
1030	*
1031	* Only fields that exist as keys in the result rows are used for sorting;
1032	* SQL expressions (MAX(dt), REPLACE(…), etc.) are silently skipped because
1033	* they don't appear as array keys after $wpdb->get_results().
1034	*
1035	* @param array $results Merged result rows (associative arrays).
1036	*
1037	* @return array Re-sorted rows.
1038	*/
1039	protected function sortMergedResults(array $results): array
1040	{
1041	if (empty($this->orderClause) \|\| empty($results)) {
1042	return $results;
1043	}
1044
1045	// Strip "ORDER BY " prefix → "counthits DESC, resource ASC"
1046	$orderStr = preg_replace('/^ORDER\s+BY\s+/i', '', $this->orderClause);
1047	$parts = array_map('trim', explode(',', $orderStr));
1048
1049	// Determine which fields actually exist in the result rows.
1050	$availableKeys = array_keys($results[0]);
1051
1052	$sortFields = [];
1053	foreach ($parts as $part) {
1054	if (preg_match('/^(.+?)\s+(ASC\|DESC)$/i', $part, $m)) {
1055	$field = $m[1];
1056	$dir = strtoupper($m[2]);
1057
1058	// Resolve SQL aggregate functions to their result-set alias.
1059	// e.g. MAX(dt) → dt (from "MAX(dt) AS dt" in the SELECT).
1060	$resolvedField = $field;
1061	if (!in_array($field, $availableKeys, true) && preg_match('/^(?:MAX\|MIN\|COUNT\|SUM\|AVG)$(\w+)$$/i', $field, $aggMatch)) {
1062	$resolvedField = $aggMatch[1];
1063	}
1064
1065	if (in_array($resolvedField, $availableKeys, true)) {
1066	$sortFields[] = ['field' => $resolvedField, 'dir' => $dir];
1067	}
1068	}
1069	}
1070
1071	if (empty($sortFields)) {
1072	return $results;
1073	}
1074
1075	usort($results, static function ($a, $b) use ($sortFields) {
1076	foreach ($sortFields as $sf) {
1077	$field = $sf['field'];
1078	$valA = $a[$field] ?? null;
1079	$valB = $b[$field] ?? null;
1080
1081	// NULLs sort last regardless of direction.
1082	if (null === $valA && null === $valB) {
1083	continue;
1084	}
1085	if (null === $valA) {
1086	return 1;
1087	}
1088	if (null === $valB) {
1089	return -1;
1090	}
1091
1092	if (is_numeric($valA) && is_numeric($valB)) {
1093	$cmp = ((float) $valA <=> (float) $valB);
1094	} else {
1095	$cmp = strcmp((string) $valA, (string) $valB);
1096	}
1097
1098	if (0 !== $cmp) {
1099	return ('DESC' === $sf['dir']) ? -$cmp : $cmp;
1100	}
1101	}
1102
1103	return 0;
1104	});
1105
1106	return $results;
1107	}
1108
1109	/**
1110	* Helper: Extract date ranges from WHERE clauses
1111	*
1112	* @return array Array of extracted date ranges with keys from, to, clauseIdx, and valueIdx
1113	*/
1114	protected function extractDateRangesFromWhere()
1115	{
1116	$dtField = 'dt';
1117	$ranges = [];
1118	$dtIdx = 0;
1119	foreach ($this->whereClauses as $idx => $clause) {
1120	if (preg_match('/' . $dtField . ' BETWEEN %s AND %s/', $clause)) {
1121	$from = $this->valuesToPrepare[$dtIdx] ?? null;
1122	$to = $this->valuesToPrepare[$dtIdx + 1] ?? null;
1123	$ranges[] = [
1124	'from' => $from,
1125	'to' => $to,
1126	'clauseIdx' => $idx,
1127	'valueIdx' => $dtIdx,
1128	];
1129	}
1130
1131	if (preg_match_all('/%s/', $clause, $m)) {
1132	$dtIdx += count($m[0]);
1133	}
1134	}
1135
1136	return $ranges;
1137	}
1138
1139	/**
1140	* Helper: Process a date range query, splitting it into historical and live parts as needed.
1141	*
1142	* @param int\|string $from Start date (Y-m-d or Y-m-d H:i:s or timestamp)
1143	* @param int\|string $to End date (Y-m-d or Y-m-d H:i:s or timestamp)
1144	* @param array $baseWhereClauses where clauses to use for the query
1145	* @param array $baseValuesToPrepare values to prepare for the query
1146	*
1147	* @return array result set
1148	*/
1149	protected function processDateRange($from, $to, $baseWhereClauses, $baseValuesToPrepare)
1150	{
1151	$todayStart = $this->getTodayDate();
1152	$fromTs = is_numeric($from) ? intval($from) : strtotime($from);
1153	$toTs = is_numeric($to) ? intval($to) : strtotime($to);
1154
1155	if ($fromTs >= $todayStart) {
1156	$liveQuery = clone $this;
1157	$liveQuery->whereClauses = $baseWhereClauses;
1158	$liveQuery->valuesToPrepare = $baseValuesToPrepare;
1159	$liveQuery->whereDate('dt', ['from' => $fromTs, 'to' => $toTs], true);
1160	$liveQuery->allowCaching(false, 0);
1161	return $liveQuery->getAll();
1162	}
1163
1164	if ($toTs < $todayStart) {
1165	$cacheQuery = clone $this;
1166	$cacheQuery->whereClauses = $baseWhereClauses;
1167	$cacheQuery->valuesToPrepare = $baseValuesToPrepare;
1168	$cacheQuery->whereDate('dt', ['from' => $fromTs, 'to' => $toTs]);
1169	$cacheQuery->allowCaching(true, $this->cacheExpiration);
1170	return $cacheQuery->getAll();
1171	}
1172
1173	$histQuery = clone $this;
1174	$histQuery->whereClauses = $baseWhereClauses;
1175	$histQuery->valuesToPrepare = $baseValuesToPrepare;
1176	$histQuery->whereDate('dt', ['from' => $fromTs, 'to' => $todayStart - 1]);
1177	$histQuery->allowCaching(true, $this->cacheExpiration);
1178
1179	$historical = $histQuery->getAll();
1180
1181	$liveQuery = clone $this;
1182	$liveQuery->whereClauses = $baseWhereClauses;
1183	$liveQuery->valuesToPrepare = $baseValuesToPrepare;
1184	$liveQuery->whereDate('dt', ['from' => $todayStart, 'to' => $toTs], true);
1185	$liveQuery->allowCaching(false, 0);
1186
1187	$live = $liveQuery->getAll();
1188
1189	if ($toTs == $todayStart) {
1190	return $historical;
1191	}
1192
1193	if ($todayStart - 1 < $fromTs) {
1194	return $live;
1195	}
1196
1197	return array_merge($historical, $live);
1198	}
1199
1200	/**
1201	* Execute the query and return a single value from the first row
1202	*
1203	* This is a shortcut for `getAll()[0][0]`
1204	*
1205	* @return mixed The value, or false/null if no rows are returned
1206	*/
1207	public function getVar()
1208	{
1209	// When caching is enabled and the date range includes today, skip cache
1210	// to stay consistent with getAll() which always fetches fresh live data.
1211	// Without this, cached scalar values (e.g. $pageviews) become stale while
1212	// getAll() returns fresh grouped data, causing percentage calculations >100%.
1213	$useCache = $this->allowCaching;
1214	if ($useCache) {
1215	[$split] = $this->getSplitDateRanges();
1216	if ($split) {
1217	$useCache = false;
1218	}
1219	}
1220
1221	$query = $this->buildQuery();
1222	$query = $this->prepareQuery($query, $this->valuesToPrepare);
1223	if ($useCache) {
1224	$cachedResult = $this->getCachedResultForQuery($query, $this->valuesToPrepare);
1225	if (false !== $cachedResult) {
1226	return $cachedResult;
1227	}
1228	}
1229
1230	$result = $this->db->get_var($query);
1231	if ($useCache) {
1232	$this->setCachedResultForQuery($query, $this->valuesToPrepare, $result, $this->cacheExpiration);
1233	}
1234
1235	return $result;
1236	}
1237
1238	/**
1239	* Execute the query and return a single row
1240	*
1241	* This is a shortcut for `getAll()[0]`
1242	*
1243	* @return array The row, or false/null if no rows are returned
1244	*/
1245	public function getRow()
1246	{
1247	$useCache = $this->allowCaching;
1248	if ($useCache) {
1249	[$split] = $this->getSplitDateRanges();
1250	if ($split) {
1251	$useCache = false;
1252	}
1253	}
1254
1255	$query = $this->buildQuery();
1256	$query = $this->prepareQuery($query, $this->valuesToPrepare);
1257	if ($useCache) {
1258	$cachedResult = $this->getCachedResultForQuery($query, $this->valuesToPrepare);
1259	if (false !== $cachedResult) {
1260	return $cachedResult;
1261	}
1262	}
1263
1264	$result = $this->db->get_row($query);
1265	if ($useCache) {
1266	$this->setCachedResultForQuery($query, $this->valuesToPrepare, $result, $this->cacheExpiration);
1267	}
1268
1269	return $result;
1270	}
1271
1272	/**
1273	* Execute the query and return a single column
1274	*
1275	* This is a shortcut for `getAll()`
1276	*
1277	* @return array The column, or false/null if no columns are returned
1278	*/
1279	public function getCol()
1280	{
1281	$useCache = $this->allowCaching;
1282	if ($useCache) {
1283	[$split] = $this->getSplitDateRanges();
1284	if ($split) {
1285	$useCache = false;
1286	}
1287	}
1288
1289	$query = $this->buildQuery();
1290	$query = $this->prepareQuery($query, $this->valuesToPrepare);
1291	if ($useCache) {
1292	$cachedResult = $this->getCachedResultForQuery($query, $this->valuesToPrepare);
1293	if (false !== $cachedResult) {
1294	return $cachedResult;
1295	}
1296	}
1297
1298	$result = $this->db->get_col($query);
1299	if ($useCache) {
1300	$this->setCachedResultForQuery($query, $this->valuesToPrepare, $result, $this->cacheExpiration);
1301	}
1302
1303	return $result;
1304	}
1305
1306	/**
1307	* Check if a where clause for a field/operator exists (e.g. 'dt BETWEEN').
1308	*
1309	* @param string $field
1310	* @param string\|null $operator
1311	*
1312	* @return bool
1313	*/
1314	public function hasWhereClause(string $field, ?string $operator = null)
1315	{
1316	foreach ($this->whereClauses as $clause) {
1317	if ($operator) {
1318	if (false !== stripos($clause, sprintf('%s %s', $field, $operator))) {
1319	return true;
1320	}
1321	} elseif (false !== stripos($clause, $field)) {
1322	return true;
1323	}
1324	}
1325
1326	return false;
1327	}
1328
1329	/**
1330	* Add a date range condition and enable cache if possible.
1331	*
1332	* @param string $field
1333	* @param array\|string $date
1334	*
1335	* @return $this
1336	*/
1337	public function whereDate($field, $date, $isLiveQuery = false)
1338	{
1339	if (empty($date)) {
1340	return $this;
1341	}
1342
1343	if (is_array($date)) {
1344	$from = $date['from'] ?? '';
1345	$to = $date['to'] ?? '';
1346	} elseif (is_string($date)) {
1347	$from = $date;
1348	$to = $date;
1349	} else {
1350	return $this;
1351	}
1352
1353	if ('dt' === $field) {
1354	if (!empty($from) && !empty($to)) {
1355	$fromTs = is_numeric($from) ? intval($from) : strtotime($from);
1356	$toTs = is_numeric($to) ? intval($to) : strtotime($to);
1357
1358	$this->whereClauses[] = sprintf('%s BETWEEN %%s AND %%s', $field);
1359	$this->valuesToPrepare[] = $fromTs;
1360	$this->valuesToPrepare[] = $toTs;
1361	$this->canUseCacheForDateRange($toTs);
1362	if ($isLiveQuery) {
1363	$this->_isLiveQuery = true;
1364	}
1365	}
1366	} elseif (!empty($from) && !empty($to)) {
1367	if (10 === strlen($from)) {
1368	$from .= ' 00:00:00';
1369	}
1370
1371	if (10 === strlen($to)) {
1372	$to .= ' 23:59:59';
1373	}
1374
1375	$this->whereClauses[] = sprintf('%s BETWEEN %%s AND %%s', $field);
1376	$this->valuesToPrepare[] = $from;
1377	$this->valuesToPrepare[] = $to;
1378	$this->canUseCacheForDateRange($to);
1379	if ($isLiveQuery) {
1380	$this->_isLiveQuery = true;
1381	}
1382	}
1383
1384	return $this;
1385	}
1386
1387	/**
1388	* Executes a query for operations like INSERT, UPDATE, DELETE.
1389	*
1390	* @return int\|bool Number of affected rows, or false on error. For INSERT, returns the insert ID.
1391	* @throws \Exception
1392	*/
1393	public function execute()
1394	{
1395	if ('select' === $this->operation) {
1396	throw new \Exception('execute() cannot be used for SELECT queries. Use getAll(), getVar(), getRow(), or getCol().');
1397	}
1398
1399	$query = $this->buildQuery();
1400
1401	if (empty($query)) {
1402	return false;
1403	}
1404
1405	// SET values must come before WHERE values to match SQL clause order
1406	$allValues = array_merge($this->setValuesToPrepare, $this->valuesToPrepare);
1407	$prepared_query = $this->prepareQuery($query, $allValues);
1408
1409	$result = $this->db->query($prepared_query);
1410
1411	if ('insert' === $this->operation) {
1412	return $this->db->insert_id ?: $result;
1413	}
1414
1415	return $result;
1416	}
1417
1418	public function getSqlQuery()
1419	{
1420	$query = $this->buildQuery();
1421	// SET values must come before WHERE values to match SQL clause order
1422	$allValues = array_merge($this->setValuesToPrepare, $this->valuesToPrepare);
1423	return $this->prepareQuery($query, $allValues);
1424	}
1425
1426	/**
1427	* Get all results from a query.
1428	* If this is a live query (i.e. the query has a live date range), this function will
1429	* split the query into two parts: a historical part that can be safely cached, and a live
1430	* part that should not be cached.
1431	* If this is not a live query, the function will simply return the result of the query.
1432	*
1433	* @return array The result of the query
1434	*/
1435	public function getAll()
1436	{
1437	if (null !== $this->_isLiveQuery && $this->_isLiveQuery) {
1438	$query = $this->buildQuery();
1439	$query = $this->prepareQuery($query, $this->valuesToPrepare);
1440	return $this->db->get_results($query, ARRAY_A);
1441	}
1442
1443	$ranges = $this->extractDateRangesFromWhere();
1444	if (count($ranges) > 1) {
1445	$results = [];
1446	foreach ($ranges as $range) {
1447	if (empty($range['from']) \|\| empty($range['to'])) {
1448	continue;
1449	}
1450
1451	$baseWhereClauses = $this->whereClauses;
1452	$baseValuesToPrepare = $this->valuesToPrepare;
1453	array_splice($baseWhereClauses, $range['clauseIdx'], 1);
1454	array_splice($baseValuesToPrepare, $range['valueIdx'], 2);
1455	$data = $this->processDateRange($range['from'], $range['to'], $baseWhereClauses, $baseValuesToPrepare);
1456	if (is_array($data)) {
1457	$results = array_merge($results, $data);
1458	}
1459	}
1460
1461	return $results;
1462	}
1463
1464	[$split, $histFrom, $histTo, $liveFrom, $liveTo, $dtIdx, $dtClauseIdx] = $this->getSplitDateRanges();
1465	if ($split) {
1466	$baseWhereClauses = $this->whereClauses;
1467	$baseValuesToPrepare = $this->valuesToPrepare;
1468	array_splice($baseWhereClauses, $dtClauseIdx, 1);
1469	$baseValues = $baseValuesToPrepare;
1470	array_splice($baseValues, $dtIdx, 2);
1471
1472	// Remove OFFSET from sub-queries: each partition is smaller
1473	// than the full date range, so applying the original OFFSET to
1474	// each one independently can skip past all rows in that
1475	// partition. Instead, fetch without OFFSET and apply it after
1476	// merging.
1477	$parsedOffset = 0;
1478	$parsedLimit = 0;
1479	if (preg_match('/LIMIT\s+(\d+)\s+OFFSET\s+(\d+)/i', $this->limitClause, $m)) {
1480	$parsedLimit = intval($m[1]);
1481	$parsedOffset = intval($m[2]);
1482	} elseif (preg_match('/LIMIT\s+(\d+)/i', $this->limitClause, $m)) {
1483	$parsedLimit = intval($m[1]);
1484	}
1485	// Sub-queries fetch up to offset+limit rows (no OFFSET) so we
1486	// have enough data to slice after merging.
1487	$subLimit = $parsedOffset + $parsedLimit;
1488
1489	// Clone for historical
1490	$histQuery = clone $this;
1491	$histQuery->whereClauses = $baseWhereClauses;
1492	$histQuery->valuesToPrepare = $baseValues;
1493	$histQuery->whereDate('dt', ['from' => $histFrom, 'to' => $histTo]);
1494	$histQuery->allowCaching(true, $this->cacheExpiration);
1495	if ($subLimit > 0) {
1496	$histQuery->limit($subLimit);
1497	}
1498	try {
1499	$historical = $histQuery->getAll();
1500	} catch (Exception $e) {
1501	$historical = [];
1502	}
1503
1504	// Clone for live
1505	$liveQuery = clone $this;
1506	$liveQuery->whereClauses = $baseWhereClauses;
1507	$liveQuery->valuesToPrepare = $baseValues;
1508	$liveQuery->whereDate('dt', ['from' => $liveFrom, 'to' => $liveTo], true);
1509	$liveQuery->allowCaching(false, 0);
1510	if ($subLimit > 0) {
1511	$liveQuery->limit($subLimit);
1512	}
1513	try {
1514	$live = $liveQuery->getAll();
1515	} catch (Exception $e) {
1516	$live = [];
1517	}
1518
1519	if (is_array($live)) {
1520	$dtList = array_map(fn ($row) => $row['dt'] ?? null, $live);
1521	}
1522
1523	// Only group-merge when the query has GROUP BY (aggregate queries).
1524	// Raw SELECT queries (e.g. get_recent) must preserve duplicate rows.
1525	if (!empty($this->groupByClause)) {
1526	$merged = $this->mergeGroupResults($live, $historical);
1527	} else {
1528	$merged = array_merge($live, $historical);
1529	}
1530
1531	// Re-sort merged results to honour the original ORDER BY.
1532	// mergeGroupResults() sums counthits but loses sort order.
1533	$merged = $this->sortMergedResults($merged);
1534
1535	if (is_array($merged)) {
1536	$dtList = array_map(fn ($row) => $row['dt'] ?? null, $merged);
1537	}
1538
1539	// Apply the original OFFSET+LIMIT after merging.
1540	// Check $parsedLimit (not $parsedOffset) so "top" reports
1541	// (which use LIMIT without OFFSET) also get capped after the
1542	// two partitions are merged and re-sorted.
1543	if ($parsedLimit > 0 && is_array($merged)) {
1544	$merged = array_slice($merged, $parsedOffset, $parsedLimit);
1545	}
1546
1547	return $merged;
1548	}
1549
1550	$query = $this->buildQuery();
1551	$query = $this->prepareQuery($query, $this->valuesToPrepare);
1552	if ($this->allowCaching) {
1553	try {
1554	$cachedResult = $this->getCachedResultForQuery($query, $this->valuesToPrepare);
1555	} catch (Exception $e) {
1556	$cachedResult = false;
1557	}
1558
1559	if (false !== $cachedResult) {
1560	return $cachedResult;
1561	}
1562	}
1563
1564	try {
1565	$result = $this->db->get_results($query, ARRAY_A);
1566	} catch (Exception $exception) {
1567	$result = [];
1568	}
1569
1570	if ($this->allowCaching) {
1571	try {
1572	$this->setCachedResultForQuery($query, $this->valuesToPrepare, $result, $this->cacheExpiration);
1573	} catch (Exception $exception) {
1574	// ignore
1575	}
1576	}
1577
1578	return $result;
1579	}
1580	}
1581