|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace SimpleCrud\Queries\Mysql; |
|
4
|
|
|
|
|
5
|
|
|
use SimpleCrud\Queries\BaseQuery; |
|
6
|
|
|
use SimpleCrud\Queries\WhereExtendedTrait; |
|
7
|
|
|
use SimpleCrud\Queries\LimitTrait; |
|
8
|
|
|
use SimpleCrud\RowCollection; |
|
9
|
|
|
use SimpleCrud\Entity; |
|
10
|
|
|
use SimpleCrud\SimpleCrudException; |
|
11
|
|
|
use PDOStatement; |
|
12
|
|
|
use PDO; |
|
13
|
|
|
|
|
14
|
|
|
/** |
|
15
|
|
|
* Manages a database select query. |
|
16
|
|
|
*/ |
|
17
|
|
|
class SelectAll extends BaseQuery |
|
18
|
|
|
{ |
|
19
|
|
|
use WhereExtendedTrait; |
|
20
|
|
|
use LimitTrait; |
|
21
|
|
|
|
|
22
|
|
|
protected $leftJoin = []; |
|
23
|
|
|
protected $orderBy = []; |
|
24
|
|
|
protected $statement; |
|
25
|
|
|
|
|
26
|
|
|
/** |
|
27
|
|
|
* Adds an ORDER BY clause. |
|
28
|
|
|
* |
|
29
|
|
|
* @param string $orderBy |
|
30
|
|
|
* @param string|null $direction |
|
31
|
|
|
* |
|
32
|
|
|
* @return self |
|
33
|
|
|
*/ |
|
34
|
|
|
public function orderBy($orderBy, $direction = null) |
|
35
|
|
|
{ |
|
36
|
|
|
if (!empty($direction)) { |
|
37
|
|
|
$orderBy .= ' '.$direction; |
|
38
|
|
|
} |
|
39
|
|
|
|
|
40
|
|
|
$this->orderBy[] = $orderBy; |
|
41
|
|
|
|
|
42
|
|
|
return $this; |
|
43
|
|
|
} |
|
44
|
|
|
|
|
45
|
|
|
/** |
|
46
|
|
|
* Adds a LEFT JOIN clause. |
|
47
|
|
|
* |
|
48
|
|
|
* @param Entity $entity |
|
49
|
|
|
* @param string $on |
|
50
|
|
|
* @param array|null $marks |
|
51
|
|
|
* |
|
52
|
|
|
* @return self |
|
53
|
|
|
*/ |
|
54
|
|
|
public function leftJoin(Entity $entity, $on = null, $marks = null) |
|
55
|
|
|
{ |
|
56
|
|
|
if ($this->entity->getRelation($entity) !== Entity::RELATION_HAS_ONE) { |
|
57
|
|
|
throw new SimpleCrudException("The items '{$this->entity->name}' and '{$entity->name}' are no related or cannot be joined"); |
|
58
|
|
|
} |
|
59
|
|
|
|
|
60
|
|
|
$this->leftJoin[] = [ |
|
61
|
|
|
'entity' => $entity, |
|
62
|
|
|
'on' => $on, |
|
63
|
|
|
]; |
|
64
|
|
|
|
|
65
|
|
|
if ($marks) { |
|
66
|
|
|
$this->marks += $marks; |
|
67
|
|
|
} |
|
68
|
|
|
|
|
69
|
|
|
return $this; |
|
70
|
|
|
} |
|
71
|
|
|
|
|
72
|
|
|
/** |
|
73
|
|
|
* Adds new marks to the query. |
|
74
|
|
|
* |
|
75
|
|
|
* @param array $marks |
|
76
|
|
|
* |
|
77
|
|
|
* @return self |
|
78
|
|
|
*/ |
|
79
|
|
|
public function marks(array $marks) |
|
80
|
|
|
{ |
|
81
|
|
|
$this->marks += $marks; |
|
82
|
|
|
|
|
83
|
|
|
return $this; |
|
84
|
|
|
} |
|
85
|
|
|
|
|
86
|
|
|
/** |
|
87
|
|
|
* Run the query and return a statement with the result. |
|
88
|
|
|
* |
|
89
|
|
|
* @return PDOStatement |
|
90
|
|
|
*/ |
|
91
|
|
View Code Duplication |
public function run() |
|
|
|
|
|
|
92
|
|
|
{ |
|
93
|
|
|
$statement = $this->entity->getDb()->execute((string) $this, $this->marks); |
|
94
|
|
|
$statement->setFetchMode(PDO::FETCH_ASSOC); |
|
95
|
|
|
|
|
96
|
|
|
return $statement; |
|
97
|
|
|
} |
|
98
|
|
|
|
|
99
|
|
|
/** |
|
100
|
|
|
* Run the query and return all values. |
|
101
|
|
|
* |
|
102
|
|
|
* @param bool $idAsKey |
|
103
|
|
|
* |
|
104
|
|
|
* @return RowCollection |
|
105
|
|
|
*/ |
|
106
|
|
|
public function get($idAsKey = true) |
|
107
|
|
|
{ |
|
108
|
|
|
$statement = $this->run(); |
|
109
|
|
|
$result = $this->entity->createCollection(); |
|
110
|
|
|
|
|
111
|
|
|
$result->idAsKey($idAsKey); |
|
112
|
|
|
|
|
113
|
|
|
while (($row = $statement->fetch())) { |
|
114
|
|
|
$result[] = $this->entity->create($this->entity->prepareDataFromDatabase($row)); |
|
115
|
|
|
} |
|
116
|
|
|
|
|
117
|
|
|
return $result; |
|
118
|
|
|
} |
|
119
|
|
|
|
|
120
|
|
|
/** |
|
121
|
|
|
* Build and return the query. |
|
122
|
|
|
* |
|
123
|
|
|
* @return string |
|
124
|
|
|
*/ |
|
125
|
|
|
public function __toString() |
|
126
|
|
|
{ |
|
127
|
|
|
$query = 'SELECT'; |
|
128
|
|
|
$query .= ' '.static::buildFields($this->entity->name, array_keys($this->entity->fields)); |
|
129
|
|
|
|
|
130
|
|
|
foreach ($this->leftJoin as $join) { |
|
131
|
|
|
$query .= ', '.static::buildFields($join['entity']->name, array_keys($join['entity']->fields), true); |
|
132
|
|
|
} |
|
133
|
|
|
|
|
134
|
|
|
$query .= $this->fieldsToString(); |
|
135
|
|
|
$query .= ' FROM `'.$this->entity->name.'`'; |
|
136
|
|
|
$query .= $this->fromToString(); |
|
137
|
|
|
|
|
138
|
|
|
foreach ($this->leftJoin as $join) { |
|
139
|
|
|
$query .= ' LEFT JOIN `'.$join['entity']->name.'`"'; |
|
140
|
|
|
|
|
141
|
|
|
if (!empty($join['on'])) { |
|
142
|
|
|
$query .= ' ON ('.$join['on'].')'; |
|
143
|
|
|
} |
|
144
|
|
|
} |
|
145
|
|
|
|
|
146
|
|
|
$query .= $this->whereToString(); |
|
147
|
|
|
|
|
148
|
|
|
if (!empty($this->orderBy)) { |
|
149
|
|
|
$query .= ' ORDER BY '.implode(', ', $this->orderBy); |
|
150
|
|
|
} |
|
151
|
|
|
|
|
152
|
|
|
$query .= $this->limitToString(); |
|
153
|
|
|
|
|
154
|
|
|
return $query; |
|
155
|
|
|
} |
|
156
|
|
|
|
|
157
|
|
|
/** |
|
158
|
|
|
* Generates the fields/tables part of a SELECT query. |
|
159
|
|
|
* |
|
160
|
|
|
* @param string $table |
|
161
|
|
|
* @param array $fields |
|
162
|
|
|
* @param bool $rename |
|
163
|
|
|
* |
|
164
|
|
|
* @return string |
|
165
|
|
|
*/ |
|
166
|
|
|
protected static function buildFields($table, array $fields, $rename = false) |
|
167
|
|
|
{ |
|
168
|
|
|
$query = []; |
|
169
|
|
|
|
|
170
|
|
|
foreach ($fields as $field) { |
|
171
|
|
|
if ($rename) { |
|
172
|
|
|
$query[] = "`{$table}`.`{$field}` as `{$table}.{$field}`"; |
|
173
|
|
|
} else { |
|
174
|
|
|
$query[] = "`{$table}`.`{$field}`"; |
|
175
|
|
|
} |
|
176
|
|
|
} |
|
177
|
|
|
|
|
178
|
|
|
return implode(', ', $query); |
|
179
|
|
|
} |
|
180
|
|
|
} |
|
181
|
|
|
|
oscarotero /
simple-crud
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.