give / src / Framework / QueryBuilder / Concerns / WhereClause.php

WhereClause.php in GiveWP – Donation Plugin and Fundraising Platform 4.16.9, at src/Framework/QueryBuilder/Concerns/WhereClause.php

515 lines 12.3 KB

Raw Download Zip

1	<?php
2
3	namespace Give\Framework\QueryBuilder\Concerns;
4
5	use Closure;
6	use Give\Framework\Database\DB;
7	use Give\Framework\QueryBuilder\Clauses\RawSQL;
8	use Give\Framework\QueryBuilder\Clauses\Where;
9	use Give\Framework\QueryBuilder\QueryBuilder;
10	use Give\Framework\QueryBuilder\Types\Operator;
11	use Give\Framework\QueryBuilder\WhereQueryBuilder;
12
13
14	/**
15	* @since 2.19.0
16	*/
17	trait WhereClause
18	{
19
20	/**
21	* @var Where[]\|RawSQL[]\|string[]
22	*/
23	protected $wheres = [];
24
25
26	/**
27	* @var bool
28	*/
29	private $includeWhereKeyword = true;
30
31	/**
32	* @param string\|Closure\|null $column The Closure will receive a Give\Framework\QueryBuilder\WhereQueryBuilder instance
33	* @param string\|Closure\|array\|null $value The Closure will receive a Give\Framework\QueryBuilder\QueryBuilder instance
34	* @param string $comparisonOperator
35	* @param string $logicalOperator
36	*
37	* @return $this
38	*/
39	private function setWhere($column, $value, $comparisonOperator, $logicalOperator)
40	{
41	// If the columns is a Closure instance, we will assume the developer
42	// wants to begin a nested where statement which is wrapped in parentheses.
43	if ($column instanceof Closure && is_null($value)) {
44	$builder = new WhereQueryBuilder();
45	call_user_func($column, $builder);
46
47	// Since this is a nested where statement, we have to remove the starting WHERE keyword
48	// which is the first returned array element from the getWhereSQL method
49	$wheres = $builder->getSQL();
50	array_shift($wheres);
51
52	$this->wheres[] = sprintf(
53	"%s (%s)",
54	empty($this->wheres) ? null : $logicalOperator,
55	implode(' ', $wheres)
56	);
57	} // If the value is a Closure instance, we will assume the developer is performing an entire sub-select within the query
58	elseif ($value instanceof Closure) {
59	$builder = new QueryBuilder();
60	call_user_func($value, $builder);
61
62	$this->wheres[] = sprintf(
63	"%s %s %s (%s)",
64	empty($this->wheres) ? null : $logicalOperator,
65	$column,
66	$comparisonOperator,
67	$builder->getSQL()
68	);
69	} // Standard WHERE clause
70	else {
71	$this->wheres[] = new Where(
72	$column,
73	$value,
74	$comparisonOperator,
75	empty($this->wheres) ? null : $logicalOperator
76	);
77	}
78
79	return $this;
80	}
81
82	/**
83	* @param string\|Closure $column The closure will receive a Give\Framework\QueryBuilder\WhereQueryBuilder instance
84	* @param string\|Closure\|array\|null $value The closure will receive a Give\Framework\QueryBuilder\QueryBuilder instance
85	* @param string $comparisonOperator
86	*
87	* @return $this
88	*/
89	public function where($column, $value = null, $comparisonOperator = '=')
90	{
91	return $this->setWhere(
92	$column,
93	$value,
94	$comparisonOperator,
95	Operator::_AND
96	);
97	}
98
99	/**
100	* @param string\|Closure $column
101	* @param string\|Closure\|array\|null $value
102	* @param string $comparisonOperator
103	*
104	* @return $this
105	*/
106	public function orWhere($column, $value = null, $comparisonOperator = '=')
107	{
108	return $this->setWhere(
109	$column,
110	$value,
111	$comparisonOperator,
112	Operator::_OR
113	);
114	}
115
116	/**
117	* @param string $column
118	* @param array\|Closure $value
119	*
120	* @return $this
121	*/
122	public function whereIn($column, $value)
123	{
124	return $this->where(
125	$column,
126	$value,
127	Operator::IN
128	);
129	}
130
131
132	/**
133	* @param string $column
134	* @param array\|Closure $value
135	*
136	* @return $this
137	*/
138	public function orWhereIn($column, $value)
139	{
140	return $this->orWhere(
141	$column,
142	$value,
143	Operator::IN
144	);
145	}
146
147	/**
148	* @param string $column
149	* @param array\|Closure $value
150	*
151	* @return $this
152	*/
153	public function whereNotIn($column, $value)
154	{
155	return $this->where(
156	$column,
157	$value,
158	Operator::NOTIN
159	);
160	}
161
162	/**
163	* @param string $column
164	* @param array\|Closure $value
165	*
166	* @return $this
167	*/
168	public function orWhereNotIn($column, $value)
169	{
170	return $this->orWhere(
171	$column,
172	$value,
173	Operator::NOTIN
174	);
175	}
176
177	/**
178	* @param string $column
179	* @param string\|int $min
180	* @param string\|int $max
181	*
182	* @return $this
183	*/
184	public function whereBetween($column, $min, $max)
185	{
186	return $this->where(
187	$column,
188	[$min, $max],
189	Operator::BETWEEN
190	);
191	}
192
193	/**
194	* @param string $column
195	* @param string\|int $min
196	* @param string\|int $max
197	*
198	* @return $this
199	*/
200	public function whereNotBetween($column, $min, $max)
201	{
202	return $this->where(
203	$column,
204	[$min, $max],
205	Operator::NOTBETWEEN
206	);
207	}
208
209	/**
210	* @param string $column
211	* @param string\|int $min
212	* @param string\|int $max
213	*
214	* @return $this
215	*/
216	public function orWhereBetween($column, $min, $max)
217	{
218	return $this->orWhere(
219	$column,
220	[$min, $max],
221	Operator::BETWEEN
222	);
223	}
224
225	/**
226	* @param string $column
227	* @param string\|int $min
228	* @param string\|int $max
229	*
230	* @return $this
231	*/
232	public function orWhereNotBetween($column, $min, $max)
233	{
234	return $this->orWhere(
235	$column,
236	[$min, $max],
237	Operator::NOTBETWEEN
238	);
239	}
240
241	/**
242	* @param string $column
243	* @param string $value
244	*
245	* @return $this
246	*/
247	public function whereLike($column, $value)
248	{
249	return $this->where(
250	$column,
251	$value,
252	Operator::LIKE
253	);
254	}
255
256	/**
257	* @param string $column
258	* @param string $value
259	*
260	* @return $this
261	*/
262	public function whereNotLike($column, $value)
263	{
264	return $this->where(
265	$column,
266	$value,
267	Operator::NOTLIKE
268	);
269	}
270
271	/**
272	* @param string $column
273	* @param string $value
274	*
275	* @return $this
276	*/
277	public function orWhereLike($column, $value)
278	{
279	return $this->orWhere(
280	$column,
281	$value,
282	Operator::LIKE
283	);
284	}
285
286	/**
287	* @param string $column
288	* @param string $value
289	*
290	* @return $this
291	*/
292	public function orWhereNotLike($column, $value)
293	{
294	return $this->orWhere(
295	$column,
296	$value,
297	Operator::NOTLIKE
298	);
299	}
300
301	/**
302	* @param string $column
303	*
304	* @return $this
305	*/
306	public function whereIsNull($column)
307	{
308	return $this->where(
309	$column,
310	null,
311	Operator::ISNULL
312	);
313	}
314
315	/**
316	* @param string $column
317	*
318	* @return $this
319	*/
320	public function orWhereIsNull($column)
321	{
322	return $this->orWhere(
323	$column,
324	null,
325	Operator::ISNULL
326	);
327	}
328
329	/**
330	* @param string $column
331	*
332	* @return $this
333	*/
334	public function whereIsNotNull($column)
335	{
336	return $this->where(
337	$column,
338	null,
339	Operator::NOTNULL
340	);
341	}
342
343	/**
344	* @param string $column
345	*
346	* @return $this
347	*/
348	public function orWhereIsNotNull($column)
349	{
350	return $this->orWhere(
351	$column,
352	null,
353	Operator::NOTNULL
354	);
355	}
356
357
358	/**
359	* @param Closure $callback The closure will receive a Give\Framework\QueryBuilder\QueryBuilder instance
360	*
361	* @return QueryBuilder\|WhereQueryBuilder
362	*/
363	public function whereExists($callback)
364	{
365	return $this->where(
366	null,
367	$callback,
368	Operator::EXISTS
369	);
370	}
371
372	/**
373	* @param Closure $callback The closure will receive a Give\Framework\QueryBuilder\QueryBuilder instance
374	*
375	* @return QueryBuilder\|WhereQueryBuilder
376	*/
377	public function whereNotExists($callback)
378	{
379	return $this->where(
380	null,
381	$callback,
382	Operator::NOTEXISTS
383	);
384	}
385
386	/**
387	* Add raw SQL WHERE clause
388	*
389	* @param $sql
390	* @param ...$args
391	*
392	* @return $this
393	*/
394	public function whereRaw($sql, ...$args)
395	{
396	$this->wheres[] = new RawSQL($sql, $args);
397
398	return $this;
399	}
400
401	/**
402	* @return string[]
403	*/
404	protected function getWhereSQL()
405	{
406	// Bailout
407	if (empty($this->wheres)) {
408	return [];
409	}
410
411	$wheres = [];
412
413	foreach ($this->wheres as $i => $where) {
414	if ($where instanceof RawSQL) {
415	if ($i === 0) {
416	// If the first element is an instance of RawSQL
417	// then we don't need the starting WHERE keyword because we assume that the dev will include that in RawSQL
418	$this->includeWhereKeyword = false;
419	}
420	$wheres[] = $where->sql;
421	continue;
422	}
423
424	if ($where instanceof Where) {
425	$wheres[] = $this->buildWhereSQL($where);
426	continue;
427	}
428
429	// If the variable $where is not an instance of the Where class
430	// it means the SQL is already generated by the Query Builder, so we just return that
431	$wheres[] = $where;
432	}
433
434
435	if ($this->includeWhereKeyword) {
436	return array_merge(['WHERE'], $wheres);
437	}
438
439	return $wheres;
440	}
441
442	/**
443	* @param Where $where
444	*
445	* @return string
446	*/
447	private function buildWhereSQL(Where $where)
448	{
449	switch ($where->comparisonOperator) {
450	// Handle membership conditions
451	case Operator::IN:
452	case Operator::NOTIN:
453
454	return DB::prepare(
455	"%1s %2s %3s",
456	$where->logicalOperator,
457	$where->column,
458	$where->comparisonOperator
459	) . ' (' . implode(
460	',',
461	array_map(function ($where) {
462	return DB::prepare('%s', $where);
463	}, $where->value)
464	) . ')';
465
466	// Handle BETWEEN conditions
467	case Operator::BETWEEN:
468	case Operator::NOTBETWEEN:
469	list($min, $max) = $where->value;
470
471	return DB::prepare(
472	"%1s %2s %3s %s AND %s",
473	$where->logicalOperator,
474	$where->column,
475	$where->comparisonOperator,
476	$min,
477	$max
478	);
479
480	// Handle LIKE conditions
481	case Operator::LIKE:
482	case Operator::NOTLIKE:
483	return DB::prepare(
484	"%1s %2s %3s %s",
485	$where->logicalOperator,
486	$where->column,
487	$where->comparisonOperator,
488	strpos($where->value, '%') !== false
489	? $where->value
490	: '%' . $where->value . '%'
491	);
492
493	// Handle NULL conditions
494	case Operator::ISNULL:
495	case Operator::NOTNULL:
496	return DB::prepare(
497	"%1s %2s %3s",
498	$where->logicalOperator,
499	$where->column,
500	$where->comparisonOperator
501	);
502
503	// Standard WHERE clause
504	default:
505	return DB::prepare(
506	"%1s %2s %3s %s",
507	$where->logicalOperator,
508	$where->column,
509	$where->comparisonOperator,
510	$where->value
511	);
512	}
513	}
514	}
515