1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Pixie\QueryBuilder; |
4
|
|
|
|
5
|
|
|
class JsonQueryBuilder extends QueryBuilderHandler |
6
|
|
|
{ |
7
|
|
|
/** |
8
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
9
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
10
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
11
|
|
|
* @param mixed|null $value |
12
|
|
|
* @return static |
13
|
|
|
*/ |
14
|
|
|
public function whereJson($key, $jsonKey, $operator = null, $value = null): self |
15
|
|
|
{ |
16
|
|
|
// If two params are given then assume operator is = |
17
|
|
|
if (3 === func_num_args()) { |
18
|
|
|
$value = $operator; |
19
|
|
|
$operator = '='; |
20
|
|
|
} |
21
|
|
|
|
22
|
|
|
return $this->whereJsonHandler($key, $jsonKey, $operator, $value, 'AND'); |
23
|
|
|
} |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
27
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
28
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
29
|
|
|
* @param mixed|null $value |
30
|
|
|
* @return static |
31
|
|
|
*/ |
32
|
|
|
public function whereNotJson($key, $jsonKey, $operator = null, $value = null): self |
33
|
|
|
{ |
34
|
|
|
// If two params are given then assume operator is = |
35
|
|
|
if (3 === func_num_args()) { |
36
|
|
|
$value = $operator; |
37
|
|
|
$operator = '='; |
38
|
|
|
} |
39
|
|
|
|
40
|
|
|
return $this->whereJsonHandler($key, $jsonKey, $operator, $value, 'AND NOT'); |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
45
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
46
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
47
|
|
|
* @param mixed|null $value |
48
|
|
|
* @return static |
49
|
|
|
*/ |
50
|
|
|
public function orWhereJson($key, $jsonKey, $operator = null, $value = null): self |
51
|
|
|
{ |
52
|
|
|
// If two params are given then assume operator is = |
53
|
|
|
if (3 === func_num_args()) { |
54
|
|
|
$value = $operator; |
55
|
|
|
$operator = '='; |
56
|
|
|
} |
57
|
|
|
|
58
|
|
|
return $this->whereJsonHandler($key, $jsonKey, $operator, $value, 'OR'); |
59
|
|
|
} |
60
|
|
|
|
61
|
|
|
/** |
62
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
63
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
64
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
65
|
|
|
* @param mixed|null $value |
66
|
|
|
* @return static |
67
|
|
|
*/ |
68
|
|
|
public function orWhereNotJson($key, $jsonKey, $operator = null, $value = null): self |
69
|
|
|
{ |
70
|
|
|
// If two params are given then assume operator is = |
71
|
|
|
if (3 === func_num_args()) { |
72
|
|
|
$value = $operator; |
73
|
|
|
$operator = '='; |
74
|
|
|
} |
75
|
|
|
|
76
|
|
|
return $this->whereJsonHandler($key, $jsonKey, $operator, $value, 'OR NOT'); |
77
|
|
|
} |
78
|
|
|
|
79
|
|
|
/** |
80
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
81
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
82
|
|
|
* @param mixed[] $values |
83
|
|
|
* @return static |
84
|
|
|
*/ |
85
|
|
|
public function whereInJson($key, $jsonKey, $values): self |
86
|
|
|
{ |
87
|
|
|
return $this->whereJsonHandler($key, $jsonKey, 'IN', $values, 'AND'); |
88
|
|
|
} |
89
|
|
|
|
90
|
|
|
/** |
91
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
92
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
93
|
|
|
* @param mixed[] $values |
94
|
|
|
* @return static |
95
|
|
|
*/ |
96
|
|
|
public function whereNotInJson($key, $jsonKey, $values): self |
97
|
|
|
{ |
98
|
|
|
return $this->whereJsonHandler($key, $jsonKey, 'NOT IN', $values, 'AND'); |
99
|
|
|
} |
100
|
|
|
|
101
|
|
|
/** |
102
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
103
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
104
|
|
|
* @param mixed[] $values |
105
|
|
|
* @return static |
106
|
|
|
*/ |
107
|
|
|
public function orWhereInJson($key, $jsonKey, $values): self |
108
|
|
|
{ |
109
|
|
|
return $this->whereJsonHandler($key, $jsonKey, 'IN', $values, 'OR'); |
110
|
|
|
} |
111
|
|
|
|
112
|
|
|
/** |
113
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
114
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
115
|
|
|
* @param mixed[] $values |
116
|
|
|
* @return static |
117
|
|
|
*/ |
118
|
|
|
public function orWhereNotInJson($key, $jsonKey, $values): self |
119
|
|
|
{ |
120
|
|
|
return $this->whereJsonHandler($key, $jsonKey, 'NOT IN', $values, 'OR'); |
121
|
|
|
} |
122
|
|
|
|
123
|
|
|
/** |
124
|
|
|
* @param string|Raw $key |
125
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
126
|
|
|
* @param mixed $valueFrom |
127
|
|
|
* @param mixed $valueTo |
128
|
|
|
* |
129
|
|
|
* @return static |
130
|
|
|
*/ |
131
|
|
|
public function whereBetweenJson($key, $jsonKey, $valueFrom, $valueTo): self |
132
|
|
|
{ |
133
|
|
|
return $this->whereJsonHandler($key, $jsonKey, 'BETWEEN', [$valueFrom, $valueTo], 'AND'); |
134
|
|
|
} |
135
|
|
|
|
136
|
|
|
/** |
137
|
|
|
* @param string|Raw $key |
138
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
139
|
|
|
* @param mixed $valueFrom |
140
|
|
|
* @param mixed $valueTo |
141
|
|
|
* |
142
|
|
|
* @return static |
143
|
|
|
*/ |
144
|
|
|
public function orWhereBetweenJson($key, $jsonKey, $valueFrom, $valueTo): self |
145
|
|
|
{ |
146
|
|
|
return $this->whereJsonHandler($key, $jsonKey, 'BETWEEN', [$valueFrom, $valueTo], 'OR'); |
147
|
|
|
} |
148
|
|
|
|
149
|
|
|
/** |
150
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
151
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
152
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
153
|
|
|
* @param mixed|null $value |
154
|
|
|
* @return static |
155
|
|
|
*/ |
156
|
|
|
public function whereDayJson($key, $jsonKey, $operator = null, $value = null): self |
157
|
|
|
{ |
158
|
|
|
// If two params are given then assume operator is = |
159
|
|
|
if (3 === func_num_args()) { |
160
|
|
|
$value = $operator; |
161
|
|
|
$operator = '='; |
162
|
|
|
} |
163
|
|
|
return $this->whereFunctionCallJsonHandler($key, $jsonKey, 'DAY', $operator, $value); |
164
|
|
|
} |
165
|
|
|
|
166
|
|
|
/** |
167
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
168
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
169
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
170
|
|
|
* @param mixed|null $value |
171
|
|
|
* @return static |
172
|
|
|
*/ |
173
|
|
|
public function whereMonthJson($key, $jsonKey, $operator = null, $value = null): self |
174
|
|
|
{ |
175
|
|
|
// If two params are given then assume operator is = |
176
|
|
|
if (3 === func_num_args()) { |
177
|
|
|
$value = $operator; |
178
|
|
|
$operator = '='; |
179
|
|
|
} |
180
|
|
|
return $this->whereFunctionCallJsonHandler($key, $jsonKey, 'MONTH', $operator, $value); |
181
|
|
|
} |
182
|
|
|
|
183
|
|
|
/** |
184
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
185
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
186
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
187
|
|
|
* @param mixed|null $value |
188
|
|
|
* @return static |
189
|
|
|
*/ |
190
|
|
|
public function whereYearJson($key, $jsonKey, $operator = null, $value = null): self |
191
|
|
|
{ |
192
|
|
|
// If two params are given then assume operator is = |
193
|
|
|
if (3 === func_num_args()) { |
194
|
|
|
$value = $operator; |
195
|
|
|
$operator = '='; |
196
|
|
|
} |
197
|
|
|
return $this->whereFunctionCallJsonHandler($key, $jsonKey, 'YEAR', $operator, $value); |
198
|
|
|
} |
199
|
|
|
|
200
|
|
|
/** |
201
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
202
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
203
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
204
|
|
|
* @param mixed|null $value |
205
|
|
|
* @return static |
206
|
|
|
*/ |
207
|
|
|
public function whereDateJson($key, $jsonKey, $operator = null, $value = null): self |
208
|
|
|
{ |
209
|
|
|
// If two params are given then assume operator is = |
210
|
|
|
if (3 === func_num_args()) { |
211
|
|
|
$value = $operator; |
212
|
|
|
$operator = '='; |
213
|
|
|
} |
214
|
|
|
return $this->whereFunctionCallJsonHandler($key, $jsonKey, 'DATE', $operator, $value); |
215
|
|
|
} |
216
|
|
|
|
217
|
|
|
/** |
218
|
|
|
* Maps a function call for a JSON where condition |
219
|
|
|
* |
220
|
|
|
* @param string|Raw $key |
221
|
|
|
* @param string|Raw|string[] $jsonKey |
222
|
|
|
* @param string $function |
223
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
224
|
|
|
* @param mixed|null $value |
225
|
|
|
* @return static |
226
|
|
|
*/ |
227
|
|
|
protected function whereFunctionCallJsonHandler($key, $jsonKey, $function, $operator, $value): self |
228
|
|
|
{ |
229
|
|
|
// Handle potential raw values. |
230
|
|
|
if ($key instanceof Raw) { |
231
|
|
|
$key = $this->adapterInstance->parseRaw($key); |
232
|
|
|
} |
233
|
|
|
if ($jsonKey instanceof Raw) { |
234
|
|
|
$jsonKey = $this->adapterInstance->parseRaw($jsonKey); |
235
|
|
|
} |
236
|
|
|
|
237
|
|
|
return $this->whereFunctionCallHandler( |
|
|
|
|
238
|
|
|
$this->jsonHandler->jsonExpressionFactory()->extractAndUnquote($key, $jsonKey), |
239
|
|
|
$function, |
240
|
|
|
$operator, |
241
|
|
|
$value |
242
|
|
|
); |
243
|
|
|
} |
244
|
|
|
|
245
|
|
|
/** |
246
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
247
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
248
|
|
|
* @param string|mixed|null $operator Can be used as value, if 3rd arg not passed |
249
|
|
|
* @param mixed|null $value |
250
|
|
|
* @param string $joiner |
251
|
|
|
* @return static |
252
|
|
|
*/ |
253
|
|
|
protected function whereJsonHandler($key, $jsonKey, $operator = null, $value = null, string $joiner = 'AND'): self |
254
|
|
|
{ |
255
|
|
|
// Handle potential raw values. |
256
|
|
|
if ($key instanceof Raw) { |
257
|
|
|
$key = $this->adapterInstance->parseRaw($key); |
258
|
|
|
} |
259
|
|
|
if ($jsonKey instanceof Raw) { |
260
|
|
|
$jsonKey = $this->adapterInstance->parseRaw($jsonKey); |
261
|
|
|
} |
262
|
|
|
|
263
|
|
|
return $this->whereHandler( |
264
|
|
|
$this->jsonHandler->jsonExpressionFactory()->extractAndUnquote($key, $jsonKey), |
265
|
|
|
$operator, |
266
|
|
|
$value, |
267
|
|
|
$joiner |
268
|
|
|
); |
269
|
|
|
} |
270
|
|
|
|
271
|
|
|
/** |
272
|
|
|
* @param string|Raw $table |
273
|
|
|
* @param string|Raw $remoteColumn |
274
|
|
|
* @param string|Raw|string[]|null $remoteJsonKeys The json key/index to search |
275
|
|
|
* @param string $operator |
276
|
|
|
* @param string|Raw $localColumn |
277
|
|
|
* @param string|Raw|string[]|null $localJsonKeys |
278
|
|
|
* @param string $type |
279
|
|
|
* |
280
|
|
|
* @return static |
281
|
|
|
*/ |
282
|
|
|
public function joinJson( |
283
|
|
|
$table, |
284
|
|
|
$remoteColumn, |
285
|
|
|
$remoteJsonKeys, |
286
|
|
|
string $operator, |
287
|
|
|
$localColumn, |
288
|
|
|
$localJsonKeys, |
289
|
|
|
$type = 'inner' |
290
|
|
|
): self { |
291
|
|
|
// Convert key if json |
292
|
|
|
if (null !== $localJsonKeys) { |
293
|
|
|
$localColumn = $this->jsonHandler->jsonExpressionFactory()->extractAndUnquote($localColumn, $localJsonKeys); |
294
|
|
|
} |
295
|
|
|
|
296
|
|
|
// Convert key if json |
297
|
|
|
if (null !== $remoteJsonKeys) { |
298
|
|
|
$remoteColumn = $this->jsonHandler->jsonExpressionFactory()->extractAndUnquote($remoteColumn, $remoteJsonKeys); |
299
|
|
|
} |
300
|
|
|
|
301
|
|
|
return $this->join($table, $remoteColumn, $operator, $localColumn, $type); |
302
|
|
|
} |
303
|
|
|
|
304
|
|
|
/** |
305
|
|
|
* @param string|Raw $table |
306
|
|
|
* @param string|Raw $remoteColumn |
307
|
|
|
* @param string|Raw|string[]|null $remoteJsonKeys The json key/index to search |
308
|
|
|
* @param string $operator |
309
|
|
|
* @param string|Raw $localColumn |
310
|
|
|
* @param string|Raw|string[]|null $localJsonKeys |
311
|
|
|
* |
312
|
|
|
* @return static |
313
|
|
|
*/ |
314
|
|
|
public function leftJoinJson( |
315
|
|
|
$table, |
316
|
|
|
$remoteColumn, |
317
|
|
|
$remoteJsonKeys, |
318
|
|
|
string $operator, |
319
|
|
|
$localColumn, |
320
|
|
|
$localJsonKeys |
321
|
|
|
): self { |
322
|
|
|
return $this->joinJson( |
323
|
|
|
$table, |
324
|
|
|
$remoteColumn, |
325
|
|
|
$remoteJsonKeys, |
326
|
|
|
$operator, |
327
|
|
|
$localColumn, |
328
|
|
|
$localJsonKeys, |
329
|
|
|
'left' |
330
|
|
|
); |
331
|
|
|
} |
332
|
|
|
|
333
|
|
|
/** |
334
|
|
|
* @param string|Raw $table |
335
|
|
|
* @param string|Raw $remoteColumn |
336
|
|
|
* @param string|Raw|string[]|null $remoteJsonKeys The json key/index to search |
337
|
|
|
* @param string $operator |
338
|
|
|
* @param string|Raw $localColumn |
339
|
|
|
* @param string|Raw|string[]|null $localJsonKeys |
340
|
|
|
* |
341
|
|
|
* @return static |
342
|
|
|
*/ |
343
|
|
|
public function rightJoinJson( |
344
|
|
|
$table, |
345
|
|
|
$remoteColumn, |
346
|
|
|
$remoteJsonKeys, |
347
|
|
|
string $operator, |
348
|
|
|
$localColumn, |
349
|
|
|
$localJsonKeys |
350
|
|
|
): self { |
351
|
|
|
return $this->joinJson( |
352
|
|
|
$table, |
353
|
|
|
$remoteColumn, |
354
|
|
|
$remoteJsonKeys, |
355
|
|
|
$operator, |
356
|
|
|
$localColumn, |
357
|
|
|
$localJsonKeys, |
358
|
|
|
'right' |
359
|
|
|
); |
360
|
|
|
} |
361
|
|
|
|
362
|
|
|
/** |
363
|
|
|
* @param string|Raw $table |
364
|
|
|
* @param string|Raw $remoteColumn |
365
|
|
|
* @param string|Raw|string[]|null $remoteJsonKeys The json key/index to search |
366
|
|
|
* @param string $operator |
367
|
|
|
* @param string|Raw $localColumn |
368
|
|
|
* @param string|Raw|string[]|null $localJsonKeys |
369
|
|
|
* |
370
|
|
|
* @return static |
371
|
|
|
*/ |
372
|
|
|
public function outerJoinJson( |
373
|
|
|
$table, |
374
|
|
|
$remoteColumn, |
375
|
|
|
$remoteJsonKeys, |
376
|
|
|
string $operator, |
377
|
|
|
$localColumn, |
378
|
|
|
$localJsonKeys |
379
|
|
|
): self { |
380
|
|
|
return $this->joinJson( |
381
|
|
|
$table, |
382
|
|
|
$remoteColumn, |
383
|
|
|
$remoteJsonKeys, |
384
|
|
|
$operator, |
385
|
|
|
$localColumn, |
386
|
|
|
$localJsonKeys, |
387
|
|
|
'outer' |
388
|
|
|
); |
389
|
|
|
} |
390
|
|
|
|
391
|
|
|
/** |
392
|
|
|
* @param string|Raw $table |
393
|
|
|
* @param string|Raw $remoteColumn |
394
|
|
|
* @param string|Raw|string[]|null $remoteJsonKeys The json key/index to search |
395
|
|
|
* @param string $operator |
396
|
|
|
* @param string|Raw $localColumn |
397
|
|
|
* @param string|Raw|string[]|null $localJsonKeys |
398
|
|
|
* |
399
|
|
|
* @return static |
400
|
|
|
*/ |
401
|
|
|
public function crossJoinJson( |
402
|
|
|
$table, |
403
|
|
|
$remoteColumn, |
404
|
|
|
$remoteJsonKeys, |
405
|
|
|
string $operator, |
406
|
|
|
$localColumn, |
407
|
|
|
$localJsonKeys |
408
|
|
|
): self { |
409
|
|
|
return $this->joinJson( |
410
|
|
|
$table, |
411
|
|
|
$remoteColumn, |
412
|
|
|
$remoteJsonKeys, |
413
|
|
|
$operator, |
414
|
|
|
$localColumn, |
415
|
|
|
$localJsonKeys, |
416
|
|
|
'cross' |
417
|
|
|
); |
418
|
|
|
} |
419
|
|
|
|
420
|
|
|
|
421
|
|
|
|
422
|
|
|
// JSON |
423
|
|
|
|
424
|
|
|
/** |
425
|
|
|
* @param string|Raw $key The database column which holds the JSON value |
426
|
|
|
* @param string|Raw|string[] $jsonKey The json key/index to search |
427
|
|
|
* @param string|null $alias The alias used to define the value in results, if not defined will use json_{$jsonKey} |
428
|
|
|
* @return static |
429
|
|
|
*/ |
430
|
|
|
public function selectJson($key, $jsonKey, ?string $alias = null): self |
431
|
|
|
{ |
432
|
|
|
// Handle potential raw values. |
433
|
|
|
if ($key instanceof Raw) { |
434
|
|
|
$key = $this->adapterInstance->parseRaw($key); |
435
|
|
|
} |
436
|
|
|
if ($jsonKey instanceof Raw) { |
437
|
|
|
$jsonKey = $this->adapterInstance->parseRaw($jsonKey); |
438
|
|
|
} |
439
|
|
|
|
440
|
|
|
// If deeply nested jsonKey. |
441
|
|
|
if (is_array($jsonKey)) { |
442
|
|
|
$jsonKey = \implode('.', $jsonKey); |
443
|
|
|
} |
444
|
|
|
|
445
|
|
|
// Add any possible prefixes to the key |
446
|
|
|
$key = $this->addTablePrefix($key, true); |
447
|
|
|
|
448
|
|
|
$alias = null === $alias ? "json_{$jsonKey}" : $alias; |
449
|
|
|
return $this->select(new Raw("JSON_UNQUOTE(JSON_EXTRACT({$key}, \"$.{$jsonKey}\")) as {$alias}")); |
450
|
|
|
} |
451
|
|
|
} |
452
|
|
|
|