1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace SilverStripe\ORM; |
4
|
|
|
|
5
|
|
|
use SilverStripe\Core\ClassInfo; |
6
|
|
|
use SilverStripe\Core\Convert; |
7
|
|
|
use SilverStripe\Core\Injector\Injector; |
8
|
|
|
use SilverStripe\ORM\Connect\Query; |
9
|
|
|
use SilverStripe\ORM\Queries\SQLConditionGroup; |
10
|
|
|
use SilverStripe\ORM\Queries\SQLSelect; |
11
|
|
|
use InvalidArgumentException; |
12
|
|
|
|
13
|
|
|
/** |
14
|
|
|
* An object representing a query of data from the DataObject's supporting database. |
15
|
|
|
* Acts as a wrapper over {@link SQLSelect} and performs all of the query generation. |
16
|
|
|
* Used extensively by {@link DataList}. |
17
|
|
|
* |
18
|
|
|
* Unlike DataList, modifiers on DataQuery modify the object rather than returning a clone. |
19
|
|
|
* DataList is immutable, DataQuery is mutable. |
20
|
|
|
*/ |
21
|
|
|
class DataQuery |
22
|
|
|
{ |
23
|
|
|
|
24
|
|
|
/** |
25
|
|
|
* @var string |
26
|
|
|
*/ |
27
|
|
|
protected $dataClass; |
28
|
|
|
|
29
|
|
|
/** |
30
|
|
|
* @var SQLSelect |
31
|
|
|
*/ |
32
|
|
|
protected $query; |
33
|
|
|
|
34
|
|
|
/** |
35
|
|
|
* Map of all field names to an array of conflicting column SQL |
36
|
|
|
* |
37
|
|
|
* E.g. |
38
|
|
|
* array( |
39
|
|
|
* 'Title' => array( |
40
|
|
|
* '"MyTable"."Title"', |
41
|
|
|
* '"AnotherTable"."Title"', |
42
|
|
|
* ) |
43
|
|
|
* ) |
44
|
|
|
* |
45
|
|
|
* @var array |
46
|
|
|
*/ |
47
|
|
|
protected $collidingFields = array(); |
48
|
|
|
|
49
|
|
|
/** |
50
|
|
|
* Allows custom callback to be registered before getFinalisedQuery is called. |
51
|
|
|
* |
52
|
|
|
* @var DataQueryManipulator[] |
53
|
|
|
*/ |
54
|
|
|
protected $dataQueryManipulators = []; |
55
|
|
|
|
56
|
|
|
private $queriedColumns = null; |
57
|
|
|
|
58
|
|
|
/** |
59
|
|
|
* @var bool |
60
|
|
|
*/ |
61
|
|
|
private $queryFinalised = false; |
62
|
|
|
|
63
|
|
|
// TODO: replace subclass_access with this |
64
|
|
|
protected $querySubclasses = true; |
65
|
|
|
// TODO: replace restrictclasses with this |
66
|
|
|
protected $filterByClassName = true; |
67
|
|
|
|
68
|
|
|
/** |
69
|
|
|
* Create a new DataQuery. |
70
|
|
|
* |
71
|
|
|
* @param string $dataClass The name of the DataObject class that you wish to query |
72
|
|
|
*/ |
73
|
|
|
public function __construct($dataClass) |
74
|
|
|
{ |
75
|
|
|
$this->dataClass = $dataClass; |
76
|
|
|
$this->initialiseQuery(); |
77
|
|
|
} |
78
|
|
|
|
79
|
|
|
/** |
80
|
|
|
* Clone this object |
81
|
|
|
*/ |
82
|
|
|
public function __clone() |
83
|
|
|
{ |
84
|
|
|
$this->query = clone $this->query; |
85
|
|
|
} |
86
|
|
|
|
87
|
|
|
/** |
88
|
|
|
* Return the {@link DataObject} class that is being queried. |
89
|
|
|
* |
90
|
|
|
* @return string |
91
|
|
|
*/ |
92
|
|
|
public function dataClass() |
93
|
|
|
{ |
94
|
|
|
return $this->dataClass; |
95
|
|
|
} |
96
|
|
|
|
97
|
|
|
/** |
98
|
|
|
* Return the {@link SQLSelect} object that represents the current query; note that it will |
99
|
|
|
* be a clone of the object. |
100
|
|
|
* |
101
|
|
|
* @return SQLSelect |
102
|
|
|
*/ |
103
|
|
|
public function query() |
104
|
|
|
{ |
105
|
|
|
return $this->getFinalisedQuery(); |
106
|
|
|
} |
107
|
|
|
|
108
|
|
|
|
109
|
|
|
/** |
110
|
|
|
* Remove a filter from the query |
111
|
|
|
* |
112
|
|
|
* @param string|array $fieldExpression The predicate of the condition to remove |
113
|
|
|
* (ignoring parameters). The expression will be considered a match if it's |
114
|
|
|
* contained within any other predicate. |
115
|
|
|
* @return DataQuery Self reference |
116
|
|
|
*/ |
117
|
|
|
public function removeFilterOn($fieldExpression) |
118
|
|
|
{ |
119
|
|
|
$matched = false; |
120
|
|
|
|
121
|
|
|
// If given a parameterised condition extract only the condition |
122
|
|
|
if (is_array($fieldExpression)) { |
123
|
|
|
reset($fieldExpression); |
124
|
|
|
$fieldExpression = key($fieldExpression); |
125
|
|
|
} |
126
|
|
|
|
127
|
|
|
$where = $this->query->getWhere(); |
128
|
|
|
// Iterate through each condition |
129
|
|
|
foreach ($where as $i => $condition) { |
130
|
|
|
// Rewrite condition groups as plain conditions before comparison |
131
|
|
|
if ($condition instanceof SQLConditionGroup) { |
132
|
|
|
$predicate = $condition->conditionSQL($parameters); |
133
|
|
|
$condition = array($predicate => $parameters); |
134
|
|
|
} |
135
|
|
|
|
136
|
|
|
// As each condition is a single length array, do a single |
137
|
|
|
// iteration to extract the predicate and parameters |
138
|
|
|
foreach ($condition as $predicate => $parameters) { |
139
|
|
|
// @see SQLSelect::addWhere for why this is required here |
140
|
|
|
if (strpos($predicate, $fieldExpression) !== false) { |
141
|
|
|
unset($where[$i]); |
142
|
|
|
$matched = true; |
143
|
|
|
} |
144
|
|
|
// Enforce single-item condition predicate => parameters structure |
145
|
|
|
break; |
146
|
|
|
} |
147
|
|
|
} |
148
|
|
|
|
149
|
|
|
// set the entire where clause back, but clear the original one first |
150
|
|
|
if ($matched) { |
151
|
|
|
$this->query->setWhere($where); |
152
|
|
|
} else { |
153
|
|
|
throw new InvalidArgumentException("Couldn't find $fieldExpression in the query filter."); |
154
|
|
|
} |
155
|
|
|
|
156
|
|
|
return $this; |
157
|
|
|
} |
158
|
|
|
|
159
|
|
|
/** |
160
|
|
|
* Set up the simplest initial query |
161
|
|
|
*/ |
162
|
|
|
protected function initialiseQuery() |
163
|
|
|
{ |
164
|
|
|
// Join on base table and let lazy loading join subtables |
165
|
|
|
$baseClass = DataObject::getSchema()->baseDataClass($this->dataClass()); |
166
|
|
|
if (!$baseClass) { |
167
|
|
|
throw new InvalidArgumentException("DataQuery::create() Can't find data classes for '{$this->dataClass}'"); |
168
|
|
|
} |
169
|
|
|
|
170
|
|
|
// Build our intial query |
171
|
|
|
$this->query = new SQLSelect(array()); |
172
|
|
|
$this->query->setDistinct(true); |
173
|
|
|
|
174
|
|
|
if ($sort = singleton($this->dataClass)->stat('default_sort')) { |
175
|
|
|
$this->sort($sort); |
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
$baseTable = DataObject::getSchema()->tableName($baseClass); |
179
|
|
|
$this->query->setFrom("\"{$baseTable}\""); |
180
|
|
|
|
181
|
|
|
$obj = Injector::inst()->get($baseClass); |
182
|
|
|
$obj->extend('augmentDataQueryCreation', $this->query, $this); |
183
|
|
|
} |
184
|
|
|
|
185
|
|
|
public function setQueriedColumns($queriedColumns) |
186
|
|
|
{ |
187
|
|
|
$this->queriedColumns = $queriedColumns; |
188
|
|
|
} |
189
|
|
|
|
190
|
|
|
/** |
191
|
|
|
* Ensure that the query is ready to execute. |
192
|
|
|
* |
193
|
|
|
* @param array|null $queriedColumns Any columns to filter the query by |
194
|
|
|
* @return SQLSelect The finalised sql query |
195
|
|
|
*/ |
196
|
|
|
public function getFinalisedQuery($queriedColumns = null) |
197
|
|
|
{ |
198
|
|
|
if (!$queriedColumns) { |
199
|
|
|
$queriedColumns = $this->queriedColumns; |
200
|
|
|
} |
201
|
|
|
if ($queriedColumns) { |
202
|
|
|
$queriedColumns = array_merge($queriedColumns, array('Created', 'LastEdited', 'ClassName')); |
203
|
|
|
} |
204
|
|
|
$query = clone $this->query; |
205
|
|
|
|
206
|
|
|
// Apply manipulators before finalising query |
207
|
|
|
foreach ($this->getDataQueryManipulators() as $manipulator) { |
208
|
|
|
$manipulator->beforeGetFinalisedQuery($this, $queriedColumns, $query); |
209
|
|
|
} |
210
|
|
|
|
211
|
|
|
$schema = DataObject::getSchema(); |
212
|
|
|
$baseDataClass = $schema->baseDataClass($this->dataClass()); |
213
|
|
|
$baseIDColumn = $schema->sqlColumnForField($baseDataClass, 'ID'); |
214
|
|
|
$ancestorClasses = ClassInfo::ancestry($this->dataClass(), true); |
215
|
|
|
|
216
|
|
|
// Generate the list of tables to iterate over and the list of columns required |
217
|
|
|
// by any existing where clauses. This second step is skipped if we're fetching |
218
|
|
|
// the whole dataobject as any required columns will get selected regardless. |
219
|
|
|
if ($queriedColumns) { |
220
|
|
|
// Specifying certain columns allows joining of child tables |
221
|
|
|
$tableClasses = ClassInfo::dataClassesFor($this->dataClass); |
222
|
|
|
|
223
|
|
|
// Ensure that any filtered columns are included in the selected columns |
224
|
|
|
foreach ($query->getWhereParameterised($parameters) as $where) { |
225
|
|
|
// Check for any columns in the form '"Column" = ?' or '"Table"."Column"' = ? |
226
|
|
|
if (preg_match_all( |
227
|
|
|
'/(?:"(?<table>[^"]+)"\.)?"(?<column>[^"]+)"(?:[^\.]|$)/', |
228
|
|
|
$where, |
229
|
|
|
$matches, |
230
|
|
|
PREG_SET_ORDER |
231
|
|
|
)) { |
232
|
|
|
foreach ($matches as $match) { |
233
|
|
|
$column = $match['column']; |
234
|
|
|
if (!in_array($column, $queriedColumns)) { |
235
|
|
|
$queriedColumns[] = $column; |
236
|
|
|
} |
237
|
|
|
} |
238
|
|
|
} |
239
|
|
|
} |
240
|
|
|
} else { |
241
|
|
|
$tableClasses = $ancestorClasses; |
242
|
|
|
} |
243
|
|
|
|
244
|
|
|
// Iterate over the tables and check what we need to select from them. If any selects are made (or the table is |
245
|
|
|
// required for a select) |
246
|
|
|
foreach ($tableClasses as $tableClass) { |
247
|
|
|
// Determine explicit columns to select |
248
|
|
|
$selectColumns = null; |
249
|
|
|
if ($queriedColumns) { |
250
|
|
|
// Restrict queried columns to that on the selected table |
251
|
|
|
$tableFields = $schema->databaseFields($tableClass, false); |
252
|
|
|
unset($tableFields['ID']); |
253
|
|
|
$selectColumns = array_intersect($queriedColumns, array_keys($tableFields)); |
254
|
|
|
} |
255
|
|
|
|
256
|
|
|
// If this is a subclass without any explicitly requested columns, omit this from the query |
257
|
|
|
if (!in_array($tableClass, $ancestorClasses) && empty($selectColumns)) { |
258
|
|
|
continue; |
259
|
|
|
} |
260
|
|
|
|
261
|
|
|
// Select necessary columns (unless an explicitly empty array) |
262
|
|
|
if ($selectColumns !== array()) { |
263
|
|
|
$this->selectColumnsFromTable($query, $tableClass, $selectColumns); |
264
|
|
|
} |
265
|
|
|
|
266
|
|
|
// Join if not the base table |
267
|
|
|
if ($tableClass !== $baseDataClass) { |
268
|
|
|
$tableName = $schema->tableName($tableClass); |
269
|
|
|
$query->addLeftJoin( |
270
|
|
|
$tableName, |
271
|
|
|
"\"{$tableName}\".\"ID\" = {$baseIDColumn}", |
272
|
|
|
$tableName, |
273
|
|
|
10 |
274
|
|
|
); |
275
|
|
|
} |
276
|
|
|
} |
277
|
|
|
|
278
|
|
|
// Resolve colliding fields |
279
|
|
|
if ($this->collidingFields) { |
|
|
|
|
280
|
|
|
foreach ($this->collidingFields as $collisionField => $collisions) { |
281
|
|
|
$caseClauses = array(); |
282
|
|
|
foreach ($collisions as $collision) { |
283
|
|
|
if (preg_match('/^"(?<table>[^"]+)"\./', $collision, $matches)) { |
284
|
|
|
$collisionTable = $matches['table']; |
285
|
|
|
$collisionClass = $schema->tableClass($collisionTable); |
286
|
|
|
if ($collisionClass) { |
287
|
|
|
$collisionClassColumn = $schema->sqlColumnForField($collisionClass, 'ClassName'); |
288
|
|
|
$collisionClasses = ClassInfo::subclassesFor($collisionClass); |
289
|
|
|
$collisionClassesSQL = implode(', ', Convert::raw2sql($collisionClasses, true)); |
290
|
|
|
$caseClauses[] = "WHEN {$collisionClassColumn} IN ({$collisionClassesSQL}) THEN $collision"; |
291
|
|
|
} |
292
|
|
|
} else { |
293
|
|
|
user_error("Bad collision item '$collision'", E_USER_WARNING); |
294
|
|
|
} |
295
|
|
|
} |
296
|
|
|
$query->selectField("CASE " . implode(" ", $caseClauses) . " ELSE NULL END", $collisionField); |
297
|
|
|
} |
298
|
|
|
} |
299
|
|
|
|
300
|
|
|
|
301
|
|
|
if ($this->filterByClassName) { |
302
|
|
|
// If querying the base class, don't bother filtering on class name |
303
|
|
|
if ($this->dataClass != $baseDataClass) { |
304
|
|
|
// Get the ClassName values to filter to |
305
|
|
|
$classNames = ClassInfo::subclassesFor($this->dataClass); |
306
|
|
|
$classNamesPlaceholders = DB::placeholders($classNames); |
307
|
|
|
$baseClassColumn = $schema->sqlColumnForField($baseDataClass, 'ClassName'); |
308
|
|
|
$query->addWhere(array( |
309
|
|
|
"{$baseClassColumn} IN ($classNamesPlaceholders)" => $classNames |
310
|
|
|
)); |
311
|
|
|
} |
312
|
|
|
} |
313
|
|
|
|
314
|
|
|
// Select ID |
315
|
|
|
$query->selectField($baseIDColumn, "ID"); |
316
|
|
|
|
317
|
|
|
// Select RecordClassName |
318
|
|
|
$baseClassColumn = $schema->sqlColumnForField($baseDataClass, 'ClassName'); |
319
|
|
|
$query->selectField( |
320
|
|
|
" |
321
|
|
|
CASE WHEN {$baseClassColumn} IS NOT NULL THEN {$baseClassColumn} |
322
|
|
|
ELSE ".Convert::raw2sql($baseDataClass, true)." END", |
323
|
|
|
"RecordClassName" |
324
|
|
|
); |
325
|
|
|
|
326
|
|
|
// TODO: Versioned, Translatable, SiteTreeSubsites, etc, could probably be better implemented as subclasses |
327
|
|
|
// of DataQuery |
328
|
|
|
|
329
|
|
|
$obj = Injector::inst()->get($this->dataClass); |
330
|
|
|
$obj->extend('augmentSQL', $query, $this); |
331
|
|
|
|
332
|
|
|
$this->ensureSelectContainsOrderbyColumns($query); |
333
|
|
|
|
334
|
|
|
// Apply post-finalisation manipulations |
335
|
|
|
foreach ($this->getDataQueryManipulators() as $manipulator) { |
336
|
|
|
$manipulator->afterGetFinalisedQuery($this, $queriedColumns, $query); |
337
|
|
|
} |
338
|
|
|
|
339
|
|
|
return $query; |
340
|
|
|
} |
341
|
|
|
|
342
|
|
|
/** |
343
|
|
|
* Ensure that if a query has an order by clause, those columns are present in the select. |
344
|
|
|
* |
345
|
|
|
* @param SQLSelect $query |
346
|
|
|
* @param array $originalSelect |
347
|
|
|
*/ |
348
|
|
|
protected function ensureSelectContainsOrderbyColumns($query, $originalSelect = array()) |
349
|
|
|
{ |
350
|
|
|
if ($orderby = $query->getOrderBy()) { |
351
|
|
|
$newOrderby = array(); |
352
|
|
|
$i = 0; |
353
|
|
|
foreach ($orderby as $k => $dir) { |
354
|
|
|
$newOrderby[$k] = $dir; |
355
|
|
|
|
356
|
|
|
// don't touch functions in the ORDER BY or public function calls |
357
|
|
|
// selected as fields |
358
|
|
|
if (strpos($k, '(') !== false) { |
359
|
|
|
continue; |
360
|
|
|
} |
361
|
|
|
|
362
|
|
|
$col = str_replace('"', '', trim($k)); |
363
|
|
|
$parts = explode('.', $col); |
364
|
|
|
|
365
|
|
|
// Pull through SortColumn references from the originalSelect variables |
366
|
|
|
if (preg_match('/_SortColumn/', $col)) { |
367
|
|
|
if (isset($originalSelect[$col])) { |
368
|
|
|
$query->selectField($originalSelect[$col], $col); |
369
|
|
|
} |
370
|
|
|
|
371
|
|
|
continue; |
372
|
|
|
} |
373
|
|
|
|
374
|
|
|
if (count($parts) == 1) { |
375
|
|
|
// Get expression for sort value |
376
|
|
|
$qualCol = "\"{$parts[0]}\""; |
377
|
|
|
$table = DataObject::getSchema()->tableForField($this->dataClass(), $parts[0]); |
378
|
|
|
if ($table) { |
|
|
|
|
379
|
|
|
$qualCol = "\"{$table}\".{$qualCol}"; |
380
|
|
|
} |
381
|
|
|
|
382
|
|
|
// remove original sort |
383
|
|
|
unset($newOrderby[$k]); |
384
|
|
|
|
385
|
|
|
// add new columns sort |
386
|
|
|
$newOrderby[$qualCol] = $dir; |
387
|
|
|
|
388
|
|
|
// To-do: Remove this if block once SQLSelect::$select has been refactored to store getSelect() |
389
|
|
|
// format internally; then this check can be part of selectField() |
390
|
|
|
$selects = $query->getSelect(); |
391
|
|
|
if (!isset($selects[$col]) && !in_array($qualCol, $selects)) { |
392
|
|
|
$query->selectField($qualCol); |
393
|
|
|
} |
394
|
|
|
} else { |
395
|
|
|
$qualCol = '"' . implode('"."', $parts) . '"'; |
396
|
|
|
|
397
|
|
|
if (!in_array($qualCol, $query->getSelect())) { |
398
|
|
|
unset($newOrderby[$k]); |
399
|
|
|
|
400
|
|
|
$newOrderby["\"_SortColumn$i\""] = $dir; |
401
|
|
|
$query->selectField($qualCol, "_SortColumn$i"); |
402
|
|
|
|
403
|
|
|
$i++; |
404
|
|
|
} |
405
|
|
|
} |
406
|
|
|
} |
407
|
|
|
|
408
|
|
|
$query->setOrderBy($newOrderby); |
409
|
|
|
} |
410
|
|
|
} |
411
|
|
|
|
412
|
|
|
/** |
413
|
|
|
* Execute the query and return the result as {@link SS_Query} object. |
414
|
|
|
* |
415
|
|
|
* @return Query |
416
|
|
|
*/ |
417
|
|
|
public function execute() |
418
|
|
|
{ |
419
|
|
|
return $this->getFinalisedQuery()->execute(); |
420
|
|
|
} |
421
|
|
|
|
422
|
|
|
/** |
423
|
|
|
* Return this query's SQL |
424
|
|
|
* |
425
|
|
|
* @param array $parameters Out variable for parameters required for this query |
426
|
|
|
* @return string The resulting SQL query (may be paramaterised) |
427
|
|
|
*/ |
428
|
|
|
public function sql(&$parameters = array()) |
429
|
|
|
{ |
430
|
|
|
return $this->getFinalisedQuery()->sql($parameters); |
431
|
|
|
} |
432
|
|
|
|
433
|
|
|
/** |
434
|
|
|
* Return the number of records in this query. |
435
|
|
|
* Note that this will issue a separate SELECT COUNT() query. |
436
|
|
|
* |
437
|
|
|
* @return int |
438
|
|
|
*/ |
439
|
|
|
public function count() |
440
|
|
|
{ |
441
|
|
|
$quotedColumn = DataObject::getSchema()->sqlColumnForField($this->dataClass(), 'ID'); |
442
|
|
|
return $this->getFinalisedQuery()->count("DISTINCT {$quotedColumn}"); |
443
|
|
|
} |
444
|
|
|
|
445
|
|
|
/** |
446
|
|
|
* Return the maximum value of the given field in this DataList |
447
|
|
|
* |
448
|
|
|
* @param String $field Unquoted database column name. Will be ANSI quoted |
449
|
|
|
* automatically so must not contain double quotes. |
450
|
|
|
* @return string |
451
|
|
|
*/ |
452
|
|
|
public function max($field) |
453
|
|
|
{ |
454
|
|
|
$table = DataObject::getSchema()->tableForField($this->dataClass, $field); |
455
|
|
|
if (!$table) { |
|
|
|
|
456
|
|
|
return $this->aggregate("MAX(\"$field\")"); |
457
|
|
|
} |
458
|
|
|
return $this->aggregate("MAX(\"$table\".\"$field\")"); |
459
|
|
|
} |
460
|
|
|
|
461
|
|
|
/** |
462
|
|
|
* Return the minimum value of the given field in this DataList |
463
|
|
|
* |
464
|
|
|
* @param string $field Unquoted database column name. Will be ANSI quoted |
465
|
|
|
* automatically so must not contain double quotes. |
466
|
|
|
* @return string |
467
|
|
|
*/ |
468
|
|
|
public function min($field) |
469
|
|
|
{ |
470
|
|
|
$table = DataObject::getSchema()->tableForField($this->dataClass, $field); |
471
|
|
|
if (!$table) { |
|
|
|
|
472
|
|
|
return $this->aggregate("MIN(\"$field\")"); |
473
|
|
|
} |
474
|
|
|
return $this->aggregate("MIN(\"$table\".\"$field\")"); |
475
|
|
|
} |
476
|
|
|
|
477
|
|
|
/** |
478
|
|
|
* Return the average value of the given field in this DataList |
479
|
|
|
* |
480
|
|
|
* @param string $field Unquoted database column name. Will be ANSI quoted |
481
|
|
|
* automatically so must not contain double quotes. |
482
|
|
|
* @return string |
483
|
|
|
*/ |
484
|
|
|
public function avg($field) |
485
|
|
|
{ |
486
|
|
|
$table = DataObject::getSchema()->tableForField($this->dataClass, $field); |
487
|
|
|
if (!$table) { |
|
|
|
|
488
|
|
|
return $this->aggregate("AVG(\"$field\")"); |
489
|
|
|
} |
490
|
|
|
return $this->aggregate("AVG(\"$table\".\"$field\")"); |
491
|
|
|
} |
492
|
|
|
|
493
|
|
|
/** |
494
|
|
|
* Return the sum of the values of the given field in this DataList |
495
|
|
|
* |
496
|
|
|
* @param string $field Unquoted database column name. Will be ANSI quoted |
497
|
|
|
* automatically so must not contain double quotes. |
498
|
|
|
* @return string |
499
|
|
|
*/ |
500
|
|
|
public function sum($field) |
501
|
|
|
{ |
502
|
|
|
$table = DataObject::getSchema()->tableForField($this->dataClass, $field); |
503
|
|
|
if (!$table) { |
|
|
|
|
504
|
|
|
return $this->aggregate("SUM(\"$field\")"); |
505
|
|
|
} |
506
|
|
|
return $this->aggregate("SUM(\"$table\".\"$field\")"); |
507
|
|
|
} |
508
|
|
|
|
509
|
|
|
/** |
510
|
|
|
* Runs a raw aggregate expression. Please handle escaping yourself |
511
|
|
|
* |
512
|
|
|
* @param string $expression An aggregate expression, such as 'MAX("Balance")', or a set of them |
513
|
|
|
* (as an escaped SQL statement) |
514
|
|
|
* @return string |
515
|
|
|
*/ |
516
|
|
|
public function aggregate($expression) |
517
|
|
|
{ |
518
|
|
|
return $this->getFinalisedQuery()->aggregate($expression)->execute()->value(); |
519
|
|
|
} |
520
|
|
|
|
521
|
|
|
/** |
522
|
|
|
* Return the first row that would be returned by this full DataQuery |
523
|
|
|
* Note that this will issue a separate SELECT ... LIMIT 1 query. |
524
|
|
|
*/ |
525
|
|
|
public function firstRow() |
526
|
|
|
{ |
527
|
|
|
return $this->getFinalisedQuery()->firstRow(); |
528
|
|
|
} |
529
|
|
|
|
530
|
|
|
/** |
531
|
|
|
* Return the last row that would be returned by this full DataQuery |
532
|
|
|
* Note that this will issue a separate SELECT ... LIMIT query. |
533
|
|
|
*/ |
534
|
|
|
public function lastRow() |
535
|
|
|
{ |
536
|
|
|
return $this->getFinalisedQuery()->lastRow(); |
537
|
|
|
} |
538
|
|
|
|
539
|
|
|
/** |
540
|
|
|
* Update the SELECT clause of the query with the columns from the given table |
541
|
|
|
* |
542
|
|
|
* @param SQLSelect $query |
543
|
|
|
* @param string $tableClass Class to select from |
544
|
|
|
* @param array $columns |
545
|
|
|
*/ |
546
|
|
|
protected function selectColumnsFromTable(SQLSelect &$query, $tableClass, $columns = null) |
547
|
|
|
{ |
548
|
|
|
// Add SQL for multi-value fields |
549
|
|
|
$schema = DataObject::getSchema(); |
550
|
|
|
$databaseFields = $schema->databaseFields($tableClass, false); |
551
|
|
|
$compositeFields = $schema->compositeFields($tableClass, false); |
552
|
|
|
unset($databaseFields['ID']); |
553
|
|
|
foreach ($databaseFields as $k => $v) { |
554
|
|
|
if ((is_null($columns) || in_array($k, $columns)) && !isset($compositeFields[$k])) { |
555
|
|
|
// Update $collidingFields if necessary |
556
|
|
|
$expressionForField = $query->expressionForField($k); |
557
|
|
|
$quotedField = $schema->sqlColumnForField($tableClass, $k); |
558
|
|
|
if ($expressionForField) { |
559
|
|
|
if (!isset($this->collidingFields[$k])) { |
560
|
|
|
$this->collidingFields[$k] = array($expressionForField); |
561
|
|
|
} |
562
|
|
|
$this->collidingFields[$k][] = $quotedField; |
563
|
|
|
} else { |
564
|
|
|
$query->selectField($quotedField, $k); |
565
|
|
|
} |
566
|
|
|
} |
567
|
|
|
} |
568
|
|
|
foreach ($compositeFields as $k => $v) { |
569
|
|
|
if ((is_null($columns) || in_array($k, $columns)) && $v) { |
570
|
|
|
$tableName = $schema->tableName($tableClass); |
571
|
|
|
$dbO = Injector::inst()->create($v, $k); |
572
|
|
|
$dbO->setTable($tableName); |
573
|
|
|
$dbO->addToQuery($query); |
574
|
|
|
} |
575
|
|
|
} |
576
|
|
|
} |
577
|
|
|
|
578
|
|
|
/** |
579
|
|
|
* Append a GROUP BY clause to this query. |
580
|
|
|
* |
581
|
|
|
* @param string $groupby Escaped SQL statement |
582
|
|
|
* @return $this |
583
|
|
|
*/ |
584
|
|
|
public function groupby($groupby) |
585
|
|
|
{ |
586
|
|
|
$this->query->addGroupBy($groupby); |
587
|
|
|
return $this; |
588
|
|
|
} |
589
|
|
|
|
590
|
|
|
/** |
591
|
|
|
* Append a HAVING clause to this query. |
592
|
|
|
* |
593
|
|
|
* @param string $having Escaped SQL statement |
594
|
|
|
* @return $this |
595
|
|
|
*/ |
596
|
|
|
public function having($having) |
597
|
|
|
{ |
598
|
|
|
$this->query->addHaving($having); |
599
|
|
|
return $this; |
600
|
|
|
} |
601
|
|
|
|
602
|
|
|
/** |
603
|
|
|
* Create a disjunctive subgroup. |
604
|
|
|
* |
605
|
|
|
* That is a subgroup joined by OR |
606
|
|
|
* |
607
|
|
|
* @return DataQuery_SubGroup |
608
|
|
|
*/ |
609
|
|
|
public function disjunctiveGroup() |
610
|
|
|
{ |
611
|
|
|
return new DataQuery_SubGroup($this, 'OR'); |
612
|
|
|
} |
613
|
|
|
|
614
|
|
|
/** |
615
|
|
|
* Create a conjunctive subgroup |
616
|
|
|
* |
617
|
|
|
* That is a subgroup joined by AND |
618
|
|
|
* |
619
|
|
|
* @return DataQuery_SubGroup |
620
|
|
|
*/ |
621
|
|
|
public function conjunctiveGroup() |
622
|
|
|
{ |
623
|
|
|
return new DataQuery_SubGroup($this, 'AND'); |
624
|
|
|
} |
625
|
|
|
|
626
|
|
|
/** |
627
|
|
|
* Adds a WHERE clause. |
628
|
|
|
* |
629
|
|
|
* @see SQLSelect::addWhere() for syntax examples, although DataQuery |
630
|
|
|
* won't expand multiple arguments as SQLSelect does. |
631
|
|
|
* |
632
|
|
|
* @param string|array|SQLConditionGroup $filter Predicate(s) to set, as escaped SQL statements or |
633
|
|
|
* paramaterised queries |
634
|
|
|
* @return DataQuery |
635
|
|
|
*/ |
636
|
|
|
public function where($filter) |
637
|
|
|
{ |
638
|
|
|
if ($filter) { |
639
|
|
|
$this->query->addWhere($filter); |
640
|
|
|
} |
641
|
|
|
return $this; |
642
|
|
|
} |
643
|
|
|
|
644
|
|
|
/** |
645
|
|
|
* Append a WHERE with OR. |
646
|
|
|
* |
647
|
|
|
* @see SQLSelect::addWhere() for syntax examples, although DataQuery |
648
|
|
|
* won't expand multiple method arguments as SQLSelect does. |
649
|
|
|
* |
650
|
|
|
* @param string|array|SQLConditionGroup $filter Predicate(s) to set, as escaped SQL statements or |
651
|
|
|
* paramaterised queries |
652
|
|
|
* @return DataQuery |
653
|
|
|
*/ |
654
|
|
|
public function whereAny($filter) |
655
|
|
|
{ |
656
|
|
|
if ($filter) { |
657
|
|
|
$this->query->addWhereAny($filter); |
658
|
|
|
} |
659
|
|
|
return $this; |
660
|
|
|
} |
661
|
|
|
|
662
|
|
|
/** |
663
|
|
|
* Set the ORDER BY clause of this query |
664
|
|
|
* |
665
|
|
|
* @see SQLSelect::orderby() |
666
|
|
|
* |
667
|
|
|
* @param String $sort Column to sort on (escaped SQL statement) |
668
|
|
|
* @param String $direction Direction ("ASC" or "DESC", escaped SQL statement) |
669
|
|
|
* @param Boolean $clear Clear existing values |
670
|
|
|
* @return DataQuery |
671
|
|
|
*/ |
672
|
|
|
public function sort($sort = null, $direction = null, $clear = true) |
673
|
|
|
{ |
674
|
|
|
if ($clear) { |
675
|
|
|
$this->query->setOrderBy($sort, $direction); |
676
|
|
|
} else { |
677
|
|
|
$this->query->addOrderBy($sort, $direction); |
678
|
|
|
} |
679
|
|
|
|
680
|
|
|
return $this; |
681
|
|
|
} |
682
|
|
|
|
683
|
|
|
/** |
684
|
|
|
* Reverse order by clause |
685
|
|
|
* |
686
|
|
|
* @return DataQuery |
687
|
|
|
*/ |
688
|
|
|
public function reverseSort() |
689
|
|
|
{ |
690
|
|
|
$this->query->reverseOrderBy(); |
691
|
|
|
return $this; |
692
|
|
|
} |
693
|
|
|
|
694
|
|
|
/** |
695
|
|
|
* Set the limit of this query. |
696
|
|
|
* |
697
|
|
|
* @param int $limit |
698
|
|
|
* @param int $offset |
699
|
|
|
* @return $this |
700
|
|
|
*/ |
701
|
|
|
public function limit($limit, $offset = 0) |
702
|
|
|
{ |
703
|
|
|
$this->query->setLimit($limit, $offset); |
704
|
|
|
return $this; |
705
|
|
|
} |
706
|
|
|
|
707
|
|
|
/** |
708
|
|
|
* Set whether this query should be distinct or not. |
709
|
|
|
* |
710
|
|
|
* @param bool $value |
711
|
|
|
* @return DataQuery |
712
|
|
|
*/ |
713
|
|
|
public function distinct($value) |
714
|
|
|
{ |
715
|
|
|
$this->query->setDistinct($value); |
716
|
|
|
return $this; |
717
|
|
|
} |
718
|
|
|
|
719
|
|
|
/** |
720
|
|
|
* Add an INNER JOIN clause to this query. |
721
|
|
|
* |
722
|
|
|
* @param String $table The unquoted table name to join to. |
723
|
|
|
* @param String $onClause The filter for the join (escaped SQL statement) |
724
|
|
|
* @param String $alias An optional alias name (unquoted) |
725
|
|
|
* @param int $order A numerical index to control the order that joins are added to the query; lower order values |
726
|
|
|
* will cause the query to appear first. The default is 20, and joins created automatically by the |
727
|
|
|
* ORM have a value of 10. |
728
|
|
|
* @param array $parameters Any additional parameters if the join is a parameterised subquery |
729
|
|
|
* @return $this |
730
|
|
|
*/ |
731
|
|
|
public function innerJoin($table, $onClause, $alias = null, $order = 20, $parameters = array()) |
732
|
|
|
{ |
733
|
|
|
if ($table) { |
734
|
|
|
$this->query->addInnerJoin($table, $onClause, $alias, $order, $parameters); |
735
|
|
|
} |
736
|
|
|
return $this; |
737
|
|
|
} |
738
|
|
|
|
739
|
|
|
/** |
740
|
|
|
* Add a LEFT JOIN clause to this query. |
741
|
|
|
* |
742
|
|
|
* @param string $table The unquoted table to join to. |
743
|
|
|
* @param string $onClause The filter for the join (escaped SQL statement). |
744
|
|
|
* @param string $alias An optional alias name (unquoted) |
745
|
|
|
* @param int $order A numerical index to control the order that joins are added to the query; lower order values |
746
|
|
|
* will cause the query to appear first. The default is 20, and joins created automatically by the |
747
|
|
|
* ORM have a value of 10. |
748
|
|
|
* @param array $parameters Any additional parameters if the join is a parameterised subquery |
749
|
|
|
* @return $this |
750
|
|
|
*/ |
751
|
|
|
public function leftJoin($table, $onClause, $alias = null, $order = 20, $parameters = array()) |
752
|
|
|
{ |
753
|
|
|
if ($table) { |
754
|
|
|
$this->query->addLeftJoin($table, $onClause, $alias, $order, $parameters); |
755
|
|
|
} |
756
|
|
|
return $this; |
757
|
|
|
} |
758
|
|
|
|
759
|
|
|
/** |
760
|
|
|
* Prefix of all joined table aliases. E.g. ->filter('Banner.Image.Title)' |
761
|
|
|
* Will join the Banner, and then Image relations |
762
|
|
|
* `$relationPrefx` will be `banner_image_` |
763
|
|
|
* Each table in the Image chain will be suffixed to this prefix. E.g. |
764
|
|
|
* `banner_image_File` and `banner_image_Image` |
765
|
|
|
* |
766
|
|
|
* This will be null if no relation is joined. |
767
|
|
|
* E.g. `->filter('Title')` |
768
|
|
|
* |
769
|
|
|
* @param string|array $relation Relation in '.' delimited string, or array of parts |
770
|
|
|
* @return string Table prefix |
771
|
|
|
*/ |
772
|
|
|
public static function applyRelationPrefix($relation) |
773
|
|
|
{ |
774
|
|
|
if (!$relation) { |
775
|
|
|
return null; |
776
|
|
|
} |
777
|
|
|
if (is_string($relation)) { |
778
|
|
|
$relation = explode(".", $relation); |
779
|
|
|
} |
780
|
|
|
return strtolower(implode('_', $relation)) . '_'; |
781
|
|
|
} |
782
|
|
|
|
783
|
|
|
/** |
784
|
|
|
* Traverse the relationship fields, and add the table |
785
|
|
|
* mappings to the query object state. This has to be called |
786
|
|
|
* in any overloaded {@link SearchFilter->apply()} methods manually. |
787
|
|
|
* |
788
|
|
|
* Note, that in order to filter against the joined relation user code must |
789
|
|
|
* use {@see tablePrefix()} to get the table alias used for this relation. |
790
|
|
|
* |
791
|
|
|
* @param string|array $relation The array/dot-syntax relation to follow |
792
|
|
|
* @param bool $linearOnly Set to true to restrict to linear relations only. Set this |
793
|
|
|
* if this relation will be used for sorting, and should not include duplicate rows. |
794
|
|
|
* @return string The model class of the related item |
795
|
|
|
*/ |
796
|
|
|
public function applyRelation($relation, $linearOnly = false) |
797
|
|
|
{ |
798
|
|
|
// NO-OP |
799
|
|
|
if (!$relation) { |
800
|
|
|
return $this->dataClass; |
801
|
|
|
} |
802
|
|
|
|
803
|
|
|
if (is_string($relation)) { |
804
|
|
|
$relation = explode(".", $relation); |
805
|
|
|
} |
806
|
|
|
|
807
|
|
|
$modelClass = $this->dataClass; |
808
|
|
|
|
809
|
|
|
$schema = DataObject::getSchema(); |
810
|
|
|
$currentRelation = []; |
811
|
|
|
foreach ($relation as $rel) { |
812
|
|
|
// Get prefix for join for this table (and parent to join on) |
813
|
|
|
$parentPrefix = $this->applyRelationPrefix($currentRelation); |
814
|
|
|
$currentRelation[] = $rel; |
815
|
|
|
$tablePrefix = $this->applyRelationPrefix($currentRelation); |
816
|
|
|
|
817
|
|
|
// Check has_one |
818
|
|
|
if ($component = $schema->hasOneComponent($modelClass, $rel)) { |
819
|
|
|
// Join via has_one |
820
|
|
|
$this->joinHasOneRelation($modelClass, $rel, $component, $parentPrefix, $tablePrefix); |
821
|
|
|
$modelClass = $component; |
822
|
|
|
continue; |
823
|
|
|
} |
824
|
|
|
|
825
|
|
|
// Check has_many |
826
|
|
|
if ($component = $schema->hasManyComponent($modelClass, $rel)) { |
827
|
|
|
// Fail on non-linear relations |
828
|
|
|
if ($linearOnly) { |
829
|
|
|
throw new InvalidArgumentException("$rel is not a linear relation on model $modelClass"); |
830
|
|
|
} |
831
|
|
|
// Join via has_many |
832
|
|
|
$this->joinHasManyRelation($modelClass, $rel, $component, $parentPrefix, $tablePrefix, 'has_many'); |
833
|
|
|
$modelClass = $component; |
834
|
|
|
continue; |
835
|
|
|
} |
836
|
|
|
|
837
|
|
|
// check belongs_to (like has_many but linear safe) |
838
|
|
|
if ($component = $schema->belongsToComponent($modelClass, $rel)) { |
839
|
|
|
// Piggy back off has_many logic |
840
|
|
|
$this->joinHasManyRelation($modelClass, $rel, $component, $parentPrefix, $tablePrefix, 'belongs_to'); |
841
|
|
|
$modelClass = $component; |
842
|
|
|
continue; |
843
|
|
|
} |
844
|
|
|
|
845
|
|
|
// Check many_many |
846
|
|
|
if ($component = $schema->manyManyComponent($modelClass, $rel)) { |
847
|
|
|
// Fail on non-linear relations |
848
|
|
|
if ($linearOnly) { |
849
|
|
|
throw new InvalidArgumentException("$rel is not a linear relation on model $modelClass"); |
850
|
|
|
} |
851
|
|
|
$this->joinManyManyRelationship( |
852
|
|
|
$component['relationClass'], |
853
|
|
|
$component['parentClass'], |
854
|
|
|
$component['childClass'], |
855
|
|
|
$component['parentField'], |
856
|
|
|
$component['childField'], |
857
|
|
|
$component['join'], |
858
|
|
|
$parentPrefix, |
859
|
|
|
$tablePrefix |
860
|
|
|
); |
861
|
|
|
$modelClass = $component['childClass']; |
862
|
|
|
continue; |
863
|
|
|
} |
864
|
|
|
|
865
|
|
|
// no relation |
866
|
|
|
throw new InvalidArgumentException("$rel is not a relation on model $modelClass"); |
867
|
|
|
} |
868
|
|
|
|
869
|
|
|
return $modelClass; |
870
|
|
|
} |
871
|
|
|
|
872
|
|
|
/** |
873
|
|
|
* Join the given has_many relation to this query. |
874
|
|
|
* Also works with belongs_to |
875
|
|
|
* |
876
|
|
|
* Doesn't work with polymorphic relationships |
877
|
|
|
* |
878
|
|
|
* @param string $localClass Name of class that has the has_many to the joined class |
879
|
|
|
* @param string $localField Name of the has_many relationship to join |
880
|
|
|
* @param string $foreignClass Class to join |
881
|
|
|
* @param string $localPrefix Table prefix for parent class |
882
|
|
|
* @param string $foreignPrefix Table prefix to use |
883
|
|
|
* @param string $type 'has_many' or 'belongs_to' |
884
|
|
|
*/ |
885
|
|
|
protected function joinHasManyRelation( |
886
|
|
|
$localClass, |
887
|
|
|
$localField, |
888
|
|
|
$foreignClass, |
889
|
|
|
$localPrefix = null, |
890
|
|
|
$foreignPrefix = null, |
891
|
|
|
$type = 'has_many' |
892
|
|
|
) { |
893
|
|
|
if (!$foreignClass || $foreignClass === DataObject::class) { |
894
|
|
|
throw new InvalidArgumentException("Could not find a has_many relationship {$localField} on {$localClass}"); |
895
|
|
|
} |
896
|
|
|
$schema = DataObject::getSchema(); |
897
|
|
|
|
898
|
|
|
// Skip if already joined |
899
|
|
|
// Note: don't just check base class, since we need to join on the table with the actual relation key |
900
|
|
|
$foreignTable = $schema->tableName($foreignClass); |
901
|
|
|
$foreignTableAliased = $foreignPrefix . $foreignTable; |
902
|
|
|
if ($this->query->isJoinedTo($foreignTableAliased)) { |
903
|
|
|
return; |
904
|
|
|
} |
905
|
|
|
|
906
|
|
|
// Join table with associated has_one |
907
|
|
|
/** @var DataObject $model */ |
908
|
|
|
$foreignKey = $schema->getRemoteJoinField($localClass, $localField, $type, $polymorphic); |
909
|
|
|
$localIDColumn = $schema->sqlColumnForField($localClass, 'ID', $localPrefix); |
910
|
|
|
if ($polymorphic) { |
911
|
|
|
$foreignKeyIDColumn = $schema->sqlColumnForField($foreignClass, "{$foreignKey}ID", $foreignPrefix); |
912
|
|
|
$foreignKeyClassColumn = $schema->sqlColumnForField($foreignClass, "{$foreignKey}Class", $foreignPrefix); |
913
|
|
|
$localClassColumn = $schema->sqlColumnForField($localClass, 'ClassName', $localPrefix); |
914
|
|
|
$joinExpression = |
915
|
|
|
"{$foreignKeyIDColumn} = {$localIDColumn} AND {$foreignKeyClassColumn} = {$localClassColumn}"; |
916
|
|
|
} else { |
917
|
|
|
$foreignKeyIDColumn = $schema->sqlColumnForField($foreignClass, $foreignKey, $foreignPrefix); |
918
|
|
|
$joinExpression = "{$foreignKeyIDColumn} = {$localIDColumn}"; |
919
|
|
|
} |
920
|
|
|
$this->query->addLeftJoin( |
921
|
|
|
$foreignTable, |
922
|
|
|
$joinExpression, |
923
|
|
|
$foreignTableAliased |
924
|
|
|
); |
925
|
|
|
|
926
|
|
|
// Add join clause to the component's ancestry classes so that the search filter could search on |
927
|
|
|
// its ancestor fields. |
928
|
|
|
$ancestry = ClassInfo::ancestry($foreignClass, true); |
929
|
|
|
$ancestry = array_reverse($ancestry); |
930
|
|
|
foreach ($ancestry as $ancestor) { |
931
|
|
|
$ancestorTable = $schema->tableName($ancestor); |
932
|
|
|
if ($ancestorTable !== $foreignTable) { |
933
|
|
|
$ancestorTableAliased = $foreignPrefix.$ancestorTable; |
934
|
|
|
$this->query->addLeftJoin( |
935
|
|
|
$ancestorTable, |
936
|
|
|
"\"{$foreignTableAliased}\".\"ID\" = \"{$ancestorTableAliased}\".\"ID\"", |
937
|
|
|
$ancestorTableAliased |
938
|
|
|
); |
939
|
|
|
} |
940
|
|
|
} |
941
|
|
|
} |
942
|
|
|
|
943
|
|
|
/** |
944
|
|
|
* Join the given class to this query with the given key |
945
|
|
|
* |
946
|
|
|
* @param string $localClass Name of class that has the has_one to the joined class |
947
|
|
|
* @param string $localField Name of the has_one relationship to joi |
948
|
|
|
* @param string $foreignClass Class to join |
949
|
|
|
* @param string $localPrefix Table prefix to use for local class |
950
|
|
|
* @param string $foreignPrefix Table prefix to use for joined table |
951
|
|
|
*/ |
952
|
|
|
protected function joinHasOneRelation( |
953
|
|
|
$localClass, |
954
|
|
|
$localField, |
955
|
|
|
$foreignClass, |
956
|
|
|
$localPrefix = null, |
957
|
|
|
$foreignPrefix = null |
958
|
|
|
) { |
959
|
|
|
if (!$foreignClass) { |
960
|
|
|
throw new InvalidArgumentException("Could not find a has_one relationship {$localField} on {$localClass}"); |
961
|
|
|
} |
962
|
|
|
|
963
|
|
|
if ($foreignClass === DataObject::class) { |
964
|
|
|
throw new InvalidArgumentException( |
965
|
|
|
"Could not join polymorphic has_one relationship {$localField} on {$localClass}" |
966
|
|
|
); |
967
|
|
|
} |
968
|
|
|
$schema = DataObject::getSchema(); |
969
|
|
|
|
970
|
|
|
// Skip if already joined |
971
|
|
|
$foreignBaseClass = $schema->baseDataClass($foreignClass); |
972
|
|
|
$foreignBaseTable = $schema->tableName($foreignBaseClass); |
973
|
|
|
if ($this->query->isJoinedTo($foreignPrefix.$foreignBaseTable)) { |
974
|
|
|
return; |
975
|
|
|
} |
976
|
|
|
|
977
|
|
|
// Join base table |
978
|
|
|
$foreignIDColumn = $schema->sqlColumnForField($foreignBaseClass, 'ID', $foreignPrefix); |
979
|
|
|
$localColumn = $schema->sqlColumnForField($localClass, "{$localField}ID", $localPrefix); |
980
|
|
|
$this->query->addLeftJoin( |
981
|
|
|
$foreignBaseTable, |
982
|
|
|
"{$foreignIDColumn} = {$localColumn}", |
983
|
|
|
$foreignPrefix.$foreignBaseTable |
984
|
|
|
); |
985
|
|
|
|
986
|
|
|
// Add join clause to the component's ancestry classes so that the search filter could search on |
987
|
|
|
// its ancestor fields. |
988
|
|
|
$ancestry = ClassInfo::ancestry($foreignClass, true); |
989
|
|
|
if (!empty($ancestry)) { |
990
|
|
|
$ancestry = array_reverse($ancestry); |
991
|
|
|
foreach ($ancestry as $ancestor) { |
992
|
|
|
$ancestorTable = $schema->tableName($ancestor); |
993
|
|
|
if ($ancestorTable !== $foreignBaseTable) { |
994
|
|
|
$ancestorTableAliased = $foreignPrefix.$ancestorTable; |
995
|
|
|
$this->query->addLeftJoin( |
996
|
|
|
$ancestorTable, |
997
|
|
|
"{$foreignIDColumn} = \"{$ancestorTableAliased}\".\"ID\"", |
998
|
|
|
$ancestorTableAliased |
999
|
|
|
); |
1000
|
|
|
} |
1001
|
|
|
} |
1002
|
|
|
} |
1003
|
|
|
} |
1004
|
|
|
|
1005
|
|
|
/** |
1006
|
|
|
* Join table via many_many relationship |
1007
|
|
|
* |
1008
|
|
|
* @param string $relationClass |
1009
|
|
|
* @param string $parentClass |
1010
|
|
|
* @param string $componentClass |
1011
|
|
|
* @param string $parentField |
1012
|
|
|
* @param string $componentField |
1013
|
|
|
* @param string $relationClassOrTable Name of relation table |
1014
|
|
|
* @param string $parentPrefix Table prefix for parent class |
1015
|
|
|
* @param string $componentPrefix Table prefix to use for both joined and mapping table |
1016
|
|
|
*/ |
1017
|
|
|
protected function joinManyManyRelationship( |
1018
|
|
|
$relationClass, |
|
|
|
|
1019
|
|
|
$parentClass, |
1020
|
|
|
$componentClass, |
1021
|
|
|
$parentField, |
1022
|
|
|
$componentField, |
1023
|
|
|
$relationClassOrTable, |
1024
|
|
|
$parentPrefix = null, |
1025
|
|
|
$componentPrefix = null |
1026
|
|
|
) { |
1027
|
|
|
$schema = DataObject::getSchema(); |
1028
|
|
|
|
1029
|
|
|
if (class_exists($relationClassOrTable)) { |
1030
|
|
|
$relationClassOrTable = $schema->tableName($relationClassOrTable); |
1031
|
|
|
} |
1032
|
|
|
|
1033
|
|
|
// Check if already joined to component alias (skip join table for the check) |
1034
|
|
|
$componentBaseClass = $schema->baseDataClass($componentClass); |
1035
|
|
|
$componentBaseTable = $schema->tableName($componentBaseClass); |
1036
|
|
|
$componentAliasedTable = $componentPrefix . $componentBaseTable; |
1037
|
|
|
if ($this->query->isJoinedTo($componentAliasedTable)) { |
1038
|
|
|
return; |
1039
|
|
|
} |
1040
|
|
|
|
1041
|
|
|
// Join parent class to join table |
1042
|
|
|
$relationAliasedTable = $componentPrefix.$relationClassOrTable; |
1043
|
|
|
$parentIDColumn = $schema->sqlColumnForField($parentClass, 'ID', $parentPrefix); |
1044
|
|
|
$this->query->addLeftJoin( |
1045
|
|
|
$relationClassOrTable, |
1046
|
|
|
"\"{$relationAliasedTable}\".\"{$parentField}\" = {$parentIDColumn}", |
1047
|
|
|
$relationAliasedTable |
1048
|
|
|
); |
1049
|
|
|
|
1050
|
|
|
// Join on base table of component class |
1051
|
|
|
$componentIDColumn = $schema->sqlColumnForField($componentBaseClass, 'ID', $componentPrefix); |
1052
|
|
|
$this->query->addLeftJoin( |
1053
|
|
|
$componentBaseTable, |
1054
|
|
|
"\"{$relationAliasedTable}\".\"{$componentField}\" = {$componentIDColumn}", |
1055
|
|
|
$componentAliasedTable |
1056
|
|
|
); |
1057
|
|
|
|
1058
|
|
|
// Add join clause to the component's ancestry classes so that the search filter could search on |
1059
|
|
|
// its ancestor fields. |
1060
|
|
|
$ancestry = ClassInfo::ancestry($componentClass, true); |
1061
|
|
|
$ancestry = array_reverse($ancestry); |
1062
|
|
|
foreach ($ancestry as $ancestor) { |
1063
|
|
|
$ancestorTable = $schema->tableName($ancestor); |
1064
|
|
|
if ($ancestorTable !== $componentBaseTable) { |
1065
|
|
|
$ancestorTableAliased = $componentPrefix.$ancestorTable; |
1066
|
|
|
$this->query->addLeftJoin( |
1067
|
|
|
$ancestorTable, |
1068
|
|
|
"{$componentIDColumn} = \"{$ancestorTableAliased}\".\"ID\"", |
1069
|
|
|
$ancestorTableAliased |
1070
|
|
|
); |
1071
|
|
|
} |
1072
|
|
|
} |
1073
|
|
|
} |
1074
|
|
|
|
1075
|
|
|
/** |
1076
|
|
|
* Removes the result of query from this query. |
1077
|
|
|
* |
1078
|
|
|
* @param DataQuery $subtractQuery |
1079
|
|
|
* @param string $field |
1080
|
|
|
* @return $this |
1081
|
|
|
*/ |
1082
|
|
|
public function subtract(DataQuery $subtractQuery, $field = 'ID') |
1083
|
|
|
{ |
1084
|
|
|
$fieldExpression = $subtractQuery->expressionForField($field); |
1085
|
|
|
$subSelect = $subtractQuery->getFinalisedQuery(); |
1086
|
|
|
$subSelect->setSelect(array()); |
1087
|
|
|
$subSelect->selectField($fieldExpression, $field); |
1088
|
|
|
$subSelect->setOrderBy(null); |
1089
|
|
|
$subSelectSQL = $subSelect->sql($subSelectParameters); |
1090
|
|
|
$this->where(array($this->expressionForField($field)." NOT IN ($subSelectSQL)" => $subSelectParameters)); |
1091
|
|
|
|
1092
|
|
|
return $this; |
1093
|
|
|
} |
1094
|
|
|
|
1095
|
|
|
/** |
1096
|
|
|
* Select the only given fields from the given table. |
1097
|
|
|
* |
1098
|
|
|
* @param string $table Unquoted table name (will be escaped automatically) |
1099
|
|
|
* @param array $fields Database column names (will be escaped automatically) |
1100
|
|
|
* @return $this |
1101
|
|
|
*/ |
1102
|
|
|
public function selectFromTable($table, $fields) |
1103
|
|
|
{ |
1104
|
|
|
$fieldExpressions = array_map(function ($item) use ($table) { |
1105
|
|
|
return Convert::symbol2sql("{$table}.{$item}"); |
1106
|
|
|
}, $fields); |
1107
|
|
|
|
1108
|
|
|
$this->query->setSelect($fieldExpressions); |
1109
|
|
|
|
1110
|
|
|
return $this; |
1111
|
|
|
} |
1112
|
|
|
|
1113
|
|
|
/** |
1114
|
|
|
* Add the given fields from the given table to the select statement. |
1115
|
|
|
* |
1116
|
|
|
* @param string $table Unquoted table name (will be escaped automatically) |
1117
|
|
|
* @param array $fields Database column names (will be escaped automatically) |
1118
|
|
|
* @return $this |
1119
|
|
|
*/ |
1120
|
|
|
public function addSelectFromTable($table, $fields) |
1121
|
|
|
{ |
1122
|
|
|
$fieldExpressions = array_map(function ($item) use ($table) { |
1123
|
|
|
return Convert::symbol2sql("{$table}.{$item}"); |
1124
|
|
|
}, $fields); |
1125
|
|
|
|
1126
|
|
|
$this->query->addSelect($fieldExpressions); |
1127
|
|
|
|
1128
|
|
|
return $this; |
1129
|
|
|
} |
1130
|
|
|
|
1131
|
|
|
/** |
1132
|
|
|
* Query the given field column from the database and return as an array. |
1133
|
|
|
* |
1134
|
|
|
* @param string $field See {@link expressionForField()}. |
1135
|
|
|
* @return array List of column values for the specified column |
1136
|
|
|
*/ |
1137
|
|
|
public function column($field = 'ID') |
1138
|
|
|
{ |
1139
|
|
|
$fieldExpression = $this->expressionForField($field); |
1140
|
|
|
$query = $this->getFinalisedQuery(array($field)); |
1141
|
|
|
$originalSelect = $query->getSelect(); |
1142
|
|
|
$query->setSelect(array()); |
1143
|
|
|
$query->selectField($fieldExpression, $field); |
1144
|
|
|
$this->ensureSelectContainsOrderbyColumns($query, $originalSelect); |
1145
|
|
|
|
1146
|
|
|
return $query->execute()->column($field); |
1147
|
|
|
} |
1148
|
|
|
|
1149
|
|
|
/** |
1150
|
|
|
* @param String $field Select statement identifier, either the unquoted column name, |
1151
|
|
|
* the full composite SQL statement, or the alias set through {@link SQLSelect->selectField()}. |
1152
|
|
|
* @return String The expression used to query this field via this DataQuery |
1153
|
|
|
*/ |
1154
|
|
|
protected function expressionForField($field) |
1155
|
|
|
{ |
1156
|
|
|
// Prepare query object for selecting this field |
1157
|
|
|
$query = $this->getFinalisedQuery(array($field)); |
1158
|
|
|
|
1159
|
|
|
// Allow query to define the expression for this field |
1160
|
|
|
$expression = $query->expressionForField($field); |
1161
|
|
|
if (!empty($expression)) { |
1162
|
|
|
return $expression; |
1163
|
|
|
} |
1164
|
|
|
|
1165
|
|
|
// Special case for ID, if not provided |
1166
|
|
|
if ($field === 'ID') { |
1167
|
|
|
return DataObject::getSchema()->sqlColumnForField($this->dataClass, 'ID'); |
1168
|
|
|
} |
1169
|
|
|
return null; |
1170
|
|
|
} |
1171
|
|
|
|
1172
|
|
|
/** |
1173
|
|
|
* Select the given field expressions. |
1174
|
|
|
* |
1175
|
|
|
* @param $fieldExpression String The field to select (escaped SQL statement) |
1176
|
|
|
* @param $alias String The alias of that field (escaped SQL statement) |
1177
|
|
|
*/ |
1178
|
|
|
public function selectField($fieldExpression, $alias = null) |
1179
|
|
|
{ |
1180
|
|
|
$this->query->selectField($fieldExpression, $alias); |
1181
|
|
|
} |
1182
|
|
|
|
1183
|
|
|
//// QUERY PARAMS |
1184
|
|
|
|
1185
|
|
|
/** |
1186
|
|
|
* An arbitrary store of query parameters that can be used by decorators. |
1187
|
|
|
*/ |
1188
|
|
|
private $queryParams; |
1189
|
|
|
|
1190
|
|
|
/** |
1191
|
|
|
* Set an arbitrary query parameter, that can be used by decorators to add additional meta-data to the query. |
1192
|
|
|
* It's expected that the $key will be namespaced, e.g, 'Versioned.stage' instead of just 'stage'. |
1193
|
|
|
* |
1194
|
|
|
* @param string $key |
1195
|
|
|
* @param string|array $value |
1196
|
|
|
* @return $this |
1197
|
|
|
*/ |
1198
|
|
|
public function setQueryParam($key, $value) |
1199
|
|
|
{ |
1200
|
|
|
$this->queryParams[$key] = $value; |
1201
|
|
|
return $this; |
1202
|
|
|
} |
1203
|
|
|
|
1204
|
|
|
/** |
1205
|
|
|
* Set an arbitrary query parameter, that can be used by decorators to add additional meta-data to the query. |
1206
|
|
|
* |
1207
|
|
|
* @param string $key |
1208
|
|
|
* @return string |
1209
|
|
|
*/ |
1210
|
|
|
public function getQueryParam($key) |
1211
|
|
|
{ |
1212
|
|
|
if (isset($this->queryParams[$key])) { |
1213
|
|
|
return $this->queryParams[$key]; |
1214
|
|
|
} |
1215
|
|
|
return null; |
1216
|
|
|
} |
1217
|
|
|
|
1218
|
|
|
/** |
1219
|
|
|
* Returns all query parameters |
1220
|
|
|
* @return array query parameters array |
1221
|
|
|
*/ |
1222
|
|
|
public function getQueryParams() |
1223
|
|
|
{ |
1224
|
|
|
return $this->queryParams; |
1225
|
|
|
} |
1226
|
|
|
|
1227
|
|
|
/** |
1228
|
|
|
* Get query manipulators |
1229
|
|
|
* |
1230
|
|
|
* @return DataQueryManipulator[] |
1231
|
|
|
*/ |
1232
|
|
|
public function getDataQueryManipulators() |
1233
|
|
|
{ |
1234
|
|
|
return $this->dataQueryManipulators; |
1235
|
|
|
} |
1236
|
|
|
|
1237
|
|
|
/** |
1238
|
|
|
* Assign callback to be invoked in getFinalisedQuery() |
1239
|
|
|
* |
1240
|
|
|
* @param DataQueryManipulator $manipulator |
1241
|
|
|
* @return $this |
1242
|
|
|
*/ |
1243
|
|
|
public function pushQueryManipulator(DataQueryManipulator $manipulator) |
1244
|
|
|
{ |
1245
|
|
|
$this->dataQueryManipulators[] = $manipulator; |
1246
|
|
|
return $this; |
1247
|
|
|
} |
1248
|
|
|
} |
1249
|
|
|
|
This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent.
Consider making the comparison explicit by using
empty(..)
or! empty(...)
instead.