1 | <?php |
||
2 | |||
3 | namespace ByJG\MicroOrm; |
||
4 | |||
5 | use ByJG\AnyDataset\Db\DbDriverInterface; |
||
6 | use ByJG\MicroOrm\Exception\InvalidArgumentException; |
||
7 | use ByJG\Serializer\BinderObject; |
||
8 | |||
9 | class Query |
||
10 | { |
||
11 | protected $fields = []; |
||
12 | protected $table = ""; |
||
13 | protected $alias = ""; |
||
14 | protected $where = []; |
||
15 | protected $groupBy = []; |
||
16 | protected $orderBy = []; |
||
17 | protected $join = []; |
||
18 | protected $limitStart = null; |
||
19 | protected $limitEnd = null; |
||
20 | protected $top = null; |
||
21 | protected $dbDriver = null; |
||
22 | |||
23 | protected $forUpdate = false; |
||
24 | |||
25 | 16 | public static function getInstance() |
|
26 | { |
||
27 | 16 | return new Query(); |
|
28 | } |
||
29 | |||
30 | /** |
||
31 | * Example: |
||
32 | * $query->fields(['name', 'price']); |
||
33 | * |
||
34 | * @param array $fields |
||
35 | * @return $this |
||
36 | * @throws \ByJG\Serializer\Exception\InvalidArgumentException |
||
37 | */ |
||
38 | 10 | public function fields(array $fields) |
|
39 | { |
||
40 | 10 | foreach ($fields as $field) { |
|
41 | 10 | if ($field instanceof Mapper) { |
|
42 | 1 | $this->addFieldFromMapper($field); |
|
43 | 1 | continue; |
|
44 | } |
||
45 | 9 | $this->fields[] = $field; |
|
46 | } |
||
47 | |||
48 | 10 | return $this; |
|
49 | } |
||
50 | |||
51 | /** |
||
52 | * @param \ByJG\MicroOrm\Mapper $mapper |
||
53 | * @throws \ByJG\Serializer\Exception\InvalidArgumentException |
||
54 | */ |
||
55 | 1 | private function addFieldFromMapper(Mapper $mapper) |
|
56 | { |
||
57 | 1 | $entityClass = $mapper->getEntity(); |
|
58 | 1 | $entity = new $entityClass(); |
|
59 | 1 | $serialized = BinderObject::toArrayFrom($entity); |
|
60 | |||
61 | 1 | foreach (array_keys($serialized) as $fieldName) { |
|
62 | 1 | $mapField = $mapper->getFieldMap($fieldName, Mapper::FIELDMAP_FIELD); |
|
63 | 1 | if (empty($mapField)) { |
|
64 | 1 | $mapField = $fieldName; |
|
65 | } |
||
66 | |||
67 | 1 | $alias = $mapper->getFieldAlias($mapField); |
|
68 | 1 | if (!empty($alias)) { |
|
69 | 1 | $alias = ' as ' . $alias; |
|
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
70 | } |
||
71 | |||
72 | 1 | $this->fields[] = $mapper->getTable() . '.' . $mapField . $alias; |
|
73 | } |
||
74 | 1 | } |
|
75 | |||
76 | /** |
||
77 | * Example |
||
78 | * $query->table('product'); |
||
79 | * |
||
80 | * @param string $table |
||
81 | * @param string $alias |
||
82 | * @return $this |
||
83 | */ |
||
84 | 43 | public function table($table, $alias = null) |
|
85 | { |
||
86 | 43 | $this->table = $table; |
|
87 | 43 | $this->alias = $alias; |
|
88 | |||
89 | 43 | return $this; |
|
90 | } |
||
91 | |||
92 | /** |
||
93 | * Example: |
||
94 | * $query->join('sales', 'product.id = sales.id'); |
||
95 | * |
||
96 | * @param Query|string $table |
||
97 | * @param string $filter |
||
98 | * @param string $alias |
||
99 | * @return $this |
||
100 | */ |
||
101 | 6 | public function join($table, $filter, $alias = null) |
|
102 | { |
||
103 | 6 | $this->join[] = [ 'table'=>$table, 'filter'=>$filter, 'type' => 'INNER', 'alias' => empty($alias) ? $table : $alias]; |
|
104 | 6 | return $this; |
|
105 | } |
||
106 | |||
107 | /** |
||
108 | * Example: |
||
109 | * $query->leftJoin('sales', 'product.id = sales.id'); |
||
110 | * |
||
111 | * @param Query|string $table |
||
112 | * @param string $filter |
||
113 | * @param string $alias |
||
114 | * @return $this |
||
115 | */ |
||
116 | 3 | public function leftJoin($table, $filter, $alias = null) |
|
117 | { |
||
118 | 3 | $this->join[] = [ 'table'=>$table, 'filter'=>$filter, 'type' => 'LEFT', 'alias' => empty($alias) ? $table : $alias]; |
|
119 | 3 | return $this; |
|
120 | } |
||
121 | |||
122 | /** |
||
123 | * Example: |
||
124 | * $query->rightJoin('sales', 'product.id = sales.id'); |
||
125 | * |
||
126 | * @param Query|string $table |
||
127 | * @param string $filter |
||
128 | * @param string $alias |
||
129 | * @return $this |
||
130 | */ |
||
131 | 2 | public function rightJoin($table, $filter, $alias = null) |
|
132 | { |
||
133 | 2 | $this->join[] = [ 'table'=>$table, 'filter'=>$filter, 'type' => 'RIGHT', 'alias' => empty($alias) ? $table : $alias]; |
|
134 | 2 | return $this; |
|
135 | } |
||
136 | |||
137 | /** |
||
138 | * Example: |
||
139 | * $query->filter('price > [[amount]]', [ 'amount' => 1000] ); |
||
140 | * |
||
141 | * @param string $filter |
||
142 | * @param array $params |
||
143 | * @return $this |
||
144 | */ |
||
145 | 40 | public function where($filter, array $params = []) |
|
146 | { |
||
147 | 40 | $this->where[] = [ 'filter' => $filter, 'params' => $params ]; |
|
148 | 40 | return $this; |
|
149 | } |
||
150 | |||
151 | /** |
||
152 | * Example: |
||
153 | * $query->groupBy(['name']); |
||
154 | * |
||
155 | * @param array $fields |
||
156 | * @return $this |
||
157 | */ |
||
158 | 4 | public function groupBy(array $fields) |
|
159 | { |
||
160 | 4 | $this->groupBy = array_merge($this->groupBy, $fields); |
|
161 | |||
162 | 4 | return $this; |
|
163 | } |
||
164 | |||
165 | /** |
||
166 | * Example: |
||
167 | * $query->orderBy(['price desc']); |
||
168 | * |
||
169 | * @param array $fields |
||
170 | * @return $this |
||
171 | */ |
||
172 | 6 | public function orderBy(array $fields) |
|
173 | { |
||
174 | 6 | $this->orderBy = array_merge($this->orderBy, $fields); |
|
175 | |||
176 | 6 | return $this; |
|
177 | } |
||
178 | |||
179 | public function forUpdate() |
||
180 | { |
||
181 | $this->forUpdate = true; |
||
182 | |||
183 | return $this; |
||
184 | } |
||
185 | |||
186 | /** |
||
187 | * @param $start |
||
188 | * @param $end |
||
189 | * @return $this |
||
190 | * @throws \ByJG\MicroOrm\Exception\InvalidArgumentException |
||
191 | */ |
||
192 | 2 | public function limit($start, $end) |
|
193 | { |
||
194 | 2 | if (!is_null($this->top)) { |
|
195 | throw new InvalidArgumentException('You cannot mix TOP and LIMIT'); |
||
196 | } |
||
197 | 2 | $this->limitStart = $start; |
|
198 | 2 | $this->limitEnd = $end; |
|
199 | 2 | return $this; |
|
200 | } |
||
201 | |||
202 | /** |
||
203 | * @param $top |
||
204 | * @return $this |
||
205 | * @throws \ByJG\MicroOrm\Exception\InvalidArgumentException |
||
206 | */ |
||
207 | 1 | public function top($top) |
|
208 | { |
||
209 | 1 | if (!is_null($this->limitStart)) { |
|
210 | throw new InvalidArgumentException('You cannot mix TOP and LIMIT'); |
||
211 | } |
||
212 | 1 | $this->top = $top; |
|
213 | 1 | return $this; |
|
214 | } |
||
215 | |||
216 | 43 | protected function getFields() |
|
217 | { |
||
218 | 43 | if (empty($this->fields)) { |
|
219 | 37 | return ' * '; |
|
220 | } |
||
221 | |||
222 | 10 | return ' ' . implode(', ', $this->fields) . ' '; |
|
223 | } |
||
224 | |||
225 | /** |
||
226 | * @return string |
||
227 | * @throws InvalidArgumentException |
||
228 | */ |
||
229 | 43 | protected function getJoin() |
|
230 | { |
||
231 | 43 | $joinStr = $this->table . (!empty($this->alias) ? " as " . $this->alias : ""); |
|
232 | 43 | foreach ($this->join as $item) { |
|
233 | 11 | $table = $item['table']; |
|
234 | 11 | if ($table instanceof Query) { |
|
235 | 3 | $subQuery = $table->build($this->dbDriver); |
|
236 | 3 | if (!empty($subQuery["params"])) { |
|
237 | 1 | throw new InvalidArgumentException("SubQuery does not support filters"); |
|
238 | } |
||
239 | 2 | if ($item["alias"] instanceof Query) { |
|
240 | 1 | throw new InvalidArgumentException("SubQuery requires you define an alias"); |
|
241 | } |
||
242 | 1 | $table = "(${subQuery["sql"]})"; |
|
243 | } |
||
244 | 9 | $alias = $item['table'] == $item['alias'] ? "" : " as ". $item['alias']; |
|
245 | 9 | $joinStr .= ' ' . $item['type'] . " JOIN $table$alias ON " . $item['filter']; |
|
246 | } |
||
247 | 43 | return $joinStr; |
|
248 | } |
||
249 | |||
250 | 43 | protected function getWhere() |
|
251 | { |
||
252 | 43 | $whereStr = []; |
|
253 | 43 | $params = []; |
|
254 | |||
255 | 43 | foreach ($this->where as $item) { |
|
256 | 39 | $whereStr[] = $item['filter']; |
|
257 | 39 | $params = array_merge($params, $item['params']); |
|
258 | } |
||
259 | |||
260 | 43 | if (empty($whereStr)) { |
|
261 | 6 | return null; |
|
262 | } |
||
263 | |||
264 | 39 | return [ implode(' AND ', $whereStr), $params ]; |
|
265 | } |
||
266 | |||
267 | /** |
||
268 | * @param \ByJG\AnyDataset\Db\DbDriverInterface|null $dbDriver |
||
269 | * @return array |
||
270 | * @throws \ByJG\MicroOrm\Exception\InvalidArgumentException |
||
271 | */ |
||
272 | 43 | public function build(DbDriverInterface $dbDriver = null) |
|
273 | { |
||
274 | 43 | $this->dbDriver = $dbDriver; |
|
275 | |||
276 | $sql = "SELECT " . |
||
277 | 43 | $this->getFields() . |
|
278 | 43 | "FROM " . $this->getJoin(); |
|
279 | |||
280 | 43 | $whereStr = $this->getWhere(); |
|
281 | 43 | $params = null; |
|
282 | 43 | if (!is_null($whereStr)) { |
|
283 | 39 | $sql .= ' WHERE ' . $whereStr[0]; |
|
284 | 39 | $params = $whereStr[1]; |
|
285 | } |
||
286 | |||
287 | 43 | $sql .= $this->addGroupBy(); |
|
288 | |||
289 | 43 | $sql .= $this->addOrderBy(); |
|
290 | |||
291 | 43 | $sql = $this->addforUpdate($dbDriver, $sql); |
|
292 | |||
293 | 43 | $sql = $this->addTop($dbDriver, $sql); |
|
294 | |||
295 | 43 | $sql = $this->addLimit($dbDriver, $sql); |
|
296 | |||
297 | 43 | $sql = ORMHelper::processLiteral($sql, $params); |
|
298 | |||
299 | 43 | return [ 'sql' => $sql, 'params' => $params ]; |
|
300 | } |
||
301 | |||
302 | 43 | private function addOrderBy() |
|
303 | { |
||
304 | 43 | if (empty($this->orderBy)) { |
|
305 | 38 | return ""; |
|
306 | } |
||
307 | 6 | return ' ORDER BY ' . implode(', ', $this->orderBy); |
|
308 | } |
||
309 | |||
310 | 43 | private function addGroupBy() |
|
311 | { |
||
312 | 43 | if (empty($this->groupBy)) { |
|
313 | 41 | return ""; |
|
314 | } |
||
315 | 4 | return ' GROUP BY ' . implode(', ', $this->groupBy); |
|
316 | } |
||
317 | |||
318 | /** |
||
319 | * @param DbDriverInterface $dbDriver |
||
320 | * @param string $sql |
||
321 | * @return string |
||
322 | * @throws \ByJG\MicroOrm\Exception\InvalidArgumentException |
||
323 | */ |
||
324 | 43 | private function addforUpdate($dbDriver, $sql) |
|
325 | { |
||
326 | 43 | if (empty($this->forUpdate)) { |
|
327 | 43 | return $sql; |
|
328 | } |
||
329 | |||
330 | if (is_null($dbDriver)) { |
||
331 | throw new InvalidArgumentException('To get FOR UPDATE working you have to pass the DbDriver'); |
||
332 | } |
||
333 | |||
334 | return $dbDriver->getDbHelper()->forUpdate($sql); |
||
335 | } |
||
336 | |||
337 | /** |
||
338 | * @param DbDriverInterface $dbDriver |
||
339 | * @param string $sql |
||
340 | * @return string |
||
341 | * @throws \ByJG\MicroOrm\Exception\InvalidArgumentException |
||
342 | */ |
||
343 | 43 | private function addTop($dbDriver, $sql) |
|
344 | { |
||
345 | 43 | if (empty($this->top)) { |
|
346 | 42 | return $sql; |
|
347 | } |
||
348 | |||
349 | 1 | if (is_null($dbDriver)) { |
|
350 | throw new InvalidArgumentException('To get Limit and Top working you have to pass the DbDriver'); |
||
351 | } |
||
352 | |||
353 | 1 | return $dbDriver->getDbHelper()->top($sql, $this->top); |
|
354 | } |
||
355 | |||
356 | /** |
||
357 | * @param DbDriverInterface $dbDriver |
||
358 | * @param string $sql |
||
359 | * @return string |
||
360 | * @throws \ByJG\MicroOrm\Exception\InvalidArgumentException |
||
361 | */ |
||
362 | 43 | private function addLimit($dbDriver, $sql) |
|
363 | { |
||
364 | 43 | if (empty($this->limitStart) && ($this->limitStart !== 0)) { |
|
365 | 41 | return $sql; |
|
366 | } |
||
367 | |||
368 | 2 | if (is_null($dbDriver)) { |
|
369 | throw new InvalidArgumentException('To get Limit and Top working you have to pass the DbDriver'); |
||
370 | } |
||
371 | |||
372 | 2 | return $dbDriver->getDbHelper()->limit($sql, $this->limitStart, $this->limitEnd); |
|
373 | } |
||
374 | } |
||
375 |