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

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

549 lines 15.4 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCommunity\Framework\Database\Query;
4
5	use FluentCommunity\Framework\Support\Str;
6	use FluentCommunity\Framework\Support\Helper;
7	use FluentCommunity\Framework\Database\Query\Builder;
8	use FluentCommunity\Framework\Database\Query\JoinLateralClause;
9
10
11	class MySqlGrammar extends Grammar
12	{
13	/**
14	* The grammar specific operators.
15	*
16	* @var string[]
17	*/
18	protected $operators = ['sounds like'];
19
20	/**
21	* Compile a "where like" clause.
22	*
23	* @param \FluentCommunity\Framework\Database\Query\Builder $query
24	* @param array $where
25	* @return string
26	*/
27	protected function whereLike(Builder $query, $where)
28	{
29	$where['operator'] = $where['not'] ? 'not ' : '';
30
31	$where['operator'] .= $where['caseSensitive'] ? 'like binary' : 'like';
32
33	return $this->whereBasic($query, $where);
34	}
35
36	/**
37	* Add a "where null" clause to the query.
38	*
39	* @param \FluentCommunity\Framework\Database\Query\Builder $query
40	* @param array $where
41	* @return string
42	*/
43	protected function whereNull(Builder $query, $where)
44	{
45	$columnValue = (string) $this->getValue($where['column']);
46
47	if ($this->isJsonSelector($columnValue)) {
48	[$field, $path] = $this->wrapJsonFieldAndPath($columnValue);
49
50	return '(json_extract('.$field.$path.') is null OR json_type(json_extract('.$field.$path.')) = \'NULL\')';
51	}
52
53	return parent::whereNull($query, $where);
54	}
55
56	/**
57	* Add a "where not null" clause to the query.
58	*
59	* @param \FluentCommunity\Framework\Database\Query\Builder $query
60	* @param array $where
61	* @return string
62	*/
63	protected function whereNotNull(Builder $query, $where)
64	{
65	$columnValue = (string) $this->getValue($where['column']);
66
67	if ($this->isJsonSelector($columnValue)) {
68	[$field, $path] = $this->wrapJsonFieldAndPath($columnValue);
69
70	return '(json_extract('.$field.$path.') is not null AND json_type(json_extract('.$field.$path.')) != \'NULL\')';
71	}
72
73	return parent::whereNotNull($query, $where);
74	}
75
76	/**
77	* Compile a "where fulltext" clause.
78	*
79	* @param \FluentCommunity\Framework\Database\Query\Builder $query
80	* @param array $where
81	* @return string
82	*/
83	public function whereFullText(Builder $query, $where)
84	{
85	$columns = $this->columnize($where['columns']);
86
87	$value = $this->parameter($where['value']);
88
89	$mode = ($where['options']['mode'] ?? []) === 'boolean'
90	? ' in boolean mode'
91	: ' in natural language mode';
92
93	$expanded = ($where['options']['expanded'] ?? []) && ($where['options']['mode'] ?? []) !== 'boolean'
94	? ' with query expansion'
95	: '';
96
97	return "match ({$columns}) against (".$value."{$mode}{$expanded})";
98	}
99
100	/**
101	* Compile the index hints for the query.
102	*
103	* @param \FluentCommunity\Framework\Database\Query\Builder $query
104	* @param \FluentCommunity\Framework\Database\Query\IndexHint $indexHint
105	* @return string
106	*/
107	protected function compileIndexHint(Builder $query, $indexHint)
108	{
109	switch ($indexHint->type) {
110	case 'hint':
111	return "use index ({$indexHint->index})";
112	case 'force':
113	return "force index ({$indexHint->index})";
114	default:
115	return "ignore index ({$indexHint->index})";
116	}
117	}
118
119	/**
120	* Compile a group limit clause.
121	*
122	* @param \FluentCommunity\Framework\Database\Query\Builder $query
123	* @return string
124	*/
125	protected function compileGroupLimit(Builder $query)
126	{
127	return $this->useLegacyGroupLimit($query)
128	? $this->compileLegacyGroupLimit($query)
129	: parent::compileGroupLimit($query);
130	}
131
132	/**
133	* Determine whether to use a legacy group limit clause for MySQL < 8.0.
134	*
135	* @param \FluentCommunity\Framework\Database\Query\Builder $query
136	* @return bool
137	*/
138	public function useLegacyGroupLimit(Builder $query)
139	{
140	$version = $query->getConnection()->getServerVersion();
141
142	return ! $query->getConnection()->isMaria() && version_compare(
143	$version, '8.0.11'
144	) < 0;
145	}
146
147	/**
148	* Compile a group limit clause for MySQL < 8.0.
149	*
150	* Derived from https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/.
151	*
152	* @param \FluentCommunity\Framework\Database\Query\Builder $query
153	* @return string
154	*/
155	protected function compileLegacyGroupLimit(Builder $query)
156	{
157	$limit = (int) $query->groupLimit['value'];
158	$offset = $query->offset;
159
160	if (isset($offset)) {
161	$offset = (int) $offset;
162	$limit += $offset;
163
164	$query->offset = null;
165	}
166
167	$column = Helper::last(explode('.', $query->groupLimit['column']));
168	$column = $this->wrap($column);
169
170	$partition = ', @laravel_row := if(@laravel_group = '.$column.', @laravel_row + 1, 1) as `laravel_row`';
171	$partition .= ', @laravel_group := '.$column;
172
173	$orders = (array) $query->orders;
174
175	array_unshift($orders, [
176	'column' => $query->groupLimit['column'],
177	'direction' => 'asc',
178	]);
179
180	$query->orders = $orders;
181
182	$components = $this->compileComponents($query);
183
184	$sql = $this->concatenate($components);
185
186	$from = '(select @laravel_row := 0, @laravel_group := 0) as `laravel_vars`, ('.$sql.') as `laravel_table`';
187
188	$sql = 'select `laravel_table`.*'.$partition.' from '.$from.' having `laravel_row` <= '.$limit;
189
190	if (isset($offset)) {
191	$sql .= ' and `laravel_row` > '.$offset;
192	}
193
194	return $sql.' order by `laravel_row`';
195	}
196
197	/**
198	* Compile an insert ignore statement into SQL.
199	*
200	* @param \FluentCommunity\Framework\Database\Query\Builder $query
201	* @param array $values
202	* @return string
203	*/
204	public function compileInsertOrIgnore(Builder $query, array $values)
205	{
206	return Str::replaceFirst(
207	'insert', 'insert ignore', $this->compileInsert($query, $values)
208	);
209	}
210
211	/**
212	* Compile an insert ignore statement using a subquery into SQL.
213	*
214	* @param \FluentCommunity\Framework\Database\Query\Builder $query
215	* @param array $columns
216	* @param string $sql
217	* @return string
218	*/
219	public function compileInsertOrIgnoreUsing(
220	Builder $query,
221	array $columns,
222	string $sql
223	) {
224	return Str::replaceFirst(
225	'insert', 'insert ignore',
226	$this->compileInsertUsing($query, $columns, $sql)
227	);
228	}
229
230	/**
231	* Compile a "JSON contains" statement into SQL.
232	*
233	* @param string $column
234	* @param string $value
235	* @return string
236	*/
237	protected function compileJsonContains($column, $value)
238	{
239	[$field, $path] = $this->wrapJsonFieldAndPath($column);
240
241	return 'json_contains('.$field.', '.$value.$path.')';
242	}
243
244	/**
245	* Compile a "JSON overlaps" statement into SQL.
246	*
247	* @param string $column
248	* @param string $value
249	* @return string
250	*/
251	protected function compileJsonOverlaps($column, $value)
252	{
253	[$field, $path] = $this->wrapJsonFieldAndPath($column);
254
255	return 'json_overlaps('.$field.', '.$value.$path.')';
256	}
257
258	/**
259	* Compile a "JSON contains key" statement into SQL.
260	*
261	* @param string $column
262	* @return string
263	*/
264	protected function compileJsonContainsKey($column)
265	{
266	[$field, $path] = $this->wrapJsonFieldAndPath($column);
267
268	return 'ifnull(json_contains_path('.$field.', \'one\''.$path.'), 0)';
269	}
270
271	/**
272	* Compile a "JSON length" statement into SQL.
273	*
274	* @param string $column
275	* @param string $operator
276	* @param string $value
277	* @return string
278	*/
279	protected function compileJsonLength($column, $operator, $value)
280	{
281	[$field, $path] = $this->wrapJsonFieldAndPath($column);
282
283	return 'json_length('.$field.$path.') '.$operator.' '.$value;
284	}
285
286	/**
287	* Compile a "JSON value cast" statement into SQL.
288	*
289	* @param string $value
290	* @return string
291	*/
292	public function compileJsonValueCast($value)
293	{
294	return 'cast('.$value.' as json)';
295	}
296
297	/**
298	* Compile the random statement into SQL.
299	*
300	* @param string $seed
301	* @return string
302	*/
303	public function compileRandom($seed)
304	{
305	return 'RAND('.$seed.')';
306	}
307
308	/**
309	* Compile the lock into SQL.
310	*
311	* @param \FluentCommunity\Framework\Database\Query\Builder $query
312	* @param bool\|string $value
313	* @return string
314	*/
315	protected function compileLock(Builder $query, $value)
316	{
317	if (! is_string($value)) {
318	return $value ? 'for update' : 'lock in share mode';
319	}
320
321	return $value;
322	}
323
324	/**
325	* Compile an insert statement into SQL.
326	*
327	* @param \FluentCommunity\Framework\Database\Query\Builder $query
328	* @param array $values
329	* @return string
330	*/
331	public function compileInsert(Builder $query, array $values)
332	{
333	if (empty($values)) {
334	$values = [[]];
335	}
336
337	return parent::compileInsert($query, $values);
338	}
339
340	/**
341	* Compile the columns for an update statement.
342	*
343	* @param \FluentCommunity\Framework\Database\Query\Builder $query
344	* @param array $values
345	* @return string
346	*/
347	protected function compileUpdateColumns(Builder $query, array $values)
348	{
349	return Helper::collect($values)->map(function ($value, $key) {
350	if ($this->isJsonSelector($key)) {
351	return $this->compileJsonUpdateColumn($key, $value);
352	}
353
354	return $this->wrap($key).' = '.$this->parameter($value);
355	})->implode(', ');
356	}
357
358	/**
359	* Compile an "upsert" statement into SQL.
360	*
361	* @param \FluentCommunity\Framework\Database\Query\Builder $query
362	* @param array $values
363	* @param array $uniqueBy
364	* @param array $update
365	* @return string
366	*/
367	public function compileUpsert(
368	Builder $query,
369	array $values,
370	array $uniqueBy,
371	array $update
372	) {
373	$useUpsertAlias = $query->connection->getConfig('use_upsert_alias');
374
375	$sql = $this->compileInsert($query, $values);
376
377	if ($useUpsertAlias) {
378	$sql .= ' as laravel_upsert_alias';
379	}
380
381	$sql .= ' on duplicate key update ';
382
383	$columns = Helper::collect($update)->map(function ($value, $key) use ($useUpsertAlias) {
384	if (! is_numeric($key)) {
385	return $this->wrap($key).' = '.$this->parameter($value);
386	}
387
388	return $useUpsertAlias
389	? $this->wrap($value).' = '.$this->wrap('laravel_upsert_alias').'.'.$this->wrap($value)
390	: $this->wrap($value).' = values('.$this->wrap($value).')';
391	})->implode(', ');
392
393	return $sql.$columns;
394	}
395
396	/**
397	* Compile a "lateral join" clause.
398	*
399	* @param \FluentCommunity\Framework\Database\Query\JoinLateralClause $join
400	* @param string $expression
401	* @return string
402	*/
403	public function compileJoinLateral(
404	JoinLateralClause $join,
405	string $expression
406	) {
407	return trim("{$join->type} join lateral {$expression} on true");
408	}
409
410	/**
411	* Prepare a JSON column being updated using the JSON_SET function.
412	*
413	* @param string $key
414	* @param mixed $value
415	* @return string
416	*/
417	protected function compileJsonUpdateColumn($key, $value)
418	{
419	if (is_bool($value)) {
420	$value = $value ? 'true' : 'false';
421	} elseif (is_array($value)) {
422	$value = 'cast(? as json)';
423	} else {
424	$value = $this->parameter($value);
425	}
426
427	[$field, $path] = $this->wrapJsonFieldAndPath($key);
428
429	return "{$field} = json_set({$field}{$path}, {$value})";
430	}
431
432	/**
433	* Compile an update statement without joins into SQL.
434	*
435	* @param \FluentCommunity\Framework\Database\Query\Builder $query
436	* @param string $table
437	* @param string $columns
438	* @param string $where
439	* @return string
440	*/
441	protected function compileUpdateWithoutJoins(Builder $query, $table, $columns, $where)
442	{
443	$sql = parent::compileUpdateWithoutJoins($query, $table, $columns, $where);
444
445	if (! empty($query->orders)) {
446	$sql .= ' '.$this->compileOrders($query, $query->orders);
447	}
448
449	if (isset($query->limit)) {
450	$sql .= ' '.$this->compileLimit($query, $query->limit);
451	}
452
453	return $sql;
454	}
455
456	/**
457	* Prepare the bindings for an update statement.
458	*
459	* Booleans, integers, and doubles are inserted into JSON updates as raw values.
460	*
461	* @param array $bindings
462	* @param array $values
463	* @return array
464	*/
465	public function prepareBindingsForUpdate(array $bindings, array $values)
466	{
467	$values = Helper::collect($values)->reject(function ($value, $column) {
468	return $this->isJsonSelector($column) && is_bool($value);
469	})->map(function ($value) {
470	return is_array($value) ? json_encode($value) : $value;
471	})->all();
472
473	return parent::prepareBindingsForUpdate($bindings, $values);
474	}
475
476	/**
477	* Compile a delete query that does not use joins.
478	*
479	* @param \FluentCommunity\Framework\Database\Query\Builder $query
480	* @param string $table
481	* @param string $where
482	* @return string
483	*/
484	protected function compileDeleteWithoutJoins(Builder $query, $table, $where)
485	{
486	$sql = parent::compileDeleteWithoutJoins($query, $table, $where);
487
488	// When using MySQL, delete statements may contain order by statements and limits
489	// so we will compile both of those here. Once we have finished compiling this
490	// we will return the completed SQL statement so it will be executed for us.
491	if (! empty($query->orders)) {
492	$sql .= ' '.$this->compileOrders($query, $query->orders);
493	}
494
495	if (isset($query->limit)) {
496	$sql .= ' '.$this->compileLimit($query, $query->limit);
497	}
498
499	return $sql;
500	}
501
502	/**
503	* Compile a query to get the number of open connections for a database.
504	*
505	* @return string
506	*/
507	public function compileThreadCount()
508	{
509	return 'select variable_value as `Value` from performance_schema.session_status where variable_name = \'threads_connected\'';
510	}
511
512	/**
513	* Wrap a single string in keyword identifiers.
514	*
515	* @param string $value
516	* @return string
517	*/
518	protected function wrapValue($value)
519	{
520	return $value === '*' ? $value : '`'.str_replace('`', '``', $value).'`';
521	}
522
523	/**
524	* Wrap the given JSON selector.
525	*
526	* @param string $value
527	* @return string
528	*/
529	protected function wrapJsonSelector($value)
530	{
531	[$field, $path] = $this->wrapJsonFieldAndPath($value);
532
533	return 'json_unquote(json_extract('.$field.$path.'))';
534	}
535
536	/**
537	* Wrap the given JSON selector for boolean values.
538	*
539	* @param string $value
540	* @return string
541	*/
542	protected function wrapJsonBooleanSelector($value)
543	{
544	[$field, $path] = $this->wrapJsonFieldAndPath($value);
545
546	return 'json_extract('.$field.$path.')';
547	}
548	}
549