1<?php 2 3namespace Illuminate\Database\Query\Grammars; 4 5use Illuminate\Database\Grammar as BaseGrammar; 6use Illuminate\Database\Query\Builder; 7use Illuminate\Database\Query\JoinClause; 8use Illuminate\Support\Arr; 9use Illuminate\Support\Str; 10use RuntimeException; 11 12class Grammar extends BaseGrammar 13{ 14 /** 15 * The grammar specific operators. 16 * 17 * @var array 18 */ 19 protected $operators = []; 20 21 /** 22 * The components that make up a select clause. 23 * 24 * @var string[] 25 */ 26 protected $selectComponents = [ 27 'aggregate', 28 'columns', 29 'from', 30 'joins', 31 'wheres', 32 'groups', 33 'havings', 34 'orders', 35 'limit', 36 'offset', 37 'lock', 38 ]; 39 40 /** 41 * Compile a select query into SQL. 42 * 43 * @param \Illuminate\Database\Query\Builder $query 44 * @return string 45 */ 46 public function compileSelect(Builder $query) 47 { 48 if ($query->unions && $query->aggregate) { 49 return $this->compileUnionAggregate($query); 50 } 51 52 // If the query does not have any columns set, we'll set the columns to the 53 // * character to just get all of the columns from the database. Then we 54 // can build the query and concatenate all the pieces together as one. 55 $original = $query->columns; 56 57 if (is_null($query->columns)) { 58 $query->columns = ['*']; 59 } 60 61 // To compile the query, we'll spin through each component of the query and 62 // see if that component exists. If it does we'll just call the compiler 63 // function for the component which is responsible for making the SQL. 64 $sql = trim($this->concatenate( 65 $this->compileComponents($query)) 66 ); 67 68 if ($query->unions) { 69 $sql = $this->wrapUnion($sql).' '.$this->compileUnions($query); 70 } 71 72 $query->columns = $original; 73 74 return $sql; 75 } 76 77 /** 78 * Compile the components necessary for a select clause. 79 * 80 * @param \Illuminate\Database\Query\Builder $query 81 * @return array 82 */ 83 protected function compileComponents(Builder $query) 84 { 85 $sql = []; 86 87 foreach ($this->selectComponents as $component) { 88 if (isset($query->$component)) { 89 $method = 'compile'.ucfirst($component); 90 91 $sql[$component] = $this->$method($query, $query->$component); 92 } 93 } 94 95 return $sql; 96 } 97 98 /** 99 * Compile an aggregated select clause. 100 * 101 * @param \Illuminate\Database\Query\Builder $query 102 * @param array $aggregate 103 * @return string 104 */ 105 protected function compileAggregate(Builder $query, $aggregate) 106 { 107 $column = $this->columnize($aggregate['columns']); 108 109 // If the query has a "distinct" constraint and we're not asking for all columns 110 // we need to prepend "distinct" onto the column name so that the query takes 111 // it into account when it performs the aggregating operations on the data. 112 if (is_array($query->distinct)) { 113 $column = 'distinct '.$this->columnize($query->distinct); 114 } elseif ($query->distinct && $column !== '*') { 115 $column = 'distinct '.$column; 116 } 117 118 return 'select '.$aggregate['function'].'('.$column.') as aggregate'; 119 } 120 121 /** 122 * Compile the "select *" portion of the query. 123 * 124 * @param \Illuminate\Database\Query\Builder $query 125 * @param array $columns 126 * @return string|null 127 */ 128 protected function compileColumns(Builder $query, $columns) 129 { 130 // If the query is actually performing an aggregating select, we will let that 131 // compiler handle the building of the select clauses, as it will need some 132 // more syntax that is best handled by that function to keep things neat. 133 if (! is_null($query->aggregate)) { 134 return; 135 } 136 137 if ($query->distinct) { 138 $select = 'select distinct '; 139 } else { 140 $select = 'select '; 141 } 142 143 return $select.$this->columnize($columns); 144 } 145 146 /** 147 * Compile the "from" portion of the query. 148 * 149 * @param \Illuminate\Database\Query\Builder $query 150 * @param string $table 151 * @return string 152 */ 153 protected function compileFrom(Builder $query, $table) 154 { 155 return 'from '.$this->wrapTable($table); 156 } 157 158 /** 159 * Compile the "join" portions of the query. 160 * 161 * @param \Illuminate\Database\Query\Builder $query 162 * @param array $joins 163 * @return string 164 */ 165 protected function compileJoins(Builder $query, $joins) 166 { 167 return collect($joins)->map(function ($join) use ($query) { 168 $table = $this->wrapTable($join->table); 169 170 $nestedJoins = is_null($join->joins) ? '' : ' '.$this->compileJoins($query, $join->joins); 171 172 $tableAndNestedJoins = is_null($join->joins) ? $table : '('.$table.$nestedJoins.')'; 173 174 return trim("{$join->type} join {$tableAndNestedJoins} {$this->compileWheres($join)}"); 175 })->implode(' '); 176 } 177 178 /** 179 * Compile the "where" portions of the query. 180 * 181 * @param \Illuminate\Database\Query\Builder $query 182 * @return string 183 */ 184 public function compileWheres(Builder $query) 185 { 186 // Each type of where clauses has its own compiler function which is responsible 187 // for actually creating the where clauses SQL. This helps keep the code nice 188 // and maintainable since each clause has a very small method that it uses. 189 if (is_null($query->wheres)) { 190 return ''; 191 } 192 193 // If we actually have some where clauses, we will strip off the first boolean 194 // operator, which is added by the query builders for convenience so we can 195 // avoid checking for the first clauses in each of the compilers methods. 196 if (count($sql = $this->compileWheresToArray($query)) > 0) { 197 return $this->concatenateWhereClauses($query, $sql); 198 } 199 200 return ''; 201 } 202 203 /** 204 * Get an array of all the where clauses for the query. 205 * 206 * @param \Illuminate\Database\Query\Builder $query 207 * @return array 208 */ 209 protected function compileWheresToArray($query) 210 { 211 return collect($query->wheres)->map(function ($where) use ($query) { 212 return $where['boolean'].' '.$this->{"where{$where['type']}"}($query, $where); 213 })->all(); 214 } 215 216 /** 217 * Format the where clause statements into one string. 218 * 219 * @param \Illuminate\Database\Query\Builder $query 220 * @param array $sql 221 * @return string 222 */ 223 protected function concatenateWhereClauses($query, $sql) 224 { 225 $conjunction = $query instanceof JoinClause ? 'on' : 'where'; 226 227 return $conjunction.' '.$this->removeLeadingBoolean(implode(' ', $sql)); 228 } 229 230 /** 231 * Compile a raw where clause. 232 * 233 * @param \Illuminate\Database\Query\Builder $query 234 * @param array $where 235 * @return string 236 */ 237 protected function whereRaw(Builder $query, $where) 238 { 239 return $where['sql']; 240 } 241 242 /** 243 * Compile a basic where clause. 244 * 245 * @param \Illuminate\Database\Query\Builder $query 246 * @param array $where 247 * @return string 248 */ 249 protected function whereBasic(Builder $query, $where) 250 { 251 $value = $this->parameter($where['value']); 252 253 $operator = str_replace('?', '??', $where['operator']); 254 255 return $this->wrap($where['column']).' '.$operator.' '.$value; 256 } 257 258 /** 259 * Compile a "where in" clause. 260 * 261 * @param \Illuminate\Database\Query\Builder $query 262 * @param array $where 263 * @return string 264 */ 265 protected function whereIn(Builder $query, $where) 266 { 267 if (! empty($where['values'])) { 268 return $this->wrap($where['column']).' in ('.$this->parameterize($where['values']).')'; 269 } 270 271 return '0 = 1'; 272 } 273 274 /** 275 * Compile a "where not in" clause. 276 * 277 * @param \Illuminate\Database\Query\Builder $query 278 * @param array $where 279 * @return string 280 */ 281 protected function whereNotIn(Builder $query, $where) 282 { 283 if (! empty($where['values'])) { 284 return $this->wrap($where['column']).' not in ('.$this->parameterize($where['values']).')'; 285 } 286 287 return '1 = 1'; 288 } 289 290 /** 291 * Compile a "where not in raw" clause. 292 * 293 * For safety, whereIntegerInRaw ensures this method is only used with integer values. 294 * 295 * @param \Illuminate\Database\Query\Builder $query 296 * @param array $where 297 * @return string 298 */ 299 protected function whereNotInRaw(Builder $query, $where) 300 { 301 if (! empty($where['values'])) { 302 return $this->wrap($where['column']).' not in ('.implode(', ', $where['values']).')'; 303 } 304 305 return '1 = 1'; 306 } 307 308 /** 309 * Compile a "where in raw" clause. 310 * 311 * For safety, whereIntegerInRaw ensures this method is only used with integer values. 312 * 313 * @param \Illuminate\Database\Query\Builder $query 314 * @param array $where 315 * @return string 316 */ 317 protected function whereInRaw(Builder $query, $where) 318 { 319 if (! empty($where['values'])) { 320 return $this->wrap($where['column']).' in ('.implode(', ', $where['values']).')'; 321 } 322 323 return '0 = 1'; 324 } 325 326 /** 327 * Compile a "where null" clause. 328 * 329 * @param \Illuminate\Database\Query\Builder $query 330 * @param array $where 331 * @return string 332 */ 333 protected function whereNull(Builder $query, $where) 334 { 335 return $this->wrap($where['column']).' is null'; 336 } 337 338 /** 339 * Compile a "where not null" clause. 340 * 341 * @param \Illuminate\Database\Query\Builder $query 342 * @param array $where 343 * @return string 344 */ 345 protected function whereNotNull(Builder $query, $where) 346 { 347 return $this->wrap($where['column']).' is not null'; 348 } 349 350 /** 351 * Compile a "between" where clause. 352 * 353 * @param \Illuminate\Database\Query\Builder $query 354 * @param array $where 355 * @return string 356 */ 357 protected function whereBetween(Builder $query, $where) 358 { 359 $between = $where['not'] ? 'not between' : 'between'; 360 361 $min = $this->parameter(reset($where['values'])); 362 363 $max = $this->parameter(end($where['values'])); 364 365 return $this->wrap($where['column']).' '.$between.' '.$min.' and '.$max; 366 } 367 368 /** 369 * Compile a "between" where clause. 370 * 371 * @param \Illuminate\Database\Query\Builder $query 372 * @param array $where 373 * @return string 374 */ 375 protected function whereBetweenColumns(Builder $query, $where) 376 { 377 $between = $where['not'] ? 'not between' : 'between'; 378 379 $min = $this->wrap(reset($where['values'])); 380 381 $max = $this->wrap(end($where['values'])); 382 383 return $this->wrap($where['column']).' '.$between.' '.$min.' and '.$max; 384 } 385 386 /** 387 * Compile a "where date" clause. 388 * 389 * @param \Illuminate\Database\Query\Builder $query 390 * @param array $where 391 * @return string 392 */ 393 protected function whereDate(Builder $query, $where) 394 { 395 return $this->dateBasedWhere('date', $query, $where); 396 } 397 398 /** 399 * Compile a "where time" clause. 400 * 401 * @param \Illuminate\Database\Query\Builder $query 402 * @param array $where 403 * @return string 404 */ 405 protected function whereTime(Builder $query, $where) 406 { 407 return $this->dateBasedWhere('time', $query, $where); 408 } 409 410 /** 411 * Compile a "where day" clause. 412 * 413 * @param \Illuminate\Database\Query\Builder $query 414 * @param array $where 415 * @return string 416 */ 417 protected function whereDay(Builder $query, $where) 418 { 419 return $this->dateBasedWhere('day', $query, $where); 420 } 421 422 /** 423 * Compile a "where month" clause. 424 * 425 * @param \Illuminate\Database\Query\Builder $query 426 * @param array $where 427 * @return string 428 */ 429 protected function whereMonth(Builder $query, $where) 430 { 431 return $this->dateBasedWhere('month', $query, $where); 432 } 433 434 /** 435 * Compile a "where year" clause. 436 * 437 * @param \Illuminate\Database\Query\Builder $query 438 * @param array $where 439 * @return string 440 */ 441 protected function whereYear(Builder $query, $where) 442 { 443 return $this->dateBasedWhere('year', $query, $where); 444 } 445 446 /** 447 * Compile a date based where clause. 448 * 449 * @param string $type 450 * @param \Illuminate\Database\Query\Builder $query 451 * @param array $where 452 * @return string 453 */ 454 protected function dateBasedWhere($type, Builder $query, $where) 455 { 456 $value = $this->parameter($where['value']); 457 458 return $type.'('.$this->wrap($where['column']).') '.$where['operator'].' '.$value; 459 } 460 461 /** 462 * Compile a where clause comparing two columns. 463 * 464 * @param \Illuminate\Database\Query\Builder $query 465 * @param array $where 466 * @return string 467 */ 468 protected function whereColumn(Builder $query, $where) 469 { 470 return $this->wrap($where['first']).' '.$where['operator'].' '.$this->wrap($where['second']); 471 } 472 473 /** 474 * Compile a nested where clause. 475 * 476 * @param \Illuminate\Database\Query\Builder $query 477 * @param array $where 478 * @return string 479 */ 480 protected function whereNested(Builder $query, $where) 481 { 482 // Here we will calculate what portion of the string we need to remove. If this 483 // is a join clause query, we need to remove the "on" portion of the SQL and 484 // if it is a normal query we need to take the leading "where" of queries. 485 $offset = $query instanceof JoinClause ? 3 : 6; 486 487 return '('.substr($this->compileWheres($where['query']), $offset).')'; 488 } 489 490 /** 491 * Compile a where condition with a sub-select. 492 * 493 * @param \Illuminate\Database\Query\Builder $query 494 * @param array $where 495 * @return string 496 */ 497 protected function whereSub(Builder $query, $where) 498 { 499 $select = $this->compileSelect($where['query']); 500 501 return $this->wrap($where['column']).' '.$where['operator']." ($select)"; 502 } 503 504 /** 505 * Compile a where exists clause. 506 * 507 * @param \Illuminate\Database\Query\Builder $query 508 * @param array $where 509 * @return string 510 */ 511 protected function whereExists(Builder $query, $where) 512 { 513 return 'exists ('.$this->compileSelect($where['query']).')'; 514 } 515 516 /** 517 * Compile a where exists clause. 518 * 519 * @param \Illuminate\Database\Query\Builder $query 520 * @param array $where 521 * @return string 522 */ 523 protected function whereNotExists(Builder $query, $where) 524 { 525 return 'not exists ('.$this->compileSelect($where['query']).')'; 526 } 527 528 /** 529 * Compile a where row values condition. 530 * 531 * @param \Illuminate\Database\Query\Builder $query 532 * @param array $where 533 * @return string 534 */ 535 protected function whereRowValues(Builder $query, $where) 536 { 537 $columns = $this->columnize($where['columns']); 538 539 $values = $this->parameterize($where['values']); 540 541 return '('.$columns.') '.$where['operator'].' ('.$values.')'; 542 } 543 544 /** 545 * Compile a "where JSON boolean" clause. 546 * 547 * @param \Illuminate\Database\Query\Builder $query 548 * @param array $where 549 * @return string 550 */ 551 protected function whereJsonBoolean(Builder $query, $where) 552 { 553 $column = $this->wrapJsonBooleanSelector($where['column']); 554 555 $value = $this->wrapJsonBooleanValue( 556 $this->parameter($where['value']) 557 ); 558 559 return $column.' '.$where['operator'].' '.$value; 560 } 561 562 /** 563 * Compile a "where JSON contains" clause. 564 * 565 * @param \Illuminate\Database\Query\Builder $query 566 * @param array $where 567 * @return string 568 */ 569 protected function whereJsonContains(Builder $query, $where) 570 { 571 $not = $where['not'] ? 'not ' : ''; 572 573 return $not.$this->compileJsonContains( 574 $where['column'], $this->parameter($where['value']) 575 ); 576 } 577 578 /** 579 * Compile a "JSON contains" statement into SQL. 580 * 581 * @param string $column 582 * @param string $value 583 * @return string 584 * 585 * @throws \RuntimeException 586 */ 587 protected function compileJsonContains($column, $value) 588 { 589 throw new RuntimeException('This database engine does not support JSON contains operations.'); 590 } 591 592 /** 593 * Prepare the binding for a "JSON contains" statement. 594 * 595 * @param mixed $binding 596 * @return string 597 */ 598 public function prepareBindingForJsonContains($binding) 599 { 600 return json_encode($binding); 601 } 602 603 /** 604 * Compile a "where JSON length" clause. 605 * 606 * @param \Illuminate\Database\Query\Builder $query 607 * @param array $where 608 * @return string 609 */ 610 protected function whereJsonLength(Builder $query, $where) 611 { 612 return $this->compileJsonLength( 613 $where['column'], $where['operator'], $this->parameter($where['value']) 614 ); 615 } 616 617 /** 618 * Compile a "JSON length" statement into SQL. 619 * 620 * @param string $column 621 * @param string $operator 622 * @param string $value 623 * @return string 624 * 625 * @throws \RuntimeException 626 */ 627 protected function compileJsonLength($column, $operator, $value) 628 { 629 throw new RuntimeException('This database engine does not support JSON length operations.'); 630 } 631 632 /** 633 * Compile the "group by" portions of the query. 634 * 635 * @param \Illuminate\Database\Query\Builder $query 636 * @param array $groups 637 * @return string 638 */ 639 protected function compileGroups(Builder $query, $groups) 640 { 641 return 'group by '.$this->columnize($groups); 642 } 643 644 /** 645 * Compile the "having" portions of the query. 646 * 647 * @param \Illuminate\Database\Query\Builder $query 648 * @param array $havings 649 * @return string 650 */ 651 protected function compileHavings(Builder $query, $havings) 652 { 653 $sql = implode(' ', array_map([$this, 'compileHaving'], $havings)); 654 655 return 'having '.$this->removeLeadingBoolean($sql); 656 } 657 658 /** 659 * Compile a single having clause. 660 * 661 * @param array $having 662 * @return string 663 */ 664 protected function compileHaving(array $having) 665 { 666 // If the having clause is "raw", we can just return the clause straight away 667 // without doing any more processing on it. Otherwise, we will compile the 668 // clause into SQL based on the components that make it up from builder. 669 if ($having['type'] === 'Raw') { 670 return $having['boolean'].' '.$having['sql']; 671 } elseif ($having['type'] === 'between') { 672 return $this->compileHavingBetween($having); 673 } 674 675 return $this->compileBasicHaving($having); 676 } 677 678 /** 679 * Compile a basic having clause. 680 * 681 * @param array $having 682 * @return string 683 */ 684 protected function compileBasicHaving($having) 685 { 686 $column = $this->wrap($having['column']); 687 688 $parameter = $this->parameter($having['value']); 689 690 return $having['boolean'].' '.$column.' '.$having['operator'].' '.$parameter; 691 } 692 693 /** 694 * Compile a "between" having clause. 695 * 696 * @param array $having 697 * @return string 698 */ 699 protected function compileHavingBetween($having) 700 { 701 $between = $having['not'] ? 'not between' : 'between'; 702 703 $column = $this->wrap($having['column']); 704 705 $min = $this->parameter(head($having['values'])); 706 707 $max = $this->parameter(last($having['values'])); 708 709 return $having['boolean'].' '.$column.' '.$between.' '.$min.' and '.$max; 710 } 711 712 /** 713 * Compile the "order by" portions of the query. 714 * 715 * @param \Illuminate\Database\Query\Builder $query 716 * @param array $orders 717 * @return string 718 */ 719 protected function compileOrders(Builder $query, $orders) 720 { 721 if (! empty($orders)) { 722 return 'order by '.implode(', ', $this->compileOrdersToArray($query, $orders)); 723 } 724 725 return ''; 726 } 727 728 /** 729 * Compile the query orders to an array. 730 * 731 * @param \Illuminate\Database\Query\Builder $query 732 * @param array $orders 733 * @return array 734 */ 735 protected function compileOrdersToArray(Builder $query, $orders) 736 { 737 return array_map(function ($order) { 738 return $order['sql'] ?? $this->wrap($order['column']).' '.$order['direction']; 739 }, $orders); 740 } 741 742 /** 743 * Compile the random statement into SQL. 744 * 745 * @param string $seed 746 * @return string 747 */ 748 public function compileRandom($seed) 749 { 750 return 'RANDOM()'; 751 } 752 753 /** 754 * Compile the "limit" portions of the query. 755 * 756 * @param \Illuminate\Database\Query\Builder $query 757 * @param int $limit 758 * @return string 759 */ 760 protected function compileLimit(Builder $query, $limit) 761 { 762 return 'limit '.(int) $limit; 763 } 764 765 /** 766 * Compile the "offset" portions of the query. 767 * 768 * @param \Illuminate\Database\Query\Builder $query 769 * @param int $offset 770 * @return string 771 */ 772 protected function compileOffset(Builder $query, $offset) 773 { 774 return 'offset '.(int) $offset; 775 } 776 777 /** 778 * Compile the "union" queries attached to the main query. 779 * 780 * @param \Illuminate\Database\Query\Builder $query 781 * @return string 782 */ 783 protected function compileUnions(Builder $query) 784 { 785 $sql = ''; 786 787 foreach ($query->unions as $union) { 788 $sql .= $this->compileUnion($union); 789 } 790 791 if (! empty($query->unionOrders)) { 792 $sql .= ' '.$this->compileOrders($query, $query->unionOrders); 793 } 794 795 if (isset($query->unionLimit)) { 796 $sql .= ' '.$this->compileLimit($query, $query->unionLimit); 797 } 798 799 if (isset($query->unionOffset)) { 800 $sql .= ' '.$this->compileOffset($query, $query->unionOffset); 801 } 802 803 return ltrim($sql); 804 } 805 806 /** 807 * Compile a single union statement. 808 * 809 * @param array $union 810 * @return string 811 */ 812 protected function compileUnion(array $union) 813 { 814 $conjunction = $union['all'] ? ' union all ' : ' union '; 815 816 return $conjunction.$this->wrapUnion($union['query']->toSql()); 817 } 818 819 /** 820 * Wrap a union subquery in parentheses. 821 * 822 * @param string $sql 823 * @return string 824 */ 825 protected function wrapUnion($sql) 826 { 827 return '('.$sql.')'; 828 } 829 830 /** 831 * Compile a union aggregate query into SQL. 832 * 833 * @param \Illuminate\Database\Query\Builder $query 834 * @return string 835 */ 836 protected function compileUnionAggregate(Builder $query) 837 { 838 $sql = $this->compileAggregate($query, $query->aggregate); 839 840 $query->aggregate = null; 841 842 return $sql.' from ('.$this->compileSelect($query).') as '.$this->wrapTable('temp_table'); 843 } 844 845 /** 846 * Compile an exists statement into SQL. 847 * 848 * @param \Illuminate\Database\Query\Builder $query 849 * @return string 850 */ 851 public function compileExists(Builder $query) 852 { 853 $select = $this->compileSelect($query); 854 855 return "select exists({$select}) as {$this->wrap('exists')}"; 856 } 857 858 /** 859 * Compile an insert statement into SQL. 860 * 861 * @param \Illuminate\Database\Query\Builder $query 862 * @param array $values 863 * @return string 864 */ 865 public function compileInsert(Builder $query, array $values) 866 { 867 // Essentially we will force every insert to be treated as a batch insert which 868 // simply makes creating the SQL easier for us since we can utilize the same 869 // basic routine regardless of an amount of records given to us to insert. 870 $table = $this->wrapTable($query->from); 871 872 if (empty($values)) { 873 return "insert into {$table} default values"; 874 } 875 876 if (! is_array(reset($values))) { 877 $values = [$values]; 878 } 879 880 $columns = $this->columnize(array_keys(reset($values))); 881 882 // We need to build a list of parameter place-holders of values that are bound 883 // to the query. Each insert should have the exact same amount of parameter 884 // bindings so we will loop through the record and parameterize them all. 885 $parameters = collect($values)->map(function ($record) { 886 return '('.$this->parameterize($record).')'; 887 })->implode(', '); 888 889 return "insert into $table ($columns) values $parameters"; 890 } 891 892 /** 893 * Compile an insert ignore statement into SQL. 894 * 895 * @param \Illuminate\Database\Query\Builder $query 896 * @param array $values 897 * @return string 898 * 899 * @throws \RuntimeException 900 */ 901 public function compileInsertOrIgnore(Builder $query, array $values) 902 { 903 throw new RuntimeException('This database engine does not support inserting while ignoring errors.'); 904 } 905 906 /** 907 * Compile an insert and get ID statement into SQL. 908 * 909 * @param \Illuminate\Database\Query\Builder $query 910 * @param array $values 911 * @param string $sequence 912 * @return string 913 */ 914 public function compileInsertGetId(Builder $query, $values, $sequence) 915 { 916 return $this->compileInsert($query, $values); 917 } 918 919 /** 920 * Compile an insert statement using a subquery into SQL. 921 * 922 * @param \Illuminate\Database\Query\Builder $query 923 * @param array $columns 924 * @param string $sql 925 * @return string 926 */ 927 public function compileInsertUsing(Builder $query, array $columns, string $sql) 928 { 929 return "insert into {$this->wrapTable($query->from)} ({$this->columnize($columns)}) $sql"; 930 } 931 932 /** 933 * Compile an update statement into SQL. 934 * 935 * @param \Illuminate\Database\Query\Builder $query 936 * @param array $values 937 * @return string 938 */ 939 public function compileUpdate(Builder $query, array $values) 940 { 941 $table = $this->wrapTable($query->from); 942 943 $columns = $this->compileUpdateColumns($query, $values); 944 945 $where = $this->compileWheres($query); 946 947 return trim( 948 isset($query->joins) 949 ? $this->compileUpdateWithJoins($query, $table, $columns, $where) 950 : $this->compileUpdateWithoutJoins($query, $table, $columns, $where) 951 ); 952 } 953 954 /** 955 * Compile the columns for an update statement. 956 * 957 * @param \Illuminate\Database\Query\Builder $query 958 * @param array $values 959 * @return string 960 */ 961 protected function compileUpdateColumns(Builder $query, array $values) 962 { 963 return collect($values)->map(function ($value, $key) { 964 return $this->wrap($key).' = '.$this->parameter($value); 965 })->implode(', '); 966 } 967 968 /** 969 * Compile an update statement without joins into SQL. 970 * 971 * @param \Illuminate\Database\Query\Builder $query 972 * @param string $table 973 * @param string $columns 974 * @param string $where 975 * @return string 976 */ 977 protected function compileUpdateWithoutJoins(Builder $query, $table, $columns, $where) 978 { 979 return "update {$table} set {$columns} {$where}"; 980 } 981 982 /** 983 * Compile an update statement with joins into SQL. 984 * 985 * @param \Illuminate\Database\Query\Builder $query 986 * @param string $table 987 * @param string $columns 988 * @param string $where 989 * @return string 990 */ 991 protected function compileUpdateWithJoins(Builder $query, $table, $columns, $where) 992 { 993 $joins = $this->compileJoins($query, $query->joins); 994 995 return "update {$table} {$joins} set {$columns} {$where}"; 996 } 997 998 /** 999 * Compile an "upsert" statement into SQL. 1000 * 1001 * @param \Illuminate\Database\Query\Builder $query 1002 * @param array $values 1003 * @param array $uniqueBy 1004 * @param array $update 1005 * @return string 1006 * 1007 * @throws \RuntimeException 1008 */ 1009 public function compileUpsert(Builder $query, array $values, array $uniqueBy, array $update) 1010 { 1011 throw new RuntimeException('This database engine does not support upserts.'); 1012 } 1013 1014 /** 1015 * Prepare the bindings for an update statement. 1016 * 1017 * @param array $bindings 1018 * @param array $values 1019 * @return array 1020 */ 1021 public function prepareBindingsForUpdate(array $bindings, array $values) 1022 { 1023 $cleanBindings = Arr::except($bindings, ['select', 'join']); 1024 1025 return array_values( 1026 array_merge($bindings['join'], $values, Arr::flatten($cleanBindings)) 1027 ); 1028 } 1029 1030 /** 1031 * Compile a delete statement into SQL. 1032 * 1033 * @param \Illuminate\Database\Query\Builder $query 1034 * @return string 1035 */ 1036 public function compileDelete(Builder $query) 1037 { 1038 $table = $this->wrapTable($query->from); 1039 1040 $where = $this->compileWheres($query); 1041 1042 return trim( 1043 isset($query->joins) 1044 ? $this->compileDeleteWithJoins($query, $table, $where) 1045 : $this->compileDeleteWithoutJoins($query, $table, $where) 1046 ); 1047 } 1048 1049 /** 1050 * Compile a delete statement without joins into SQL. 1051 * 1052 * @param \Illuminate\Database\Query\Builder $query 1053 * @param string $table 1054 * @param string $where 1055 * @return string 1056 */ 1057 protected function compileDeleteWithoutJoins(Builder $query, $table, $where) 1058 { 1059 return "delete from {$table} {$where}"; 1060 } 1061 1062 /** 1063 * Compile a delete statement with joins into SQL. 1064 * 1065 * @param \Illuminate\Database\Query\Builder $query 1066 * @param string $table 1067 * @param string $where 1068 * @return string 1069 */ 1070 protected function compileDeleteWithJoins(Builder $query, $table, $where) 1071 { 1072 $alias = last(explode(' as ', $table)); 1073 1074 $joins = $this->compileJoins($query, $query->joins); 1075 1076 return "delete {$alias} from {$table} {$joins} {$where}"; 1077 } 1078 1079 /** 1080 * Prepare the bindings for a delete statement. 1081 * 1082 * @param array $bindings 1083 * @return array 1084 */ 1085 public function prepareBindingsForDelete(array $bindings) 1086 { 1087 return Arr::flatten( 1088 Arr::except($bindings, 'select') 1089 ); 1090 } 1091 1092 /** 1093 * Compile a truncate table statement into SQL. 1094 * 1095 * @param \Illuminate\Database\Query\Builder $query 1096 * @return array 1097 */ 1098 public function compileTruncate(Builder $query) 1099 { 1100 return ['truncate table '.$this->wrapTable($query->from) => []]; 1101 } 1102 1103 /** 1104 * Compile the lock into SQL. 1105 * 1106 * @param \Illuminate\Database\Query\Builder $query 1107 * @param bool|string $value 1108 * @return string 1109 */ 1110 protected function compileLock(Builder $query, $value) 1111 { 1112 return is_string($value) ? $value : ''; 1113 } 1114 1115 /** 1116 * Determine if the grammar supports savepoints. 1117 * 1118 * @return bool 1119 */ 1120 public function supportsSavepoints() 1121 { 1122 return true; 1123 } 1124 1125 /** 1126 * Compile the SQL statement to define a savepoint. 1127 * 1128 * @param string $name 1129 * @return string 1130 */ 1131 public function compileSavepoint($name) 1132 { 1133 return 'SAVEPOINT '.$name; 1134 } 1135 1136 /** 1137 * Compile the SQL statement to execute a savepoint rollback. 1138 * 1139 * @param string $name 1140 * @return string 1141 */ 1142 public function compileSavepointRollBack($name) 1143 { 1144 return 'ROLLBACK TO SAVEPOINT '.$name; 1145 } 1146 1147 /** 1148 * Wrap a value in keyword identifiers. 1149 * 1150 * @param \Illuminate\Database\Query\Expression|string $value 1151 * @param bool $prefixAlias 1152 * @return string 1153 */ 1154 public function wrap($value, $prefixAlias = false) 1155 { 1156 if ($this->isExpression($value)) { 1157 return $this->getValue($value); 1158 } 1159 1160 // If the value being wrapped has a column alias we will need to separate out 1161 // the pieces so we can wrap each of the segments of the expression on its 1162 // own, and then join these both back together using the "as" connector. 1163 if (stripos($value, ' as ') !== false) { 1164 return $this->wrapAliasedValue($value, $prefixAlias); 1165 } 1166 1167 // If the given value is a JSON selector we will wrap it differently than a 1168 // traditional value. We will need to split this path and wrap each part 1169 // wrapped, etc. Otherwise, we will simply wrap the value as a string. 1170 if ($this->isJsonSelector($value)) { 1171 return $this->wrapJsonSelector($value); 1172 } 1173 1174 return $this->wrapSegments(explode('.', $value)); 1175 } 1176 1177 /** 1178 * Wrap the given JSON selector. 1179 * 1180 * @param string $value 1181 * @return string 1182 * 1183 * @throws \RuntimeException 1184 */ 1185 protected function wrapJsonSelector($value) 1186 { 1187 throw new RuntimeException('This database engine does not support JSON operations.'); 1188 } 1189 1190 /** 1191 * Wrap the given JSON selector for boolean values. 1192 * 1193 * @param string $value 1194 * @return string 1195 */ 1196 protected function wrapJsonBooleanSelector($value) 1197 { 1198 return $this->wrapJsonSelector($value); 1199 } 1200 1201 /** 1202 * Wrap the given JSON boolean value. 1203 * 1204 * @param string $value 1205 * @return string 1206 */ 1207 protected function wrapJsonBooleanValue($value) 1208 { 1209 return $value; 1210 } 1211 1212 /** 1213 * Split the given JSON selector into the field and the optional path and wrap them separately. 1214 * 1215 * @param string $column 1216 * @return array 1217 */ 1218 protected function wrapJsonFieldAndPath($column) 1219 { 1220 $parts = explode('->', $column, 2); 1221 1222 $field = $this->wrap($parts[0]); 1223 1224 $path = count($parts) > 1 ? ', '.$this->wrapJsonPath($parts[1], '->') : ''; 1225 1226 return [$field, $path]; 1227 } 1228 1229 /** 1230 * Wrap the given JSON path. 1231 * 1232 * @param string $value 1233 * @param string $delimiter 1234 * @return string 1235 */ 1236 protected function wrapJsonPath($value, $delimiter = '->') 1237 { 1238 $value = preg_replace("/([\\\\]+)?\\'/", "''", $value); 1239 1240 return '\'$."'.str_replace($delimiter, '"."', $value).'"\''; 1241 } 1242 1243 /** 1244 * Determine if the given string is a JSON selector. 1245 * 1246 * @param string $value 1247 * @return bool 1248 */ 1249 protected function isJsonSelector($value) 1250 { 1251 return Str::contains($value, '->'); 1252 } 1253 1254 /** 1255 * Concatenate an array of segments, removing empties. 1256 * 1257 * @param array $segments 1258 * @return string 1259 */ 1260 protected function concatenate($segments) 1261 { 1262 return implode(' ', array_filter($segments, function ($value) { 1263 return (string) $value !== ''; 1264 })); 1265 } 1266 1267 /** 1268 * Remove the leading boolean from a statement. 1269 * 1270 * @param string $value 1271 * @return string 1272 */ 1273 protected function removeLeadingBoolean($value) 1274 { 1275 return preg_replace('/and |or /i', '', $value, 1); 1276 } 1277 1278 /** 1279 * Get the grammar specific operators. 1280 * 1281 * @return array 1282 */ 1283 public function getOperators() 1284 { 1285 return $this->operators; 1286 } 1287} 1288