← All changes | base/abstracts/db-query-builder.php +114 -56 1.0.2 → 1.0.28 View file →

		@@ -3,8 +3,13 @@
3	3	namespace Booktics\Abstracts;
4	4
5	5	use wpdb;
6	6
	7	+/**
	8	+ * Query builder for Booktics models.
	9	+ *
	10	+ * @property string $table
	11	+ */
7	12	class DB_Query_Builder {
8	13	/** @var wpdb */
9	14	private $db;
10	15	/** @var string */
		@@ -35,9 +40,9 @@
35	40	* Set the table name for the query
36	41	*
37	42	* @param string $table Table name without prefix
38	43	*
39		- * @return self Returns the current instance for method chaining
	44	+ * @return self
40	45	*/
41	46	public function table( string $table ): self {
42	47	$this->table = booktics_get_table_name( $table );
43	48
		@@ -46,11 +51,11 @@
46	51
47	52	/**
48	53	* Select specific columns for the query
49	54	*
50		- * @param array $columns Array of column names to select
	55	+ * @param array $columns
51	56	*
52		- * @return self Returns the current instance for method chaining
	57	+ * @return self
53	58	*/
54	59	public function select( array $columns ): self {
55	60	$this->columns = $columns;
56	61
		@@ -59,13 +64,13 @@
59	64
60	65	/**
61	66	* Add a WHERE condition to the query
62	67	*
63		- * @param string $column Column name
64		- * @param string $operator Comparison operator (=, >, <, etc.)
65		- * @param mixed $value Value to compare against
	68	+ * @param string $column
	69	+ * @param string $operator
	70	+ * @param mixed $value
66	71	*
67		- * @return self Returns the current instance for method chaining
	72	+ * @return self
68	73	*/
69	74	public function where( string $column, string $operator, $value ): self {
70	75	$this->conditions[] = "$column $operator %s";
71	76	$this->values[] = $value;
		@@ -73,15 +78,34 @@
73	78	return $this;
74	79	}
75	80
76	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	+ /**
77	101	* Add an OR WHERE condition to the query
78	102	*
79		- * @param string $column Column name
80		- * @param string $operator Comparison operator (=, >, <, etc.)
81		- * @param mixed $value Value to compare against
	103	+ * @param string $column
	104	+ * @param string $operator
	105	+ * @param mixed $value
82	106	*
83		- * @return self Returns the current instance for method chaining
	107	+ * @return self
84	108	*/
85	109	public function or_where( string $column, string $operator, $value ): self {
86	110	$last = array_pop( $this->conditions );
87	111	$this->conditions[] = "($last OR $column $operator %s)";
		@@ -90,38 +114,46 @@
90	114	return $this;
91	115	}
92	116
93	117	/**
94		- * add and where with a group of conditions
	118	+ * Add a group of AND conditions
95	119	*
96	120	* @param callable $callback
97	121	*
98		- * @return $this
	122	+ * @return self
99	123	*/
100	124	public function where_group( callable $callback ): self {
101		- $query = new self( $this->db );
	125	+ $query = clone $this;
	126	+ $query->conditions = array();
	127	+ $query->values = array();
102	128	$callback( $query );
103		- $group = implode( ' AND ', $query->conditions );
104		- $this->conditions[] = "($group)";
105		- $this->values = array_merge( $this->values, $query->values );
	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	+ }
106	134
107	135	return $this;
108	136	}
109	137
110	138	/**
111		- * add or where with a group of conditions
	139	+ * Add a group of OR conditions
112	140	*
113	141	* @param callable $callback
114	142	*
115		- * @return $this
	143	+ * @return self
116	144	*/
117	145	public function or_where_group( callable $callback ): self {
118		- $query = new self( $this->db );
	146	+ $query = clone $this;
	147	+ $query->conditions = array();
	148	+ $query->values = array();
119	149	$callback( $query );
120		- $group = implode( ' AND ', $query->conditions );
121		- $last = array_pop( $this->conditions );
122		- $this->conditions[] = "($last OR ($group))";
123		- $this->values = array_merge( $this->values, $query->values );
	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	+ }
124	156
125	157	return $this;
126	158	}
127	159
		@@ -170,16 +202,13 @@
170	202	* @return int
171	203	*/
172	204	public function count(): int {
173	205	$sql = "SELECT COUNT(*) as count FROM {$this->table}";
174		-
175	206	if ( $this->conditions ) {
176	207	$sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
177	208	}
178		-
179	209	$prepared = $this->db->prepare( $sql, ...$this->values );
180	210	$result = $this->db->get_row( $prepared );
181		-
182	211	if ( $result === null ) {
183	212	throw new \RuntimeException( sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
184	213	}
185	214
		@@ -188,22 +217,19 @@
188	217
189	218	/**
190	219	* Calculate sum of a column
191	220	*
192		- * @param string $column Column name to sum
	221	+ * @param string $column
193	222	*
194	223	* @return float
195	224	*/
196	225	public function sum( string $column ): float {
197	226	$sql = "SELECT SUM($column) as sum FROM {$this->table}";
198		-
199	227	if ( $this->conditions ) {
200	228	$sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
201	229	}
202		-
203	230	$prepared = $this->db->prepare( $sql, ...$this->values );
204	231	$result = $this->db->get_row( $prepared );
205		-
206	232	if ( $result === null ) {
207	233	throw new \RuntimeException( sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
208	234	}
209	235
		@@ -216,28 +242,22 @@
216	242	* @return array
217	243	*/
218	244	public function get(): array {
219	245	$sql = 'SELECT ' . implode( ', ', $this->columns ) . " FROM {$this->table}";
220		-
221	246	if ( $this->conditions ) {
222	247	$sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
223	248	}
224		-
225	249	if ( ! empty( $this->order_by ) ) {
226	250	$sql .= ' ORDER BY ' . ( $this->order_by[0] ?? 'id' ) . ' ' . ( $this->order_by[1] ?? 'ASC' );
227	251	}
228		-
229	252	if ( $this->limit !== null ) {
230	253	$sql .= " LIMIT {$this->limit}";
231	254	}
232		-
233	255	if ( $this->offset !== null ) {
234	256	$sql .= " OFFSET {$this->offset}";
235	257	}
236		-
237	258	$prepared = $this->db->prepare( $sql, ...$this->values );
238	259	$results = $this->db->get_results( $prepared );
239		-
240	260	if ( $results === null ) {
241	261	throw new \RuntimeException( esc_html( "Query failed: {$this->db->last_error}" ) );
242	262	}
243	263
		@@ -250,20 +270,18 @@
250	270	* @return object|null
251	271	*/
252	272	public function first(): ?object {
253	273	$sql = 'SELECT ' . implode( ', ', $this->columns ) . " FROM {$this->table}";
254		-
255	274	if ( $this->conditions ) {
256	275	$sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
257	276	}
258		-
259		- $sql .= ' LIMIT 1';
260		-
	277	+ $sql .= ' LIMIT 1';
261	278	$prepared = $this->db->prepare( $sql, ...$this->values );
262	279	$result = $this->db->get_row( $prepared );
263		-
264		- if ( $result === null && $this->db->last_error ) {
265		- throw new \RuntimeException( sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
	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	+ }
266	284	}
267	285
268	286	return $result;
269	287	}
		@@ -290,10 +308,9 @@
290	308	*
291	309	* @return int|false
292	310	*/
293	311	public function insert( array $data ) {
294		- $sql = $this->prepare_insert( $data );
295		-
	312	+ $sql = $this->prepare_insert( $data );
296	313	$query_result = $this->db->query( $sql );
297	314
298	315	return $query_result ? $this->db->insert_id : false;
299	316	}
		@@ -330,9 +347,9 @@
330	347
331	348	/**
332	349	* Update records in the table
333	350	*
334		- * @param array $data Data to update
	351	+ * @param array $data
335	352	*
336	353	* @return bool
337	354	*/
338	355	public function update( array $data ): bool {
		@@ -338,9 +355,8 @@
338	355	public function update( array $data ): bool {
339	356	if ( empty( $this->conditions ) ) {
340	357	throw new \RuntimeException( 'Update operation requires WHERE conditions' );
341	358	}
342		-
343	359	$set = array();
344	360	foreach ( $data as $column => $value ) {
345	361	if ( is_null( $value ) ) {
346	362	$set[] = "$column = NULL";
		@@ -349,19 +365,16 @@
349	365	} else {
350	366	$set[] = "$column = '" . esc_sql( $value ) . "'";
351	367	}
352	368	}
353		-
354		- $sql = sprintf(
	369	+ $sql = sprintf(
355	370	'UPDATE %s SET %s WHERE %s',
356	371	$this->table,
357	372	implode( ', ', $set ),
358	373	implode( ' AND ', $this->conditions )
359	374	);
360		-
361	375	$prepared = $this->db->prepare( $sql, ...$this->values );
362	376	$result = $this->db->query( $prepared );
363		-
364	377	if ( $result === false ) {
365	378	throw new \RuntimeException( sprintf( 'Update failed: %s', esc_html( $this->db->last_error ) ) );
366	379	}
367	380
		@@ -376,21 +389,66 @@
376	389	public function delete(): bool {
377	390	if ( empty( $this->conditions ) ) {
378	391	throw new \RuntimeException( 'Delete operation requires WHERE conditions' );
379	392	}
380		-
381		- $sql = sprintf(
	393	+ $sql = sprintf(
382	394	'DELETE FROM %s WHERE %s',
383	395	$this->table,
384	396	implode( ' AND ', $this->conditions )
385	397	);
386		-
387	398	$prepared = $this->db->prepare( $sql, ...$this->values );
388	399	$result = $this->db->query( $prepared );
389		-
390	400	if ( $result === false ) {
391	401	throw new \RuntimeException( sprintf( 'Delete failed: %s', esc_html( $this->db->last_error ) ) );
392	402	}
393	403
394	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 );
395	453	}
396	454	}