1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* Created by PhpStorm. |
4
|
|
|
* User: jg |
5
|
|
|
* Date: 21/06/16 |
6
|
|
|
* Time: 12:01 |
7
|
|
|
*/ |
8
|
|
|
|
9
|
|
|
namespace ByJG\MicroOrm; |
10
|
|
|
|
11
|
|
|
use ByJG\AnyDataset\DbDriverInterface; |
12
|
|
|
use ByJG\Serializer\BinderObject; |
13
|
|
|
|
14
|
|
|
class Query |
15
|
|
|
{ |
16
|
|
|
protected $fields = []; |
17
|
|
|
protected $table = ""; |
18
|
|
|
protected $where = []; |
19
|
|
|
protected $groupBy = []; |
20
|
|
|
protected $orderBy = []; |
21
|
|
|
protected $join = []; |
22
|
|
|
protected $limitStart = null; |
23
|
|
|
protected $limitEnd = null; |
24
|
|
|
protected $top = null; |
25
|
|
|
|
26
|
|
|
protected $forUpdate = false; |
27
|
|
|
|
28
|
6 |
|
public static function getInstance() |
29
|
|
|
{ |
30
|
6 |
|
return new Query(); |
31
|
|
|
} |
32
|
|
|
|
33
|
|
|
/** |
34
|
|
|
* Example: |
35
|
|
|
* $query->fields(['name', 'price']); |
36
|
|
|
* |
37
|
|
|
* @param array $fields |
38
|
|
|
* @return $this |
39
|
|
|
*/ |
40
|
5 |
|
public function fields(array $fields) |
41
|
|
|
{ |
42
|
5 |
|
foreach ($fields as $field) { |
43
|
5 |
|
if ($field instanceof Mapper) { |
44
|
1 |
|
$this->addFieldFromMapper($field); |
45
|
1 |
|
continue; |
46
|
|
|
} |
47
|
4 |
|
$this->fields[] = $field; |
48
|
5 |
|
} |
49
|
|
|
|
50
|
5 |
|
return $this; |
51
|
|
|
} |
52
|
|
|
|
53
|
1 |
|
private function addFieldFromMapper(Mapper $mapper) |
54
|
|
|
{ |
55
|
1 |
|
$entityClass = $mapper->getEntity(); |
56
|
1 |
|
$entity = new $entityClass(); |
57
|
1 |
|
$serialized = BinderObject::toArrayFrom($entity); |
58
|
|
|
|
59
|
1 |
|
foreach (array_keys($serialized) as $fieldName) { |
60
|
1 |
|
$mapField = $mapper->getFieldMap($fieldName, Mapper::FIELDMAP_FIELD); |
61
|
1 |
|
if (empty($mapField)) { |
62
|
1 |
|
$mapField = $fieldName; |
63
|
1 |
|
} |
64
|
|
|
|
65
|
1 |
|
$alias = $mapper->getFieldAlias($mapField); |
66
|
1 |
|
if (!empty($alias)) { |
67
|
1 |
|
$alias = ' as ' . $alias; |
68
|
1 |
|
} |
69
|
|
|
|
70
|
1 |
|
$this->fields[] = $mapper->getTable() . '.' . $mapField . $alias; |
71
|
1 |
|
} |
72
|
1 |
|
} |
73
|
|
|
|
74
|
|
|
/** |
75
|
|
|
* Example |
76
|
|
|
* $query->table('product'); |
77
|
|
|
* |
78
|
|
|
* @param string $table |
79
|
|
|
* @return $this |
80
|
|
|
*/ |
81
|
24 |
|
public function table($table) |
82
|
|
|
{ |
83
|
24 |
|
$this->table = $table; |
84
|
|
|
|
85
|
24 |
|
return $this; |
86
|
|
|
} |
87
|
|
|
|
88
|
|
|
/** |
89
|
|
|
* Example: |
90
|
|
|
* $query->join('sales', 'product.id = sales.id'); |
91
|
|
|
* |
92
|
|
|
* @param string $table |
93
|
|
|
* @param string $filter |
94
|
|
|
* @return $this |
95
|
|
|
*/ |
96
|
1 |
|
public function join($table, $filter) |
97
|
|
|
{ |
98
|
1 |
|
$this->join[] = [ 'table'=>$table, 'filter'=>$filter, 'type' => 'INNER']; |
99
|
1 |
|
return $this; |
100
|
|
|
} |
101
|
|
|
|
102
|
|
|
/** |
103
|
|
|
* Example: |
104
|
|
|
* $query->join('sales', 'product.id = sales.id'); |
105
|
|
|
* |
106
|
|
|
* @param string $table |
107
|
|
|
* @param string $filter |
108
|
|
|
* @return $this |
109
|
|
|
*/ |
110
|
1 |
|
public function leftJoin($table, $filter) |
111
|
|
|
{ |
112
|
1 |
|
$this->join[] = [ 'table'=>$table, 'filter'=>$filter, 'type' => 'LEFT']; |
113
|
1 |
|
return $this; |
114
|
|
|
} |
115
|
|
|
|
116
|
|
|
/** |
117
|
|
|
* Example: |
118
|
|
|
* $query->filter('price > [[amount]]', [ 'amount' => 1000] ); |
119
|
|
|
* |
120
|
|
|
* @param string $filter |
121
|
|
|
* @param array $params |
122
|
|
|
* @return $this |
123
|
|
|
*/ |
124
|
22 |
|
public function where($filter, array $params = []) |
125
|
|
|
{ |
126
|
22 |
|
$this->where[] = [ 'filter' => $filter, 'params' => $params ]; |
127
|
22 |
|
return $this; |
128
|
|
|
} |
129
|
|
|
|
130
|
|
|
/** |
131
|
|
|
* Example: |
132
|
|
|
* $query->groupBy(['name']); |
133
|
|
|
* |
134
|
|
|
* @param array $fields |
135
|
|
|
* @return $this |
136
|
|
|
*/ |
137
|
1 |
|
public function groupBy(array $fields) |
138
|
|
|
{ |
139
|
1 |
|
$this->groupBy = array_merge($this->groupBy, $fields); |
140
|
|
|
|
141
|
1 |
|
return $this; |
142
|
|
|
} |
143
|
|
|
|
144
|
|
|
/** |
145
|
|
|
* Example: |
146
|
|
|
* $query->orderBy(['price desc']); |
147
|
|
|
* |
148
|
|
|
* @param array $fields |
149
|
|
|
* @return $this |
150
|
|
|
*/ |
151
|
4 |
|
public function orderBy(array $fields) |
152
|
|
|
{ |
153
|
4 |
|
$this->orderBy = array_merge($this->orderBy, $fields); |
154
|
|
|
|
155
|
4 |
|
return $this; |
156
|
|
|
} |
157
|
|
|
|
158
|
|
|
public function forUpdate() |
159
|
|
|
{ |
160
|
|
|
$this->forUpdate = true; |
161
|
|
|
|
162
|
|
|
return $this; |
163
|
|
|
} |
164
|
|
|
|
165
|
1 |
|
public function limit($start, $end) |
166
|
|
|
{ |
167
|
1 |
|
if (!is_null($this->top)) { |
168
|
|
|
throw new \InvalidArgumentException('You cannot mix TOP and LIMIT'); |
169
|
|
|
} |
170
|
1 |
|
$this->limitStart = $start; |
171
|
1 |
|
$this->limitEnd = $end; |
172
|
1 |
|
return $this; |
173
|
|
|
} |
174
|
|
|
|
175
|
1 |
|
public function top($top) |
176
|
|
|
{ |
177
|
1 |
|
if (!is_null($this->limitStart)) { |
178
|
|
|
throw new \InvalidArgumentException('You cannot mix TOP and LIMIT'); |
179
|
|
|
} |
180
|
1 |
|
$this->top = $top; |
181
|
1 |
|
return $this; |
182
|
|
|
} |
183
|
|
|
|
184
|
24 |
View Code Duplication |
protected function getFields() |
|
|
|
|
185
|
|
|
{ |
186
|
24 |
|
if (empty($this->fields)) { |
187
|
20 |
|
return ' * '; |
188
|
|
|
} |
189
|
|
|
|
190
|
5 |
|
return ' ' . implode(', ', $this->fields) . ' '; |
191
|
|
|
} |
192
|
|
|
|
193
|
24 |
|
protected function getJoin() |
194
|
|
|
{ |
195
|
24 |
|
$join = $this->table; |
196
|
24 |
|
foreach ($this->join as $item) { |
197
|
2 |
|
$join .= ' ' . $item['type'] . ' JOIN ' . $item['table'] . ' ON ' . $item['filter']; |
198
|
24 |
|
} |
199
|
24 |
|
return $join; |
200
|
|
|
} |
201
|
|
|
|
202
|
24 |
View Code Duplication |
protected function getWhere() |
|
|
|
|
203
|
|
|
{ |
204
|
24 |
|
$where = []; |
205
|
24 |
|
$params = []; |
206
|
|
|
|
207
|
24 |
|
foreach ($this->where as $item) { |
208
|
22 |
|
$where[] = $item['filter']; |
209
|
22 |
|
$params = array_merge($params, $item['params']); |
210
|
24 |
|
} |
211
|
|
|
|
212
|
24 |
|
if (empty($where)) { |
213
|
3 |
|
return null; |
214
|
|
|
} |
215
|
|
|
|
216
|
22 |
|
return [ implode(' AND ', $where), $params ]; |
217
|
|
|
} |
218
|
|
|
|
219
|
|
|
/** |
220
|
|
|
* @param \ByJG\AnyDataset\DbDriverInterface|null $dbDriver |
221
|
|
|
* @return array |
222
|
|
|
*/ |
223
|
24 |
|
public function build(DbDriverInterface $dbDriver = null) |
224
|
|
|
{ |
225
|
|
|
$sql = "SELECT " . |
226
|
24 |
|
$this->getFields() . |
227
|
24 |
|
"FROM " . $this->getJoin(); |
228
|
|
|
|
229
|
24 |
|
$where = $this->getWhere(); |
230
|
24 |
|
$params = null; |
231
|
24 |
|
if (!is_null($where)) { |
232
|
22 |
|
$sql .= ' WHERE ' . $where[0]; |
233
|
22 |
|
$params = $where[1]; |
234
|
22 |
|
} |
235
|
|
|
|
236
|
24 |
|
if (!empty($this->groupBy)) { |
237
|
1 |
|
$sql .= ' GROUP BY ' . implode(', ', $this->groupBy); |
238
|
1 |
|
} |
239
|
|
|
|
240
|
24 |
|
if (!empty($this->orderBy)) { |
241
|
4 |
|
$sql .= ' ORDER BY ' . implode(', ', $this->orderBy); |
242
|
4 |
|
} |
243
|
|
|
|
244
|
24 |
View Code Duplication |
if (!empty($this->forUpdate)) { |
|
|
|
|
245
|
|
|
if (is_null($dbDriver)) { |
246
|
|
|
throw new \InvalidArgumentException('To get FOR UPDATE working you have to pass the DbDriver'); |
247
|
|
|
} |
248
|
|
|
$sql = $dbDriver->getDbHelper()->forUpdate($sql); |
249
|
|
|
} |
250
|
|
|
|
251
|
24 |
View Code Duplication |
if (!empty($this->top)) { |
|
|
|
|
252
|
1 |
|
if (is_null($dbDriver)) { |
253
|
|
|
throw new \InvalidArgumentException('To get Limit and Top working you have to pass the DbDriver'); |
254
|
|
|
} |
255
|
1 |
|
$sql = $dbDriver->getDbHelper()->top($sql, $this->top); |
256
|
1 |
|
} |
257
|
|
|
|
258
|
24 |
|
if (!empty($this->limitStart) || ($this->limitStart === 0)) { |
259
|
1 |
|
if (is_null($dbDriver)) { |
260
|
|
|
throw new \InvalidArgumentException('To get Limit and Top working you have to pass the DbDriver'); |
261
|
|
|
} |
262
|
1 |
|
$sql = $dbDriver->getDbHelper()->limit($sql, $this->limitStart, $this->limitEnd); |
263
|
1 |
|
} |
264
|
|
|
|
265
|
24 |
|
$sql = ORMHelper::processLiteral($sql, $params); |
266
|
|
|
|
267
|
24 |
|
return [ 'sql' => $sql, 'params' => $params ]; |
268
|
|
|
} |
269
|
|
|
} |
270
|
|
|
|
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.