@@ -15,189 +15,189 @@ |
||
15 | 15 | */ |
16 | 16 | class FindObjectsFromSqlQueryFactory extends AbstractQueryFactory |
17 | 17 | { |
18 | - private $mainTable; |
|
19 | - private $from; |
|
20 | - private $filterString; |
|
21 | - private $cache; |
|
22 | - private $cachePrefix; |
|
23 | - |
|
24 | - public function __construct(string $mainTable, string $from, $filterString, $orderBy, TDBMService $tdbmService, Schema $schema, OrderByAnalyzer $orderByAnalyzer, SchemaAnalyzer $schemaAnalyzer, Cache $cache, string $cachePrefix) |
|
25 | - { |
|
26 | - parent::__construct($tdbmService, $schema, $orderByAnalyzer, $orderBy); |
|
27 | - $this->mainTable = $mainTable; |
|
28 | - $this->from = $from; |
|
29 | - $this->filterString = $filterString; |
|
30 | - $this->schemaAnalyzer = $schemaAnalyzer; |
|
31 | - $this->cache = $cache; |
|
32 | - $this->cachePrefix = $cachePrefix; |
|
33 | - } |
|
34 | - |
|
35 | - protected function compute() |
|
36 | - { |
|
37 | - $connection = $this->tdbmService->getConnection(); |
|
38 | - |
|
39 | - $columnsList = null; |
|
40 | - |
|
41 | - $allFetchedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable); |
|
42 | - |
|
43 | - $columnDescList = []; |
|
44 | - |
|
45 | - $tableGroupName = $this->getTableGroupName($allFetchedTables); |
|
46 | - |
|
47 | - foreach ($this->schema->getTable($this->mainTable)->getColumns() as $column) { |
|
48 | - $columnName = $column->getName(); |
|
49 | - $columnDescList[] = [ |
|
50 | - 'as' => $columnName, |
|
51 | - 'table' => $this->mainTable, |
|
52 | - 'column' => $columnName, |
|
53 | - 'type' => $column->getType(), |
|
54 | - 'tableGroup' => $tableGroupName, |
|
55 | - ]; |
|
56 | - } |
|
57 | - |
|
58 | - $sql = 'SELECT DISTINCT '.implode(', ', array_map(function ($columnDesc) { |
|
59 | - return $this->tdbmService->getConnection()->quoteIdentifier($this->mainTable).'.'.$this->tdbmService->getConnection()->quoteIdentifier($columnDesc['column']); |
|
60 | - }, $columnDescList)).' FROM '.$this->from; |
|
61 | - |
|
62 | - if (count($allFetchedTables) > 1 || $this->orderBy) { |
|
63 | - list($columnDescList, $columnsList, $orderString) = $this->getColumnsList($this->mainTable, [], $this->orderBy); |
|
64 | - } |
|
65 | - |
|
66 | - // Let's compute the COUNT. |
|
67 | - $pkColumnNames = $this->schema->getTable($this->mainTable)->getPrimaryKeyColumns(); |
|
68 | - $pkColumnNames = array_map(function ($pkColumn) { |
|
69 | - return $this->tdbmService->getConnection()->quoteIdentifier($this->mainTable).'.'.$this->tdbmService->getConnection()->quoteIdentifier($pkColumn); |
|
70 | - }, $pkColumnNames); |
|
71 | - |
|
72 | - $countSql = 'SELECT COUNT(DISTINCT '.implode(', ', $pkColumnNames).') FROM '.$this->from; |
|
73 | - |
|
74 | - if (!empty($this->filterString)) { |
|
75 | - $sql .= ' WHERE '.$this->filterString; |
|
76 | - $countSql .= ' WHERE '.$this->filterString; |
|
77 | - } |
|
78 | - |
|
79 | - if (!empty($orderString)) { |
|
80 | - $sql .= ' ORDER BY '.$orderString; |
|
81 | - } |
|
82 | - |
|
83 | - if (stripos($countSql, 'GROUP BY') !== false) { |
|
84 | - throw new TDBMException('Unsupported use of GROUP BY in SQL request.'); |
|
85 | - } |
|
86 | - |
|
87 | - if ($columnsList !== null) { |
|
88 | - $joinSql = ''; |
|
89 | - $parentFks = $this->getParentRelationshipForeignKeys($this->mainTable); |
|
90 | - foreach ($parentFks as $fk) { |
|
91 | - $joinSql .= sprintf(' JOIN %s ON (%s.%s = %s.%s)', |
|
92 | - $connection->quoteIdentifier($fk->getForeignTableName()), |
|
93 | - $connection->quoteIdentifier($fk->getLocalTableName()), |
|
94 | - $connection->quoteIdentifier($fk->getLocalColumns()[0]), |
|
95 | - $connection->quoteIdentifier($fk->getForeignTableName()), |
|
96 | - $connection->quoteIdentifier($fk->getForeignColumns()[0]) |
|
97 | - ); |
|
98 | - } |
|
99 | - |
|
100 | - $childrenFks = $this->getChildrenRelationshipForeignKeys($this->mainTable); |
|
101 | - foreach ($childrenFks as $fk) { |
|
102 | - $joinSql .= sprintf(' LEFT JOIN %s ON (%s.%s = %s.%s)', |
|
103 | - $connection->quoteIdentifier($fk->getLocalTableName()), |
|
104 | - $connection->quoteIdentifier($fk->getForeignTableName()), |
|
105 | - $connection->quoteIdentifier($fk->getForeignColumns()[0]), |
|
106 | - $connection->quoteIdentifier($fk->getLocalTableName()), |
|
107 | - $connection->quoteIdentifier($fk->getLocalColumns()[0]) |
|
108 | - ); |
|
109 | - } |
|
110 | - |
|
111 | - $sql = 'SELECT '.implode(', ', $columnsList).' FROM ('.$sql.') AS '.$this->mainTable.' '.$joinSql; |
|
112 | - if (!empty($orderString)) { |
|
113 | - $sql .= ' ORDER BY '.$orderString; |
|
114 | - } |
|
115 | - } |
|
116 | - |
|
117 | - $this->magicSql = $sql; |
|
118 | - $this->magicSqlCount = $countSql; |
|
119 | - $this->columnDescList = $columnDescList; |
|
120 | - } |
|
121 | - |
|
122 | - /** |
|
123 | - * @param string $tableName |
|
124 | - * |
|
125 | - * @return ForeignKeyConstraint[] |
|
126 | - */ |
|
127 | - private function getParentRelationshipForeignKeys($tableName) |
|
128 | - { |
|
129 | - return $this->fromCache($this->cachePrefix.'_parentrelationshipfks_'.$tableName, function () use ($tableName) { |
|
130 | - return $this->getParentRelationshipForeignKeysWithoutCache($tableName); |
|
131 | - }); |
|
132 | - } |
|
133 | - |
|
134 | - /** |
|
135 | - * @param string $tableName |
|
136 | - * |
|
137 | - * @return ForeignKeyConstraint[] |
|
138 | - */ |
|
139 | - private function getParentRelationshipForeignKeysWithoutCache($tableName) |
|
140 | - { |
|
141 | - $parentFks = []; |
|
142 | - $currentTable = $tableName; |
|
143 | - while ($currentFk = $this->schemaAnalyzer->getParentRelationship($currentTable)) { |
|
144 | - $currentTable = $currentFk->getForeignTableName(); |
|
145 | - $parentFks[] = $currentFk; |
|
146 | - } |
|
147 | - |
|
148 | - return $parentFks; |
|
149 | - } |
|
150 | - |
|
151 | - /** |
|
152 | - * @param string $tableName |
|
153 | - * |
|
154 | - * @return ForeignKeyConstraint[] |
|
155 | - */ |
|
156 | - private function getChildrenRelationshipForeignKeys(string $tableName) : array |
|
157 | - { |
|
158 | - return $this->fromCache($this->cachePrefix.'_childrenrelationshipfks_'.$tableName, function () use ($tableName) { |
|
159 | - return $this->getChildrenRelationshipForeignKeysWithoutCache($tableName); |
|
160 | - }); |
|
161 | - } |
|
162 | - |
|
163 | - /** |
|
164 | - * @param string $tableName |
|
165 | - * |
|
166 | - * @return ForeignKeyConstraint[] |
|
167 | - */ |
|
168 | - private function getChildrenRelationshipForeignKeysWithoutCache(string $tableName) : array |
|
169 | - { |
|
170 | - $children = $this->schemaAnalyzer->getChildrenRelationships($tableName); |
|
171 | - |
|
172 | - if (!empty($children)) { |
|
173 | - $fksTables = array_map(function (ForeignKeyConstraint $fk) { |
|
174 | - return $this->getChildrenRelationshipForeignKeys($fk->getLocalTableName()); |
|
175 | - }, $children); |
|
176 | - |
|
177 | - $fks = array_merge($children, call_user_func_array('array_merge', $fksTables)); |
|
178 | - |
|
179 | - return $fks; |
|
180 | - } else { |
|
181 | - return []; |
|
182 | - } |
|
183 | - } |
|
184 | - |
|
185 | - /** |
|
186 | - * Returns an item from cache or computes it using $closure and puts it in cache. |
|
187 | - * |
|
188 | - * @param string $key |
|
189 | - * @param callable $closure |
|
190 | - * |
|
191 | - * @return mixed |
|
192 | - */ |
|
193 | - protected function fromCache(string $key, callable $closure) |
|
194 | - { |
|
195 | - $item = $this->cache->fetch($key); |
|
196 | - if ($item === false) { |
|
197 | - $item = $closure(); |
|
198 | - $this->cache->save($key, $item); |
|
199 | - } |
|
200 | - |
|
201 | - return $item; |
|
202 | - } |
|
18 | + private $mainTable; |
|
19 | + private $from; |
|
20 | + private $filterString; |
|
21 | + private $cache; |
|
22 | + private $cachePrefix; |
|
23 | + |
|
24 | + public function __construct(string $mainTable, string $from, $filterString, $orderBy, TDBMService $tdbmService, Schema $schema, OrderByAnalyzer $orderByAnalyzer, SchemaAnalyzer $schemaAnalyzer, Cache $cache, string $cachePrefix) |
|
25 | + { |
|
26 | + parent::__construct($tdbmService, $schema, $orderByAnalyzer, $orderBy); |
|
27 | + $this->mainTable = $mainTable; |
|
28 | + $this->from = $from; |
|
29 | + $this->filterString = $filterString; |
|
30 | + $this->schemaAnalyzer = $schemaAnalyzer; |
|
31 | + $this->cache = $cache; |
|
32 | + $this->cachePrefix = $cachePrefix; |
|
33 | + } |
|
34 | + |
|
35 | + protected function compute() |
|
36 | + { |
|
37 | + $connection = $this->tdbmService->getConnection(); |
|
38 | + |
|
39 | + $columnsList = null; |
|
40 | + |
|
41 | + $allFetchedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable); |
|
42 | + |
|
43 | + $columnDescList = []; |
|
44 | + |
|
45 | + $tableGroupName = $this->getTableGroupName($allFetchedTables); |
|
46 | + |
|
47 | + foreach ($this->schema->getTable($this->mainTable)->getColumns() as $column) { |
|
48 | + $columnName = $column->getName(); |
|
49 | + $columnDescList[] = [ |
|
50 | + 'as' => $columnName, |
|
51 | + 'table' => $this->mainTable, |
|
52 | + 'column' => $columnName, |
|
53 | + 'type' => $column->getType(), |
|
54 | + 'tableGroup' => $tableGroupName, |
|
55 | + ]; |
|
56 | + } |
|
57 | + |
|
58 | + $sql = 'SELECT DISTINCT '.implode(', ', array_map(function ($columnDesc) { |
|
59 | + return $this->tdbmService->getConnection()->quoteIdentifier($this->mainTable).'.'.$this->tdbmService->getConnection()->quoteIdentifier($columnDesc['column']); |
|
60 | + }, $columnDescList)).' FROM '.$this->from; |
|
61 | + |
|
62 | + if (count($allFetchedTables) > 1 || $this->orderBy) { |
|
63 | + list($columnDescList, $columnsList, $orderString) = $this->getColumnsList($this->mainTable, [], $this->orderBy); |
|
64 | + } |
|
65 | + |
|
66 | + // Let's compute the COUNT. |
|
67 | + $pkColumnNames = $this->schema->getTable($this->mainTable)->getPrimaryKeyColumns(); |
|
68 | + $pkColumnNames = array_map(function ($pkColumn) { |
|
69 | + return $this->tdbmService->getConnection()->quoteIdentifier($this->mainTable).'.'.$this->tdbmService->getConnection()->quoteIdentifier($pkColumn); |
|
70 | + }, $pkColumnNames); |
|
71 | + |
|
72 | + $countSql = 'SELECT COUNT(DISTINCT '.implode(', ', $pkColumnNames).') FROM '.$this->from; |
|
73 | + |
|
74 | + if (!empty($this->filterString)) { |
|
75 | + $sql .= ' WHERE '.$this->filterString; |
|
76 | + $countSql .= ' WHERE '.$this->filterString; |
|
77 | + } |
|
78 | + |
|
79 | + if (!empty($orderString)) { |
|
80 | + $sql .= ' ORDER BY '.$orderString; |
|
81 | + } |
|
82 | + |
|
83 | + if (stripos($countSql, 'GROUP BY') !== false) { |
|
84 | + throw new TDBMException('Unsupported use of GROUP BY in SQL request.'); |
|
85 | + } |
|
86 | + |
|
87 | + if ($columnsList !== null) { |
|
88 | + $joinSql = ''; |
|
89 | + $parentFks = $this->getParentRelationshipForeignKeys($this->mainTable); |
|
90 | + foreach ($parentFks as $fk) { |
|
91 | + $joinSql .= sprintf(' JOIN %s ON (%s.%s = %s.%s)', |
|
92 | + $connection->quoteIdentifier($fk->getForeignTableName()), |
|
93 | + $connection->quoteIdentifier($fk->getLocalTableName()), |
|
94 | + $connection->quoteIdentifier($fk->getLocalColumns()[0]), |
|
95 | + $connection->quoteIdentifier($fk->getForeignTableName()), |
|
96 | + $connection->quoteIdentifier($fk->getForeignColumns()[0]) |
|
97 | + ); |
|
98 | + } |
|
99 | + |
|
100 | + $childrenFks = $this->getChildrenRelationshipForeignKeys($this->mainTable); |
|
101 | + foreach ($childrenFks as $fk) { |
|
102 | + $joinSql .= sprintf(' LEFT JOIN %s ON (%s.%s = %s.%s)', |
|
103 | + $connection->quoteIdentifier($fk->getLocalTableName()), |
|
104 | + $connection->quoteIdentifier($fk->getForeignTableName()), |
|
105 | + $connection->quoteIdentifier($fk->getForeignColumns()[0]), |
|
106 | + $connection->quoteIdentifier($fk->getLocalTableName()), |
|
107 | + $connection->quoteIdentifier($fk->getLocalColumns()[0]) |
|
108 | + ); |
|
109 | + } |
|
110 | + |
|
111 | + $sql = 'SELECT '.implode(', ', $columnsList).' FROM ('.$sql.') AS '.$this->mainTable.' '.$joinSql; |
|
112 | + if (!empty($orderString)) { |
|
113 | + $sql .= ' ORDER BY '.$orderString; |
|
114 | + } |
|
115 | + } |
|
116 | + |
|
117 | + $this->magicSql = $sql; |
|
118 | + $this->magicSqlCount = $countSql; |
|
119 | + $this->columnDescList = $columnDescList; |
|
120 | + } |
|
121 | + |
|
122 | + /** |
|
123 | + * @param string $tableName |
|
124 | + * |
|
125 | + * @return ForeignKeyConstraint[] |
|
126 | + */ |
|
127 | + private function getParentRelationshipForeignKeys($tableName) |
|
128 | + { |
|
129 | + return $this->fromCache($this->cachePrefix.'_parentrelationshipfks_'.$tableName, function () use ($tableName) { |
|
130 | + return $this->getParentRelationshipForeignKeysWithoutCache($tableName); |
|
131 | + }); |
|
132 | + } |
|
133 | + |
|
134 | + /** |
|
135 | + * @param string $tableName |
|
136 | + * |
|
137 | + * @return ForeignKeyConstraint[] |
|
138 | + */ |
|
139 | + private function getParentRelationshipForeignKeysWithoutCache($tableName) |
|
140 | + { |
|
141 | + $parentFks = []; |
|
142 | + $currentTable = $tableName; |
|
143 | + while ($currentFk = $this->schemaAnalyzer->getParentRelationship($currentTable)) { |
|
144 | + $currentTable = $currentFk->getForeignTableName(); |
|
145 | + $parentFks[] = $currentFk; |
|
146 | + } |
|
147 | + |
|
148 | + return $parentFks; |
|
149 | + } |
|
150 | + |
|
151 | + /** |
|
152 | + * @param string $tableName |
|
153 | + * |
|
154 | + * @return ForeignKeyConstraint[] |
|
155 | + */ |
|
156 | + private function getChildrenRelationshipForeignKeys(string $tableName) : array |
|
157 | + { |
|
158 | + return $this->fromCache($this->cachePrefix.'_childrenrelationshipfks_'.$tableName, function () use ($tableName) { |
|
159 | + return $this->getChildrenRelationshipForeignKeysWithoutCache($tableName); |
|
160 | + }); |
|
161 | + } |
|
162 | + |
|
163 | + /** |
|
164 | + * @param string $tableName |
|
165 | + * |
|
166 | + * @return ForeignKeyConstraint[] |
|
167 | + */ |
|
168 | + private function getChildrenRelationshipForeignKeysWithoutCache(string $tableName) : array |
|
169 | + { |
|
170 | + $children = $this->schemaAnalyzer->getChildrenRelationships($tableName); |
|
171 | + |
|
172 | + if (!empty($children)) { |
|
173 | + $fksTables = array_map(function (ForeignKeyConstraint $fk) { |
|
174 | + return $this->getChildrenRelationshipForeignKeys($fk->getLocalTableName()); |
|
175 | + }, $children); |
|
176 | + |
|
177 | + $fks = array_merge($children, call_user_func_array('array_merge', $fksTables)); |
|
178 | + |
|
179 | + return $fks; |
|
180 | + } else { |
|
181 | + return []; |
|
182 | + } |
|
183 | + } |
|
184 | + |
|
185 | + /** |
|
186 | + * Returns an item from cache or computes it using $closure and puts it in cache. |
|
187 | + * |
|
188 | + * @param string $key |
|
189 | + * @param callable $closure |
|
190 | + * |
|
191 | + * @return mixed |
|
192 | + */ |
|
193 | + protected function fromCache(string $key, callable $closure) |
|
194 | + { |
|
195 | + $item = $this->cache->fetch($key); |
|
196 | + if ($item === false) { |
|
197 | + $item = $closure(); |
|
198 | + $this->cache->save($key, $item); |
|
199 | + } |
|
200 | + |
|
201 | + return $item; |
|
202 | + } |
|
203 | 203 | } |
@@ -55,17 +55,17 @@ discard block |
||
55 | 55 | ]; |
56 | 56 | } |
57 | 57 | |
58 | - $sql = 'SELECT DISTINCT '.implode(', ', array_map(function ($columnDesc) { |
|
58 | + $sql = 'SELECT DISTINCT '.implode(', ', array_map(function($columnDesc) { |
|
59 | 59 | return $this->tdbmService->getConnection()->quoteIdentifier($this->mainTable).'.'.$this->tdbmService->getConnection()->quoteIdentifier($columnDesc['column']); |
60 | 60 | }, $columnDescList)).' FROM '.$this->from; |
61 | 61 | |
62 | - if (count($allFetchedTables) > 1 || $this->orderBy) { |
|
62 | + if (count($allFetchedTables)>1 || $this->orderBy) { |
|
63 | 63 | list($columnDescList, $columnsList, $orderString) = $this->getColumnsList($this->mainTable, [], $this->orderBy); |
64 | 64 | } |
65 | 65 | |
66 | 66 | // Let's compute the COUNT. |
67 | 67 | $pkColumnNames = $this->schema->getTable($this->mainTable)->getPrimaryKeyColumns(); |
68 | - $pkColumnNames = array_map(function ($pkColumn) { |
|
68 | + $pkColumnNames = array_map(function($pkColumn) { |
|
69 | 69 | return $this->tdbmService->getConnection()->quoteIdentifier($this->mainTable).'.'.$this->tdbmService->getConnection()->quoteIdentifier($pkColumn); |
70 | 70 | }, $pkColumnNames); |
71 | 71 | |
@@ -126,7 +126,7 @@ discard block |
||
126 | 126 | */ |
127 | 127 | private function getParentRelationshipForeignKeys($tableName) |
128 | 128 | { |
129 | - return $this->fromCache($this->cachePrefix.'_parentrelationshipfks_'.$tableName, function () use ($tableName) { |
|
129 | + return $this->fromCache($this->cachePrefix.'_parentrelationshipfks_'.$tableName, function() use ($tableName) { |
|
130 | 130 | return $this->getParentRelationshipForeignKeysWithoutCache($tableName); |
131 | 131 | }); |
132 | 132 | } |
@@ -155,7 +155,7 @@ discard block |
||
155 | 155 | */ |
156 | 156 | private function getChildrenRelationshipForeignKeys(string $tableName) : array |
157 | 157 | { |
158 | - return $this->fromCache($this->cachePrefix.'_childrenrelationshipfks_'.$tableName, function () use ($tableName) { |
|
158 | + return $this->fromCache($this->cachePrefix.'_childrenrelationshipfks_'.$tableName, function() use ($tableName) { |
|
159 | 159 | return $this->getChildrenRelationshipForeignKeysWithoutCache($tableName); |
160 | 160 | }); |
161 | 161 | } |
@@ -170,7 +170,7 @@ discard block |
||
170 | 170 | $children = $this->schemaAnalyzer->getChildrenRelationships($tableName); |
171 | 171 | |
172 | 172 | if (!empty($children)) { |
173 | - $fksTables = array_map(function (ForeignKeyConstraint $fk) { |
|
173 | + $fksTables = array_map(function(ForeignKeyConstraint $fk) { |
|
174 | 174 | return $this->getChildrenRelationshipForeignKeys($fk->getLocalTableName()); |
175 | 175 | }, $children); |
176 | 176 |