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