1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
/** |
4
|
|
|
* This file is part of the miBadger package. |
5
|
|
|
* |
6
|
|
|
* @author Michael Webbers <[email protected]> |
7
|
|
|
* @license http://opensource.org/licenses/Apache-2.0 Apache v2 License |
8
|
|
|
* @version 1.0.0 |
9
|
|
|
*/ |
10
|
|
|
|
11
|
|
|
namespace miBadger\Query; |
12
|
|
|
|
13
|
|
|
/** |
14
|
|
|
* The query builder class. |
15
|
|
|
* |
16
|
|
|
* @since 1.0.0 |
17
|
|
|
*/ |
18
|
|
|
class QueryBuilder implements QueryInterface |
19
|
|
|
{ |
20
|
|
|
/* @var string The modifier. SELECT, INSERT INTO, UPDATE or DELETE */ |
21
|
|
|
private $modifier; |
22
|
|
|
|
23
|
|
|
/* @var string The table name. */ |
24
|
|
|
private $table; |
25
|
|
|
|
26
|
|
|
/* @var array The columns. */ |
27
|
|
|
private $columns; |
28
|
|
|
|
29
|
|
|
/* @var array The values. */ |
30
|
|
|
private $values; |
31
|
|
|
|
32
|
|
|
/* @var array The where conditions. */ |
33
|
|
|
private $where; |
34
|
|
|
|
35
|
|
|
/* @var array The group by conditions. */ |
36
|
|
|
private $groupBy; |
37
|
|
|
|
38
|
|
|
/* @var array The order by conditions. */ |
39
|
|
|
private $orderBy; |
40
|
|
|
|
41
|
|
|
/* @var string The limit. */ |
42
|
|
|
private $limit; |
43
|
|
|
|
44
|
|
|
/* @var string The offset. */ |
45
|
|
|
private $offset; |
46
|
|
|
|
47
|
|
|
/** |
48
|
|
|
* Construct a query object with the given table. |
49
|
|
|
* |
50
|
|
|
* @param string $table |
51
|
|
|
*/ |
52
|
28 |
|
public function __construct($table) |
53
|
|
|
{ |
54
|
28 |
|
$this->table = $table; |
55
|
28 |
|
$this->where = []; |
56
|
28 |
|
$this->groupBy = []; |
57
|
28 |
|
$this->orderBy = []; |
58
|
28 |
|
} |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* Returns a string representation of the query object. |
62
|
|
|
* |
63
|
|
|
* @return string a string representation of the query object. |
64
|
|
|
*/ |
65
|
28 |
|
public function __toString() |
66
|
|
|
{ |
67
|
28 |
|
switch ($this->modifier) { |
68
|
28 |
|
case self::SELECT: |
69
|
18 |
|
$result = $this->getSelectClause(); |
70
|
|
|
|
71
|
18 |
|
if ($where = $this->getWhereClause()) { |
72
|
3 |
|
$result .= ' ' . $where; |
73
|
3 |
|
} |
74
|
|
|
|
75
|
18 |
|
if ($groupBy = $this->getGroupByClause()) { |
76
|
2 |
|
$result .= ' ' . $groupBy; |
77
|
2 |
|
} |
78
|
|
|
|
79
|
18 |
|
if ($orderBy = $this->getOrderByClause()) { |
80
|
4 |
|
$result .= ' ' . $orderBy; |
81
|
4 |
|
} |
82
|
|
|
|
83
|
18 |
|
if ($limit = $this->getLimitClause()) { |
84
|
4 |
|
$result .= ' ' . $limit; |
85
|
4 |
|
} |
86
|
|
|
|
87
|
18 |
|
if ($offset = $this->getOffsetClause()) { |
88
|
2 |
|
$result .= ' ' . $offset; |
89
|
2 |
|
} |
90
|
|
|
|
91
|
18 |
|
return $result; |
92
|
|
|
|
93
|
10 |
|
case self::INSERT: |
94
|
2 |
|
$result = $this->getInsertClause(); |
95
|
|
|
|
96
|
2 |
|
return $result; |
97
|
|
|
|
98
|
8 |
View Code Duplication |
case self::UPDATE: |
|
|
|
|
99
|
3 |
|
$result = $this->getUpdateClause(); |
100
|
|
|
|
101
|
3 |
|
if ($where = $this->getWhereClause()) { |
102
|
3 |
|
$result .= ' ' . $where; |
103
|
3 |
|
} |
104
|
|
|
|
105
|
3 |
|
if ($limit = $this->getLimitClause()) { |
106
|
1 |
|
$result .= ' ' . $limit; |
107
|
1 |
|
} |
108
|
|
|
|
109
|
3 |
|
return $result; |
110
|
|
|
|
111
|
5 |
View Code Duplication |
case self::DELETE: |
|
|
|
|
112
|
3 |
|
$result = $this->getDeleteClause(); |
113
|
|
|
|
114
|
3 |
|
if ($where = $this->getWhereClause()) { |
115
|
3 |
|
$result .= ' ' . $where; |
116
|
3 |
|
} |
117
|
|
|
|
118
|
3 |
|
if ($limit = $this->getLimitClause()) { |
119
|
1 |
|
$result .= ' ' . $limit; |
120
|
1 |
|
} |
121
|
|
|
|
122
|
3 |
|
return $result; |
123
|
|
|
|
124
|
2 |
|
default: |
125
|
2 |
|
return ''; |
126
|
2 |
|
} |
127
|
|
|
} |
128
|
|
|
|
129
|
|
|
/** |
130
|
|
|
* {@inheritdoc} |
131
|
|
|
*/ |
132
|
18 |
|
public function select($columns = ['*']) |
133
|
|
|
{ |
134
|
18 |
|
$this->columns = is_array($columns) ? $columns : func_get_args(); |
135
|
18 |
|
$this->modifier = self::SELECT; |
136
|
|
|
|
137
|
18 |
|
return $this; |
138
|
|
|
} |
139
|
|
|
|
140
|
|
|
/** |
141
|
|
|
* Returns the select clause. |
142
|
|
|
* |
143
|
|
|
* @return string the select clause. |
144
|
|
|
*/ |
145
|
18 |
|
public function getSelectClause() |
146
|
|
|
{ |
147
|
18 |
|
return sprintf('SELECT %s FROM %s', implode(', ', $this->columns), $this->table); |
148
|
|
|
} |
149
|
|
|
|
150
|
|
|
/** |
151
|
|
|
* {@inheritdoc} |
152
|
|
|
*/ |
153
|
2 |
|
public function insert(array $values) |
154
|
|
|
{ |
155
|
2 |
|
$this->values = $values; |
156
|
2 |
|
$this->modifier = self::INSERT; |
157
|
|
|
|
158
|
2 |
|
return $this; |
159
|
|
|
} |
160
|
|
|
|
161
|
|
|
/** |
162
|
|
|
* Returns the insert clause. |
163
|
|
|
* |
164
|
|
|
* @return string the insert clause. |
165
|
|
|
*/ |
166
|
2 |
|
public function getInsertClause() |
167
|
|
|
{ |
168
|
2 |
|
$columns = []; |
169
|
2 |
|
$values = []; |
170
|
|
|
|
171
|
2 |
|
foreach ($this->values as $key => $value) { |
172
|
2 |
|
$columns[] = $key; |
173
|
2 |
|
$values[] = sprintf('%s', $value); |
174
|
2 |
|
} |
175
|
|
|
|
176
|
2 |
|
return sprintf('INSERT INTO %s (%s) VALUES (%s)', $this->table, implode(', ', $columns), implode(', ', $values)); |
177
|
|
|
} |
178
|
|
|
|
179
|
|
|
/** |
180
|
|
|
* {@inheritdoc} |
181
|
|
|
*/ |
182
|
3 |
|
public function update(array $values) |
183
|
|
|
{ |
184
|
3 |
|
$this->values = $values; |
185
|
3 |
|
$this->modifier = self::UPDATE; |
186
|
|
|
|
187
|
3 |
|
return $this; |
188
|
|
|
} |
189
|
|
|
|
190
|
|
|
/** |
191
|
|
|
* Returns the update clause. |
192
|
|
|
* |
193
|
|
|
* @return string the update clause. |
194
|
|
|
*/ |
195
|
3 |
|
public function getUpdateClause() |
196
|
|
|
{ |
197
|
3 |
|
$placeholders = []; |
198
|
|
|
|
199
|
3 |
|
foreach ($this->values as $key => $value) { |
200
|
3 |
|
$placeholders[] = sprintf('%s = %s', $key, $value); |
201
|
3 |
|
} |
202
|
|
|
|
203
|
3 |
|
return sprintf('UPDATE %s SET %s', $this->table, implode(', ', $placeholders)); |
204
|
|
|
} |
205
|
|
|
|
206
|
|
|
/** |
207
|
|
|
* {@inheritdoc} |
208
|
|
|
*/ |
209
|
3 |
|
public function delete() |
210
|
|
|
{ |
211
|
3 |
|
$this->modifier = self::DELETE; |
212
|
|
|
|
213
|
3 |
|
return $this; |
214
|
|
|
} |
215
|
|
|
|
216
|
|
|
/** |
217
|
|
|
* Returns the delete clause. |
218
|
|
|
* |
219
|
|
|
* @return string the delete clause. |
220
|
|
|
*/ |
221
|
3 |
|
public function getDeleteClause() |
222
|
|
|
{ |
223
|
3 |
|
return sprintf('DELETE FROM %s', $this->table); |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
/** |
227
|
|
|
* {@inheritdoc} |
228
|
|
|
*/ |
229
|
9 |
|
public function where($column, $operator, $value) |
230
|
|
|
{ |
231
|
9 |
|
$this->where[] = [$column, $operator, $value]; |
232
|
|
|
|
233
|
9 |
|
return $this; |
234
|
|
|
} |
235
|
|
|
|
236
|
|
|
/** |
237
|
|
|
* Returns the where clause. |
238
|
|
|
* |
239
|
|
|
* @return string the where clause. |
240
|
|
|
*/ |
241
|
24 |
|
private function getWhereClause() |
242
|
|
|
{ |
243
|
24 |
|
if (empty($this->where)) { |
244
|
15 |
|
return ''; |
245
|
|
|
} |
246
|
|
|
|
247
|
9 |
|
$result = []; |
248
|
|
|
|
249
|
9 |
|
foreach ($this->where as $key => $value) { |
250
|
9 |
|
$result[] = sprintf('%s %s %s', $value[0], $value[1], $value[2]); |
251
|
9 |
|
} |
252
|
|
|
|
253
|
9 |
|
return sprintf('WHERE %s', implode(' AND ', $result)); |
254
|
|
|
} |
255
|
|
|
|
256
|
|
|
/** |
257
|
|
|
* {@inheritdoc} |
258
|
|
|
*/ |
259
|
2 |
|
public function groupBy($column) |
260
|
|
|
{ |
261
|
2 |
|
$this->groupBy[] = $column; |
262
|
|
|
|
263
|
2 |
|
return $this; |
264
|
|
|
} |
265
|
|
|
|
266
|
|
|
/** |
267
|
|
|
* Returns the group by clause. |
268
|
|
|
* |
269
|
|
|
* @return string the group by clause. |
270
|
|
|
*/ |
271
|
18 |
|
public function getGroupByClause() |
272
|
|
|
{ |
273
|
18 |
|
if (empty($this->groupBy)) { |
274
|
16 |
|
return ''; |
275
|
|
|
} |
276
|
|
|
|
277
|
2 |
|
return sprintf('GROUP BY %s', implode(', ', $this->groupBy)); |
278
|
|
|
} |
279
|
|
|
|
280
|
|
|
/** |
281
|
|
|
* {@inheritdoc} |
282
|
|
|
*/ |
283
|
4 |
|
public function orderBy($column, $order = null) |
284
|
|
|
{ |
285
|
4 |
|
if (strcasecmp($order, 'asc') == 0) { |
286
|
1 |
|
$column .= ' ASC'; |
287
|
4 |
|
} elseif(strcasecmp($order, 'desc') == 0) { |
288
|
2 |
|
$column .= ' DESC'; |
289
|
2 |
|
} |
290
|
|
|
|
291
|
4 |
|
$this->orderBy[] = $column; |
292
|
|
|
|
293
|
4 |
|
return $this; |
294
|
|
|
} |
295
|
|
|
|
296
|
|
|
/** |
297
|
|
|
* Returns the order by clause. |
298
|
|
|
* |
299
|
|
|
* @return string the order by clause. |
300
|
|
|
*/ |
301
|
18 |
|
public function getOrderByClause() |
302
|
|
|
{ |
303
|
18 |
|
if (empty($this->orderBy)) { |
304
|
14 |
|
return ''; |
305
|
|
|
} |
306
|
|
|
|
307
|
4 |
|
return sprintf('ORDER BY %s', implode(', ', $this->orderBy)); |
308
|
|
|
} |
309
|
|
|
|
310
|
|
|
/** |
311
|
|
|
* {@inheritdoc} |
312
|
|
|
*/ |
313
|
6 |
|
public function limit($limit) |
314
|
|
|
{ |
315
|
6 |
|
$this->limit = $limit; |
316
|
|
|
|
317
|
6 |
|
return $this; |
318
|
|
|
} |
319
|
|
|
|
320
|
|
|
/** |
321
|
|
|
* Returns the limit clause. |
322
|
|
|
* |
323
|
|
|
* @return string the limit clause. |
324
|
|
|
*/ |
325
|
24 |
|
private function getLimitClause() |
326
|
|
|
{ |
327
|
24 |
|
if (!$this->limit) { |
328
|
18 |
|
return ''; |
329
|
|
|
} |
330
|
|
|
|
331
|
6 |
|
return sprintf('LIMIT %s', $this->limit); |
332
|
|
|
} |
333
|
|
|
|
334
|
|
|
/** |
335
|
|
|
* {@inheritdoc} |
336
|
|
|
*/ |
337
|
2 |
|
public function offset($offset) |
338
|
|
|
{ |
339
|
2 |
|
$this->offset = $offset; |
340
|
|
|
|
341
|
2 |
|
return $this; |
342
|
|
|
} |
343
|
|
|
|
344
|
|
|
/** |
345
|
|
|
* Returns the offset clause. |
346
|
|
|
* |
347
|
|
|
* @return string the offset clause. |
348
|
|
|
*/ |
349
|
18 |
|
private function getOffsetClause() |
350
|
|
|
{ |
351
|
18 |
|
if (!$this->limit || !$this->offset) { |
352
|
16 |
|
return ''; |
353
|
|
|
} |
354
|
|
|
|
355
|
2 |
|
return sprintf('OFFSET %s', $this->offset); |
356
|
|
|
} |
357
|
|
|
} |
358
|
|
|
|
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.