Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
100.00% |
144 / 144 |
|
100.00% |
45 / 45 |
CRAP | |
100.00% |
1 / 1 |
| QueryBuilder | |
100.00% |
144 / 144 |
|
100.00% |
45 / 45 |
69 | |
100.00% |
1 / 1 |
| select | |
100.00% |
13 / 13 |
|
100.00% |
1 / 1 |
5 | |||
| selectMax | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| selectMin | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| selectAvg | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| selectSum | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| returning | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| distinct | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| explain | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| table | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| from | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| like | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orLike | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| notLike | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orNotLike | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| having | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orHaving | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| where | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orWhere | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| whereIn | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orWhereIn | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| whereNotIn | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orWhereNotIn | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| set | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
2 | |||
| join | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
1 | |||
| groupBy | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
2 | |||
| orderBy | |
100.00% |
13 / 13 |
|
100.00% |
1 / 1 |
4 | |||
| limit | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| groupStart | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| notGroupStart | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| orGroupStart | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| orNotGroupStart | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| groupEnd | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| get | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
3 | |||
| getWhere | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| countAll | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| countAllResults | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
2 | |||
| insert | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| insertBatch | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| update | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| updateBatch | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
4 | |||
| delete | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| getCompiledSelect | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| getCompiledInsert | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| getCompiledUpdate | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| getCompiledDelete | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| 1 | <?php declare(strict_types=1); |
| 2 | /** |
| 3 | * Query |
| 4 | * |
| 5 | * SQL Query Builder / Database Abstraction Layer |
| 6 | * |
| 7 | * PHP version 8.1 |
| 8 | * |
| 9 | * @package Query |
| 10 | * @author Timothy J. Warren <tim@timshome.page> |
| 11 | * @copyright 2012 - 2023 Timothy J. Warren |
| 12 | * @license http://www.opensource.org/licenses/mit-license.html MIT License |
| 13 | * @link https://git.timshomepage.net/aviat/Query |
| 14 | * @version 4.0.0 |
| 15 | */ |
| 16 | |
| 17 | namespace Query; |
| 18 | |
| 19 | use PDOStatement; |
| 20 | use function is_array; |
| 21 | use function is_int; |
| 22 | |
| 23 | use function mb_trim; |
| 24 | |
| 25 | /** |
| 26 | * Convenience class for creating sql queries |
| 27 | */ |
| 28 | class QueryBuilder extends QueryBuilderBase implements QueryBuilderInterface |
| 29 | { |
| 30 | // -------------------------------------------------------------------------- |
| 31 | // ! Select Queries |
| 32 | // -------------------------------------------------------------------------- |
| 33 | /** |
| 34 | * Specifies rows to select in a query |
| 35 | */ |
| 36 | public function select(string $fields): self |
| 37 | { |
| 38 | // Split fields by comma |
| 39 | $fieldsArray = explode(',', $fields); |
| 40 | $fieldsArray = array_map('mb_trim', $fieldsArray); |
| 41 | |
| 42 | // Split on 'As' |
| 43 | foreach ($fieldsArray as $key => $field) |
| 44 | { |
| 45 | if (stripos($field, 'as') !== FALSE) |
| 46 | { |
| 47 | $fieldsArray[$key] = preg_split('` as `i', (string) $field); |
| 48 | $fieldsArray[$key] = array_map('mb_trim', $fieldsArray[$key]); |
| 49 | } |
| 50 | } |
| 51 | |
| 52 | // Quote the identifiers |
| 53 | $safeArray = $this->driver->quoteIdent($fieldsArray); |
| 54 | |
| 55 | unset($fieldsArray); |
| 56 | |
| 57 | // Join the strings back together |
| 58 | foreach ($safeArray as $i => $iValue) |
| 59 | { |
| 60 | if (is_array($iValue)) |
| 61 | { |
| 62 | $safeArray[$i] = implode(' AS ', $iValue); |
| 63 | } |
| 64 | } |
| 65 | |
| 66 | $this->state->appendSelectString(implode(', ', $safeArray)); |
| 67 | |
| 68 | return $this; |
| 69 | } |
| 70 | |
| 71 | /** |
| 72 | * Selects the maximum value of a field from a query |
| 73 | * |
| 74 | * @param bool|string $as |
| 75 | */ |
| 76 | public function selectMax(string $field, $as=FALSE): self |
| 77 | { |
| 78 | // Create the select string |
| 79 | $this->state->appendSelectString(' MAX' . $this->_select($field, $as)); |
| 80 | |
| 81 | return $this; |
| 82 | } |
| 83 | |
| 84 | /** |
| 85 | * Selects the minimum value of a field from a query |
| 86 | * |
| 87 | * @param bool|string $as |
| 88 | */ |
| 89 | public function selectMin(string $field, $as=FALSE): self |
| 90 | { |
| 91 | // Create the select string |
| 92 | $this->state->appendSelectString(' MIN' . $this->_select($field, $as)); |
| 93 | |
| 94 | return $this; |
| 95 | } |
| 96 | |
| 97 | /** |
| 98 | * Selects the average value of a field from a query |
| 99 | * |
| 100 | * @param bool|string $as |
| 101 | */ |
| 102 | public function selectAvg(string $field, $as=FALSE): self |
| 103 | { |
| 104 | // Create the select string |
| 105 | $this->state->appendSelectString(' AVG' . $this->_select($field, $as)); |
| 106 | |
| 107 | return $this; |
| 108 | } |
| 109 | |
| 110 | /** |
| 111 | * Selects the sum of a field from a query |
| 112 | * |
| 113 | * @param bool|string $as |
| 114 | */ |
| 115 | public function selectSum(string $field, $as=FALSE): self |
| 116 | { |
| 117 | // Create the select string |
| 118 | $this->state->appendSelectString(' SUM' . $this->_select($field, $as)); |
| 119 | |
| 120 | return $this; |
| 121 | } |
| 122 | |
| 123 | /** |
| 124 | * Add a 'returning' clause to an insert,update, or delete query |
| 125 | * |
| 126 | * @return $this |
| 127 | */ |
| 128 | public function returning(string $fields = ''): self |
| 129 | { |
| 130 | $this->returning = TRUE; |
| 131 | |
| 132 | // Re-use the string select field for generating the returning type clause |
| 133 | if ($fields !== '') |
| 134 | { |
| 135 | return $this->select($fields); |
| 136 | } |
| 137 | |
| 138 | return $this; |
| 139 | } |
| 140 | |
| 141 | /** |
| 142 | * Adds the 'distinct' keyword to a query |
| 143 | */ |
| 144 | public function distinct(): self |
| 145 | { |
| 146 | // Prepend the keyword to the select string |
| 147 | $this->state->setSelectString(' DISTINCT' . $this->state->getSelectString()); |
| 148 | |
| 149 | return $this; |
| 150 | } |
| 151 | |
| 152 | /** |
| 153 | * Tell the database to give you the query plan instead of result set |
| 154 | */ |
| 155 | public function explain(): self |
| 156 | { |
| 157 | $this->explain = TRUE; |
| 158 | |
| 159 | return $this; |
| 160 | } |
| 161 | |
| 162 | /** |
| 163 | * Specify the database table to select from |
| 164 | * |
| 165 | * Alias of `from` method to better match CodeIgniter 4 |
| 166 | */ |
| 167 | public function table(string $tableName): self |
| 168 | { |
| 169 | return $this->from($tableName); |
| 170 | } |
| 171 | |
| 172 | /** |
| 173 | * Specify the database table to select from |
| 174 | */ |
| 175 | public function from(string $tableName): self |
| 176 | { |
| 177 | // Split identifiers on spaces |
| 178 | $identArray = explode(' ', mb_trim($tableName)); |
| 179 | $identArray = array_map('mb_trim', $identArray); |
| 180 | |
| 181 | // Quote the identifiers |
| 182 | $identArray[0] = $this->driver->quoteTable($identArray[0]); |
| 183 | $identArray = $this->driver->quoteIdent($identArray); |
| 184 | |
| 185 | // Paste it back together |
| 186 | $this->state->setFromString(implode(' ', $identArray)); |
| 187 | |
| 188 | return $this; |
| 189 | } |
| 190 | |
| 191 | // -------------------------------------------------------------------------- |
| 192 | // ! 'Like' methods |
| 193 | // -------------------------------------------------------------------------- |
| 194 | /** |
| 195 | * Creates a Like clause in the sql statement |
| 196 | */ |
| 197 | public function like(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self |
| 198 | { |
| 199 | return $this->_like($field, $values, LikeType::parse($pos)); |
| 200 | } |
| 201 | |
| 202 | /** |
| 203 | * Generates an OR Like clause |
| 204 | */ |
| 205 | public function orLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self |
| 206 | { |
| 207 | return $this->_like($field, $values, LikeType::parse($pos), 'LIKE', 'OR'); |
| 208 | } |
| 209 | |
| 210 | /** |
| 211 | * Generates a NOT LIKE clause |
| 212 | */ |
| 213 | public function notLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self |
| 214 | { |
| 215 | return $this->_like($field, $values, LikeType::parse($pos), 'NOT LIKE'); |
| 216 | } |
| 217 | |
| 218 | /** |
| 219 | * Generates a OR NOT LIKE clause |
| 220 | */ |
| 221 | public function orNotLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self |
| 222 | { |
| 223 | return $this->_like($field, $values, LikeType::parse($pos), 'NOT LIKE', 'OR'); |
| 224 | } |
| 225 | |
| 226 | // -------------------------------------------------------------------------- |
| 227 | // ! Having methods |
| 228 | // -------------------------------------------------------------------------- |
| 229 | /** |
| 230 | * Generates a 'Having' clause |
| 231 | */ |
| 232 | public function having(mixed $key, mixed $values=[]): self |
| 233 | { |
| 234 | return $this->_having($key, $values); |
| 235 | } |
| 236 | |
| 237 | /** |
| 238 | * Generates a 'Having' clause prefixed with 'OR' |
| 239 | */ |
| 240 | public function orHaving(mixed $key, mixed $values=[]): self |
| 241 | { |
| 242 | return $this->_having($key, $values, 'OR'); |
| 243 | } |
| 244 | |
| 245 | // -------------------------------------------------------------------------- |
| 246 | // ! 'Where' methods |
| 247 | // -------------------------------------------------------------------------- |
| 248 | /** |
| 249 | * Specify condition(s) in the where clause of a query |
| 250 | * Note: this function works with key / value, or a |
| 251 | * passed array with key / value pairs |
| 252 | */ |
| 253 | public function where(mixed $key, mixed $values=[]): self |
| 254 | { |
| 255 | return $this->_whereString($key, $values); |
| 256 | } |
| 257 | |
| 258 | /** |
| 259 | * Where clause prefixed with "OR" |
| 260 | */ |
| 261 | public function orWhere(mixed $key, mixed $values=[]): self |
| 262 | { |
| 263 | return $this->_whereString($key, $values, 'OR'); |
| 264 | } |
| 265 | |
| 266 | /** |
| 267 | * Where clause with 'IN' statement |
| 268 | */ |
| 269 | public function whereIn(string $field, mixed $val=[]): self |
| 270 | { |
| 271 | return $this->_whereIn($field, $val); |
| 272 | } |
| 273 | |
| 274 | /** |
| 275 | * Where in statement prefixed with "or" |
| 276 | */ |
| 277 | public function orWhereIn(string $field, mixed $val=[]): self |
| 278 | { |
| 279 | return $this->_whereIn($field, $val, 'IN', 'OR'); |
| 280 | } |
| 281 | |
| 282 | /** |
| 283 | * WHERE NOT IN (FOO) clause |
| 284 | */ |
| 285 | public function whereNotIn(string $field, mixed $val=[]): self |
| 286 | { |
| 287 | return $this->_whereIn($field, $val, 'NOT IN'); |
| 288 | } |
| 289 | |
| 290 | /** |
| 291 | * OR WHERE NOT IN (FOO) clause |
| 292 | */ |
| 293 | public function orWhereNotIn(string $field, mixed $val=[]): self |
| 294 | { |
| 295 | return $this->_whereIn($field, $val, 'NOT IN', 'OR'); |
| 296 | } |
| 297 | |
| 298 | // -------------------------------------------------------------------------- |
| 299 | // ! Other Query Modifier methods |
| 300 | // -------------------------------------------------------------------------- |
| 301 | /** |
| 302 | * Sets values for inserts / updates / deletes |
| 303 | */ |
| 304 | public function set(mixed $key, mixed $val = NULL): self |
| 305 | { |
| 306 | $pairs = is_scalar($key) ? [$key => $val] : $key; |
| 307 | |
| 308 | $keys = array_keys($pairs); |
| 309 | $values = array_values($pairs); |
| 310 | |
| 311 | $this->state->appendSetArrayKeys($keys); |
| 312 | $this->state->appendValues($values); |
| 313 | |
| 314 | // Use the keys of the array to make the insert/update string |
| 315 | // Escape the field names |
| 316 | $this->state->setSetArrayKeys( |
| 317 | array_map([$this->driver, '_quote'], $this->state->getSetArrayKeys()) |
| 318 | ); |
| 319 | |
| 320 | // Generate the "set" string |
| 321 | $setString = implode('=?,', $this->state->getSetArrayKeys()); |
| 322 | $setString .= '=?'; |
| 323 | |
| 324 | $this->state->setSetString($setString); |
| 325 | |
| 326 | return $this; |
| 327 | } |
| 328 | |
| 329 | /** |
| 330 | * Creates a join phrase in a compiled query |
| 331 | */ |
| 332 | public function join(string $table, string $condition, JoinType|string $type=JoinType::INNER): self |
| 333 | { |
| 334 | // Prefix and quote table name |
| 335 | $tableArr = explode(' ', mb_trim($table)); |
| 336 | $tableArr[0] = $this->driver->quoteTable($tableArr[0]); |
| 337 | $tableArr = $this->driver->quoteIdent($tableArr); |
| 338 | $table = implode(' ', $tableArr); |
| 339 | |
| 340 | // Parse out the join condition |
| 341 | $parsedCondition = $this->parser->compileJoin($condition); |
| 342 | $condition = $table . ' ON ' . $parsedCondition; |
| 343 | |
| 344 | $this->state->appendMap("\n" . strtoupper(JoinType::parse($type)->value) . ' JOIN ', $condition, MapType::JOIN); |
| 345 | |
| 346 | return $this; |
| 347 | } |
| 348 | |
| 349 | /** |
| 350 | * Group the results by the selected field(s) |
| 351 | */ |
| 352 | public function groupBy(mixed $field): self |
| 353 | { |
| 354 | if ( ! is_scalar($field)) |
| 355 | { |
| 356 | $newGroupArray = array_merge( |
| 357 | $this->state->getGroupArray(), |
| 358 | array_map([$this->driver, 'quoteIdent'], $field) |
| 359 | ); |
| 360 | $this->state->setGroupArray($newGroupArray); |
| 361 | } |
| 362 | else |
| 363 | { |
| 364 | $this->state->appendGroupArray($this->driver->quoteIdent($field)); |
| 365 | } |
| 366 | |
| 367 | $this->state->setGroupString(' GROUP BY ' . implode(',', $this->state->getGroupArray())); |
| 368 | |
| 369 | return $this; |
| 370 | } |
| 371 | |
| 372 | /** |
| 373 | * Order the results by the selected field(s) |
| 374 | */ |
| 375 | public function orderBy(string $field, string $type=''): self |
| 376 | { |
| 377 | // When ordering by random, do an ascending order if the driver |
| 378 | // doesn't support random ordering |
| 379 | if (stripos($type, 'rand') !== FALSE) |
| 380 | { |
| 381 | $rand = $this->driver->getSql()->random(); |
| 382 | $type = $rand ?? 'ASC'; |
| 383 | } |
| 384 | |
| 385 | // Set fields for later manipulation |
| 386 | $field = $this->driver->quoteIdent($field); |
| 387 | $this->state->setOrderArray($field, $type); |
| 388 | |
| 389 | $orderClauses = []; |
| 390 | |
| 391 | // Flatten key/val pairs into an array of space-separated pairs |
| 392 | foreach ($this->state->getOrderArray() as $k => $v) |
| 393 | { |
| 394 | $orderClauses[] = $k . ' ' . strtoupper($v); |
| 395 | } |
| 396 | |
| 397 | // Set the final string |
| 398 | $orderString = isset($rand) |
| 399 | ? "\nORDER BY" . $rand |
| 400 | : "\nORDER BY " . implode(', ', $orderClauses); |
| 401 | |
| 402 | $this->state->setOrderString($orderString); |
| 403 | |
| 404 | return $this; |
| 405 | } |
| 406 | |
| 407 | /** |
| 408 | * Set a limit on the current sql statement |
| 409 | */ |
| 410 | public function limit(int $limit, ?int $offset=NULL): self |
| 411 | { |
| 412 | $this->state->setLimit($limit); |
| 413 | $this->state->setOffset($offset); |
| 414 | |
| 415 | return $this; |
| 416 | } |
| 417 | |
| 418 | // -------------------------------------------------------------------------- |
| 419 | // ! Query Grouping Methods |
| 420 | // -------------------------------------------------------------------------- |
| 421 | /** |
| 422 | * Adds a paren to the current query for query grouping |
| 423 | */ |
| 424 | public function groupStart(): self |
| 425 | { |
| 426 | $conj = empty($this->state->getQueryMap()) ? ' WHERE ' : ' '; |
| 427 | |
| 428 | $this->state->appendMap($conj, '(', MapType::GROUP_START); |
| 429 | |
| 430 | return $this; |
| 431 | } |
| 432 | |
| 433 | /** |
| 434 | * Adds a paren to the current query for query grouping, |
| 435 | * prefixed with 'NOT' |
| 436 | */ |
| 437 | public function notGroupStart(): self |
| 438 | { |
| 439 | $conj = empty($this->state->getQueryMap()) ? ' WHERE ' : ' AND '; |
| 440 | |
| 441 | $this->state->appendMap($conj, ' NOT (', MapType::GROUP_START); |
| 442 | |
| 443 | return $this; |
| 444 | } |
| 445 | |
| 446 | /** |
| 447 | * Adds a paren to the current query for query grouping, |
| 448 | * prefixed with 'OR' |
| 449 | */ |
| 450 | public function orGroupStart(): self |
| 451 | { |
| 452 | $this->state->appendMap('', ' OR (', MapType::GROUP_START); |
| 453 | |
| 454 | return $this; |
| 455 | } |
| 456 | |
| 457 | /** |
| 458 | * Adds a paren to the current query for query grouping, |
| 459 | * prefixed with 'OR NOT' |
| 460 | */ |
| 461 | public function orNotGroupStart(): self |
| 462 | { |
| 463 | $this->state->appendMap('', ' OR NOT (', MapType::GROUP_START); |
| 464 | |
| 465 | return $this; |
| 466 | } |
| 467 | |
| 468 | /** |
| 469 | * Ends a query group |
| 470 | */ |
| 471 | public function groupEnd(): self |
| 472 | { |
| 473 | $this->state->appendMap('', ')', MapType::GROUP_END); |
| 474 | |
| 475 | return $this; |
| 476 | } |
| 477 | |
| 478 | // -------------------------------------------------------------------------- |
| 479 | // ! Query execution methods |
| 480 | // -------------------------------------------------------------------------- |
| 481 | /** |
| 482 | * Select and retrieve all records from the current table, and/or |
| 483 | * execute current compiled query |
| 484 | */ |
| 485 | public function get(string $table='', ?int $limit=NULL, ?int $offset=NULL): PDOStatement |
| 486 | { |
| 487 | // Set the table |
| 488 | if ( ! empty($table)) |
| 489 | { |
| 490 | $this->from($table); |
| 491 | } |
| 492 | |
| 493 | // Set the limit, if it exists |
| 494 | if (is_int($limit)) |
| 495 | { |
| 496 | $this->limit($limit, $offset); |
| 497 | } |
| 498 | |
| 499 | return $this->_run(QueryType::SELECT, $table); |
| 500 | } |
| 501 | |
| 502 | /** |
| 503 | * Convenience method for get() with a where clause |
| 504 | * |
| 505 | * @param mixed $where |
| 506 | */ |
| 507 | public function getWhere(string $table, $where=[], ?int $limit=NULL, ?int $offset=NULL): PDOStatement |
| 508 | { |
| 509 | // Create the where clause |
| 510 | $this->where($where); |
| 511 | |
| 512 | // Return the result |
| 513 | return $this->get($table, $limit, $offset); |
| 514 | } |
| 515 | |
| 516 | /** |
| 517 | * Retrieve the number of rows in the selected table |
| 518 | */ |
| 519 | public function countAll(string $table): int |
| 520 | { |
| 521 | $sql = 'SELECT * FROM ' . $this->driver->quoteTable($table); |
| 522 | $res = $this->driver->query($sql); |
| 523 | |
| 524 | return (int) (is_countable($res->fetchAll()) ? count($res->fetchAll()) : 0); |
| 525 | } |
| 526 | |
| 527 | /** |
| 528 | * Retrieve the number of results for the generated query - used |
| 529 | * in place of the get() method |
| 530 | */ |
| 531 | public function countAllResults(string $table='', bool $reset = TRUE): int |
| 532 | { |
| 533 | // Set the table |
| 534 | if ( ! empty($table)) |
| 535 | { |
| 536 | $this->from($table); |
| 537 | } |
| 538 | |
| 539 | $result = $this->_run(QueryType::SELECT, $table, NULL, NULL, $reset); |
| 540 | $rows = $result->fetchAll(); |
| 541 | |
| 542 | return (int) count($rows); |
| 543 | } |
| 544 | |
| 545 | /** |
| 546 | * Creates an insert clause, and executes it |
| 547 | */ |
| 548 | public function insert(string $table, mixed $data=[]): PDOStatement |
| 549 | { |
| 550 | if ( ! empty($data)) |
| 551 | { |
| 552 | $this->set($data); |
| 553 | } |
| 554 | |
| 555 | return $this->_run(QueryType::INSERT, $table); |
| 556 | } |
| 557 | |
| 558 | /** |
| 559 | * Creates and executes a batch insertion query |
| 560 | * @param mixed $data |
| 561 | */ |
| 562 | public function insertBatch(string $table, $data=[]): ?PDOStatement |
| 563 | { |
| 564 | // Get the generated values and sql string |
| 565 | [$sql, $data] = $this->driver->insertBatch($table, $data); |
| 566 | |
| 567 | return $sql !== NULL |
| 568 | ? $this->_run(QueryType::INSERT_BATCH, $table, $sql, $data) |
| 569 | : NULL; |
| 570 | } |
| 571 | |
| 572 | /** |
| 573 | * Creates an update clause, and executes it |
| 574 | */ |
| 575 | public function update(string $table, mixed $data=[]): PDOStatement |
| 576 | { |
| 577 | if ( ! empty($data)) |
| 578 | { |
| 579 | $this->set($data); |
| 580 | } |
| 581 | |
| 582 | return $this->_run(QueryType::UPDATE, $table); |
| 583 | } |
| 584 | |
| 585 | /** |
| 586 | * Creates a batch update, and executes it. |
| 587 | * Returns the number of affected rows |
| 588 | */ |
| 589 | public function updateBatch(string $table, array $data, string $where): ?int |
| 590 | { |
| 591 | if (empty($table) || empty($data) || empty($where)) |
| 592 | { |
| 593 | return NULL; |
| 594 | } |
| 595 | |
| 596 | // Get the generated values and sql string |
| 597 | [$sql, $data, $affectedRows] = $this->driver->updateBatch($table, $data, $where); |
| 598 | |
| 599 | $this->_run(QueryType::UPDATE_BATCH, $table, $sql, $data); |
| 600 | |
| 601 | return $affectedRows; |
| 602 | } |
| 603 | |
| 604 | /** |
| 605 | * Deletes data from a table |
| 606 | */ |
| 607 | public function delete(string $table, mixed $where=''): PDOStatement |
| 608 | { |
| 609 | // Set the where clause |
| 610 | if ( ! empty($where)) |
| 611 | { |
| 612 | $this->where($where); |
| 613 | } |
| 614 | |
| 615 | return $this->_run(QueryType::DELETE, $table); |
| 616 | } |
| 617 | |
| 618 | // -------------------------------------------------------------------------- |
| 619 | // ! SQL Returning Methods |
| 620 | // -------------------------------------------------------------------------- |
| 621 | /** |
| 622 | * Returns the generated 'select' sql query |
| 623 | */ |
| 624 | public function getCompiledSelect(string $table='', bool $reset=TRUE): string |
| 625 | { |
| 626 | // Set the table |
| 627 | if ( ! empty($table)) |
| 628 | { |
| 629 | $this->from($table); |
| 630 | } |
| 631 | |
| 632 | return $this->_getCompile(QueryType::SELECT, $table, $reset); |
| 633 | } |
| 634 | |
| 635 | /** |
| 636 | * Returns the generated 'insert' sql query |
| 637 | */ |
| 638 | public function getCompiledInsert(string $table, bool $reset=TRUE): string |
| 639 | { |
| 640 | return $this->_getCompile(QueryType::INSERT, $table, $reset); |
| 641 | } |
| 642 | |
| 643 | /** |
| 644 | * Returns the generated 'update' sql query |
| 645 | */ |
| 646 | public function getCompiledUpdate(string $table='', bool $reset=TRUE): string |
| 647 | { |
| 648 | return $this->_getCompile(QueryType::UPDATE, $table, $reset); |
| 649 | } |
| 650 | |
| 651 | /** |
| 652 | * Returns the generated 'delete' sql query |
| 653 | */ |
| 654 | public function getCompiledDelete(string $table='', bool $reset=TRUE): string |
| 655 | { |
| 656 | return $this->_getCompile(QueryType::DELETE, $table, $reset); |
| 657 | } |
| 658 | } |