1<?php 2 3namespace ipl\Sql; 4 5/** 6 * SQL SELECT query 7 */ 8class Select implements CommonTableExpressionInterface, LimitOffsetInterface, OrderByInterface, WhereInterface 9{ 10 use CommonTableExpression; 11 use LimitOffset; 12 use OrderBy; 13 use Where; 14 15 /** @var bool Whether the query is DISTINCT */ 16 protected $distinct = false; 17 18 /** @var array|null The columns for the SELECT query */ 19 protected $columns; 20 21 /** @var array|null FROM part of the query, i.e. the table names to select data from */ 22 protected $from; 23 24 /** 25 * The tables to JOIN 26 * 27 * [ 28 * [ $joinType, $tableName, $condition ], 29 * ... 30 * ] 31 * 32 * @var array 33 */ 34 protected $join; 35 36 /** @var array|null The columns for the GROUP BY part of the query */ 37 protected $groupBy; 38 39 /** @var array|null Internal representation for the HAVING part of the query */ 40 protected $having; 41 42 /** 43 * The queries to UNION 44 * 45 * [ 46 * [ new Select(), (bool) 'UNION ALL' ], 47 * ... 48 * ] 49 * 50 * @var array 51 */ 52 protected $union; 53 54 /** 55 * Get whether to SELECT DISTINCT 56 * 57 * @return bool 58 */ 59 public function getDistinct() 60 { 61 return $this->distinct; 62 } 63 64 /** 65 * Set whether to SELECT DISTINCT 66 * 67 * @param bool $distinct 68 * 69 * @return $this 70 */ 71 public function distinct($distinct = true) 72 { 73 $this->distinct = $distinct; 74 75 return $this; 76 } 77 78 /** 79 * Get the columns for the SELECT query 80 * 81 * @return array 82 */ 83 public function getColumns() 84 { 85 return $this->columns ?: []; 86 } 87 88 /** 89 * Add columns to the SELECT query 90 * 91 * Multiple calls to this method will not overwrite the previous set columns but append the columns to the query. 92 * 93 * Note that this method does NOT quote the columns you specify for the SELECT. 94 * If you allow user input here, you must protected yourself against SQL injection using 95 * {@link Connection::quoteIdentifier()} for the column names passed to this method. 96 * If you are using special column names, e.g. reserved keywords for your DBMS, you are required to use 97 * {@link Connection::quoteIdentifier()} as well. 98 * 99 * @param string|ExpressionInterface|Select|array $columns The column(s) to add to the SELECT. 100 * The items can be any mix of the following: 'column', 101 * 'column as alias', ['alias' => 'column'] 102 * 103 * @return $this 104 */ 105 public function columns($columns) 106 { 107 if (! is_array($columns)) { 108 $columns = [$columns]; 109 } 110 111 $this->columns = array_merge($this->columns ?: [], $columns); 112 113 return $this; 114 } 115 116 /** 117 * Get the FROM part of the query 118 * 119 * @return array|null 120 */ 121 public function getFrom() 122 { 123 return $this->from; 124 } 125 126 /** 127 * Add a FROM part to the query 128 * 129 * Multiple calls to this method will not overwrite the previous set FROM part but append the tables to the FROM. 130 * 131 * Note that this method does NOT quote the tables you specify for the FROM. 132 * If you allow user input here, you must protected yourself against SQL injection using 133 * {@link Connection::quoteIdentifier()} for the table names passed to this method. 134 * If you are using special table names, e.g. reserved keywords for your DBMS, you are required to use 135 * {@link Connection::quoteIdentifier()} as well. 136 * 137 * @param string|Select|array $tables The table(s) to add to the FROM part. The items can be any mix of the 138 * following: ['table', 'table alias', 'alias' => 'table'] 139 * 140 * @return $this 141 */ 142 public function from($tables) 143 { 144 if (! is_array($tables)) { 145 $tables = [$tables]; 146 } 147 148 $this->from = array_merge($this->from ?: [], $tables); 149 150 return $this; 151 } 152 153 /** 154 * Get the JOIN part(s) of the query 155 * 156 * @return array|null 157 */ 158 public function getJoin() 159 { 160 return $this->join; 161 } 162 163 /** 164 * Add a INNER JOIN part to the query 165 * 166 * @param string|Select|array $table The table to be joined, can be any of the following: 167 * 'table' 'table alias' ['alias' => 'table'] 168 * @param string|ExpressionInterface|Select|array $condition The join condition, i.e. the ON part of the JOIN. 169 * Please see {@link WhereInterface::where()} 170 * for the supported formats and 171 * restrictions regarding quoting of the field names. 172 * @param string $operator The operator to combine multiple conditions with, 173 * if the condition is in the array format 174 * 175 * @return $this 176 */ 177 public function join($table, $condition, $operator = Sql::ALL) 178 { 179 $this->join[] = ['INNER', $table, $this->buildCondition($condition, $operator)]; 180 181 return $this; 182 } 183 184 /** 185 * Add a LEFT JOIN part to the query 186 * 187 * @param string|Select|array $table The table to be joined, can be any of the following: 188 * 'table' 'table alias' ['alias' => 'table'] 189 * @param string|ExpressionInterface|Select|array $condition The join condition, i.e. the ON part of the JOIN. 190 * Please see {@link WhereInterface::where()} 191 * for the supported formats and 192 * restrictions regarding quoting of the field names. 193 * @param string $operator The operator to combine multiple conditions with, 194 * if the condition is in the array format 195 * 196 * @return $this 197 */ 198 public function joinLeft($table, $condition, $operator = Sql::ALL) 199 { 200 $this->join[] = ['LEFT', $table, $this->buildCondition($condition, $operator)]; 201 202 return $this; 203 } 204 205 /** 206 * Add a RIGHT JOIN part to the query 207 * 208 * @param string|Select|array $table The table to be joined, can be any of the following: 209 * 'table' 'table alias' ['alias' => 'table'] 210 * @param string|ExpressionInterface|Select|array $condition The join condition, i.e. the ON part of the JOIN. 211 * Please see {@link WhereInterface::where()} 212 * for the supported formats and 213 * restrictions regarding quoting of the field names. 214 * @param string $operator The operator to combine multiple conditions with, 215 * if the condition is in the array format 216 * 217 * @return $this 218 */ 219 public function joinRight($table, $condition, $operator = Sql::ALL) 220 { 221 $this->join[] = ['RIGHT', $table, $this->buildCondition($condition, $operator)]; 222 223 return $this; 224 } 225 226 /** 227 * Get the GROUP BY part of the query 228 * 229 * @return array|null 230 */ 231 public function getGroupBy() 232 { 233 return $this->groupBy; 234 } 235 236 /** 237 * Add a GROUP BY part to the query - either plain columns or expressions or scalar subqueries 238 * 239 * This method does NOT quote the columns you specify for the GROUP BY. 240 * If you allow user input here, you must protected yourself against SQL injection using 241 * {@link Connection::quoteIdentifier()} for the field names passed to this method. 242 * If you are using special field names, e.g. reserved keywords for your DBMS, you are required to use 243 * {@link Connection::quoteIdentifier()} as well. 244 * 245 * Note that this method does not override an already set GROUP BY part. Instead, multiple calls to this function 246 * add the specified GROUP BY part. 247 * 248 * @param string|ExpressionInterface|Select|array $groupBy 249 * 250 * @return $this 251 */ 252 public function groupBy($groupBy) 253 { 254 $this->groupBy = array_merge( 255 $this->groupBy === null ? [] : $this->groupBy, 256 is_array($groupBy) ? $groupBy : [$groupBy] 257 ); 258 259 return $this; 260 } 261 262 /** 263 * Get the HAVING part of the query 264 * 265 * @return array|null 266 */ 267 public function getHaving() 268 { 269 return $this->having; 270 } 271 272 /** 273 * Add a HAVING part of the query 274 * 275 * This method lets you specify the HAVING part of the query using one of the two following supported formats: 276 * * String format, e.g. 'id = 1' 277 * * Array format, e.g. ['id' => 1, ...] 278 * 279 * This method does NOT quote the columns you specify for the HAVING. 280 * If you allow user input here, you must protected yourself against SQL injection using 281 * {@link Connection::quoteIdentifier()} for the field names passed to this method. 282 * If you are using special field names, e.g. reserved keywords for your DBMS, you are required to use 283 * {@link Connection::quoteIdentifier()} as well. 284 * 285 * Note that this method does not override an already set HAVING part. Instead, multiple calls to this function add 286 * the specified HAVING part using the AND operator. 287 * 288 * @param string|ExpressionInterface|Select|array $condition The HAVING condition 289 * @param string $operator The operator to combine multiple conditions with, 290 * if the condition is in the array format 291 * 292 * @return $this 293 */ 294 public function having($condition, $operator = Sql::ALL) 295 { 296 $this->mergeCondition($this->having, $this->buildCondition($condition, $operator), Sql::ALL); 297 298 return $this; 299 } 300 301 /** 302 * Add a OR part to the HAVING part of the query 303 * 304 * Please see {@link having()} for the supported formats and restrictions regarding quoting of the field names. 305 * 306 * @param string|ExpressionInterface|Select|array $condition The HAVING condition 307 * @param string $operator The operator to combine multiple conditions with, 308 * if the condition is in the array format 309 * 310 * @return $this 311 */ 312 public function orHaving($condition, $operator = Sql::ALL) 313 { 314 $this->mergeCondition($this->having, $this->buildCondition($condition, $operator), Sql::ANY); 315 316 return $this; 317 } 318 319 /** 320 * Add a AND NOT part to the HAVING part of the query 321 * 322 * Please see {@link having()} for the supported formats and restrictions regarding quoting of the field names. 323 * 324 * @param string|ExpressionInterface|Select|array $condition The HAVING condition 325 * @param string $operator The operator to combine multiple conditions with, 326 * if the condition is in the array format 327 * 328 * @return $this 329 */ 330 public function notHaving($condition, $operator = Sql::ALL) 331 { 332 $this->mergeCondition($this->having, $this->buildCondition($condition, $operator), Sql::NOT_ALL); 333 334 return $this; 335 } 336 337 /** 338 * Add a OR NOT part to the HAVING part of the query 339 * 340 * Please see {@link having()} for the supported formats and restrictions regarding quoting of the field names. 341 * 342 * @param string|ExpressionInterface|Select|array $condition The HAVING condition 343 * @param string $operator The operator to combine multiple conditions with, 344 * if the condition is in the array format 345 * 346 * @return $this 347 */ 348 public function orNotHaving($condition, $operator = Sql::ALL) 349 { 350 $this->mergeCondition($this->having, $this->buildCondition($condition, $operator), Sql::NOT_ANY); 351 352 return $this; 353 } 354 355 /** 356 * Get the UNION parts of the query 357 * 358 * @return array|null 359 */ 360 public function getUnion() 361 { 362 return $this->union; 363 } 364 365 /** 366 * Combine a query with UNION 367 * 368 * @param Select|string $query 369 * 370 * @return $this 371 */ 372 public function union($query) 373 { 374 $this->union[] = [$query, false]; 375 376 return $this; 377 } 378 379 /** 380 * Combine a query with UNION ALL 381 * 382 * @param Select|string $query 383 * 384 * @return $this 385 */ 386 public function unionAll($query) 387 { 388 $this->union[] = [$query, true]; 389 390 return $this; 391 } 392 393 /** 394 * Reset the DISTINCT part of the query 395 * 396 * @return $this 397 */ 398 public function resetDistinct() 399 { 400 $this->distinct = false; 401 402 return $this; 403 } 404 405 /** 406 * Reset the columns of the query 407 * 408 * @return $this 409 */ 410 public function resetColumns() 411 { 412 $this->columns = null; 413 414 return $this; 415 } 416 417 /** 418 * Reset the FROM part of the query 419 * 420 * @return $this 421 */ 422 public function resetFrom() 423 { 424 $this->from = null; 425 426 return $this; 427 } 428 429 /** 430 * Reset the JOIN parts of the query 431 * 432 * @return $this 433 */ 434 public function resetJoin() 435 { 436 $this->join = null; 437 438 return $this; 439 } 440 441 /** 442 * Reset the GROUP BY part of the query 443 * 444 * @return $this 445 */ 446 public function resetGroupBy() 447 { 448 $this->groupBy = null; 449 450 return $this; 451 } 452 453 /** 454 * Reset the HAVING part of the query 455 * 456 * @return $this 457 */ 458 public function resetHaving() 459 { 460 $this->having = null; 461 462 return $this; 463 } 464 465 /** 466 * Reset the ORDER BY part of the query 467 * 468 * @return $this 469 */ 470 public function resetOrderBy() 471 { 472 $this->orderBy = null; 473 474 return $this; 475 } 476 477 /** 478 * Reset the limit of the query 479 * 480 * @return $this 481 */ 482 public function resetLimit() 483 { 484 $this->limit = null; 485 486 return $this; 487 } 488 489 /** 490 * Reset the offset of the query 491 * 492 * @return $this 493 */ 494 public function resetOffset() 495 { 496 $this->offset = null; 497 498 return $this; 499 } 500 501 /** 502 * Reset queries combined with UNION and UNION ALL 503 * 504 * @return $this 505 */ 506 public function resetUnion() 507 { 508 $this->union = null; 509 510 return $this; 511 } 512 513 /** 514 * Reset the WHERE part of the query 515 * 516 * @return $this 517 */ 518 public function resetWhere() 519 { 520 $this->where = null; 521 522 return $this; 523 } 524 525 /** 526 * Get the count query 527 * 528 * @return Select 529 */ 530 public function getCountQuery() 531 { 532 $countQuery = clone $this; 533 534 $countQuery->orderBy = null; 535 $countQuery->limit = null; 536 $countQuery->offset = null; 537 538 if (! empty($countQuery->groupBy)) { 539 $countQuery = (new Select())->from(['s' => $countQuery]); 540 } 541 542 $countQuery->columns = ['cnt' => 'COUNT(*)']; 543 544 return $countQuery; 545 } 546 547 public function __clone() 548 { 549 $this->cloneCte(); 550 $this->cloneOrderBy(); 551 $this->cloneWhere(); 552 553 if ($this->columns !== null) { 554 foreach ($this->columns as &$value) { 555 if ($value instanceof ExpressionInterface || $value instanceof Select) { 556 $value = clone $value; 557 } 558 } 559 unset($value); 560 } 561 562 if ($this->from !== null) { 563 foreach ($this->from as &$from) { 564 if ($from instanceof Select) { 565 $from = clone $from; 566 } 567 } 568 unset($from); 569 } 570 571 if ($this->join !== null) { 572 foreach ($this->join as &$join) { 573 if (is_array($join[1])) { 574 foreach ($join[1] as &$table) { 575 if ($table instanceof Select) { 576 $table = clone $table; 577 } 578 } 579 unset($table); 580 } elseif ($join[1] instanceof Select) { 581 $join[1] = clone $join[1]; 582 } 583 584 $this->cloneCondition($join[2]); 585 } 586 unset($join); 587 } 588 589 if ($this->groupBy !== null) { 590 foreach ($this->groupBy as &$value) { 591 if ($value instanceof ExpressionInterface || $value instanceof Select) { 592 $value = clone $value; 593 } 594 } 595 unset($value); 596 } 597 598 if ($this->having !== null) { 599 $this->cloneCondition($this->having); 600 } 601 602 if ($this->union !== null) { 603 foreach ($this->union as &$union) { 604 $union[0] = clone $union[0]; 605 } 606 unset($union); 607 } 608 } 609} 610