1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Blasttech\EloquentRelatedPlus; |
4
|
|
|
|
5
|
|
|
use Illuminate\Database\Eloquent\Builder; |
6
|
|
|
use Illuminate\Database\Eloquent\Model; |
7
|
|
|
use Illuminate\Database\Eloquent\Relations\BelongsTo; |
8
|
|
|
use Illuminate\Database\Eloquent\Relations\HasMany; |
9
|
|
|
use Illuminate\Database\Eloquent\Relations\HasOneOrMany; |
10
|
|
|
use Illuminate\Database\Eloquent\Relations\Relation; |
11
|
|
|
use Illuminate\Database\Query\Expression; |
12
|
|
|
use Illuminate\Database\Query\JoinClause; |
13
|
|
|
use Illuminate\Support\Facades\DB; |
14
|
|
|
use Illuminate\Support\Facades\Schema; |
15
|
|
|
|
16
|
|
|
/** |
17
|
|
|
* Trait RelatedPlusTrait |
18
|
|
|
* |
19
|
|
|
* @property array nullable |
20
|
|
|
* @property array order_fields |
21
|
|
|
* @property array order_defaults |
22
|
|
|
* @property array order_relations |
23
|
|
|
* @property array order_with |
24
|
|
|
* @property array search_fields |
25
|
|
|
* @property string connection |
26
|
|
|
*/ |
27
|
|
|
trait RelatedPlusTrait |
28
|
|
|
{ |
29
|
|
|
use CustomOrderTrait, HelperMethodTrait, JoinsTrait, SearchTrait; |
30
|
|
|
|
31
|
|
|
/** |
32
|
|
|
* Boot method for trait |
33
|
|
|
* |
34
|
|
|
*/ |
35
|
|
|
public static function bootRelatedPlusTrait() |
36
|
|
|
{ |
37
|
|
|
static::saving(function ($model) { |
38
|
|
|
if (!empty($model->nullable)) { |
39
|
|
|
/* @var \Illuminate\Database\Eloquent\Model|RelatedPlusTrait|static $model */ |
40
|
|
|
$model->setAttributesNull(); |
|
|
|
|
41
|
|
|
} |
42
|
|
|
}); |
43
|
|
|
} |
44
|
|
|
|
45
|
|
|
/** |
46
|
|
|
* Set empty fields to null |
47
|
|
|
*/ |
48
|
|
|
protected function setAttributesNull() |
49
|
|
|
{ |
50
|
|
|
foreach ($this->attributes as $key => $value) { |
|
|
|
|
51
|
|
|
if (isset($this->nullable[$key])) { |
52
|
|
|
$this->{$key} = empty(trim($value)) ? null : $value; |
53
|
|
|
} |
54
|
|
|
} |
55
|
|
|
} |
56
|
|
|
|
57
|
|
|
/** |
58
|
|
|
* Get the table associated with the model. |
59
|
|
|
* |
60
|
|
|
* @return string |
61
|
|
|
*/ |
62
|
|
|
abstract public function getTable(); |
63
|
|
|
|
64
|
|
|
/** |
65
|
|
|
* Add joins for one or more relations |
66
|
|
|
* This determines the foreign key relations automatically to prevent the need to figure out the columns. |
67
|
|
|
* Usages: |
68
|
|
|
* $query->modelJoin('customers') |
69
|
|
|
* $query->modelJoin('customer.client') |
70
|
|
|
* |
71
|
|
|
* @param Builder $query |
72
|
|
|
* @param string $relationName |
73
|
|
|
* @param string $operator |
74
|
|
|
* @param string $type |
75
|
|
|
* @param bool $where |
76
|
|
|
* @param bool $relatedSelect |
77
|
|
|
* @param string|null $direction |
78
|
|
|
* |
79
|
|
|
* @return Builder |
80
|
|
|
*/ |
81
|
|
|
public function scopeModelJoin( |
82
|
|
|
Builder $query, |
83
|
|
|
$relationName, |
84
|
|
|
$operator = '=', |
85
|
|
|
$type = 'left', |
86
|
|
|
$where = false, |
87
|
|
|
$relatedSelect = true, |
88
|
|
|
$direction = null |
89
|
|
|
) { |
90
|
|
|
foreach ($this->parseRelationNames($relationName) as $relation) { |
91
|
|
|
$table = $this->getRelationTables($relation); |
92
|
|
|
|
93
|
|
|
// Add selects |
94
|
|
|
$query = $this->modelJoinSelects($query, $table, $relatedSelect); |
95
|
|
|
|
96
|
|
|
$query->relationJoin($table, $relation, $operator, $type, $where, $direction); |
97
|
|
|
} |
98
|
|
|
|
99
|
|
|
return $query; |
100
|
|
|
} |
101
|
|
|
|
102
|
|
|
/** |
103
|
|
|
* Add selects for model join |
104
|
|
|
* |
105
|
|
|
* @param Builder $query |
106
|
|
|
* @param \stdClass $table |
107
|
|
|
* @param bool $relatedSelect |
108
|
|
|
* @return mixed |
|
|
|
|
109
|
|
|
*/ |
110
|
|
|
protected function modelJoinSelects($query, $table, $relatedSelect) |
111
|
|
|
{ |
112
|
|
|
/** @var Model $query */ |
113
|
|
|
if (empty($query->getQuery()->columns)) { |
114
|
|
|
$query->select($this->getTable() . ".*"); |
115
|
|
|
} |
116
|
|
|
if ($relatedSelect) { |
117
|
|
|
$query = $this->selectRelated($query, $table); |
118
|
|
|
} |
119
|
|
|
|
120
|
|
|
return $query; |
121
|
|
|
} |
122
|
|
|
|
123
|
|
|
/** |
124
|
|
|
* Add select for related table fields |
125
|
|
|
* |
126
|
|
|
* @param Builder $query |
127
|
|
|
* @param \stdClass $table |
128
|
|
|
* @return Builder |
129
|
|
|
*/ |
130
|
|
|
public function selectRelated(Builder $query, $table) |
131
|
|
|
{ |
132
|
|
|
$connection = $this->connection; |
133
|
|
|
|
134
|
|
|
foreach (Schema::connection($connection)->getColumnListing($table->name) as $relatedColumn) { |
135
|
|
|
$query->addSelect( |
136
|
|
|
new Expression("`$table->alias`.`$relatedColumn` AS `$table->alias.$relatedColumn`") |
137
|
|
|
); |
138
|
|
|
} |
139
|
|
|
|
140
|
|
|
return $query; |
141
|
|
|
} |
142
|
|
|
|
143
|
|
|
/** |
144
|
|
|
* Set the order of a model |
145
|
|
|
* |
146
|
|
|
* @param Builder $query |
147
|
|
|
* @param string $orderField |
148
|
|
|
* @param string $direction |
149
|
|
|
* @return Builder |
150
|
|
|
*/ |
151
|
|
|
public function scopeOrderByCustom(Builder $query, $orderField, $direction) |
152
|
|
|
{ |
153
|
|
|
if ($this->hasOrderFieldsAndDefaults($orderField, $direction)) { |
154
|
|
|
$query = $this->removeGlobalScope($query, 'order'); |
155
|
|
|
} |
156
|
|
|
|
157
|
|
|
return $query->setCustomOrder($orderField, $direction); |
158
|
|
|
} |
159
|
|
|
|
160
|
|
|
/** |
161
|
|
|
* Use a model method to add columns or joins if in the order options |
162
|
|
|
* |
163
|
|
|
* @param Builder $query |
164
|
|
|
* @param string $order |
165
|
|
|
* @return Builder |
166
|
|
|
*/ |
167
|
|
|
public function scopeOrderByWith(Builder $query, $order) |
168
|
|
|
{ |
169
|
|
|
if (isset($this->order_with[$order])) { |
170
|
|
|
$query = $this->addOrderWith($query, $order); |
171
|
|
|
} |
172
|
|
|
|
173
|
|
|
if (isset($this->order_fields[$order])) { |
174
|
|
|
$query = $this->addOrderJoin($query, $order); |
175
|
|
|
} |
176
|
|
|
|
177
|
|
|
return $query; |
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
/** |
181
|
|
|
* Execute a scope in the order_width settings |
182
|
|
|
* |
183
|
|
|
* @param Builder $query |
184
|
|
|
* @param string $order |
185
|
|
|
* @return Builder |
186
|
|
|
*/ |
187
|
|
|
protected function addOrderWith(Builder $query, $order) |
188
|
|
|
{ |
189
|
|
|
$with = 'with' . $this->order_with[$order]; |
190
|
|
|
|
191
|
|
|
return $query->$with(); |
192
|
|
|
} |
193
|
|
|
|
194
|
|
|
/** |
195
|
|
|
* Add join from order_fields |
196
|
|
|
* |
197
|
|
|
* @param Builder $query |
198
|
|
|
* @param string $order |
199
|
|
|
* @return Builder |
200
|
|
|
*/ |
201
|
|
|
protected function addOrderJoin(Builder $query, $order) |
202
|
|
|
{ |
203
|
|
|
$orderOption = (explode('.', $this->order_fields[$order]))[0]; |
204
|
|
|
|
205
|
|
|
if (isset($this->order_relations[$orderOption])) { |
206
|
|
|
$query->modelJoin( |
207
|
|
|
$this->order_relations[$orderOption], |
208
|
|
|
'=', |
209
|
|
|
'left', |
210
|
|
|
false, |
211
|
|
|
false |
212
|
|
|
); |
213
|
|
|
} |
214
|
|
|
|
215
|
|
|
return $query; |
216
|
|
|
} |
217
|
|
|
|
218
|
|
|
/** |
219
|
|
|
* Join a model |
220
|
|
|
* |
221
|
|
|
* @param Builder $query |
222
|
|
|
* @param \stdClass $table |
223
|
|
|
* @param Relation $relation |
224
|
|
|
* @param string $operator |
225
|
|
|
* @param string $type |
226
|
|
|
* @param boolean $where |
227
|
|
|
* @param string $direction |
|
|
|
|
228
|
|
|
* @return Builder |
229
|
|
|
*/ |
230
|
|
|
public function scopeRelationJoin( |
231
|
|
|
Builder $query, |
232
|
|
|
$table, |
233
|
|
|
$relation, |
234
|
|
|
$operator, |
235
|
|
|
$type, |
236
|
|
|
$where, |
237
|
|
|
$direction = null |
238
|
|
|
) { |
239
|
|
|
$fullTableName = $this->getTableWithAlias($table); |
240
|
|
|
|
241
|
|
|
return $query->join($fullTableName, function (JoinClause $join) use ( |
242
|
|
|
$table, |
243
|
|
|
$relation, |
244
|
|
|
$operator, |
245
|
|
|
$direction |
246
|
|
|
) { |
247
|
|
|
$this->relationJoinType($relation, $join, $table, $operator, $direction); |
248
|
|
|
}, null, null, $type, $where); |
249
|
|
|
} |
250
|
|
|
|
251
|
|
|
/** |
252
|
|
|
* If the relation is one-to-many, just get the first related record |
253
|
|
|
* |
254
|
|
|
* @param JoinClause $joinClause |
255
|
|
|
* @param string $column |
256
|
|
|
* @param HasMany|Relation $relation |
257
|
|
|
* @param string $table |
258
|
|
|
* @param string $direction |
259
|
|
|
* |
260
|
|
|
* @return JoinClause |
261
|
|
|
*/ |
262
|
|
|
public function hasManyJoinWhere(JoinClause $joinClause, $column, $relation, $table, $direction) |
263
|
|
|
{ |
264
|
|
|
return $joinClause->where( |
265
|
|
|
$column, |
266
|
|
|
function ($subQuery) use ($table, $direction, $relation, $column) { |
267
|
|
|
$subQuery = $this->joinOne( |
268
|
|
|
$subQuery->from($table), |
269
|
|
|
$relation, |
270
|
|
|
$column, |
271
|
|
|
$direction |
272
|
|
|
); |
273
|
|
|
|
274
|
|
|
// Add any where statements with the relationship |
275
|
|
|
$subQuery = $this->addRelatedWhereConstraints($subQuery, $relation, $table); |
276
|
|
|
|
277
|
|
|
// Add any order statements with the relationship |
278
|
|
|
return $this->addOrder($subQuery, $relation, $table); |
279
|
|
|
} |
280
|
|
|
); |
281
|
|
|
} |
282
|
|
|
|
283
|
|
|
/** |
284
|
|
|
* Adds a where for a relation's join columns and and min/max for a given column |
285
|
|
|
* |
286
|
|
|
* @param Builder $query |
287
|
|
|
* @param Relation $relation |
288
|
|
|
* @param string $column |
289
|
|
|
* @param string $direction |
290
|
|
|
* @return Builder |
291
|
|
|
*/ |
292
|
|
|
public function joinOne($query, $relation, $column, $direction) |
293
|
|
|
{ |
294
|
|
|
// Get join fields |
295
|
|
|
$joinColumns = $this->getJoinColumns($relation); |
296
|
|
|
|
297
|
|
|
return $this->selectMinMax( |
298
|
|
|
$query->whereColumn($joinColumns->first, '=', $joinColumns->second), |
299
|
|
|
$column, |
300
|
|
|
$direction |
301
|
|
|
); |
302
|
|
|
} |
303
|
|
|
|
304
|
|
|
/** |
305
|
|
|
* Adds a select for a min or max on the given column, depending on direction given |
306
|
|
|
* |
307
|
|
|
* @param Builder $query |
308
|
|
|
* @param string $column |
309
|
|
|
* @param string $direction |
310
|
|
|
* @return Builder |
311
|
|
|
*/ |
312
|
|
|
public function selectMinMax($query, $column, $direction) |
313
|
|
|
{ |
314
|
|
|
$column = $this->addBackticks($column); |
315
|
|
|
|
316
|
|
|
/** @var Model $query */ |
317
|
|
|
if ($direction == 'asc') { |
318
|
|
|
return $query->select(DB::raw('MIN(' . $column . ')')); |
319
|
|
|
} else { |
320
|
|
|
return $query->select(DB::raw('MAX(' . $column . ')')); |
321
|
|
|
} |
322
|
|
|
} |
323
|
|
|
|
324
|
|
|
/** |
325
|
|
|
* Add wheres if they exist for a relation |
326
|
|
|
* |
327
|
|
|
* @param Builder|JoinClause $builder |
328
|
|
|
* @param Relation|BelongsTo|HasOneOrMany $relation |
329
|
|
|
* @param string $table |
330
|
|
|
* @return Builder|JoinClause $builder |
331
|
|
|
*/ |
332
|
|
|
protected function addRelatedWhereConstraints($builder, $relation, $table) |
333
|
|
|
{ |
334
|
|
|
// Get where clauses from the relationship |
335
|
|
|
$wheres = collect($relation->toBase()->wheres) |
336
|
|
|
->where('type', 'Basic') |
337
|
|
|
->map(function ($where) use ($table) { |
338
|
|
|
// Add table name to column if it is absent |
339
|
|
|
return [$this->columnWithTableName($table, $where['column']), $where['operator'], $where['value']]; |
340
|
|
|
})->toArray(); |
341
|
|
|
|
342
|
|
|
if (!empty($wheres)) { |
343
|
|
|
$builder->where($wheres); |
344
|
|
|
} |
345
|
|
|
|
346
|
|
|
return $builder; |
347
|
|
|
} |
348
|
|
|
|
349
|
|
|
/** |
350
|
|
|
* Add orderBy if orders exist for a relation |
351
|
|
|
* |
352
|
|
|
* @param Builder|JoinClause $builder |
353
|
|
|
* @param Relation|BelongsTo|HasOneOrMany $relation |
354
|
|
|
* @param string $table |
355
|
|
|
* @return Builder|JoinClause $builder |
356
|
|
|
*/ |
357
|
|
|
protected function addOrder($builder, $relation, $table) |
358
|
|
|
{ |
359
|
|
|
/** @var Model $builder */ |
360
|
|
|
if (!empty($relation->toBase()->orders)) { |
361
|
|
|
// Get where clauses from the relationship |
362
|
|
|
foreach ($relation->toBase()->orders as $order) { |
363
|
|
|
$builder->orderBy($this->columnWithTableName($table, $order['column']), $order['direction']); |
364
|
|
|
} |
365
|
|
|
} |
366
|
|
|
|
367
|
|
|
return $builder; |
368
|
|
|
} |
369
|
|
|
|
370
|
|
|
/** |
371
|
|
|
* Add where statements for the model search fields |
372
|
|
|
* |
373
|
|
|
* @param Builder $query |
374
|
|
|
* @param string $searchText |
375
|
|
|
* @return Builder |
376
|
|
|
*/ |
377
|
|
|
public function scopeSearch(Builder $query, $searchText = '') |
378
|
|
|
{ |
379
|
|
|
$searchText = trim($searchText); |
380
|
|
|
|
381
|
|
|
// If search is set |
382
|
|
|
if ($searchText != "" && $this->hasSearchFields()) { |
383
|
|
|
$query = $this->checkSearchFields($query, $searchText); |
384
|
|
|
} |
385
|
|
|
|
386
|
|
|
return $query; |
387
|
|
|
} |
388
|
|
|
|
389
|
|
|
/** |
390
|
|
|
* Switch a query to be a subquery of a model |
391
|
|
|
* |
392
|
|
|
* @param Builder $query |
393
|
|
|
* @param Builder $model |
394
|
|
|
* @return Builder |
395
|
|
|
*/ |
396
|
|
|
public function scopeSetSubquery(Builder $query, $model) |
397
|
|
|
{ |
398
|
|
|
$sql = $this->toSqlWithBindings($model); |
399
|
|
|
$table = $model->getQuery()->from; |
400
|
|
|
|
401
|
|
|
return $query |
402
|
|
|
->from(DB::raw("({$sql}) as " . $table)) |
403
|
|
|
->select($table . '.*'); |
404
|
|
|
} |
405
|
|
|
|
406
|
|
|
/** |
407
|
|
|
* Set the model order |
408
|
|
|
* |
409
|
|
|
* @param Builder $query |
410
|
|
|
* @param string $column |
411
|
|
|
* @param string $direction |
412
|
|
|
* @return Builder |
413
|
|
|
*/ |
414
|
|
|
public function scopeSetCustomOrder(Builder $query, $column, $direction) |
415
|
|
|
{ |
416
|
|
|
if (isset($this->order_defaults)) { |
417
|
|
|
$column = $this->setOrderColumn($column); |
418
|
|
|
$direction = $this->setOrderDirection($direction); |
419
|
|
|
} |
420
|
|
|
|
421
|
|
|
return $this->setOrder($query, $column, $direction); |
422
|
|
|
} |
423
|
|
|
|
424
|
|
|
/** |
425
|
|
|
* Check if column being sorted by is from a related model |
426
|
|
|
* |
427
|
|
|
* @param Builder $query |
428
|
|
|
* @param string $column |
429
|
|
|
* @param string $direction |
430
|
|
|
* @return Builder |
431
|
|
|
*/ |
432
|
|
|
public function scopeOrderByCheckModel(Builder $query, $column, $direction) |
433
|
|
|
{ |
434
|
|
|
/** @var Model $query */ |
435
|
|
|
$query->orderBy(DB::raw($column), $direction); |
436
|
|
|
|
437
|
|
|
if (isset($this->order_relations) && (strpos($column, |
438
|
|
|
'.') !== false || isset($this->order_relations[$column]))) { |
439
|
|
|
$query = $this->joinRelatedTable($query, $this->getTableFromColumn($column)); |
440
|
|
|
} |
441
|
|
|
|
442
|
|
|
return $query; |
443
|
|
|
} |
444
|
|
|
} |
445
|
|
|
|
It seems like the method you are trying to call exists only in some of the possible types.
Let’s take a look at an example:
Available Fixes
Add an additional type-check:
Only allow a single type to be passed if the variable comes from a parameter: