1: <?php namespace Laravel\Database;
2:
3: use Closure;
4: use Laravel\Database;
5: use Laravel\Paginator;
6: use Laravel\Database\Query\Grammars\Postgres;
7: use Laravel\Database\Query\Grammars\SQLServer;
8:
9: class Query {
10:
11: /**
12: * The database connection.
13: *
14: * @var Connection
15: */
16: public $connection;
17:
18: /**
19: * The query grammar instance.
20: *
21: * @var Query\Grammars\Grammar
22: */
23: public $grammar;
24:
25: /**
26: * The SELECT clause.
27: *
28: * @var array
29: */
30: public $selects;
31:
32: /**
33: * The aggregating column and function.
34: *
35: * @var array
36: */
37: public $aggregate;
38:
39: /**
40: * Indicates if the query should return distinct results.
41: *
42: * @var bool
43: */
44: public $distinct = false;
45:
46: /**
47: * The table name.
48: *
49: * @var string
50: */
51: public $from;
52:
53: /**
54: * The table joins.
55: *
56: * @var array
57: */
58: public $joins;
59:
60: /**
61: * The WHERE clauses.
62: *
63: * @var array
64: */
65: public $wheres;
66:
67: /**
68: * The GROUP BY clauses.
69: *
70: * @var array
71: */
72: public $groupings;
73:
74: /**
75: * The HAVING clauses.
76: *
77: * @var array
78: */
79: public $havings;
80:
81: /**
82: * The ORDER BY clauses.
83: *
84: * @var array
85: */
86: public $orderings;
87:
88: /**
89: * The LIMIT value.
90: *
91: * @var int
92: */
93: public $limit;
94:
95: /**
96: * The OFFSET value.
97: *
98: * @var int
99: */
100: public $offset;
101:
102: /**
103: * The query value bindings.
104: *
105: * @var array
106: */
107: public $bindings = array();
108:
109: /**
110: * Create a new query instance.
111: *
112: * @param Connection $connection
113: * @param Grammar $grammar
114: * @param string $table
115: * @return void
116: */
117: public function __construct(Connection $connection, Query\Grammars\Grammar $grammar, $table)
118: {
119: $this->from = $table;
120: $this->grammar = $grammar;
121: $this->connection = $connection;
122: }
123:
124: /**
125: * Force the query to return distinct results.
126: *
127: * @return Query
128: */
129: public function distinct()
130: {
131: $this->distinct = true;
132: return $this;
133: }
134:
135: /**
136: * Add an array of columns to the SELECT clause.
137: *
138: * @param array $columns
139: * @return Query
140: */
141: public function select($columns = array('*'))
142: {
143: $this->selects = (array) $columns;
144: return $this;
145: }
146:
147: /**
148: * Add a join clause to the query.
149: *
150: * @param string $table
151: * @param string $column1
152: * @param string $operator
153: * @param string $column2
154: * @param string $type
155: * @return Query
156: */
157: public function join($table, $column1, $operator = null, $column2 = null, $type = 'INNER')
158: {
159: // If the "column" is really an instance of a Closure, the developer is
160: // trying to create a join with a complex "ON" clause. So, we will add
161: // the join, and then call the Closure with the join/
162: if ($column1 instanceof Closure)
163: {
164: $this->joins[] = new Query\Join($type, $table);
165:
166: call_user_func($column1, end($this->joins));
167: }
168:
169: // If the column is just a string, we can assume that the join just
170: // has a simple on clause, and we'll create the join instance and
171: // add the clause automatically for the develoepr.
172: else
173: {
174: $join = new Query\Join($type, $table);
175:
176: $join->on($column1, $operator, $column2);
177:
178: $this->joins[] = $join;
179: }
180:
181: return $this;
182: }
183:
184: /**
185: * Add a left join to the query.
186: *
187: * @param string $table
188: * @param string $column1
189: * @param string $operator
190: * @param string $column2
191: * @return Query
192: */
193: public function left_join($table, $column1, $operator = null, $column2 = null)
194: {
195: return $this->join($table, $column1, $operator, $column2, 'LEFT');
196: }
197:
198: /**
199: * Reset the where clause to its initial state.
200: *
201: * @return void
202: */
203: public function reset_where()
204: {
205: list($this->wheres, $this->bindings) = array(array(), array());
206: }
207:
208: /**
209: * Add a raw where condition to the query.
210: *
211: * @param string $where
212: * @param array $bindings
213: * @param string $connector
214: * @return Query
215: */
216: public function raw_where($where, $bindings = array(), $connector = 'AND')
217: {
218: $this->wheres[] = array('type' => 'where_raw', 'connector' => $connector, 'sql' => $where);
219:
220: $this->bindings = array_merge($this->bindings, $bindings);
221:
222: return $this;
223: }
224:
225: /**
226: * Add a raw or where condition to the query.
227: *
228: * @param string $where
229: * @param array $bindings
230: * @return Query
231: */
232: public function raw_or_where($where, $bindings = array())
233: {
234: return $this->raw_where($where, $bindings, 'OR');
235: }
236:
237: /**
238: * Add a where condition to the query.
239: *
240: * @param string $column
241: * @param string $operator
242: * @param mixed $value
243: * @param string $connector
244: * @return Query
245: */
246: public function where($column, $operator = null, $value = null, $connector = 'AND')
247: {
248: // If a Closure is passed into the method, it means a nested where
249: // clause is being initiated, so we will take a different course
250: // of action than when the statement is just a simple where.
251: if ($column instanceof Closure)
252: {
253: return $this->where_nested($column, $connector);
254: }
255:
256: $type = 'where';
257:
258: $this->wheres[] = compact('type', 'column', 'operator', 'value', 'connector');
259:
260: $this->bindings[] = $value;
261:
262: return $this;
263: }
264:
265: /**
266: * Add an or where condition to the query.
267: *
268: * @param string $column
269: * @param string $operator
270: * @param mixed $value
271: * @return Query
272: */
273: public function or_where($column, $operator = null, $value = null)
274: {
275: return $this->where($column, $operator, $value, 'OR');
276: }
277:
278: /**
279: * Add an or where condition for the primary key to the query.
280: *
281: * @param mixed $value
282: * @return Query
283: */
284: public function or_where_id($value)
285: {
286: return $this->or_where('id', '=', $value);
287: }
288:
289: /**
290: * Add a where in condition to the query.
291: *
292: * @param string $column
293: * @param array $values
294: * @param string $connector
295: * @param bool $not
296: * @return Query
297: */
298: public function where_in($column, $values, $connector = 'AND', $not = false)
299: {
300: $type = ($not) ? 'where_not_in' : 'where_in';
301:
302: $this->wheres[] = compact('type', 'column', 'values', 'connector');
303:
304: $this->bindings = array_merge($this->bindings, $values);
305:
306: return $this;
307: }
308:
309: /**
310: * Add an or where in condition to the query.
311: *
312: * @param string $column
313: * @param array $values
314: * @return Query
315: */
316: public function or_where_in($column, $values)
317: {
318: return $this->where_in($column, $values, 'OR');
319: }
320:
321: /**
322: * Add a where not in condition to the query.
323: *
324: * @param string $column
325: * @param array $values
326: * @param string $connector
327: * @return Query
328: */
329: public function where_not_in($column, $values, $connector = 'AND')
330: {
331: return $this->where_in($column, $values, $connector, true);
332: }
333:
334: /**
335: * Add an or where not in condition to the query.
336: *
337: * @param string $column
338: * @param array $values
339: * @return Query
340: */
341: public function or_where_not_in($column, $values)
342: {
343: return $this->where_not_in($column, $values, 'OR');
344: }
345:
346: /**
347: * Add a BETWEEN condition to the query
348: *
349: * @param string $column
350: * @param mixed $min
351: * @param mixed $max
352: * @param string $connector
353: * @param boolean $not
354: * @return Query
355: */
356: public function where_between($column, $min, $max, $connector = 'AND', $not = false)
357: {
358: $type = ($not) ? 'where_not_between' : 'where_between';
359:
360: $this->wheres[] = compact('type', 'column', 'min', 'max', 'connector');
361:
362: $this->bindings[] = $min;
363: $this->bindings[] = $max;
364:
365: return $this;
366: }
367:
368: /**
369: * Add a OR BETWEEN condition to the query
370: *
371: * @param string $column
372: * @param mixed $min
373: * @param mixed $max
374: * @return Query
375: */
376: public function or_where_between($column, $min, $max)
377: {
378: return $this->where_between($column, $min, $max, 'OR');
379: }
380:
381: /**
382: * Add a NOT BETWEEN condition to the query
383: *
384: * @param string $column
385: * @param mixed $min
386: * @param mixed $max
387: * @return Query
388: */
389: public function where_not_between($column, $min, $max, $connector = 'AND')
390: {
391: return $this->where_between($column, $min, $max, $connector, true);
392: }
393:
394: /**
395: * Add a OR NOT BETWEEN condition to the query
396: *
397: * @param string $column
398: * @param mixed $min
399: * @param mixed $max
400: * @return Query
401: */
402: public function or_where_not_between($column, $min, $max)
403: {
404: return $this->where_not_between($column, $min, $max, 'OR');
405: }
406:
407: /**
408: * Add a where null condition to the query.
409: *
410: * @param string $column
411: * @param string $connector
412: * @param bool $not
413: * @return Query
414: */
415: public function where_null($column, $connector = 'AND', $not = false)
416: {
417: $type = ($not) ? 'where_not_null' : 'where_null';
418:
419: $this->wheres[] = compact('type', 'column', 'connector');
420:
421: return $this;
422: }
423:
424: /**
425: * Add an or where null condition to the query.
426: *
427: * @param string $column
428: * @return Query
429: */
430: public function or_where_null($column)
431: {
432: return $this->where_null($column, 'OR');
433: }
434:
435: /**
436: * Add a where not null condition to the query.
437: *
438: * @param string $column
439: * @param string $connector
440: * @return Query
441: */
442: public function where_not_null($column, $connector = 'AND')
443: {
444: return $this->where_null($column, $connector, true);
445: }
446:
447: /**
448: * Add an or where not null condition to the query.
449: *
450: * @param string $column
451: * @return Query
452: */
453: public function or_where_not_null($column)
454: {
455: return $this->where_not_null($column, 'OR');
456: }
457:
458: /**
459: * Add a nested where condition to the query.
460: *
461: * @param Closure $callback
462: * @param string $connector
463: * @return Query
464: */
465: public function where_nested($callback, $connector = 'AND')
466: {
467: $type = 'where_nested';
468:
469: // To handle a nested where statement, we will actually instantiate a new
470: // Query instance and run the callback over that instance, which will
471: // allow the developer to have a fresh query instance
472: $query = new Query($this->connection, $this->grammar, $this->from);
473:
474: call_user_func($callback, $query);
475:
476: // Once the callback has been run on the query, we will store the nested
477: // query instance on the where clause array so that it's passed to the
478: // query's query grammar instance when building.
479: if ($query->wheres !== null)
480: {
481: $this->wheres[] = compact('type', 'query', 'connector');
482: }
483:
484: $this->bindings = array_merge($this->bindings, $query->bindings);
485:
486: return $this;
487: }
488:
489: /**
490: * Add dynamic where conditions to the query.
491: *
492: * @param string $method
493: * @param array $parameters
494: * @return Query
495: */
496: private function dynamic_where($method, $parameters)
497: {
498: $finder = substr($method, 6);
499:
500: $flags = PREG_SPLIT_DELIM_CAPTURE;
501:
502: $segments = preg_split('/(_and_|_or_)/i', $finder, -1, $flags);
503:
504: // The connector variable will determine which connector will be used
505: // for the condition. We'll change it as we come across new boolean
506: // connectors in the dynamic method string.
507: //
508: // The index variable helps us get the correct parameter value for
509: // the where condition. We increment it each time we add another
510: // condition to the query's where clause.
511: $connector = 'AND';
512:
513: $index = 0;
514:
515: foreach ($segments as $segment)
516: {
517: // If the segment is not a boolean connector, we can assume it it is
518: // a column name, and we'll add it to the query as a new constraint
519: // of the query's where clause and keep iterating the segments.
520: if ($segment != '_and_' and $segment != '_or_')
521: {
522: $this->where($segment, '=', $parameters[$index], $connector);
523:
524: $index++;
525: }
526: // Otherwise, we will store the connector so we know how the next
527: // where clause we find in the query should be connected to the
528: // previous one and will add it when we find the next one.
529: else
530: {
531: $connector = trim(strtoupper($segment), '_');
532: }
533: }
534:
535: return $this;
536: }
537:
538: /**
539: * Add a grouping to the query.
540: *
541: * @param string $column
542: * @return Query
543: */
544: public function group_by($column)
545: {
546: $this->groupings[] = $column;
547: return $this;
548: }
549:
550: /**
551: * Add a having to the query.
552: *
553: * @param string $column
554: * @param string $operator
555: * @param mixed $value
556: */
557: public function having($column, $operator, $value)
558: {
559: $this->havings[] = compact('column', 'operator', 'value');
560:
561: $this->bindings[] = $value;
562:
563: return $this;
564: }
565:
566: /**
567: * Add an ordering to the query.
568: *
569: * @param string $column
570: * @param string $direction
571: * @return Query
572: */
573: public function order_by($column, $direction = 'asc')
574: {
575: $this->orderings[] = compact('column', 'direction');
576: return $this;
577: }
578:
579: /**
580: * Set the query offset.
581: *
582: * @param int $value
583: * @return Query
584: */
585: public function skip($value)
586: {
587: $this->offset = $value;
588: return $this;
589: }
590:
591: /**
592: * Set the query limit.
593: *
594: * @param int $value
595: * @return Query
596: */
597: public function take($value)
598: {
599: $this->limit = $value;
600: return $this;
601: }
602:
603: /**
604: * Set the query limit and offset for a given page.
605: *
606: * @param int $page
607: * @param int $per_page
608: * @return Query
609: */
610: public function for_page($page, $per_page)
611: {
612: return $this->skip(($page - 1) * $per_page)->take($per_page);
613: }
614:
615: /**
616: * Find a record by the primary key.
617: *
618: * @param int $id
619: * @param array $columns
620: * @return object
621: */
622: public function find($id, $columns = array('*'))
623: {
624: return $this->where('id', '=', $id)->first($columns);
625: }
626:
627: /**
628: * Execute the query as a SELECT statement and return a single column.
629: *
630: * @param string $column
631: * @return mixed
632: */
633: public function only($column)
634: {
635: $sql = $this->grammar->select($this->select(array($column)));
636:
637: return $this->connection->only($sql, $this->bindings);
638: }
639:
640: /**
641: * Execute the query as a SELECT statement and return the first result.
642: *
643: * @param array $columns
644: * @return mixed
645: */
646: public function first($columns = array('*'))
647: {
648: $columns = (array) $columns;
649:
650: // Since we only need the first result, we'll go ahead and set the
651: // limit clause to 1, since this will be much faster than getting
652: // all of the rows and then only returning the first.
653: $results = $this->take(1)->get($columns);
654:
655: return (count($results) > 0) ? $results[0] : null;
656: }
657:
658: /**
659: * Get an array with the values of a given column.
660: *
661: * @param string $column
662: * @param string $key
663: * @return array
664: */
665: public function lists($column, $key = null)
666: {
667: $columns = (is_null($key)) ? array($column) : array($column, $key);
668:
669: $results = $this->get($columns);
670:
671: // First we will get the array of values for the requested column.
672: // Of course, this array will simply have numeric keys. After we
673: // have this array we will determine if we need to key the array
674: // by another column from the result set.
675: $values = array_map(function($row) use ($column)
676: {
677: return $row->$column;
678:
679: }, $results);
680:
681: // If a key was provided, we will extract an array of keys and
682: // set the keys on the array of values using the array_combine
683: // function provided by PHP, which should give us the proper
684: // array form to return from the method.
685: if ( ! is_null($key) && count($results))
686: {
687: return array_combine(array_map(function($row) use ($key)
688: {
689: return $row->$key;
690:
691: }, $results), $values);
692: }
693:
694: return $values;
695: }
696:
697: /**
698: * Execute the query as a SELECT statement.
699: *
700: * @param array $columns
701: * @return array
702: */
703: public function get($columns = array('*'))
704: {
705: if (is_null($this->selects)) $this->select($columns);
706:
707: $sql = $this->grammar->select($this);
708:
709: $results = $this->connection->query($sql, $this->bindings);
710:
711: // If the query has an offset and we are using the SQL Server grammar,
712: // we need to spin through the results and remove the "rownum" from
713: // each of the objects since there is no "offset".
714: if ($this->offset > 0 and $this->grammar instanceof SQLServer)
715: {
716: array_walk($results, function($result)
717: {
718: unset($result->rownum);
719: });
720: }
721:
722: // Reset the SELECT clause so more queries can be performed using
723: // the same instance. This is helpful for getting aggregates and
724: // then getting actual results from the query.
725: $this->selects = null;
726:
727: return $results;
728: }
729:
730: /**
731: * Get an aggregate value.
732: *
733: * @param string $aggregator
734: * @param array $columns
735: * @return mixed
736: */
737: public function aggregate($aggregator, $columns)
738: {
739: // We'll set the aggregate value so the grammar does not try to compile
740: // a SELECT clause on the query. If an aggregator is present, it's own
741: // grammar function will be used to build the SQL syntax.
742: $this->aggregate = compact('aggregator', 'columns');
743:
744: $sql = $this->grammar->select($this);
745:
746: $result = $this->connection->only($sql, $this->bindings);
747:
748: // Reset the aggregate so more queries can be performed using the same
749: // instance. This is helpful for getting aggregates and then getting
750: // actual results from the query such as during paging.
751: $this->aggregate = null;
752:
753: return $result;
754: }
755:
756: /**
757: * Get the paginated query results as a Paginator instance.
758: *
759: * @param int $per_page
760: * @param array $columns
761: * @return Paginator
762: */
763: public function paginate($per_page = 20, $columns = array('*'))
764: {
765: // Because some database engines may throw errors if we leave orderings
766: // on the query when retrieving the total number of records, we'll drop
767: // all of the ordreings and put them back on the query.
768: list($orderings, $this->orderings) = array($this->orderings, null);
769:
770: $total = $this->count(reset($columns));
771:
772: $page = Paginator::page($total, $per_page);
773:
774: $this->orderings = $orderings;
775:
776: // Now we're ready to get the actual pagination results from the table
777: // using the for_page and get methods. The "for_page" method provides
778: // a convenient way to set the paging limit and offset.
779: $results = $this->for_page($page, $per_page)->get($columns);
780:
781: return Paginator::make($results, $total, $per_page);
782: }
783:
784: /**
785: * Insert an array of values into the database table.
786: *
787: * @param array $values
788: * @return bool
789: */
790: public function insert($values)
791: {
792: // Force every insert to be treated like a batch insert to make creating
793: // the binding array simpler since we can just spin through the inserted
794: // rows as if there/ was more than one every time.
795: if ( ! is_array(reset($values))) $values = array($values);
796:
797: $bindings = array();
798:
799: // We need to merge the the insert values into the array of the query
800: // bindings so that they will be bound to the PDO statement when it
801: // is executed by the database connection.
802: foreach ($values as $value)
803: {
804: $bindings = array_merge($bindings, array_values($value));
805: }
806:
807: $sql = $this->grammar->insert($this, $values);
808:
809: return $this->connection->query($sql, $bindings);
810: }
811:
812: /**
813: * Insert an array of values into the database table and return the key.
814: *
815: * @param array $values
816: * @param string $column
817: * @return mixed
818: */
819: public function insert_get_id($values, $column = 'id')
820: {
821: $sql = $this->grammar->insert_get_id($this, $values, $column);
822:
823: $result = $this->connection->query($sql, array_values($values));
824:
825: // If the key is not auto-incrementing, we will just return the inserted value
826: if (isset($values[$column]))
827: {
828: return $values[$column];
829: }
830: else if ($this->grammar instanceof Postgres)
831: {
832: $row = (array) $result[0];
833:
834: return (int) $row[$column];
835: }
836: else
837: {
838: return (int) $this->connection->pdo->lastInsertId();
839: }
840: }
841:
842: /**
843: * Increment the value of a column by a given amount.
844: *
845: * @param string $column
846: * @param int $amount
847: * @return int
848: */
849: public function increment($column, $amount = 1)
850: {
851: return $this->adjust($column, $amount, ' + ');
852: }
853:
854: /**
855: * Decrement the value of a column by a given amount.
856: *
857: * @param string $column
858: * @param int $amount
859: * @return int
860: */
861: public function decrement($column, $amount = 1)
862: {
863: return $this->adjust($column, $amount, ' - ');
864: }
865:
866: /**
867: * Adjust the value of a column up or down by a given amount.
868: *
869: * @param string $column
870: * @param int $amount
871: * @param string $operator
872: * @return int
873: */
874: protected function adjust($column, $amount, $operator)
875: {
876: $wrapped = $this->grammar->wrap($column);
877:
878: // To make the adjustment to the column, we'll wrap the expression in an
879: // Expression instance, which forces the adjustment to be injected into
880: // the query as a string instead of bound.
881: $value = Database::raw($wrapped.$operator.$amount);
882:
883: return $this->update(array($column => $value));
884: }
885:
886: /**
887: * Update an array of values in the database table.
888: *
889: * @param array $values
890: * @return int
891: */
892: public function update($values)
893: {
894: // For update statements, we need to merge the bindings such that the update
895: // values occur before the where bindings in the array since the sets will
896: // precede any of the where clauses in the SQL syntax that is generated.
897: $bindings = array_merge(array_values($values), $this->bindings);
898:
899: $sql = $this->grammar->update($this, $values);
900:
901: return $this->connection->query($sql, $bindings);
902: }
903:
904: /**
905: * Execute the query as a DELETE statement.
906: *
907: * Optionally, an ID may be passed to the method do delete a specific row.
908: *
909: * @param int $id
910: * @return int
911: */
912: public function delete($id = null)
913: {
914: // If an ID is given to the method, we'll set the where clause to
915: // match on the value of the ID. This allows the developer to
916: // quickly delete a row by its primary key value.
917: if ( ! is_null($id))
918: {
919: $this->where('id', '=', $id);
920: }
921:
922: $sql = $this->grammar->delete($this);
923:
924: return $this->connection->query($sql, $this->bindings);
925: }
926:
927: /**
928: * Magic Method for handling dynamic functions.
929: *
930: * This method handles calls to aggregates as well as dynamic where clauses.
931: */
932: public function __call($method, $parameters)
933: {
934: if (strpos($method, 'where_') === 0)
935: {
936: return $this->dynamic_where($method, $parameters, $this);
937: }
938:
939: // All of the aggregate methods are handled by a single method, so we'll
940: // catch them all here and then pass them off to the agregate method
941: // instead of creating methods for each one of them.
942: if (in_array($method, array('count', 'min', 'max', 'avg', 'sum')))
943: {
944: if (count($parameters) == 0) $parameters[0] = '*';
945:
946: return $this->aggregate(strtoupper($method), (array) $parameters[0]);
947: }
948:
949: throw new \Exception("Method [$method] is not defined on the Query class.");
950: }
951:
952: }
953: