Total Complexity | 144 |
Total Lines | 1309 |
Duplicated Lines | 0 % |
Changes | 2 | ||
Bugs | 2 | Features | 0 |
Complex classes like DataQuery often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use DataQuery, and based on these observations, apply Extract Interface, too.
1 | <?php |
||
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) |
||
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) |
||
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) |
||
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') |
||
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() |
||
1279 | } |
||
1280 | |||
1281 | /** |
||
1282 | * Get query manipulators |
||
1283 | * |
||
1284 | * @return DataQueryManipulator[] |
||
1285 | */ |
||
1286 | public function getDataQueryManipulators() |
||
1287 | { |
||
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) |
||
1331 | } |
||
1332 | } |
||
1333 |