1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Simply\Database; |
4
|
|
|
|
5
|
|
|
use Simply\Database\Connection\Connection; |
6
|
|
|
|
7
|
|
|
/** |
8
|
|
|
* Provides convenience for writing custom queries while taking advantage of existing defined schemas. |
9
|
|
|
* @author Riikka Kalliomäki <[email protected]> |
10
|
|
|
* @copyright Copyright (c) 2018 Riikka Kalliomäki |
11
|
|
|
* @license http://opensource.org/licenses/mit-license.php MIT License |
12
|
|
|
*/ |
13
|
|
|
class Query |
14
|
|
|
{ |
15
|
|
|
/** @var Connection The database connection */ |
16
|
|
|
private $connection; |
17
|
|
|
|
18
|
|
|
/** @var string The SQL query to perform */ |
19
|
|
|
private $sql; |
20
|
|
|
|
21
|
|
|
/** @var Schema[] The schemas provided for the query */ |
22
|
|
|
private $schemas; |
23
|
|
|
|
24
|
|
|
/** @var array Parameters for the query */ |
25
|
|
|
private $parameters; |
26
|
|
|
|
27
|
|
|
/** |
28
|
|
|
* Query constructor. |
29
|
|
|
* @param Connection $connection The database connection |
30
|
|
|
* @param string $sql The SQL query to perform |
31
|
|
|
*/ |
32
|
9 |
|
public function __construct(Connection $connection, string $sql) |
33
|
|
|
{ |
34
|
9 |
|
$this->connection = $connection; |
35
|
9 |
|
$this->sql = $sql; |
36
|
9 |
|
$this->schemas = []; |
37
|
9 |
|
$this->parameters = []; |
38
|
9 |
|
} |
39
|
|
|
|
40
|
|
|
/** |
41
|
|
|
* Returns a new query object with the given schema. |
42
|
|
|
* @param Schema $schema The schema to attach to the new query |
43
|
|
|
* @param string $alias Alias to use for the table in the query |
44
|
|
|
* @return Query A new query object with the given schema |
45
|
|
|
*/ |
46
|
9 |
|
public function withSchema(Schema $schema, string $alias = ''): self |
47
|
|
|
{ |
48
|
9 |
|
$query = clone $this; |
49
|
9 |
|
$query->schemas[$this->formatAlias($alias)] = $schema; |
50
|
|
|
|
51
|
9 |
|
return $query; |
52
|
|
|
} |
53
|
|
|
|
54
|
|
|
/** |
55
|
|
|
* Returns a new query with the given parameters. |
56
|
|
|
* @param array $parameters The list of parameters to add |
57
|
|
|
* @return Query A new query object with the given parameters |
58
|
|
|
*/ |
59
|
2 |
|
public function withParameters(array $parameters): self |
60
|
|
|
{ |
61
|
2 |
|
$query = clone $this; |
62
|
|
|
|
63
|
2 |
|
foreach ($parameters as $key => $parameter) { |
64
|
2 |
|
if (\is_int($key)) { |
65
|
1 |
|
$query->parameters[] = $parameter; |
66
|
1 |
|
continue; |
67
|
|
|
} |
68
|
|
|
|
69
|
2 |
|
$query->parameters[$key] = $parameter; |
70
|
|
|
} |
71
|
|
|
|
72
|
2 |
|
return $query; |
73
|
|
|
} |
74
|
|
|
|
75
|
|
|
/** |
76
|
|
|
* Returns a new query object without any schemas. |
77
|
|
|
* @return Query A new query object without any attached schemas |
78
|
|
|
*/ |
79
|
1 |
|
public function withoutSchemas(): self |
80
|
|
|
{ |
81
|
1 |
|
$query = clone $this; |
82
|
1 |
|
$query->schemas = []; |
83
|
|
|
|
84
|
1 |
|
return $query; |
85
|
|
|
} |
86
|
|
|
|
87
|
|
|
/** |
88
|
|
|
* Returns a new query object without any inserted parameters. |
89
|
|
|
* @return Query A new query object without any inserted parameters |
90
|
|
|
*/ |
91
|
1 |
|
public function withoutParameters(): self |
92
|
|
|
{ |
93
|
1 |
|
$query = clone $this; |
94
|
1 |
|
$query->parameters = []; |
95
|
|
|
|
96
|
1 |
|
return $query; |
97
|
|
|
} |
98
|
|
|
|
99
|
|
|
/** |
100
|
|
|
* Executes the query and returns a PDOStatement instance for the executed query. |
101
|
|
|
* @return \PDOStatement Result statement from the executed query |
102
|
|
|
*/ |
103
|
7 |
|
public function fetchResult(): \PDOStatement |
104
|
|
|
{ |
105
|
7 |
|
$placeholders = []; |
106
|
|
|
|
107
|
7 |
|
foreach ($this->schemas as $alias => $schema) { |
108
|
7 |
|
if ($alias === '') { |
109
|
4 |
|
$placeholders['{table}'] = $this->connection->formatTable($schema->getTable()); |
110
|
4 |
|
$placeholders['{fields}'] = $this->connection->formatFields($schema->getFields()); |
111
|
|
|
|
112
|
4 |
|
continue; |
113
|
|
|
} |
114
|
|
|
|
115
|
4 |
|
$placeholders["{{$alias}.table}"] = $this->connection->formatTable($schema->getTable(), $alias); |
116
|
4 |
|
$placeholders["{{$alias}.fields}"] = $this->connection->formatFields( |
117
|
4 |
|
$schema->getFields(), |
118
|
4 |
|
$alias, |
119
|
4 |
|
$this->formatPrefix($alias) |
120
|
|
|
); |
121
|
|
|
} |
122
|
|
|
|
123
|
7 |
|
$query = $this->connection->query(strtr($this->sql, $placeholders), $this->parameters); |
124
|
7 |
|
$query->setFetchMode(\PDO::FETCH_ASSOC); |
125
|
|
|
|
126
|
7 |
|
return $query; |
127
|
|
|
} |
128
|
|
|
|
129
|
|
|
/** |
130
|
|
|
* Executes the query and returns all result rows as an associated array. |
131
|
|
|
* @return array[] All the result rows as an associated array |
132
|
|
|
*/ |
133
|
2 |
|
public function fetchRows(): array |
134
|
|
|
{ |
135
|
2 |
|
return iterator_to_array($this->generateRows()); |
136
|
|
|
} |
137
|
|
|
|
138
|
|
|
/** |
139
|
|
|
* Executes the query and returns models for each row with optionally included relationships. |
140
|
|
|
* @param string $alias The alias of the schema to use for creating models |
141
|
|
|
* @param string[] $relationships List of unique relationships to fill for the model from the result row |
142
|
|
|
* @return Model[] The resulting models from the query |
143
|
|
|
*/ |
144
|
4 |
|
public function fetchModels(string $alias = '', array $relationships = []): array |
145
|
|
|
{ |
146
|
4 |
|
return iterator_to_array($this->generateModels($alias, $relationships)); |
147
|
|
|
} |
148
|
|
|
|
149
|
|
|
/** |
150
|
|
|
* Executes the query and returns an array of results that have been passed through the given callback. |
151
|
|
|
* @param callable $callback The callback to call for each result row |
152
|
|
|
* @return array The return values from the callback called for each result row |
153
|
|
|
*/ |
154
|
1 |
|
public function fetchCallback(callable $callback): array |
155
|
|
|
{ |
156
|
1 |
|
return iterator_to_array($this->generateCallback($callback)); |
157
|
|
|
} |
158
|
|
|
|
159
|
|
|
/** |
160
|
|
|
* Returns a generator that fetches the rows one by one for memory efficient processing. |
161
|
|
|
* @return \Generator A generator that fetches the rows one by one |
162
|
|
|
*/ |
163
|
3 |
|
public function generateRows(): \Generator |
164
|
|
|
{ |
165
|
3 |
|
foreach ($this->fetchResult() as $row) { |
166
|
2 |
|
yield $row; |
167
|
|
|
} |
168
|
3 |
|
} |
169
|
|
|
|
170
|
|
|
/** |
171
|
|
|
* Returns a generator that fetches the models one by one for memory efficient processing. |
172
|
|
|
* @param string $alias The alias of the schema to use for creating models |
173
|
|
|
* @param array $relationships List of unique relationships to fill for the model from the result row |
174
|
|
|
* @return \Generator Generator that fetches the models one by one |
175
|
|
|
*/ |
176
|
5 |
|
public function generateModels(string $alias = '', array $relationships = []): \Generator |
177
|
|
|
{ |
178
|
5 |
|
$alias = $this->formatAlias($alias); |
179
|
|
|
|
180
|
5 |
|
if (!isset($this->schemas[$alias])) { |
181
|
3 |
|
if ($alias !== '' || \count($this->schemas) !== 1) { |
182
|
2 |
|
throw new \InvalidArgumentException('No schema selected for generating database models'); |
183
|
|
|
} |
184
|
|
|
|
185
|
1 |
|
$alias = array_keys($this->schemas)[0]; |
186
|
|
|
} |
187
|
|
|
|
188
|
3 |
|
$schema = $this->schemas[$alias]; |
189
|
3 |
|
$prefix = $this->formatPrefix($alias); |
190
|
3 |
|
$modelRelationships = []; |
191
|
|
|
|
192
|
3 |
|
foreach ($relationships as $key => $name) { |
193
|
1 |
|
$modelRelationships[$this->formatPrefix($key)] = $name; |
194
|
|
|
} |
195
|
|
|
|
196
|
3 |
|
foreach ($this->fetchResult() as $row) { |
197
|
3 |
|
yield $schema->createModelFromRow($row, $prefix, $modelRelationships); |
198
|
|
|
} |
199
|
3 |
|
} |
200
|
|
|
|
201
|
|
|
/** |
202
|
|
|
* Returns a generator that returns the result of the callback called for each result row one by one. |
203
|
|
|
* @param callable $callback The callback to call for each result row |
204
|
|
|
* @return \Generator Generator that calls the callback for each result row and returns the result |
205
|
|
|
*/ |
206
|
2 |
|
public function generateCallback(callable $callback): \Generator |
207
|
|
|
{ |
208
|
2 |
|
foreach ($this->fetchResult() as $row) { |
209
|
2 |
|
yield $callback($row); |
210
|
|
|
} |
211
|
2 |
|
} |
212
|
|
|
|
213
|
|
|
/** |
214
|
|
|
* Returns the string followed by a single underscore. |
215
|
|
|
* @param string $prefix The prefix to format into canonical format |
216
|
|
|
* @return string The prefix string formatted into canonical format |
217
|
|
|
*/ |
218
|
4 |
|
private function formatPrefix(string $prefix): string |
219
|
|
|
{ |
220
|
4 |
|
$alias = $this->formatAlias($prefix); |
221
|
|
|
|
222
|
4 |
|
if ($alias === '') { |
223
|
1 |
|
return $alias; |
224
|
|
|
} |
225
|
|
|
|
226
|
4 |
|
return $alias . '_'; |
227
|
|
|
} |
228
|
|
|
|
229
|
|
|
/** |
230
|
|
|
* Returns the string without any following underscores. |
231
|
|
|
* @param string $alias The alias to format into canonical format |
232
|
|
|
* @return string The alias string formatted into canonical format |
233
|
|
|
*/ |
234
|
9 |
|
private function formatAlias(string $alias): string |
235
|
|
|
{ |
236
|
9 |
|
return rtrim($alias, '_'); |
237
|
|
|
} |
238
|
|
|
} |
239
|
|
|
|