vakata /
database
Complex classes like TableQuery often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use TableQuery, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 11 | class TableQuery implements \IteratorAggregate, \ArrayAccess, \Countable |
||
| 12 | { |
||
| 13 | const SEP = '___'; |
||
| 14 | /** |
||
| 15 | * @var DBInterface |
||
| 16 | */ |
||
| 17 | protected $db; |
||
| 18 | /** |
||
| 19 | * @var Table |
||
| 20 | */ |
||
| 21 | protected $definition; |
||
| 22 | /** |
||
| 23 | * @var TableQueryIterator|null |
||
| 24 | */ |
||
| 25 | protected $qiterator; |
||
| 26 | |||
| 27 | /** |
||
| 28 | * @var array |
||
| 29 | */ |
||
| 30 | protected $where = []; |
||
| 31 | /** |
||
| 32 | * @var array |
||
| 33 | */ |
||
| 34 | protected $order = []; |
||
| 35 | /** |
||
| 36 | * @var array |
||
| 37 | */ |
||
| 38 | protected $group = []; |
||
| 39 | /** |
||
| 40 | * @var array |
||
| 41 | */ |
||
| 42 | protected $having = []; |
||
| 43 | /** |
||
| 44 | * @var int[] |
||
| 45 | */ |
||
| 46 | protected $li_of = [0,0]; |
||
| 47 | /** |
||
| 48 | * @var array |
||
| 49 | */ |
||
| 50 | protected $fields = []; |
||
| 51 | /** |
||
| 52 | * @var array |
||
| 53 | */ |
||
| 54 | protected $withr = []; |
||
| 55 | /** |
||
| 56 | * @var array |
||
| 57 | */ |
||
| 58 | protected $joins = []; |
||
| 59 | /** |
||
| 60 | * @var array |
||
| 61 | */ |
||
| 62 | protected $pkey = []; |
||
| 63 | |||
| 64 | /** |
||
| 65 | * Create an instance |
||
| 66 | * @param DBInterface $db the database connection |
||
| 67 | * @param Table|string $table the name or definition of the main table in the query |
||
| 68 | */ |
||
| 69 | 13 | public function __construct(DBInterface $db, $table) |
|
| 70 | { |
||
| 71 | 13 | $this->db = $db; |
|
| 72 | 13 | $this->definition = $table instanceof Table ? $table : $this->db->definition((string)$table); |
|
| 73 | 13 | $primary = $this->definition->getPrimaryKey(); |
|
| 74 | 13 | $columns = $this->definition->getColumns(); |
|
| 75 | 13 | $this->pkey = count($primary) ? $primary : $columns; |
|
| 76 | 13 | $this->columns($columns); |
|
| 77 | 13 | } |
|
| 78 | public function __clone() |
||
| 79 | { |
||
| 80 | $this->reset(); |
||
| 81 | } |
||
| 82 | /** |
||
| 83 | * Get the table definition of the queried table |
||
| 84 | * @return Table the definition |
||
| 85 | */ |
||
| 86 | public function getDefinition() : Table |
||
| 87 | { |
||
| 88 | return $this->definition; |
||
| 89 | } |
||
| 90 | |||
| 91 | 13 | protected function getColumn($column) |
|
| 92 | { |
||
| 93 | 13 | $column = explode('.', $column); |
|
| 94 | 13 | if (count($column) === 1) { |
|
| 95 | 13 | $column = [ $this->definition->getName(), $column[0] ]; |
|
| 96 | 13 | $col = $this->definition->getColumn($column[1]); |
|
| 97 | 2 | } elseif (count($column) === 2) { |
|
| 98 | 2 | if ($column[0] === $this->definition->getName()) { |
|
| 99 | $col = $this->definition->getColumn($column[1]); |
||
| 100 | if (!$col) { |
||
| 101 | throw new DBException('Invalid column name in own table'); |
||
| 102 | } |
||
| 103 | } else { |
||
| 104 | 2 | if ($this->definition->hasRelation($column[0])) { |
|
| 105 | 1 | $col = $this->definition->getRelation($column[0])->table->getColumn($column[1]); |
|
| 106 | 1 | if (!$col) { |
|
| 107 | 1 | throw new DBException('Invalid column name in related table'); |
|
| 108 | } |
||
| 109 | 1 | } else if (isset($this->joins[$column[0]])) { |
|
| 110 | 1 | $col = $this->joins[$column[0]]->table->getColumn($column[1]); |
|
| 111 | 1 | if (!$col) { |
|
| 112 | 1 | throw new DBException('Invalid column name in related table'); |
|
| 113 | } |
||
| 114 | } else { |
||
| 115 | 2 | throw new DBException('Invalid foreign table name: ' . implode(',', $column)); |
|
| 116 | } |
||
| 117 | } |
||
| 118 | } else { |
||
| 119 | $name = array_pop($column); |
||
| 120 | $this->with(implode('.', $column)); |
||
| 121 | $table = $this->definition; |
||
| 122 | $table = array_reduce( |
||
| 123 | $column, |
||
| 124 | function ($carry, $item) use (&$table) { |
||
| 125 | $table = $table->getRelation($item)->table; |
||
| 126 | return $table; |
||
| 127 | } |
||
| 128 | ); |
||
| 129 | $col = $table->getColumn($name); |
||
| 130 | $column = [ implode(static::SEP, $column), $name ]; |
||
| 131 | } |
||
| 132 | 13 | return [ 'name' => implode('.', $column), 'data' => $col ]; |
|
| 133 | } |
||
| 134 | 4 | protected function normalizeValue(TableColumn $col, $value) |
|
| 135 | { |
||
| 136 | 4 | if ($value === null && $col->isNullable()) { |
|
| 137 | return null; |
||
| 138 | } |
||
| 139 | 4 | switch ($col->getBasicType()) { |
|
| 140 | 4 | case 'date': |
|
| 141 | if (is_string($value)) { |
||
| 142 | $temp = strtotime($value); |
||
| 143 | if (!$temp) { |
||
| 144 | return null; |
||
| 145 | } |
||
| 146 | return date('Y-m-d', strtotime($value)); |
||
| 147 | } |
||
| 148 | if (is_int($value)) { |
||
| 149 | return date('Y-m-d', $value); |
||
| 150 | } |
||
| 151 | if ($value instanceof \DateTime) { |
||
| 152 | return $value->format('Y-m-d'); |
||
| 153 | } |
||
| 154 | return $value; |
||
| 155 | 4 | case 'datetime': |
|
| 156 | if (is_string($value)) { |
||
| 157 | $temp = strtotime($value); |
||
| 158 | if (!$temp) { |
||
| 159 | return null; |
||
| 160 | } |
||
| 161 | return date('Y-m-d H:i:s', strtotime($value)); |
||
| 162 | } |
||
| 163 | if (is_int($value)) { |
||
| 164 | return date('Y-m-d H:i:s', $value); |
||
| 165 | } |
||
| 166 | if ($value instanceof \DateTime) { |
||
| 167 | return $value->format('Y-m-d H:i:s'); |
||
| 168 | } |
||
| 169 | return $value; |
||
| 170 | 4 | case 'enum': |
|
| 171 | if (is_int($value)) { |
||
| 172 | return $value; |
||
| 173 | } |
||
| 174 | if (!in_array($value, $col->getValues())) { |
||
| 175 | return 0; |
||
| 176 | } |
||
| 177 | return $value; |
||
| 178 | 4 | case 'int': |
|
| 179 | 1 | return (int)$value; |
|
| 180 | default: |
||
| 181 | 3 | return $value; |
|
| 182 | } |
||
| 183 | } |
||
| 184 | |||
| 185 | /** |
||
| 186 | * Filter the results by a column and a value |
||
| 187 | * @param string $column the column name to filter by (related columns can be used - for example: author.name) |
||
| 188 | * @param mixed $value a required value, array of values or range of values (range example: ['beg'=>1,'end'=>3]) |
||
| 189 | * @param bool $negate optional boolean indicating that the filter should be negated |
||
| 190 | * @return $this |
||
| 191 | */ |
||
| 192 | 2 | public function filter(string $column, $value, bool $negate = false) : TableQuery |
|
| 193 | { |
||
| 194 | 2 | list($name, $column) = array_values($this->getColumn($column)); |
|
| 195 | 2 | if (is_null($value)) { |
|
| 196 | return $negate ? |
||
| 197 | $this->where($name . ' IS NOT NULL') : |
||
| 198 | $this->where($name . ' IS NULL'); |
||
| 199 | } |
||
| 200 | 2 | if (!is_array($value)) { |
|
| 201 | 2 | return $negate ? |
|
| 202 | $this->where( |
||
| 203 | $name . ' <> ?', |
||
| 204 | [ $this->normalizeValue($column, $value) ] |
||
| 205 | ) : |
||
| 206 | 2 | $this->where( |
|
| 207 | 2 | $name . ' = ?', |
|
| 208 | 2 | [ $this->normalizeValue($column, $value) ] |
|
| 209 | ); |
||
| 210 | } |
||
| 211 | if (isset($value['beg']) && isset($value['end'])) { |
||
| 212 | return $negate ? |
||
| 213 | $this->where( |
||
| 214 | $name.' NOT BETWEEN ? AND ?', |
||
| 215 | [ |
||
| 216 | $this->normalizeValue($column, $value['beg']), |
||
| 217 | $this->normalizeValue($column, $value['end']) |
||
| 218 | ] |
||
| 219 | ) : |
||
| 220 | $this->where( |
||
| 221 | $name.' BETWEEN ? AND ?', |
||
| 222 | [ |
||
| 223 | $this->normalizeValue($column, $value['beg']), |
||
| 224 | $this->normalizeValue($column, $value['end']) |
||
| 225 | ] |
||
| 226 | ); |
||
| 227 | } |
||
| 228 | return $negate ? |
||
| 229 | $this->where( |
||
| 230 | $name . ' NOT IN (??)', |
||
| 231 | [ array_map(function ($v) use ($column) { return $this->normalizeValue($column, $v); }, $value) ] |
||
| 232 | ) : |
||
| 233 | $this->where( |
||
| 234 | $name . ' IN (??)', |
||
| 235 | [ array_map(function ($v) use ($column) { return $this->normalizeValue($column, $v); }, $value) ] |
||
| 236 | ); |
||
| 237 | } |
||
| 238 | /** |
||
| 239 | * Sort by a column |
||
| 240 | * @param string $column the column name to sort by (related columns can be used - for example: author.name) |
||
| 241 | * @param bool|boolean $desc should the sorting be in descending order, defaults to `false` |
||
| 242 | * @return $this |
||
| 243 | */ |
||
| 244 | public function sort(string $column, bool $desc = false) : TableQuery |
||
| 245 | { |
||
| 246 | return $this->order($this->getColumn($column)['name'] . ' ' . ($desc ? 'DESC' : 'ASC')); |
||
| 247 | } |
||
| 248 | /** |
||
| 249 | * Group by a column (or columns) |
||
| 250 | * @param string|array $column the column name (or names) to group by |
||
| 251 | * @return $this |
||
| 252 | */ |
||
| 253 | 1 | public function group($column) : TableQuery |
|
| 254 | { |
||
| 255 | 1 | if (!is_array($column)) { |
|
| 256 | 1 | $column = [ $column ]; |
|
| 257 | } |
||
| 258 | 1 | foreach ($column as $k => $v) { |
|
| 259 | 1 | $column[$k] = $this->getColumn($v)['name']; |
|
| 260 | } |
||
| 261 | 1 | return $this->groupBy(implode(', ', $column), []); |
|
| 262 | } |
||
| 263 | /** |
||
| 264 | * Get a part of the data |
||
| 265 | * @param int|integer $page the page number to get (1-based), defaults to 1 |
||
| 266 | * @param int|integer $perPage the number of records per page - defaults to 25 |
||
| 267 | * @return $this |
||
| 268 | */ |
||
| 269 | public function paginate(int $page = 1, int $perPage = 25) : TableQuery |
||
| 270 | { |
||
| 271 | return $this->limit($perPage, ($page - 1) * $perPage); |
||
| 272 | } |
||
| 273 | 1 | public function __call($name, $data) |
|
| 274 | { |
||
| 275 | 1 | if (strpos($name, 'filterBy') === 0) { |
|
| 276 | return $this->filter(strtolower(substr($name, 8)), $data[0]); |
||
| 277 | } |
||
| 278 | 1 | if (strpos($name, 'sortBy') === 0) { |
|
| 279 | return $this->sort(strtolower(substr($name, 6)), $data[0]); |
||
| 280 | } |
||
| 281 | 1 | if (strpos($name, 'groupBy') === 0) { |
|
| 282 | 1 | return $this->group(strtolower(substr($name, 7))); |
|
| 283 | } |
||
| 284 | } |
||
| 285 | /** |
||
| 286 | * Remove all filters, sorting, etc |
||
| 287 | * @return $this |
||
| 288 | */ |
||
| 289 | public function reset() : TableQuery |
||
| 290 | { |
||
| 291 | $this->where = []; |
||
| 292 | $this->joins = []; |
||
| 293 | $this->group = []; |
||
| 294 | $this->withr = []; |
||
| 295 | $this->order = []; |
||
| 296 | $this->having = []; |
||
| 297 | $this->li_of = [0,0]; |
||
| 298 | $this->qiterator = null; |
||
| 299 | return $this; |
||
| 300 | } |
||
| 301 | /** |
||
| 302 | * Apply advanced grouping |
||
| 303 | * @param string $sql SQL statement to use in the GROUP BY clause |
||
| 304 | * @param array $params optional params for the statement (defaults to an empty array) |
||
| 305 | * @return $this |
||
| 306 | */ |
||
| 307 | 1 | public function groupBy(string $sql, array $params = []) : TableQuery |
|
| 308 | { |
||
| 309 | 1 | $this->qiterator = null; |
|
| 310 | 1 | $this->group = [ $sql, $params ]; |
|
| 311 | 1 | return $this; |
|
| 312 | } |
||
| 313 | /** |
||
| 314 | * Join a table to the query (no need to do this for relations defined with foreign keys) |
||
| 315 | * @param Table|string $table the table to join |
||
| 316 | * @param array $fields what to join on (joined_table_field => other_field) |
||
| 317 | * @param string|null $name alias for the join, defaults to the table name |
||
| 318 | * @param bool $multiple are multiple rows joined (results in a LEFT JOIN), default to true |
||
| 319 | * @return $this |
||
| 320 | */ |
||
| 321 | 1 | public function join($table, array $fields, string $name = null, bool $multiple = true) |
|
| 322 | { |
||
| 323 | 1 | $table = $table instanceof Table ? $table : $this->db->definition((string)$table); |
|
| 324 | 1 | $name = $name ?? $table->getName(); |
|
| 325 | 1 | if (isset($this->joins[$name]) || $this->definition->hasRelation($name)) { |
|
| 326 | throw new DBException('Alias / table name already in use'); |
||
| 327 | } |
||
| 328 | 1 | $this->joins[$name] = new TableRelation($name, $table, [], $multiple); |
|
| 329 | 1 | foreach ($fields as $k => $v) { |
|
| 330 | 1 | $k = explode('.', $k, 2); |
|
| 331 | 1 | $k = count($k) == 2 ? $k[1] : $k[0]; |
|
| 332 | 1 | $this->joins[$name]->keymap[$this->getColumn($name . '.' . $k)['name']] = $this->getColumn($v)['name']; |
|
| 333 | } |
||
| 334 | 1 | return $this; |
|
| 335 | } |
||
| 336 | /** |
||
| 337 | * Apply an advanced filter (can be called multiple times) |
||
| 338 | * @param string $sql SQL statement to be used in the where clause |
||
| 339 | * @param array $params parameters for the SQL statement (defaults to an empty array) |
||
| 340 | * @return $this |
||
| 341 | */ |
||
| 342 | 3 | public function where(string $sql, array $params = []) : TableQuery |
|
| 343 | { |
||
| 344 | 3 | $this->qiterator = null; |
|
| 345 | 3 | $this->where[] = [ $sql, $params ]; |
|
| 346 | 3 | return $this; |
|
| 347 | } |
||
| 348 | /** |
||
| 349 | * Apply an advanced HAVING filter (can be called multiple times) |
||
| 350 | * @param string $sql SQL statement to be used in the HAING clause |
||
| 351 | * @param array $params parameters for the SQL statement (defaults to an empty array) |
||
| 352 | * @return $this |
||
| 353 | */ |
||
| 354 | 1 | public function having(string $sql, array $params = []) : TableQuery |
|
| 355 | { |
||
| 356 | 1 | $this->qiterator = null; |
|
| 357 | 1 | $this->having[] = [ $sql, $params ]; |
|
| 358 | 1 | return $this; |
|
| 359 | } |
||
| 360 | /** |
||
| 361 | * Apply advanced sorting |
||
| 362 | * @param string $sql SQL statement to use in the ORDER clause |
||
| 363 | * @param array $params optional params for the statement (defaults to an empty array) |
||
| 364 | * @return $this |
||
| 365 | */ |
||
| 366 | 1 | public function order(string $sql, array $params = []) : TableQuery |
|
| 367 | { |
||
| 368 | 1 | $this->qiterator = null; |
|
| 369 | 1 | $this->order = [ $sql, $params ]; |
|
| 370 | 1 | return $this; |
|
| 371 | } |
||
| 372 | /** |
||
| 373 | * Apply an advanced limit |
||
| 374 | * @param int $limit number of rows to return |
||
| 375 | * @param int $offset number of rows to skip from the beginning (defaults to 0) |
||
| 376 | * @return $this |
||
| 377 | */ |
||
| 378 | 1 | public function limit(int $limit, int $offset = 0) : TableQuery |
|
| 379 | { |
||
| 380 | 1 | $this->qiterator = null; |
|
| 381 | 1 | $this->li_of = [ $limit, $offset ]; |
|
| 382 | 1 | return $this; |
|
| 383 | } |
||
| 384 | /** |
||
| 385 | * Get the number of records |
||
| 386 | * @return int the total number of records (does not respect pagination) |
||
| 387 | */ |
||
| 388 | 3 | public function count() : int |
|
| 389 | { |
||
| 390 | 3 | $table = $this->definition->getName(); |
|
| 391 | 3 | $sql = 'SELECT COUNT(DISTINCT '.$table.'.'.implode(', '.$table.'.', $this->pkey).') FROM '.$table.' '; |
|
| 392 | 3 | $par = []; |
|
| 393 | |||
| 394 | 3 | $relations = $this->withr; |
|
| 395 | 3 | foreach ($this->definition->getRelations() as $k => $v) { |
|
| 396 | 3 | foreach ($this->where as $vv) { |
|
| 397 | 1 | if (strpos($vv[0], $k . '.') !== false) { |
|
| 398 | 1 | $relations[$k] = [ $v, $table ]; |
|
| 399 | } |
||
| 400 | } |
||
| 401 | 3 | if (isset($this->order[0]) && strpos($this->order[0], $k . '.') !== false) { |
|
| 402 | 3 | $relations[$k] = [ $v, $table ]; |
|
| 403 | } |
||
| 404 | } |
||
| 405 | |||
| 406 | 3 | foreach ($this->joins as $k => $v) { |
|
| 407 | $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON '; |
||
| 408 | $tmp = []; |
||
| 409 | foreach ($v->keymap as $kk => $vv) { |
||
| 410 | $tmp[] = $kk.' = '.$vv; |
||
| 411 | } |
||
| 412 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 413 | } |
||
| 414 | 3 | foreach ($relations as $k => $v) { |
|
| 415 | 1 | $table = $v[1]; |
|
| 416 | 1 | $v = $v[0]; |
|
| 417 | 1 | if ($v->pivot) { |
|
| 418 | 1 | $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$k.'_pivot ON '; |
|
| 419 | 1 | $tmp = []; |
|
| 420 | 1 | foreach ($v->keymap as $kk => $vv) { |
|
| 421 | 1 | $tmp[] = $table.'.'.$kk.' = '.$k.'_pivot.'.$vv.' '; |
|
| 422 | } |
||
| 423 | 1 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 424 | 1 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$k.' ON '; |
|
| 425 | 1 | $tmp = []; |
|
| 426 | 1 | foreach ($v->pivot_keymap as $kk => $vv) { |
|
| 427 | 1 | $tmp[] = $k.'.'.$vv.' = '.$k.'_pivot.'.$kk.' '; |
|
| 428 | } |
||
| 429 | 1 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 430 | } else { |
||
| 431 | 1 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$k.' ON '; |
|
| 432 | 1 | $tmp = []; |
|
| 433 | 1 | foreach ($v->keymap as $kk => $vv) { |
|
| 434 | 1 | $tmp[] = $table.'.'.$kk.' = '.$k.'.'.$vv.' '; |
|
| 435 | } |
||
| 436 | 1 | if ($v->sql) { |
|
| 437 | $tmp[] = $v->sql . ' '; |
||
| 438 | $par = array_merge($par, $v->par ?? []); |
||
| 439 | } |
||
| 440 | 1 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 441 | } |
||
| 442 | } |
||
| 443 | 3 | if (count($this->where)) { |
|
| 444 | 1 | $sql .= 'WHERE '; |
|
| 445 | 1 | $tmp = []; |
|
| 446 | 1 | foreach ($this->where as $v) { |
|
| 447 | 1 | $tmp[] = '(' . $v[0] . ')'; |
|
| 448 | 1 | $par = array_merge($par, $v[1]); |
|
| 449 | } |
||
| 450 | 1 | $sql .= implode(' AND ', $tmp).' '; |
|
| 451 | } |
||
| 452 | 3 | if (count($this->group)) { |
|
| 453 | $sql .= 'GROUP BY ' . $this->group[0] . ' '; |
||
| 454 | $par = array_merge($par, $this->group[1]); |
||
| 455 | } |
||
| 456 | 3 | if (count($this->having)) { |
|
| 457 | $sql .= 'HAVING '; |
||
| 458 | $tmp = []; |
||
| 459 | foreach ($this->having as $v) { |
||
| 460 | $tmp[] = '(' . $v[0] . ')'; |
||
| 461 | $par = array_merge($par, $v[1]); |
||
| 462 | } |
||
| 463 | $sql .= implode(' AND ', $tmp).' '; |
||
| 464 | } |
||
| 465 | 3 | return $this->db->one($sql, $par); |
|
| 466 | } |
||
| 467 | /** |
||
| 468 | * Specify which columns to fetch (be default all table columns are fetched) |
||
| 469 | * @param array $fields optional array of columns to select (related columns can be used too) |
||
| 470 | * @return $this |
||
| 471 | */ |
||
| 472 | 13 | public function columns(array $fields) : TableQuery |
|
| 473 | { |
||
| 474 | 13 | foreach ($fields as $k => $v) { |
|
| 475 | 13 | if (strpos($v, '*') !== false) { |
|
| 476 | $temp = explode('.', $v); |
||
| 477 | if (count($temp) === 1) { |
||
| 478 | $table = $this->definition->getName(); |
||
| 479 | $cols = $this->definition->getColumns(); |
||
| 480 | } else if (count($temp) === 2) { |
||
| 481 | $table = $temp[0]; |
||
| 482 | if ($this->definition->hasRelation($table)) { |
||
| 483 | $cols = $this->definition->getRelation($table)->table->getColumns(); |
||
| 484 | } else if (isset($this->joins[$table])) { |
||
| 485 | $cols = $this->joins[$table]->table->getColumns(); |
||
| 486 | } else { |
||
| 487 | throw new DBException('Invalid foreign table name'); |
||
| 488 | } |
||
| 489 | } else { |
||
| 490 | array_pop($temp); |
||
| 491 | $this->with(implode('.', $temp)); |
||
| 492 | $table = array_reduce( |
||
| 493 | $temp, |
||
| 494 | function ($carry, $item) use (&$table) { |
||
| 495 | return $table->getRelation($item)->table; |
||
| 496 | } |
||
| 497 | ); |
||
| 498 | $cols = $table->getColumns(); |
||
| 499 | $table = implode(static::SEP, $temp); |
||
| 500 | } |
||
| 501 | unset($fields[$k]); |
||
| 502 | foreach ($cols as $col) { |
||
| 503 | 13 | $fields[] = $table . '.' . $col; |
|
| 504 | } |
||
| 505 | } |
||
| 506 | } |
||
| 507 | 13 | $primary = $this->definition->getPrimaryKey(); |
|
| 508 | 13 | foreach ($fields as $k => $v) { |
|
| 509 | try { |
||
| 510 | 13 | $fields[$k] = $this->getColumn($v)['name']; |
|
| 511 | 1 | } catch (DBException $e) { |
|
| 512 | 13 | $fields[$k] = $v; |
|
| 513 | } |
||
| 514 | } |
||
| 515 | 13 | foreach ($primary as $field) { |
|
| 516 | 13 | $field = $this->getColumn($field)['name']; |
|
| 517 | 13 | if (!in_array($field, $fields)) { |
|
| 518 | 13 | $fields[] = $field; |
|
| 519 | } |
||
| 520 | } |
||
| 521 | 13 | $this->fields = $fields; |
|
| 522 | 13 | return $this; |
|
| 523 | } |
||
| 524 | /** |
||
| 525 | * Perform the actual fetch |
||
| 526 | * @param array|null $fields optional array of columns to select (related columns can be used too) |
||
| 527 | * @return TableQueryIterator the query result as an iterator |
||
| 528 | */ |
||
| 529 | 11 | public function iterator(array $fields = null) : TableQueryIterator |
|
| 530 | { |
||
| 531 | 11 | if ($this->qiterator) { |
|
| 532 | 4 | return $this->qiterator; |
|
| 533 | } |
||
| 534 | 11 | $table = $this->definition->getName(); |
|
| 535 | 11 | if ($fields !== null) { |
|
| 536 | 1 | $this->columns($fields); |
|
| 537 | } |
||
| 538 | 11 | $relations = $this->withr; |
|
| 539 | 11 | foreach ($this->definition->getRelations() as $k => $relation) { |
|
| 540 | 11 | foreach ($this->fields as $field) { |
|
| 541 | 11 | if (strpos($field, $k . '.') === 0) { |
|
| 542 | 11 | $relations[$k] = [ $relation, $table ]; |
|
| 543 | } |
||
| 544 | } |
||
| 545 | 11 | foreach ($this->where as $v) { |
|
| 546 | if (strpos($v[0], $k . '.') !== false) { |
||
| 547 | $relations[$k] = [ $relation, $table ]; |
||
| 548 | } |
||
| 549 | } |
||
| 550 | 11 | if (isset($this->order[0]) && strpos($this->order[0], $k . '.') !== false) { |
|
| 551 | 11 | $relations[$k] = [ $relation, $table ]; |
|
| 552 | } |
||
| 553 | } |
||
| 554 | 11 | $select = []; |
|
| 555 | 11 | foreach ($this->fields as $k => $field) { |
|
| 556 | 11 | $select[] = $field . (!is_numeric($k) ? ' ' . $k : ''); |
|
| 557 | } |
||
| 558 | 11 | foreach ($this->withr as $name => $relation) { |
|
| 559 | 4 | foreach ($relation[0]->table->getColumns() as $column) { |
|
| 560 | 4 | $select[] = $name . '.' . $column . ' ' . $name . static::SEP . $column; |
|
| 561 | } |
||
| 562 | } |
||
| 563 | 11 | $sql = 'SELECT '.implode(', ', $select).' FROM '.$table.' '; |
|
| 564 | 11 | $par = []; |
|
| 565 | 11 | foreach ($this->joins as $k => $v) { |
|
| 566 | 1 | $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON '; |
|
| 567 | 1 | $tmp = []; |
|
| 568 | 1 | foreach ($v->keymap as $kk => $vv) { |
|
| 569 | 1 | $tmp[] = $kk.' = '.$vv; |
|
| 570 | } |
||
| 571 | 1 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 572 | } |
||
| 573 | 11 | foreach ($relations as $relation => $v) { |
|
| 574 | 4 | $table = $v[1]; |
|
| 575 | 4 | $v = $v[0]; |
|
| 576 | 4 | if ($v->pivot) { |
|
| 577 | 3 | $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$relation.'_pivot ON '; |
|
| 578 | 3 | $tmp = []; |
|
| 579 | 3 | foreach ($v->keymap as $kk => $vv) { |
|
| 580 | 3 | $tmp[] = $table.'.'.$kk.' = '.$relation.'_pivot.'.$vv.' '; |
|
| 581 | } |
||
| 582 | 3 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 583 | 3 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$relation.' ON '; |
|
| 584 | 3 | $tmp = []; |
|
| 585 | 3 | foreach ($v->pivot_keymap as $kk => $vv) { |
|
| 586 | 3 | $tmp[] = $relation.'.'.$vv.' = '.$relation.'_pivot.'.$kk.' '; |
|
| 587 | } |
||
| 588 | 3 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 589 | } else { |
||
| 590 | 4 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$relation.' ON '; |
|
| 591 | 4 | $tmp = []; |
|
| 592 | 4 | foreach ($v->keymap as $kk => $vv) { |
|
| 593 | 4 | $tmp[] = $table.'.'.$kk.' = '.$relation.'.'.$vv.' '; |
|
| 594 | } |
||
| 595 | 4 | if ($v->sql) { |
|
| 596 | $tmp[] = $v->sql . ' '; |
||
| 597 | $par = array_merge($par, $v->par ?? []); |
||
| 598 | } |
||
| 599 | 4 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 600 | } |
||
| 601 | } |
||
| 602 | 11 | if (count($this->where)) { |
|
| 603 | $sql .= 'WHERE '; |
||
| 604 | $tmp = []; |
||
| 605 | foreach ($this->where as $v) { |
||
| 606 | $tmp[] = '(' . $v[0] . ')'; |
||
| 607 | $par = array_merge($par, $v[1]); |
||
| 608 | } |
||
| 609 | $sql .= implode(' AND ', $tmp).' '; |
||
| 610 | } |
||
| 611 | 11 | if (count($this->group)) { |
|
| 612 | 1 | $sql .= 'GROUP BY ' . $this->group[0] . ' '; |
|
| 613 | 1 | $par = array_merge($par, $this->group[1]); |
|
| 614 | } |
||
| 615 | 11 | if (count($this->having)) { |
|
| 616 | 1 | $sql .= 'HAVING '; |
|
| 617 | 1 | $tmp = []; |
|
| 618 | 1 | foreach ($this->having as $v) { |
|
| 619 | 1 | $tmp[] = '(' . $v[0] . ')'; |
|
| 620 | 1 | $par = array_merge($par, $v[1]); |
|
| 621 | } |
||
| 622 | 1 | $sql .= implode(' AND ', $tmp).' '; |
|
| 623 | } |
||
| 624 | 11 | if (count($this->order)) { |
|
| 625 | 1 | $sql .= 'ORDER BY ' . $this->order[0] . ' '; |
|
| 626 | 1 | $par = array_merge($par, $this->order[1]); |
|
| 627 | } |
||
| 628 | 11 | $porder = []; |
|
| 629 | 11 | foreach ($this->definition->getPrimaryKey() as $field) { |
|
| 630 | 11 | $porder[] = $this->getColumn($field)['name']; |
|
| 631 | } |
||
| 632 | 11 | if (count($porder)) { |
|
| 633 | 11 | $sql .= (count($this->order) ? ', ' : 'ORDER BY ') . implode(', ', $porder) . ' '; |
|
| 634 | } |
||
| 635 | |||
| 636 | 11 | if ($this->li_of[0]) { |
|
| 637 | 1 | if ($this->db->driverName() === 'oracle') { |
|
| 638 | if ((int)$this->db->driverOption('version', 0) >= 12) { |
||
| 639 | $sql .= 'OFFSET ' . $this->li_of[1] . ' ROWS FETCH NEXT ' . $this->li_of[0] . ' ROWS ONLY'; |
||
| 640 | } else { |
||
| 641 | $f = array_map(function ($v) { |
||
| 642 | $v = explode(' ', trim($v), 2); |
||
| 643 | if (count($v) === 2) { return $v[1]; } |
||
| 644 | $v = explode('.', $v[0], 2); |
||
| 645 | return count($v) === 2 ? $v[1] : $v[0]; |
||
| 646 | }, $select); |
||
| 647 | $sql = "SELECT " . implode(', ', $f) . " |
||
| 648 | FROM ( |
||
| 649 | SELECT tbl__.*, rownum rnum__ FROM ( |
||
| 650 | " . $sql . " |
||
| 651 | ) tbl__ |
||
| 652 | WHERE rownum <= " . ($this->li_of[0] + $this->li_of[1]) . " |
||
| 653 | ) WHERE rnum__ > " . $this->li_of[1]; |
||
| 654 | } |
||
| 655 | } else { |
||
| 656 | 1 | $sql .= 'LIMIT ' . $this->li_of[0] . ' OFFSET ' . $this->li_of[1]; |
|
| 657 | } |
||
| 658 | } |
||
| 659 | 11 | return $this->qiterator = new TableQueryIterator( |
|
| 660 | 11 | $this->db->get($sql, $par), |
|
| 661 | 11 | $this->pkey, |
|
| 662 | 11 | $this->withr |
|
| 663 | ); |
||
| 664 | } |
||
| 665 | /** |
||
| 666 | * Perform the actual fetch |
||
| 667 | * @param array|null $fields optional array of columns to select (related columns can be used too) |
||
| 668 | * @return array the query result as an array |
||
| 669 | */ |
||
| 670 | 1 | public function select(array $fields = null) : array |
|
| 674 | /** |
||
| 675 | * Insert a new row in the table |
||
| 676 | * @param array $data key value pairs, where each key is the column name and the value is the value to insert |
||
| 677 | * @return array the inserted ID where keys are column names and values are column values |
||
| 678 | */ |
||
| 679 | 1 | public function insert(array $data) : array |
|
| 680 | { |
||
| 681 | 1 | $table = $this->definition->getName(); |
|
| 682 | 1 | $columns = $this->definition->getFullColumns(); |
|
| 683 | 1 | $insert = []; |
|
| 684 | 1 | foreach ($data as $column => $value) { |
|
| 685 | 1 | if (isset($columns[$column])) { |
|
| 686 | 1 | $insert[$column] = $this->normalizeValue($columns[$column], $value); |
|
| 687 | } |
||
| 688 | } |
||
| 689 | 1 | if (!count($insert)) { |
|
| 690 | throw new DBException('No valid columns to insert'); |
||
| 691 | } |
||
| 692 | 1 | $sql = 'INSERT INTO '.$table.' ('.implode(', ', array_keys($insert)).') VALUES (??)'; |
|
| 693 | 1 | $par = [$insert]; |
|
| 694 | 1 | $primary = $this->definition->getPrimaryKey(); |
|
| 695 | 1 | if (!count($primary)) { |
|
| 696 | $this->db->query($sql, $par); |
||
| 697 | return []; |
||
| 698 | } |
||
| 699 | 1 | if ($this->db->driverName() === 'oracle') { |
|
| 700 | $ret = []; |
||
| 701 | foreach ($primary as $k) { |
||
| 702 | $ret[$k] = str_repeat(' ', 255); |
||
| 703 | $par[] = &$ret[$k]; |
||
| 704 | } |
||
| 705 | $sql .= ' RETURNING ' . implode(',', $primary) . ' INTO ' . implode(',', array_fill(0, count($primary), '?')); |
||
| 706 | $this->db->query($sql, $par); |
||
| 707 | return $ret; |
||
| 708 | } else { |
||
| 709 | 1 | $ret = []; |
|
| 710 | 1 | $ins = $this->db->query($sql, $par)->insertID(); |
|
| 711 | 1 | foreach ($primary as $k) { |
|
| 712 | 1 | $ret[$k] = $data[$k] ?? $ins; |
|
| 713 | } |
||
| 714 | 1 | return $ret; |
|
| 715 | } |
||
| 716 | } |
||
| 717 | /** |
||
| 718 | * Update the filtered rows with new data |
||
| 719 | * @param array $data key value pairs, where each key is the column name and the value is the value to insert |
||
| 720 | * @return int the number of affected rows |
||
| 721 | */ |
||
| 722 | 1 | public function update(array $data) : int |
|
| 723 | { |
||
| 724 | 1 | $table = $this->definition->getName(); |
|
| 725 | 1 | $columns = $this->definition->getFullColumns(); |
|
| 726 | 1 | $update = []; |
|
| 727 | 1 | foreach ($data as $column => $value) { |
|
| 728 | 1 | if (isset($columns[$column])) { |
|
| 729 | 1 | $update[$column] = $this->normalizeValue($columns[$column], $value); |
|
| 730 | } |
||
| 731 | } |
||
| 732 | 1 | if (!count($update)) { |
|
| 733 | throw new DBException('No valid columns to update'); |
||
| 734 | } |
||
| 735 | 1 | $sql = 'UPDATE '.$table.' SET '; |
|
| 736 | 1 | $par = []; |
|
| 737 | $sql .= implode(', ', array_map(function ($v) { return $v . ' = ?'; }, array_keys($update))) . ' '; |
||
| 738 | 1 | $par = array_merge($par, array_values($update)); |
|
| 739 | 1 | if (count($this->where)) { |
|
| 740 | 1 | $sql .= 'WHERE '; |
|
| 741 | 1 | $tmp = []; |
|
| 742 | 1 | foreach ($this->where as $v) { |
|
| 743 | 1 | $tmp[] = $v[0]; |
|
| 744 | 1 | $par = array_merge($par, $v[1]); |
|
| 745 | } |
||
| 746 | 1 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 747 | } |
||
| 748 | 1 | if (count($this->order)) { |
|
| 749 | $sql .= $this->order[0]; |
||
| 750 | $par = array_merge($par, $this->order[1]); |
||
| 751 | } |
||
| 752 | 1 | return $this->db->query($sql, $par)->affected(); |
|
| 753 | } |
||
| 754 | /** |
||
| 755 | * Delete the filtered rows from the DB |
||
| 756 | * @return int the number of deleted rows |
||
| 757 | */ |
||
| 758 | 1 | public function delete() : int |
|
| 759 | { |
||
| 760 | 1 | $table = $this->definition->getName(); |
|
| 761 | 1 | $sql = 'DELETE FROM '.$table.' '; |
|
| 762 | 1 | $par = []; |
|
| 763 | 1 | if (count($this->where)) { |
|
| 764 | 1 | $sql .= 'WHERE '; |
|
| 765 | 1 | $tmp = []; |
|
| 766 | 1 | foreach ($this->where as $v) { |
|
| 767 | 1 | $tmp[] = $v[0]; |
|
| 768 | 1 | $par = array_merge($par, $v[1]); |
|
| 769 | } |
||
| 770 | 1 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 771 | } |
||
| 772 | 1 | if (count($this->order)) { |
|
| 773 | $sql .= $this->order[0]; |
||
| 774 | $par = array_merge($par, $this->order[1]); |
||
| 775 | } |
||
| 776 | 1 | return $this->db->query($sql, $par)->affected(); |
|
| 777 | } |
||
| 778 | /** |
||
| 779 | * Solve the n+1 queries problem by prefetching a relation by name |
||
| 780 | * @param string $relation the relation name to fetch along with the data |
||
| 781 | * @return $this |
||
| 782 | */ |
||
| 783 | 4 | public function with(string $relation) : TableQuery |
|
| 784 | { |
||
| 785 | 4 | $this->qiterator = null; |
|
| 786 | 4 | $parts = explode('.', $relation); |
|
| 787 | 4 | $table = $this->definition; |
|
| 788 | 4 | array_reduce( |
|
| 789 | 4 | $parts, |
|
| 790 | 4 | function ($carry, $item) use (&$table) { |
|
| 791 | 4 | $relation = $table->getRelation($item); |
|
| 792 | 4 | if (!$relation) { |
|
| 793 | throw new DBException('Invalid relation name'); |
||
| 794 | } |
||
| 795 | 4 | $name = $carry ? $carry . static::SEP . $item : $item; |
|
| 796 | 4 | $this->withr[$name] = [ $relation, $carry ?? $table->getName() ]; |
|
| 797 | 4 | $table = $relation->table; |
|
| 798 | 4 | return $name; |
|
| 799 | 4 | } |
|
| 800 | ); |
||
| 801 | 4 | return $this; |
|
| 802 | } |
||
| 803 | |||
| 804 | 1 | public function getIterator() |
|
| 805 | { |
||
| 806 | 1 | return $this->iterator(); |
|
| 807 | } |
||
| 808 | |||
| 809 | 9 | public function offsetGet($offset) |
|
| 810 | { |
||
| 811 | 9 | return $this->iterator()->offsetGet($offset); |
|
| 812 | } |
||
| 813 | public function offsetExists($offset) |
||
| 817 | public function offsetUnset($offset) |
||
| 818 | { |
||
| 819 | return $this->iterator()->offsetUnset($offset); |
||
| 820 | } |
||
| 821 | public function offsetSet($offset, $value) |
||
| 822 | { |
||
| 823 | return $this->iterator()->offsetSet($offset, $value); |
||
| 824 | } |
||
| 825 | |||
| 826 | public function collection(array $fields = null) : Collection |
||
| 827 | { |
||
| 828 | return new Collection($this->iterator($fields)); |
||
| 829 | } |
||
| 830 | } |
||
| 831 |
