1: <?php namespace Laravel\Database\Query\Grammars;
2:
3: use Laravel\Database\Query;
4: use Laravel\Database\Expression;
5:
6: class Grammar extends \Laravel\Database\Grammar {
7:
8: /**
9: * The format for properly saving a DateTime.
10: *
11: * @var string
12: */
13: public $datetime = 'Y-m-d H:i:s';
14:
15: /**
16: * All of the query components in the order they should be built.
17: *
18: * @var array
19: */
20: protected $components = array(
21: 'aggregate', 'selects', 'from', 'joins', 'wheres',
22: 'groupings', 'havings', 'orderings', 'limit', 'offset',
23: );
24:
25: /**
26: * Compile a SQL SELECT statement from a Query instance.
27: *
28: * @param Query $query
29: * @return string
30: */
31: public function select(Query $query)
32: {
33: return $this->concatenate($this->components($query));
34: }
35:
36: /**
37: * Generate the SQL for every component of the query.
38: *
39: * @param Query $query
40: * @return array
41: */
42: final protected function components($query)
43: {
44: // Each portion of the statement is compiled by a function corresponding
45: // to an item in the components array. This lets us to keep the creation
46: // of the query very granular and very flexible.
47: foreach ($this->components as $component)
48: {
49: if ( ! is_null($query->$component))
50: {
51: $sql[$component] = call_user_func(array($this, $component), $query);
52: }
53: }
54:
55: return (array) $sql;
56: }
57:
58: /**
59: * Concatenate an array of SQL segments, removing those that are empty.
60: *
61: * @param array $components
62: * @return string
63: */
64: final protected function concatenate($components)
65: {
66: return implode(' ', array_filter($components, function($value)
67: {
68: return (string) $value !== '';
69: }));
70: }
71:
72: /**
73: * Compile the SELECT clause for a query.
74: *
75: * @param Query $query
76: * @return string
77: */
78: protected function selects(Query $query)
79: {
80: if ( ! is_null($query->aggregate)) return;
81:
82: $select = ($query->distinct) ? 'SELECT DISTINCT ' : 'SELECT ';
83:
84: return $select.$this->columnize($query->selects);
85: }
86:
87: /**
88: * Compile an aggregating SELECT clause for a query.
89: *
90: * @param Query $query
91: * @return string
92: */
93: protected function aggregate(Query $query)
94: {
95: $column = $this->columnize($query->aggregate['columns']);
96:
97: // If the "distinct" flag is set and we're not aggregating everything
98: // we'll set the distinct clause on the query, since this is used
99: // to count all of the distinct values in a column, etc.
100: if ($query->distinct and $column !== '*')
101: {
102: $column = 'DISTINCT '.$column;
103: }
104:
105: return 'SELECT '.$query->aggregate['aggregator'].'('.$column.') AS '.$this->wrap('aggregate');
106: }
107:
108: /**
109: * Compile the FROM clause for a query.
110: *
111: * @param Query $query
112: * @return string
113: */
114: protected function from(Query $query)
115: {
116: return 'FROM '.$this->wrap_table($query->from);
117: }
118:
119: /**
120: * Compile the JOIN clauses for a query.
121: *
122: * @param Query $query
123: * @return string
124: */
125: protected function joins(Query $query)
126: {
127: // We need to iterate through each JOIN clause that is attached to the
128: // query and translate it into SQL. The table and the columns will be
129: // wrapped in identifiers to avoid naming collisions.
130: foreach ($query->joins as $join)
131: {
132: $table = $this->wrap_table($join->table);
133:
134: $clauses = array();
135:
136: // Each JOIN statement may have multiple clauses, so we will iterate
137: // through each clause creating the conditions then we'll join all
138: // of them together at the end to build the clause.
139: foreach ($join->clauses as $clause)
140: {
141: extract($clause);
142:
143: $column1 = $this->wrap($column1);
144:
145: $column2 = $this->wrap($column2);
146:
147: $clauses[] = "{$connector} {$column1} {$operator} {$column2}";
148: }
149:
150: // The first clause will have a connector on the front, but it is
151: // not needed on the first condition, so we will strip it off of
152: // the condition before adding it to the array of joins.
153: $search = array('AND ', 'OR ');
154:
155: $clauses[0] = str_replace($search, '', $clauses[0]);
156:
157: $clauses = implode(' ', $clauses);
158:
159: $sql[] = "{$join->type} JOIN {$table} ON {$clauses}";
160: }
161:
162: // Finally, we should have an array of JOIN clauses that we can
163: // implode together and return as the complete SQL for the
164: // join clause of the query under construction.
165: return implode(' ', $sql);
166: }
167:
168: /**
169: * Compile the WHERE clause for a query.
170: *
171: * @param Query $query
172: * @return string
173: */
174: final protected function wheres(Query $query)
175: {
176: if (is_null($query->wheres)) return '';
177:
178: // Each WHERE clause array has a "type" that is assigned by the query
179: // builder, and each type has its own compiler function. We will call
180: // the appropriate compiler for each where clause.
181: foreach ($query->wheres as $where)
182: {
183: $sql[] = $where['connector'].' '.$this->{$where['type']}($where);
184: }
185:
186: if (isset($sql))
187: {
188: // We attach the boolean connector to every where segment just
189: // for convenience. Once we have built the entire clause we'll
190: // remove the first instance of a connector.
191: return 'WHERE '.preg_replace('/AND |OR /', '', implode(' ', $sql), 1);
192: }
193: }
194:
195: /**
196: * Compile a nested WHERE clause.
197: *
198: * @param array $where
199: * @return string
200: */
201: protected function where_nested($where)
202: {
203: return '('.substr($this->wheres($where['query']), 6).')';
204: }
205:
206: /**
207: * Compile a simple WHERE clause.
208: *
209: * @param array $where
210: * @return string
211: */
212: protected function where($where)
213: {
214: $parameter = $this->parameter($where['value']);
215:
216: return $this->wrap($where['column']).' '.$where['operator'].' '.$parameter;
217: }
218:
219: /**
220: * Compile a WHERE IN clause.
221: *
222: * @param array $where
223: * @return string
224: */
225: protected function where_in($where)
226: {
227: $parameters = $this->parameterize($where['values']);
228:
229: return $this->wrap($where['column']).' IN ('.$parameters.')';
230: }
231:
232: /**
233: * Compile a WHERE NOT IN clause.
234: *
235: * @param array $where
236: * @return string
237: */
238: protected function where_not_in($where)
239: {
240: $parameters = $this->parameterize($where['values']);
241:
242: return $this->wrap($where['column']).' NOT IN ('.$parameters.')';
243: }
244:
245: /**
246: * Compile a WHERE BETWEEN clause
247: *
248: * @param array $where
249: * @return string
250: */
251: protected function where_between($where)
252: {
253: $min = $this->parameter($where['min']);
254: $max = $this->parameter($where['max']);
255:
256: return $this->wrap($where['column']).' BETWEEN '.$min.' AND '.$max;
257: }
258:
259: /**
260: * Compile a WHERE NOT BETWEEN clause
261: * @param array $where
262: * @return string
263: */
264: protected function where_not_between($where)
265: {
266: $min = $this->parameter($where['min']);
267: $max = $this->parameter($where['max']);
268:
269: return $this->wrap($where['column']).' NOT BETWEEN '.$min.' AND '.$max;
270: }
271:
272: /**
273: * Compile a WHERE NULL clause.
274: *
275: * @param array $where
276: * @return string
277: */
278: protected function where_null($where)
279: {
280: return $this->wrap($where['column']).' IS NULL';
281: }
282:
283: /**
284: * Compile a WHERE NULL clause.
285: *
286: * @param array $where
287: * @return string
288: */
289: protected function where_not_null($where)
290: {
291: return $this->wrap($where['column']).' IS NOT NULL';
292: }
293:
294: /**
295: * Compile a raw WHERE clause.
296: *
297: * @param array $where
298: * @return string
299: */
300: final protected function where_raw($where)
301: {
302: return $where['sql'];
303: }
304:
305: /**
306: * Compile the GROUP BY clause for a query.
307: *
308: * @param Query $query
309: * @return string
310: */
311: protected function groupings(Query $query)
312: {
313: return 'GROUP BY '.$this->columnize($query->groupings);
314: }
315:
316: /**
317: * Compile the HAVING clause for a query.
318: *
319: * @param Query $query
320: * @return string
321: */
322: protected function havings(Query $query)
323: {
324: if (is_null($query->havings)) return '';
325:
326: foreach ($query->havings as $having)
327: {
328: $sql[] = 'AND '.$this->wrap($having['column']).' '.$having['operator'].' '.$this->parameter($having['value']);
329: }
330:
331: return 'HAVING '.preg_replace('/AND /', '', implode(' ', $sql), 1);
332: }
333:
334: /**
335: * Compile the ORDER BY clause for a query.
336: *
337: * @param Query $query
338: * @return string
339: */
340: protected function orderings(Query $query)
341: {
342: foreach ($query->orderings as $ordering)
343: {
344: $sql[] = $this->wrap($ordering['column']).' '.strtoupper($ordering['direction']);
345: }
346:
347: return 'ORDER BY '.implode(', ', $sql);
348: }
349:
350: /**
351: * Compile the LIMIT clause for a query.
352: *
353: * @param Query $query
354: * @return string
355: */
356: protected function limit(Query $query)
357: {
358: return 'LIMIT '.$query->limit;
359: }
360:
361: /**
362: * Compile the OFFSET clause for a query.
363: *
364: * @param Query $query
365: * @return string
366: */
367: protected function offset(Query $query)
368: {
369: return 'OFFSET '.$query->offset;
370: }
371:
372: /**
373: * Compile a SQL INSERT statement from a Query instance.
374: *
375: * This method handles the compilation of single row inserts and batch inserts.
376: *
377: * @param Query $query
378: * @param array $values
379: * @return string
380: */
381: public function insert(Query $query, $values)
382: {
383: $table = $this->wrap_table($query->from);
384:
385: // Force every insert to be treated like a batch insert. This simply makes
386: // creating the SQL syntax a little easier on us since we can always treat
387: // the values as if it contains multiple inserts.
388: if ( ! is_array(reset($values))) $values = array($values);
389:
390: // Since we only care about the column names, we can pass any of the insert
391: // arrays into the "columnize" method. The columns should be the same for
392: // every record inserted into the table.
393: $columns = $this->columnize(array_keys(reset($values)));
394:
395: // Build the list of parameter place-holders of values bound to the query.
396: // Each insert should have the same number of bound parameters, so we can
397: // just use the first array of values.
398: $parameters = $this->parameterize(reset($values));
399:
400: $parameters = implode(', ', array_fill(0, count($values), "($parameters)"));
401:
402: return "INSERT INTO {$table} ({$columns}) VALUES {$parameters}";
403: }
404:
405: /**
406: * Compile a SQL INSERT and get ID statement from a Query instance.
407: *
408: * @param Query $query
409: * @param array $values
410: * @param string $column
411: * @return string
412: */
413: public function insert_get_id(Query $query, $values, $column)
414: {
415: return $this->insert($query, $values);
416: }
417:
418: /**
419: * Compile a SQL UPDATE statement from a Query instance.
420: *
421: * @param Query $query
422: * @param array $values
423: * @return string
424: */
425: public function update(Query $query, $values)
426: {
427: $table = $this->wrap_table($query->from);
428:
429: // Each column in the UPDATE statement needs to be wrapped in the keyword
430: // identifiers, and a place-holder needs to be created for each value in
431: // the array of bindings, so we'll build the sets first.
432: foreach ($values as $column => $value)
433: {
434: $columns[] = $this->wrap($column).' = '.$this->parameter($value);
435: }
436:
437: $columns = implode(', ', $columns);
438:
439: // UPDATE statements may be constrained by a WHERE clause, so we'll run
440: // the entire where compilation process for those constraints. This is
441: // easily achieved by passing it to the "wheres" method.
442: return trim("UPDATE {$table} SET {$columns} ".$this->wheres($query));
443: }
444:
445: /**
446: * Compile a SQL DELETE statement from a Query instance.
447: *
448: * @param Query $query
449: * @return string
450: */
451: public function delete(Query $query)
452: {
453: $table = $this->wrap_table($query->from);
454:
455: return trim("DELETE FROM {$table} ".$this->wheres($query));
456: }
457:
458: /**
459: * Transform an SQL short-cuts into real SQL for PDO.
460: *
461: * @param string $sql
462: * @param array $bindings
463: * @return string
464: */
465: public function shortcut($sql, &$bindings)
466: {
467: // Laravel provides an easy short-cut notation for writing raw WHERE IN
468: // statements. If (...) is in the query, it will be replaced with the
469: // correct number of parameters based on the query bindings.
470: if (strpos($sql, '(...)') !== false)
471: {
472: for ($i = 0; $i < count($bindings); $i++)
473: {
474: // If the binding is an array, we can just assume it's used to fill a
475: // where in condition, so we'll just replace the next place-holder
476: // in the query with the constraint and splice the bindings.
477: if (is_array($bindings[$i]))
478: {
479: $parameters = $this->parameterize($bindings[$i]);
480:
481: array_splice($bindings, $i, 1, $bindings[$i]);
482:
483: $sql = preg_replace('~\(\.\.\.\)~', "({$parameters})", $sql, 1);
484: }
485: }
486: }
487:
488: return trim($sql);
489: }
490:
491: }