fluent-community / vendor / wpfluent / framework / src / WPFluent / Database / Query / Grammar.php

Grammar.php in FluentCommunity – Ultra-Fast High-Performance Social Network, Community, LMS & Online Courses 1.0.92, at vendor/wpfluent/framework/src/WPFluent/Database/Query/Grammar.php

1,587 lines 44.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCommunity\Framework\Database\Query;
4
5	use RuntimeException;
6	use FluentCommunity\Framework\Support\Arr;
7	use FluentCommunity\Framework\Support\Helper;
8	use FluentCommunity\Framework\Database\BaseGrammar;
9	use FluentCommunity\Framework\Database\Query\Builder;
10	use FluentCommunity\Framework\Database\Query\Expression;
11	use FluentCommunity\Framework\Database\Query\JoinClause;
12	use FluentCommunity\Framework\Database\Query\JoinLateralClause;
13	use FluentCommunity\Framework\Database\Concerns\CompilesJsonPaths;
14
15	class Grammar extends BaseGrammar
16	{
17	use CompilesJsonPaths;
18
19	/**
20	* The grammar specific operators.
21	*
22	* @var array
23	*/
24	protected $operators = [];
25
26	/**
27	* The grammar specific bitwise operators.
28	*
29	* @var array
30	*/
31	protected $bitwiseOperators = [];
32
33	/**
34	* The components that make up a select clause.
35	*
36	* @var string[]
37	*/
38	protected $selectComponents = [
39	'aggregate',
40	'columns',
41	'from',
42	'indexHint',
43	'joins',
44	'wheres',
45	'groups',
46	'havings',
47	'orders',
48	'limit',
49	'offset',
50	'lock',
51	];
52
53	/**
54	* Compile a select query into SQL.
55	*
56	* @param \FluentCommunity\Framework\Database\Query\Builder $query
57	* @return string
58	*/
59	public function compileSelect(Builder $query)
60	{
61	if (($query->unions \|\| $query->havings) && $query->aggregate) {
62	return $this->compileUnionAggregate($query);
63	}
64
65	// If a "group limit" is in place, we will need to compile the SQL to use a
66	// different syntax. This primarily supports limits on eager loads using
67	// Eloquent. We'll also set the columns if they have not been defined.
68	if (isset($query->groupLimit)) {
69	if (is_null($query->columns)) {
70	$query->columns = ['*'];
71	}
72
73	return $this->compileGroupLimit($query);
74	}
75
76	// If the query does not have any columns set, we'll set the columns to the
77	// * character to just get all of the columns from the database. Then we
78	// can build the query and concatenate all the pieces together as one.
79	$original = $query->columns;
80
81	if (is_null($query->columns)) {
82	$query->columns = ['*'];
83	}
84
85	// To compile the query, we'll spin through each component of the query and
86	// see if that component exists. If it does we'll just call the compiler
87	// function for the component which is responsible for making the SQL.
88	$sql = trim($this->concatenate(
89	$this->compileComponents($query))
90	);
91
92	if ($query->unions) {
93	$sql = $this->wrapUnion($sql).' '.$this->compileUnions($query);
94	}
95
96	$query->columns = $original;
97
98	return $sql;
99	}
100
101	/**
102	* Compile the components necessary for a select clause.
103	*
104	* @param \FluentCommunity\Framework\Database\Query\Builder $query
105	* @return array
106	*/
107	protected function compileComponents(Builder $query)
108	{
109	$sql = [];
110
111	foreach ($this->selectComponents as $component) {
112	if (isset($query->$component)) {
113	$method = 'compile'.ucfirst($component);
114
115	$sql[$component] = $this->$method($query, $query->$component);
116	}
117	}
118
119	return $sql;
120	}
121
122	/**
123	* Compile an aggregated select clause.
124	*
125	* @param \FluentCommunity\Framework\Database\Query\Builder $query
126	* @param array $aggregate
127	* @return string
128	*/
129	protected function compileAggregate(Builder $query, $aggregate)
130	{
131	$column = $this->columnize($aggregate['columns']);
132
133	// If the query has a "distinct" constraint and we're not asking for all columns
134	// we need to prepend "distinct" onto the column name so that the query takes
135	// it into account when it performs the aggregating operations on the data.
136	if (is_array($query->distinct)) {
137	$column = 'distinct '.$this->columnize($query->distinct);
138	} elseif ($query->distinct && $column !== '*') {
139	$column = 'distinct '.$column;
140	}
141
142	return 'select '.$aggregate['function'].'('.$column.') as aggregate';
143	}
144
145	/**
146	* Compile the "select *" portion of the query.
147	*
148	* @param \FluentCommunity\Framework\Database\Query\Builder $query
149	* @param array $columns
150	* @return string\|null
151	*/
152	protected function compileColumns(Builder $query, $columns)
153	{
154	// If the query is actually performing an aggregating select, we will let that
155	// compiler handle the building of the select clauses, as it will need some
156	// more syntax that is best handled by that function to keep things neat.
157	if (! is_null($query->aggregate)) {
158	return;
159	}
160
161	if ($query->distinct) {
162	$select = 'select distinct ';
163	} else {
164	$select = 'select ';
165	}
166
167	return $select.$this->columnize($columns);
168	}
169
170	/**
171	* Compile the "from" portion of the query.
172	*
173	* @param \FluentCommunity\Framework\Database\Query\Builder $query
174	* @param string $table
175	* @return string
176	*/
177	protected function compileFrom(Builder $query, $table)
178	{
179	return 'from '.$this->wrapTable($table);
180	}
181
182	/**
183	* Compile the "join" portions of the query.
184	*
185	* @param \FluentCommunity\Framework\Database\Query\Builder $query
186	* @param array $joins
187	* @return string
188	*/
189	protected function compileJoins(Builder $query, $joins)
190	{
191	return Helper::collect($joins)->map(function ($join) use ($query) {
192	$table = $this->wrapTable($join->table);
193
194	$nestedJoins = is_null($join->joins) ? '' : ' '.$this->compileJoins($query, $join->joins);
195
196	$tableAndNestedJoins = is_null($join->joins) ? $table : '('.$table.$nestedJoins.')';
197
198	if ($join instanceof JoinLateralClause) {
199	return $this->compileJoinLateral($join, $tableAndNestedJoins);
200	}
201
202	return trim("{$join->type} join {$tableAndNestedJoins} {$this->compileWheres($join)}");
203	})->implode(' ');
204	}
205
206	/**
207	* Compile a "lateral join" clause.
208	*
209	* @param \FluentCommunity\Framework\Database\Query\JoinLateralClause $join
210	* @param string $expression
211	* @return string
212	*
213	* @throws \RuntimeException
214	*/
215	public function compileJoinLateral(JoinLateralClause $join, string $expression)
216	{
217	throw new RuntimeException('This database engine does not support lateral joins.');
218	}
219
220	/**
221	* Compile the "where" portions of the query.
222	*
223	* @param \FluentCommunity\Framework\Database\Query\Builder $query
224	* @return string
225	*/
226	public function compileWheres(Builder $query)
227	{
228	// Each type of where clause has its own compiler function, which is responsible
229	// for actually creating the where clauses SQL. This helps keep the code nice
230	// and maintainable since each clause has a very small method that it uses.
231	if (is_null($query->wheres)) {
232	return '';
233	}
234
235	// If we actually have some where clauses, we will strip off the first boolean
236	// operator, which is added by the query builders for convenience so we can
237	// avoid checking for the first clauses in each of the compilers methods.
238	if (count($sql = $this->compileWheresToArray($query)) > 0) {
239	return $this->concatenateWhereClauses($query, $sql);
240	}
241
242	return '';
243	}
244
245	/**
246	* Get an array of all the where clauses for the query.
247	*
248	* @param \FluentCommunity\Framework\Database\Query\Builder $query
249	* @return array
250	*/
251	protected function compileWheresToArray($query)
252	{
253	return Helper::collect($query->wheres)->map(function ($where) use ($query) {
254	return $where['boolean'].' '.$this->{"where{$where['type']}"}($query, $where);
255	})->all();
256	}
257
258	/**
259	* Format the where clause statements into one string.
260	*
261	* @param \FluentCommunity\Framework\Database\Query\Builder $query
262	* @param array $sql
263	* @return string
264	*/
265	protected function concatenateWhereClauses($query, $sql)
266	{
267	$conjunction = $query instanceof JoinClause ? 'on' : 'where';
268
269	return $conjunction.' '.$this->removeLeadingBoolean(implode(' ', $sql));
270	}
271
272	/**
273	* Compile a raw where clause.
274	*
275	* @param \FluentCommunity\Framework\Database\Query\Builder $query
276	* @param array $where
277	* @return string
278	*/
279	protected function whereRaw(Builder $query, $where)
280	{
281	return $where['sql'] instanceof Expression ? $where['sql']->getValue($this) : $where['sql'];
282	}
283
284	/**
285	* Compile a basic where clause.
286	*
287	* @param \FluentCommunity\Framework\Database\Query\Builder $query
288	* @param array $where
289	* @return string
290	*/
291	protected function whereBasic(Builder $query, $where)
292	{
293	$value = $this->parameter($where['value']);
294
295	$operator = str_replace('?', '??', $where['operator']);
296
297	return $this->wrap($where['column']).' '.$operator.' '.$value;
298	}
299
300	/**
301	* Compile a bitwise operator where clause.
302	*
303	* @param \FluentCommunity\Framework\Database\Query\Builder $query
304	* @param array $where
305	* @return string
306	*/
307	protected function whereBitwise(Builder $query, $where)
308	{
309	return $this->whereBasic($query, $where);
310	}
311
312	/**
313	* Compile a "where like" clause.
314	*
315	* @param \FluentCommunity\Framework\Database\Query\Builder $query
316	* @param array $where
317	* @return string
318	*/
319	protected function whereLike(Builder $query, $where)
320	{
321	if ($where['caseSensitive']) {
322	throw new RuntimeException('This database engine does not support case sensitive like operations.');
323	}
324
325	$where['operator'] = $where['not'] ? 'not like' : 'like';
326
327	return $this->whereBasic($query, $where);
328	}
329
330	/**
331	* Compile a "where in" clause.
332	*
333	* @param \FluentCommunity\Framework\Database\Query\Builder $query
334	* @param array $where
335	* @return string
336	*/
337	protected function whereIn(Builder $query, $where)
338	{
339	if (! empty($where['values'])) {
340	return $this->wrap($where['column']).' in ('.$this->parameterize($where['values']).')';
341	}
342
343	return '0 = 1';
344	}
345
346	/**
347	* Compile a "where not in" clause.
348	*
349	* @param \FluentCommunity\Framework\Database\Query\Builder $query
350	* @param array $where
351	* @return string
352	*/
353	protected function whereNotIn(Builder $query, $where)
354	{
355	if (! empty($where['values'])) {
356	return $this->wrap($where['column']).' not in ('.$this->parameterize($where['values']).')';
357	}
358
359	return '1 = 1';
360	}
361
362	/**
363	* Compile a "where not in raw" clause.
364	*
365	* For safety, whereIntegerInRaw ensures this method is only used with integer values.
366	*
367	* @param \FluentCommunity\Framework\Database\Query\Builder $query
368	* @param array $where
369	* @return string
370	*/
371	protected function whereNotInRaw(Builder $query, $where)
372	{
373	if (! empty($where['values'])) {
374	return $this->wrap($where['column']).' not in ('.implode(', ', $where['values']).')';
375	}
376
377	return '1 = 1';
378	}
379
380	/**
381	* Compile a "where in raw" clause.
382	*
383	* For safety, whereIntegerInRaw ensures this method is only used with integer values.
384	*
385	* @param \FluentCommunity\Framework\Database\Query\Builder $query
386	* @param array $where
387	* @return string
388	*/
389	protected function whereInRaw(Builder $query, $where)
390	{
391	if (! empty($where['values'])) {
392	return $this->wrap($where['column']).' in ('.implode(', ', $where['values']).')';
393	}
394
395	return '0 = 1';
396	}
397
398	/**
399	* Compile a "where null" clause.
400	*
401	* @param \FluentCommunity\Framework\Database\Query\Builder $query
402	* @param array $where
403	* @return string
404	*/
405	protected function whereNull(Builder $query, $where)
406	{
407	return $this->wrap($where['column']).' is null';
408	}
409
410	/**
411	* Compile a "where not null" clause.
412	*
413	* @param \FluentCommunity\Framework\Database\Query\Builder $query
414	* @param array $where
415	* @return string
416	*/
417	protected function whereNotNull(Builder $query, $where)
418	{
419	return $this->wrap($where['column']).' is not null';
420	}
421
422	/**
423	* Compile a "between" where clause.
424	*
425	* @param \FluentCommunity\Framework\Database\Query\Builder $query
426	* @param array $where
427	* @return string
428	*/
429	protected function whereBetween(Builder $query, $where)
430	{
431	$between = $where['not'] ? 'not between' : 'between';
432
433	$min = $this->parameter(is_array($where['values']) ? reset($where['values']) : $where['values'][0]);
434
435	$max = $this->parameter(is_array($where['values']) ? end($where['values']) : $where['values'][1]);
436
437	return $this->wrap($where['column']).' '.$between.' '.$min.' and '.$max;
438	}
439
440	/**
441	* Compile a "between" where clause.
442	*
443	* @param \FluentCommunity\Framework\Database\Query\Builder $query
444	* @param array $where
445	* @return string
446	*/
447	protected function whereBetweenColumns(Builder $query, $where)
448	{
449	$between = $where['not'] ? 'not between' : 'between';
450
451	$min = $this->wrap(is_array($where['values']) ? reset($where['values']) : $where['values'][0]);
452
453	$max = $this->wrap(is_array($where['values']) ? end($where['values']) : $where['values'][1]);
454
455	return $this->wrap($where['column']).' '.$between.' '.$min.' and '.$max;
456	}
457
458	/**
459	* Compile a "where date" clause.
460	*
461	* @param \FluentCommunity\Framework\Database\Query\Builder $query
462	* @param array $where
463	* @return string
464	*/
465	protected function whereDate(Builder $query, $where)
466	{
467	return $this->dateBasedWhere('date', $query, $where);
468	}
469
470	/**
471	* Compile a "where time" clause.
472	*
473	* @param \FluentCommunity\Framework\Database\Query\Builder $query
474	* @param array $where
475	* @return string
476	*/
477	protected function whereTime(Builder $query, $where)
478	{
479	return $this->dateBasedWhere('time', $query, $where);
480	}
481
482	/**
483	* Compile a "where day" clause.
484	*
485	* @param \FluentCommunity\Framework\Database\Query\Builder $query
486	* @param array $where
487	* @return string
488	*/
489	protected function whereDay(Builder $query, $where)
490	{
491	return $this->dateBasedWhere('day', $query, $where);
492	}
493
494	/**
495	* Compile a "where month" clause.
496	*
497	* @param \FluentCommunity\Framework\Database\Query\Builder $query
498	* @param array $where
499	* @return string
500	*/
501	protected function whereMonth(Builder $query, $where)
502	{
503	return $this->dateBasedWhere('month', $query, $where);
504	}
505
506	/**
507	* Compile a "where year" clause.
508	*
509	* @param \FluentCommunity\Framework\Database\Query\Builder $query
510	* @param array $where
511	* @return string
512	*/
513	protected function whereYear(Builder $query, $where)
514	{
515	return $this->dateBasedWhere('year', $query, $where);
516	}
517
518	/**
519	* Compile a date based where clause.
520	*
521	* @param string $type
522	* @param \FluentCommunity\Framework\Database\Query\Builder $query
523	* @param array $where
524	* @return string
525	*/
526	protected function dateBasedWhere($type, Builder $query, $where)
527	{
528	$value = $this->parameter($where['value']);
529
530	return $type.'('.$this->wrap($where['column']).') '.$where['operator'].' '.$value;
531	}
532
533	/**
534	* Compile a where clause comparing two columns.
535	*
536	* @param \FluentCommunity\Framework\Database\Query\Builder $query
537	* @param array $where
538	* @return string
539	*/
540	protected function whereColumn(Builder $query, $where)
541	{
542	return $this->wrap($where['first']).' '.$where['operator'].' '.$this->wrap($where['second']);
543	}
544
545	/**
546	* Compile a nested where clause.
547	*
548	* @param \FluentCommunity\Framework\Database\Query\Builder $query
549	* @param array $where
550	* @return string
551	*/
552	protected function whereNested(Builder $query, $where)
553	{
554	// Here we will calculate what portion of the string we need to remove. If this
555	// is a join clause query, we need to remove the "on" portion of the SQL and
556	// if it is a normal query we need to take the leading "where" of queries.
557	$offset = $where['query'] instanceof JoinClause ? 3 : 6;
558
559	return '('.substr($this->compileWheres($where['query']), $offset).')';
560	}
561
562	/**
563	* Compile a where condition with a sub-select.
564	*
565	* @param \FluentCommunity\Framework\Database\Query\Builder $query
566	* @param array $where
567	* @return string
568	*/
569	protected function whereSub(Builder $query, $where)
570	{
571	$select = $this->compileSelect($where['query']);
572
573	return $this->wrap($where['column']).' '.$where['operator']." ($select)";
574	}
575
576	/**
577	* Compile a where exists clause.
578	*
579	* @param \FluentCommunity\Framework\Database\Query\Builder $query
580	* @param array $where
581	* @return string
582	*/
583	protected function whereExists(Builder $query, $where)
584	{
585	return 'exists ('.$this->compileSelect($where['query']).')';
586	}
587
588	/**
589	* Compile a where exists clause.
590	*
591	* @param \FluentCommunity\Framework\Database\Query\Builder $query
592	* @param array $where
593	* @return string
594	*/
595	protected function whereNotExists(Builder $query, $where)
596	{
597	return 'not exists ('.$this->compileSelect($where['query']).')';
598	}
599
600	/**
601	* Compile a where row values condition.
602	*
603	* @param \FluentCommunity\Framework\Database\Query\Builder $query
604	* @param array $where
605	* @return string
606	*/
607	protected function whereRowValues(Builder $query, $where)
608	{
609	$columns = $this->columnize($where['columns']);
610
611	$values = $this->parameterize($where['values']);
612
613	return '('.$columns.') '.$where['operator'].' ('.$values.')';
614	}
615
616	/**
617	* Compile a "where JSON boolean" clause.
618	*
619	* @param \FluentCommunity\Framework\Database\Query\Builder $query
620	* @param array $where
621	* @return string
622	*/
623	protected function whereJsonBoolean(Builder $query, $where)
624	{
625	$column = $this->wrapJsonBooleanSelector($where['column']);
626
627	$value = $this->wrapJsonBooleanValue(
628	$this->parameter($where['value'])
629	);
630
631	return $column.' '.$where['operator'].' '.$value;
632	}
633
634	/**
635	* Compile a "where JSON contains" clause.
636	*
637	* @param \FluentCommunity\Framework\Database\Query\Builder $query
638	* @param array $where
639	* @return string
640	*/
641	protected function whereJsonContains(Builder $query, $where)
642	{
643	$not = $where['not'] ? 'not ' : '';
644
645	return $not.$this->compileJsonContains(
646	$where['column'],
647	$this->parameter($where['value'])
648	);
649	}
650
651	/**
652	* Compile a "JSON contains" statement into SQL.
653	*
654	* @param string $column
655	* @param string $value
656	* @return string
657	*
658	* @throws \RuntimeException
659	*/
660	protected function compileJsonContains($column, $value)
661	{
662	throw new RuntimeException('This database engine does not support JSON contains operations.');
663	}
664
665	/**
666	* Compile a "where JSON overlaps" clause.
667	*
668	* @param \FluentCommunity\Framework\Database\Query\Builder $query
669	* @param array $where
670	* @return string
671	*/
672	protected function whereJsonOverlaps(Builder $query, $where)
673	{
674	$not = $where['not'] ? 'not ' : '';
675
676	return $not.$this->compileJsonOverlaps(
677	$where['column'],
678	$this->parameter($where['value'])
679	);
680	}
681
682	/**
683	* Compile a "JSON overlaps" statement into SQL.
684	*
685	* @param string $column
686	* @param string $value
687	* @return string
688	*
689	* @throws \RuntimeException
690	*/
691	protected function compileJsonOverlaps($column, $value)
692	{
693	throw new RuntimeException('This database engine does not support JSON overlaps operations.');
694	}
695
696	/**
697	* Prepare the binding for a "JSON contains" statement.
698	*
699	* @param mixed $binding
700	* @return string
701	*/
702	public function prepareBindingForJsonContains($binding)
703	{
704	return json_encode($binding, JSON_UNESCAPED_UNICODE);
705	}
706
707	/**
708	* Compile a "where JSON contains key" clause.
709	*
710	* @param \FluentCommunity\Framework\Database\Query\Builder $query
711	* @param array $where
712	* @return string
713	*/
714	protected function whereJsonContainsKey(Builder $query, $where)
715	{
716	$not = $where['not'] ? 'not ' : '';
717
718	return $not.$this->compileJsonContainsKey(
719	$where['column']
720	);
721	}
722
723	/**
724	* Compile a "JSON contains key" statement into SQL.
725	*
726	* @param string $column
727	* @return string
728	*
729	* @throws \RuntimeException
730	*/
731	protected function compileJsonContainsKey($column)
732	{
733	throw new RuntimeException('This database engine does not support JSON contains key operations.');
734	}
735
736	/**
737	* Compile a "where JSON length" clause.
738	*
739	* @param \FluentCommunity\Framework\Database\Query\Builder $query
740	* @param array $where
741	* @return string
742	*/
743	protected function whereJsonLength(Builder $query, $where)
744	{
745	return $this->compileJsonLength(
746	$where['column'],
747	$where['operator'],
748	$this->parameter($where['value'])
749	);
750	}
751
752	/**
753	* Compile a "JSON length" statement into SQL.
754	*
755	* @param string $column
756	* @param string $operator
757	* @param string $value
758	* @return string
759	*
760	* @throws \RuntimeException
761	*/
762	protected function compileJsonLength($column, $operator, $value)
763	{
764	throw new RuntimeException('This database engine does not support JSON length operations.');
765	}
766
767	/**
768	* Compile a "JSON value cast" statement into SQL.
769	*
770	* @param string $value
771	* @return string
772	*/
773	public function compileJsonValueCast($value)
774	{
775	return $value;
776	}
777
778	/**
779	* Compile a "where fulltext" clause.
780	*
781	* @param \FluentCommunity\Framework\Database\Query\Builder $query
782	* @param array $where
783	* @return string
784	*/
785	public function whereFullText(Builder $query, $where)
786	{
787	throw new RuntimeException('This database engine does not support fulltext search operations.');
788	}
789
790	/**
791	* Compile a clause based on an expression.
792	*
793	* @param \FluentCommunity\Framework\Database\Query\Builder $query
794	* @param array $where
795	* @return string
796	*/
797	public function whereExpression(Builder $query, $where)
798	{
799	return $where['column']->getValue($this);
800	}
801
802	/**
803	* Compile the "group by" portions of the query.
804	*
805	* @param \FluentCommunity\Framework\Database\Query\Builder $query
806	* @param array $groups
807	* @return string
808	*/
809	protected function compileGroups(Builder $query, $groups)
810	{
811	return 'group by '.$this->columnize($groups);
812	}
813
814	/**
815	* Compile the "having" portions of the query.
816	*
817	* @param \FluentCommunity\Framework\Database\Query\Builder $query
818	* @return string
819	*/
820	protected function compileHavings(Builder $query)
821	{
822	return 'having '.$this->removeLeadingBoolean(Helper::collect($query->havings)->map(function ($having) {
823	return $having['boolean'].' '.$this->compileHaving($having);
824	})->implode(' '));
825	}
826
827	/**
828	* Compile a single having clause.
829	*
830	* @param array $having
831	* @return string
832	*/
833	protected function compileHaving(array $having)
834	{
835	// If the having clause is "raw", we can just return the clause straight away
836	// without doing any more processing on it. Otherwise, we will compile the
837	// clause into SQL based on the components that make it up from builder.
838	switch ($having['type']) {
839	case 'Raw':
840	return $having['sql'];
841	case 'between':
842	return $this->compileHavingBetween($having);
843	case 'Null':
844	return $this->compileHavingNull($having);
845	case 'NotNull':
846	return $this->compileHavingNotNull($having);
847	case 'bit':
848	return $this->compileHavingBit($having);
849	case 'Expression':
850	return $this->compileHavingExpression($having);
851	case 'Nested':
852	return $this->compileNestedHavings($having);
853	default:
854	return $this->compileBasicHaving($having);
855	}
856	}
857
858	/**
859	* Compile a basic having clause.
860	*
861	* @param array $having
862	* @return string
863	*/
864	protected function compileBasicHaving($having)
865	{
866	$column = $this->wrap($having['column']);
867
868	$parameter = $this->parameter($having['value']);
869
870	return $column.' '.$having['operator'].' '.$parameter;
871	}
872
873	/**
874	* Compile a "between" having clause.
875	*
876	* @param array $having
877	* @return string
878	*/
879	protected function compileHavingBetween($having)
880	{
881	$between = $having['not'] ? 'not between' : 'between';
882
883	$column = $this->wrap($having['column']);
884
885	$min = $this->parameter(head($having['values']));
886
887	$max = $this->parameter(last($having['values']));
888
889	return $column.' '.$between.' '.$min.' and '.$max;
890	}
891
892	/**
893	* Compile a having null clause.
894	*
895	* @param array $having
896	* @return string
897	*/
898	protected function compileHavingNull($having)
899	{
900	$column = $this->wrap($having['column']);
901
902	return $column.' is null';
903	}
904
905	/**
906	* Compile a having not null clause.
907	*
908	* @param array $having
909	* @return string
910	*/
911	protected function compileHavingNotNull($having)
912	{
913	$column = $this->wrap($having['column']);
914
915	return $column.' is not null';
916	}
917
918	/**
919	* Compile a having clause involving a bit operator.
920	*
921	* @param array $having
922	* @return string
923	*/
924	protected function compileHavingBit($having)
925	{
926	$column = $this->wrap($having['column']);
927
928	$parameter = $this->parameter($having['value']);
929
930	return '('.$column.' '.$having['operator'].' '.$parameter.') != 0';
931	}
932
933	/**
934	* Compile a having clause involving an expression.
935	*
936	* @param array $having
937	* @return string
938	*/
939	protected function compileHavingExpression($having)
940	{
941	return $having['column']->getValue($this);
942	}
943
944	/**
945	* Compile a nested having clause.
946	*
947	* @param array $having
948	* @return string
949	*/
950	protected function compileNestedHavings($having)
951	{
952	return '('.substr($this->compileHavings($having['query']), 7).')';
953	}
954
955	/**
956	* Compile the "order by" portions of the query.
957	*
958	* @param \FluentCommunity\Framework\Database\Query\Builder $query
959	* @param array $orders
960	* @return string
961	*/
962	protected function compileOrders(Builder $query, $orders)
963	{
964	if (! empty($orders)) {
965	return 'order by '.implode(', ', $this->compileOrdersToArray($query, $orders));
966	}
967
968	return '';
969	}
970
971	/**
972	* Compile the query orders to an array.
973	*
974	* @param \FluentCommunity\Framework\Database\Query\Builder $query
975	* @param array $orders
976	* @return array
977	*/
978	protected function compileOrdersToArray(Builder $query, $orders)
979	{
980	return array_map(function ($order) {
981	return $order['sql'] ?? $this->wrap($order['column']).' '.$order['direction'];
982	}, $orders);
983	}
984
985	/**
986	* Compile the random statement into SQL.
987	*
988	* @param string\|int $seed
989	* @return string
990	*/
991	public function compileRandom($seed)
992	{
993	return 'RANDOM()';
994	}
995
996	/**
997	* Compile the "limit" portions of the query.
998	*
999	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1000	* @param int $limit
1001	* @return string
1002	*/
1003	protected function compileLimit(Builder $query, $limit)
1004	{
1005	return 'limit '.(int) $limit;
1006	}
1007
1008	/**
1009	* Compile a group limit clause.
1010	*
1011	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1012	* @return string
1013	*/
1014	protected function compileGroupLimit(Builder $query)
1015	{
1016	$selectBindings = array_merge($query->getRawBindings()['select'], $query->getRawBindings()['order']);
1017
1018	$query->setBindings($selectBindings, 'select');
1019	$query->setBindings([], 'order');
1020
1021	$limit = (int) $query->groupLimit['value'];
1022	$offset = $query->offset;
1023
1024	if (isset($offset)) {
1025	$offset = (int) $offset;
1026	$limit += $offset;
1027
1028	$query->offset = null;
1029	}
1030
1031	$components = $this->compileComponents($query);
1032
1033	$components['columns'] .= $this->compileRowNumber(
1034	$query->groupLimit['column'],
1035	$components['orders'] ?? ''
1036	);
1037
1038	unset($components['orders']);
1039
1040	$table = $this->wrap('laravel_table');
1041	$row = $this->wrap('laravel_row');
1042
1043	$sql = $this->concatenate($components);
1044
1045	$sql = 'select * from ('.$sql.') as '.$table.' where '.$row.' <= '.$limit;
1046
1047	if (isset($offset)) {
1048	$sql .= ' and '.$row.' > '.$offset;
1049	}
1050
1051	return $sql.' order by '.$row;
1052	}
1053
1054	/**
1055	* Compile a row number clause.
1056	*
1057	* @param string $partition
1058	* @param string $orders
1059	* @return string
1060	*/
1061	protected function compileRowNumber($partition, $orders)
1062	{
1063	$over = trim('partition by '.$this->wrap($partition).' '.$orders);
1064
1065	return ', row_number() over ('.$over.') as '.$this->wrap('laravel_row');
1066	}
1067
1068	/**
1069	* Compile the "offset" portions of the query.
1070	*
1071	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1072	* @param int $offset
1073	* @return string
1074	*/
1075	protected function compileOffset(Builder $query, $offset)
1076	{
1077	return 'offset '.(int) $offset;
1078	}
1079
1080	/**
1081	* Compile the "union" queries attached to the main query.
1082	*
1083	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1084	* @return string
1085	*/
1086	protected function compileUnions(Builder $query)
1087	{
1088	$sql = '';
1089
1090	foreach ($query->unions as $union) {
1091	$sql .= $this->compileUnion($union);
1092	}
1093
1094	if (! empty($query->unionOrders)) {
1095	$sql .= ' '.$this->compileOrders($query, $query->unionOrders);
1096	}
1097
1098	if (isset($query->unionLimit)) {
1099	$sql .= ' '.$this->compileLimit($query, $query->unionLimit);
1100	}
1101
1102	if (isset($query->unionOffset)) {
1103	$sql .= ' '.$this->compileOffset($query, $query->unionOffset);
1104	}
1105
1106	return ltrim($sql);
1107	}
1108
1109	/**
1110	* Compile a single union statement.
1111	*
1112	* @param array $union
1113	* @return string
1114	*/
1115	protected function compileUnion(array $union)
1116	{
1117	$conjunction = $union['all'] ? ' union all ' : ' union ';
1118
1119	return $conjunction.$this->wrapUnion($union['query']->toSql());
1120	}
1121
1122	/**
1123	* Wrap a union subquery in parentheses.
1124	*
1125	* @param string $sql
1126	* @return string
1127	*/
1128	protected function wrapUnion($sql)
1129	{
1130	return '('.$sql.')';
1131	}
1132
1133	/**
1134	* Compile a union aggregate query into SQL.
1135	*
1136	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1137	* @return string
1138	*/
1139	protected function compileUnionAggregate(Builder $query)
1140	{
1141	$sql = $this->compileAggregate($query, $query->aggregate);
1142
1143	$query->aggregate = null;
1144
1145	return $sql.' from ('.$this->compileSelect($query).') as '.$this->wrapTable('temp_table');
1146	}
1147
1148	/**
1149	* Compile an exists statement into SQL.
1150	*
1151	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1152	* @return string
1153	*/
1154	public function compileExists(Builder $query)
1155	{
1156	$select = $this->compileSelect($query);
1157
1158	return "select exists({$select}) as {$this->wrap('exists')}";
1159	}
1160
1161	/**
1162	* Compile an insert statement into SQL.
1163	*
1164	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1165	* @param array $values
1166	* @return string
1167	*/
1168	public function compileInsert(Builder $query, array $values)
1169	{
1170	// Essentially we will force every insert to be treated as a batch insert which
1171	// simply makes creating the SQL easier for us since we can utilize the same
1172	// basic routine regardless of an amount of records given to us to insert.
1173	$table = $this->wrapTable($query->from);
1174
1175	if (empty($values)) {
1176	return "insert into {$table} default values";
1177	}
1178
1179	if (! is_array(reset($values))) {
1180	$values = [$values];
1181	}
1182
1183	$columns = $this->columnize(array_keys(reset($values)));
1184
1185	// We need to build a list of parameter place-holders of values that are bound
1186	// to the query. Each insert should have the exact same number of parameter
1187	// bindings so we will loop through the record and parameterize them all.
1188	$parameters = Helper::collect($values)->map(function ($record) {
1189	return '('.$this->parameterize($record).')';
1190	})->implode(', ');
1191
1192	return "insert into $table ($columns) values $parameters";
1193	}
1194
1195	/**
1196	* Compile an insert ignore statement into SQL.
1197	*
1198	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1199	* @param array $values
1200	* @return string
1201	*
1202	* @throws \RuntimeException
1203	*/
1204	public function compileInsertOrIgnore(Builder $query, array $values)
1205	{
1206	throw new RuntimeException('This database engine does not support inserting while ignoring errors.');
1207	}
1208
1209	/**
1210	* Compile an insert and get ID statement into SQL.
1211	*
1212	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1213	* @param array $values
1214	* @param string $sequence
1215	* @return string
1216	*/
1217	public function compileInsertGetId(Builder $query, $values, $sequence)
1218	{
1219	return $this->compileInsert($query, $values);
1220	}
1221
1222	/**
1223	* Compile an insert statement using a subquery into SQL.
1224	*
1225	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1226	* @param array $columns
1227	* @param string $sql
1228	* @return string
1229	*/
1230	public function compileInsertUsing(Builder $query, array $columns, string $sql)
1231	{
1232	$table = $this->wrapTable($query->from);
1233
1234	if (empty($columns) \|\| $columns === ['*']) {
1235	return "insert into {$table} $sql";
1236	}
1237
1238	return "insert into {$table} ({$this->columnize($columns)}) $sql";
1239	}
1240
1241	/**
1242	* Compile an insert ignore statement using a subquery into SQL.
1243	*
1244	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1245	* @param array $columns
1246	* @param string $sql
1247	* @return string
1248	*
1249	* @throws \RuntimeException
1250	*/
1251	public function compileInsertOrIgnoreUsing(Builder $query, array $columns, string $sql)
1252	{
1253	throw new RuntimeException('This database engine does not support inserting while ignoring errors.');
1254	}
1255
1256	/**
1257	* Compile an update statement into SQL.
1258	*
1259	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1260	* @param array $values
1261	* @return string
1262	*/
1263	public function compileUpdate(Builder $query, array $values)
1264	{
1265	$table = $this->wrapTable($query->from);
1266
1267	$columns = $this->compileUpdateColumns($query, $values);
1268
1269	$where = $this->compileWheres($query);
1270
1271	return trim(
1272	isset($query->joins)
1273	? $this->compileUpdateWithJoins($query, $table, $columns, $where)
1274	: $this->compileUpdateWithoutJoins($query, $table, $columns, $where)
1275	);
1276	}
1277
1278	/**
1279	* Compile the columns for an update statement.
1280	*
1281	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1282	* @param array $values
1283	* @return string
1284	*/
1285	protected function compileUpdateColumns(Builder $query, array $values)
1286	{
1287	return Helper::collect($values)->map(function ($value, $key) {
1288	return $this->wrap($key).' = '.$this->parameter($value);
1289	})->implode(', ');
1290	}
1291
1292	/**
1293	* Compile an update statement without joins into SQL.
1294	*
1295	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1296	* @param string $table
1297	* @param string $columns
1298	* @param string $where
1299	* @return string
1300	*/
1301	protected function compileUpdateWithoutJoins(Builder $query, $table, $columns, $where)
1302	{
1303	return "update {$table} set {$columns} {$where}";
1304	}
1305
1306	/**
1307	* Compile an update statement with joins into SQL.
1308	*
1309	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1310	* @param string $table
1311	* @param string $columns
1312	* @param string $where
1313	* @return string
1314	*/
1315	protected function compileUpdateWithJoins(Builder $query, $table, $columns, $where)
1316	{
1317	$joins = $this->compileJoins($query, $query->joins);
1318
1319	return "update {$table} {$joins} set {$columns} {$where}";
1320	}
1321
1322	/**
1323	* Compile an "upsert" statement into SQL.
1324	*
1325	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1326	* @param array $values
1327	* @param array $uniqueBy
1328	* @param array $update
1329	* @return string
1330	*
1331	* @throws \RuntimeException
1332	*/
1333	public function compileUpsert(Builder $query, array $values, array $uniqueBy, array $update)
1334	{
1335	throw new RuntimeException('This database engine does not support upserts.');
1336	}
1337
1338	/**
1339	* Prepare the bindings for an update statement.
1340	*
1341	* @param array $bindings
1342	* @param array $values
1343	* @return array
1344	*/
1345	public function prepareBindingsForUpdate(array $bindings, array $values)
1346	{
1347	$cleanBindings = Arr::except($bindings, ['select', 'join']);
1348
1349	$values = Arr::flatten(array_map(fn ($value) => Helper::value($value), $values));
1350
1351	return array_values(
1352	array_merge($bindings['join'], $values, Arr::flatten($cleanBindings))
1353	);
1354	}
1355
1356	/**
1357	* Compile a delete statement into SQL.
1358	*
1359	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1360	* @return string
1361	*/
1362	public function compileDelete(Builder $query)
1363	{
1364	$table = $this->wrapTable($query->from);
1365
1366	$where = $this->compileWheres($query);
1367
1368	return trim(
1369	isset($query->joins)
1370	? $this->compileDeleteWithJoins($query, $table, $where)
1371	: $this->compileDeleteWithoutJoins($query, $table, $where)
1372	);
1373	}
1374
1375	/**
1376	* Compile a delete statement without joins into SQL.
1377	*
1378	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1379	* @param string $table
1380	* @param string $where
1381	* @return string
1382	*/
1383	protected function compileDeleteWithoutJoins(Builder $query, $table, $where)
1384	{
1385	return "delete from {$table} {$where}";
1386	}
1387
1388	/**
1389	* Compile a delete statement with joins into SQL.
1390	*
1391	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1392	* @param string $table
1393	* @param string $where
1394	* @return string
1395	*/
1396	protected function compileDeleteWithJoins(Builder $query, $table, $where)
1397	{
1398	$alias = last(explode(' as ', $table));
1399
1400	$joins = $this->compileJoins($query, $query->joins);
1401
1402	return "delete {$alias} from {$table} {$joins} {$where}";
1403	}
1404
1405	/**
1406	* Prepare the bindings for a delete statement.
1407	*
1408	* @param array $bindings
1409	* @return array
1410	*/
1411	public function prepareBindingsForDelete(array $bindings)
1412	{
1413	return Arr::flatten(
1414	Arr::except($bindings, 'select')
1415	);
1416	}
1417
1418	/**
1419	* Compile a truncate table statement into SQL.
1420	*
1421	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1422	* @return array
1423	*/
1424	public function compileTruncate(Builder $query)
1425	{
1426	return ['truncate table '.$this->wrapTable($query->from) => []];
1427	}
1428
1429	/**
1430	* Compile the lock into SQL.
1431	*
1432	* @param \FluentCommunity\Framework\Database\Query\Builder $query
1433	* @param bool\|string $value
1434	* @return string
1435	*/
1436	protected function compileLock(Builder $query, $value)
1437	{
1438	return is_string($value) ? $value : '';
1439	}
1440
1441	/**
1442	* Compile a query to get the number of open connections for a database.
1443	*
1444	* @return string\|null
1445	*/
1446	public function compileThreadCount()
1447	{
1448	return null;
1449	}
1450
1451	/**
1452	* Determine if the grammar supports savepoints.
1453	*
1454	* @return bool
1455	*/
1456	public function supportsSavepoints()
1457	{
1458	return true;
1459	}
1460
1461	/**
1462	* Compile the SQL statement to define a savepoint.
1463	*
1464	* @param string $name
1465	* @return string
1466	*/
1467	public function compileSavepoint($name)
1468	{
1469	return 'SAVEPOINT '.$name;
1470	}
1471
1472	/**
1473	* Compile the SQL statement to execute a savepoint rollback.
1474	*
1475	* @param string $name
1476	* @return string
1477	*/
1478	public function compileSavepointRollBack($name)
1479	{
1480	return 'ROLLBACK TO SAVEPOINT '.$name;
1481	}
1482
1483	/**
1484	* Wrap the given JSON selector for boolean values.
1485	*
1486	* @param string $value
1487	* @return string
1488	*/
1489	protected function wrapJsonBooleanSelector($value)
1490	{
1491	return $this->wrapJsonSelector($value);
1492	}
1493
1494	/**
1495	* Wrap the given JSON boolean value.
1496	*
1497	* @param string $value
1498	* @return string
1499	*/
1500	protected function wrapJsonBooleanValue($value)
1501	{
1502	return $value;
1503	}
1504
1505	/**
1506	* Concatenate an array of segments, removing empties.
1507	*
1508	* @param array $segments
1509	* @return string
1510	*/
1511	protected function concatenate($segments)
1512	{
1513	return implode(' ', array_filter($segments, function ($value) {
1514	return (string) $value !== '';
1515	}));
1516	}
1517
1518	/**
1519	* Remove the leading boolean from a statement.
1520	*
1521	* @param string $value
1522	* @return string
1523	*/
1524	protected function removeLeadingBoolean($value)
1525	{
1526	return preg_replace('/and \|or /i', '', $value, 1);
1527	}
1528
1529	/**
1530	* Substitute the given bindings into the given raw SQL query.
1531	*
1532	* @param string $sql
1533	* @param array $bindings
1534	* @return string
1535	*/
1536	public function substituteBindingsIntoRawSql($sql, $bindings)
1537	{
1538	$bindings = array_map(fn ($value) => $this->escape($value), $bindings);
1539
1540	$query = '';
1541
1542	$isStringLiteral = false;
1543
1544	for ($i = 0; $i < strlen($sql); $i++) {
1545	$char = $sql[$i];
1546	$nextChar = $sql[$i + 1] ?? null;
1547
1548	// Single quotes can be escaped as '' according to the SQL standard while
1549	// MySQL uses \'. Postgres has operators like ?\| that must get encoded
1550	// in PHP like ??\|. We should skip over the escaped characters here.
1551	if (in_array($char.$nextChar, ["\'", "''", '??'])) {
1552	$query .= $char.$nextChar;
1553	$i += 1;
1554	} elseif ($char === "'") { // Starting / leaving string literal...
1555	$query .= $char;
1556	$isStringLiteral = ! $isStringLiteral;
1557	} elseif ($char === '?' && ! $isStringLiteral) { // Substitutable binding...
1558	$query .= array_shift($bindings) ?? '?';
1559	} else { // Normal character...
1560	$query .= $char;
1561	}
1562	}
1563
1564	return $query;
1565	}
1566
1567	/**
1568	* Get the grammar specific operators.
1569	*
1570	* @return array
1571	*/
1572	public function getOperators()
1573	{
1574	return $this->operators;
1575	}
1576
1577	/**
1578	* Get the grammar specific bitwise operators.
1579	*
1580	* @return array
1581	*/
1582	public function getBitwiseOperators()
1583	{
1584	return $this->bitwiseOperators;
1585	}
1586	}
1587