booktics / base / abstracts / db-query-builder.php

db-query-builder.php in Booktics – Appointment Booking Calendar for Service Businesses 1.0.19, at base/abstracts/db-query-builder.php

455 lines 12.0 KB

Raw Download Zip

1	<?php
2
3	namespace Booktics\Abstracts;
4
5	use wpdb;
6
7	/**
8	* Query builder for Booktics models.
9	*
10	* @property string $table
11	*/
12	class DB_Query_Builder {
13	/** @var wpdb */
14	private $db;
15	/** @var string */
16	private $table;
17	/** @var array */
18	private $conditions = array();
19	/** @var array */
20	private $values = array();
21	/** @var array */
22	private $columns = array( '*' );
23	/** @var int\|null */
24	private $limit = null;
25	/** @var int\|null */
26	private $offset = null;
27
28	private $order_by = array();
29
30	/**
31	* Initialize the query builder with a database connection
32	*
33	* @param wpdb $db WordPress database instance
34	*/
35	public function __construct( wpdb $db ) {
36	$this->db = $db;
37	}
38
39	/**
40	* Set the table name for the query
41	*
42	* @param string $table Table name without prefix
43	*
44	* @return self
45	*/
46	public function table( string $table ): self {
47	$this->table = booktics_get_table_name( $table );
48
49	return $this;
50	}
51
52	/**
53	* Select specific columns for the query
54	*
55	* @param array $columns
56	*
57	* @return self
58	*/
59	public function select( array $columns ): self {
60	$this->columns = $columns;
61
62	return $this;
63	}
64
65	/**
66	* Add a WHERE condition to the query
67	*
68	* @param string $column
69	* @param string $operator
70	* @param mixed $value
71	*
72	* @return self
73	*/
74	public function where( string $column, string $operator, $value ): self {
75	$this->conditions[] = "$column $operator %s";
76	$this->values[] = $value;
77
78	return $this;
79	}
80
81	/**
82	* Add a WHERE IN condition to the query
83	*
84	* @param string $column
85	* @param array $values
86	*
87	* @return self
88	*/
89	public function where_in( string $column, array $values ): self {
90	if ( empty( $values ) ) {
91	return $this;
92	}
93	$placeholders = implode( ',', array_fill( 0, count( $values ), '%s' ) );
94	$this->conditions[] = "$column IN ($placeholders)";
95	$this->values = array_merge( $this->values, $values );
96
97	return $this;
98	}
99
100	/**
101	* Add an OR WHERE condition to the query
102	*
103	* @param string $column
104	* @param string $operator
105	* @param mixed $value
106	*
107	* @return self
108	*/
109	public function or_where( string $column, string $operator, $value ): self {
110	$last = array_pop( $this->conditions );
111	$this->conditions[] = "($last OR $column $operator %s)";
112	$this->values[] = $value;
113
114	return $this;
115	}
116
117	/**
118	* Add a group of AND conditions
119	*
120	* @param callable $callback
121	*
122	* @return self
123	*/
124	public function where_group( callable $callback ): self {
125	$query = clone $this;
126	$query->conditions = array();
127	$query->values = array();
128	$callback( $query );
129	if ( ! empty( $query->conditions ) ) {
130	$group = implode( ' AND ', $query->conditions );
131	$this->conditions[] = "($group)";
132	$this->values = array_merge( $this->values, $query->values );
133	}
134
135	return $this;
136	}
137
138	/**
139	* Add a group of OR conditions
140	*
141	* @param callable $callback
142	*
143	* @return self
144	*/
145	public function or_where_group( callable $callback ): self {
146	$query = clone $this;
147	$query->conditions = array();
148	$query->values = array();
149	$callback( $query );
150	if ( ! empty( $query->conditions ) ) {
151	$group = implode( ' AND ', $query->conditions );
152	$last = array_pop( $this->conditions );
153	$this->conditions[] = "($last OR ($group))";
154	$this->values = array_merge( $this->values, $query->values );
155	}
156
157	return $this;
158	}
159
160	/**
161	* Set the maximum number of records to retrieve
162	*
163	* @param int $limit
164	*
165	* @return self
166	*/
167	public function limit( int $limit ): self {
168	$this->limit = $limit;
169
170	return $this;
171	}
172
173	/**
174	* Set the starting position of records
175	*
176	* @param int $offset
177	*
178	* @return self
179	*/
180	public function offset( int $offset ): self {
181	$this->offset = $offset;
182
183	return $this;
184	}
185
186	/**
187	* Set the ordering condition
188	*
189	* @param array $order_by
190	*
191	* @return self
192	*/
193	public function order_by( array $order_by ): self {
194	$this->order_by = $order_by;
195
196	return $this;
197	}
198
199	/**
200	* Get total count of records
201	*
202	* @return int
203	*/
204	public function count(): int {
205	$sql = "SELECT COUNT(*) as count FROM {$this->table}";
206	if ( $this->conditions ) {
207	$sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
208	}
209	$prepared = $this->db->prepare( $sql, ...$this->values );
210	$result = $this->db->get_row( $prepared );
211	if ( $result === null ) {
212	throw new \RuntimeException( sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
213	}
214
215	return (int) $result->count;
216	}
217
218	/**
219	* Calculate sum of a column
220	*
221	* @param string $column
222	*
223	* @return float
224	*/
225	public function sum( string $column ): float {
226	$sql = "SELECT SUM($column) as sum FROM {$this->table}";
227	if ( $this->conditions ) {
228	$sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
229	}
230	$prepared = $this->db->prepare( $sql, ...$this->values );
231	$result = $this->db->get_row( $prepared );
232	if ( $result === null ) {
233	throw new \RuntimeException( sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
234	}
235
236	return (float) $result->sum;
237	}
238
239	/**
240	* Get query result
241	*
242	* @return array
243	*/
244	public function get(): array {
245	$sql = 'SELECT ' . implode( ', ', $this->columns ) . " FROM {$this->table}";
246	if ( $this->conditions ) {
247	$sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
248	}
249	if ( ! empty( $this->order_by ) ) {
250	$sql .= ' ORDER BY ' . ( $this->order_by[0] ?? 'id' ) . ' ' . ( $this->order_by[1] ?? 'ASC' );
251	}
252	if ( $this->limit !== null ) {
253	$sql .= " LIMIT {$this->limit}";
254	}
255	if ( $this->offset !== null ) {
256	$sql .= " OFFSET {$this->offset}";
257	}
258	$prepared = $this->db->prepare( $sql, ...$this->values );
259	$results = $this->db->get_results( $prepared );
260	if ( $results === null ) {
261	throw new \RuntimeException( esc_html( "Query failed: {$this->db->last_error}" ) );
262	}
263
264	return $results;
265	}
266
267	/**
268	* Get the first record matching the query
269	*
270	* @return object\|null
271	*/
272	public function first(): ?object {
273	$sql = 'SELECT ' . implode( ', ', $this->columns ) . " FROM {$this->table}";
274	if ( $this->conditions ) {
275	$sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
276	}
277	$sql .= ' LIMIT 1';
278	$prepared = $this->db->prepare( $sql, ...$this->values );
279	$result = $this->db->get_row( $prepared );
280	if ( $this->db->last_error ) {
281	if ( function_exists( 'is_wp_error' ) ) {
282	return new \WP_Error( 'db_query_failed', sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
283	}
284	}
285
286	return $result;
287	}
288
289	/**
290	* Convert query result to array
291	*
292	* @return array
293	*/
294	public function to_array(): array {
295	$results = $this->get();
296
297	return array_map(
298	function ( $row ) {
299	return (array) $row;
300	}, $results
301	);
302	}
303
304	/**
305	* Insert data into the table
306	*
307	* @param array $data
308	*
309	* @return int\|false
310	*/
311	public function insert( array $data ) {
312	$sql = $this->prepare_insert( $data );
313	$query_result = $this->db->query( $sql );
314
315	return $query_result ? $this->db->insert_id : false;
316	}
317
318	/**
319	* Prepare SQL INSERT statement
320	*
321	* @param array $data
322	*
323	* @return string
324	*/
325	private function prepare_insert( array $data ): string {
326	$columns = array_keys( $data );
327	$values = array_map(
328	function ( $value ) {
329	if ( is_null( $value ) ) {
330	return 'NULL';
331	}
332	if ( is_numeric( $value ) ) {
333	return $value;
334	}
335
336	return "'" . esc_sql( $value ) . "'";
337	}, array_values( $data )
338	);
339
340	return sprintf(
341	'INSERT INTO %s (%s) VALUES (%s)',
342	$this->table,
343	implode( ', ', $columns ),
344	implode( ', ', $values )
345	);
346	}
347
348	/**
349	* Update records in the table
350	*
351	* @param array $data
352	*
353	* @return bool
354	*/
355	public function update( array $data ): bool {
356	if ( empty( $this->conditions ) ) {
357	throw new \RuntimeException( 'Update operation requires WHERE conditions' );
358	}
359	$set = array();
360	foreach ( $data as $column => $value ) {
361	if ( is_null( $value ) ) {
362	$set[] = "$column = NULL";
363	} elseif ( is_numeric( $value ) ) {
364	$set[] = "$column = $value";
365	} else {
366	$set[] = "$column = '" . esc_sql( $value ) . "'";
367	}
368	}
369	$sql = sprintf(
370	'UPDATE %s SET %s WHERE %s',
371	$this->table,
372	implode( ', ', $set ),
373	implode( ' AND ', $this->conditions )
374	);
375	$prepared = $this->db->prepare( $sql, ...$this->values );
376	$result = $this->db->query( $prepared );
377	if ( $result === false ) {
378	throw new \RuntimeException( sprintf( 'Update failed: %s', esc_html( $this->db->last_error ) ) );
379	}
380
381	return true;
382	}
383
384	/**
385	* Delete records from the table
386	*
387	* @return bool
388	*/
389	public function delete(): bool {
390	if ( empty( $this->conditions ) ) {
391	throw new \RuntimeException( 'Delete operation requires WHERE conditions' );
392	}
393	$sql = sprintf(
394	'DELETE FROM %s WHERE %s',
395	$this->table,
396	implode( ' AND ', $this->conditions )
397	);
398	$prepared = $this->db->prepare( $sql, ...$this->values );
399	$result = $this->db->query( $prepared );
400	if ( $result === false ) {
401	throw new \RuntimeException( sprintf( 'Delete failed: %s', esc_html( $this->db->last_error ) ) );
402	}
403
404	return true;
405	}
406
407	/**
408	* Paginate the query results
409	*
410	* @param int $page
411	* @param int $per_page
412	*
413	* @return array
414	*/
415	public function paginate( int $page = 1, int $per_page = 10 ): array {
416	$total = $this->count();
417	$offset = ( $page - 1 ) * $per_page;
418	$items = $this->limit( $per_page )->offset( $offset )->get();
419
420	return array(
421	'total' => $total,
422	'per_page' => $per_page,
423	'current_page' => $page,
424	'last_page' => (int) ceil( $total / $per_page ),
425	'items' => $items,
426	);
427	}
428
429	/**
430	* Reset the query builder state (for reuse)
431	*
432	* @return self
433	*/
434	public function reset(): self {
435	$this->conditions = array();
436	$this->values = array();
437	$this->columns = array( '*' );
438	$this->limit = null;
439	$this->offset = null;
440	$this->order_by = array();
441
442	return $this;
443	}
444
445	/**
446	* Clone handler for safe query grouping
447	*/
448	public function __clone() {
449	$this->conditions = array_merge( array(), $this->conditions );
450	$this->values = array_merge( array(), $this->values );
451	$this->columns = array_merge( array(), $this->columns );
452	$this->order_by = array_merge( array(), $this->order_by );
453	}
454	}
455