1: <?php
2: /**
3: * Zend Framework
4: *
5: * LICENSE
6: *
7: * This source file is subject to the new BSD license that is bundled
8: * with this package in the file LICENSE.txt.
9: * It is also available through the world-wide-web at this URL:
10: * http://framework.zend.com/license/new-bsd
11: * If you did not receive a copy of the license and are unable to
12: * obtain it through the world-wide-web, please send an email
13: * to license@zend.com so we can send you a copy immediately.
14: *
15: * @category Zend
16: * @package Zend_Db
17: * @subpackage Select
18: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
19: * @license http://framework.zend.com/license/new-bsd New BSD License
20: * @version $Id: Select.php 23516 2010-12-16 13:26:11Z bittarman $
21: */
22:
23:
24: /**
25: * @see Zend_Db_Adapter_Abstract
26: */
27: require_once 'Zend/Db/Adapter/Abstract.php';
28:
29: /**
30: * @see Zend_Db_Expr
31: */
32: require_once 'Zend/Db/Expr.php';
33:
34:
35: /**
36: * Class for SQL SELECT generation and results.
37: *
38: * @category Zend
39: * @package Zend_Db
40: * @subpackage Select
41: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
42: * @license http://framework.zend.com/license/new-bsd New BSD License
43: */
44: class Zend_Db_Select
45: {
46:
47: const DISTINCT = 'distinct';
48: const COLUMNS = 'columns';
49: const FROM = 'from';
50: const UNION = 'union';
51: const WHERE = 'where';
52: const GROUP = 'group';
53: const HAVING = 'having';
54: const ORDER = 'order';
55: const LIMIT_COUNT = 'limitcount';
56: const LIMIT_OFFSET = 'limitoffset';
57: const FOR_UPDATE = 'forupdate';
58:
59: const INNER_JOIN = 'inner join';
60: const LEFT_JOIN = 'left join';
61: const RIGHT_JOIN = 'right join';
62: const FULL_JOIN = 'full join';
63: const CROSS_JOIN = 'cross join';
64: const NATURAL_JOIN = 'natural join';
65:
66: const SQL_WILDCARD = '*';
67: const SQL_SELECT = 'SELECT';
68: const SQL_UNION = 'UNION';
69: const SQL_UNION_ALL = 'UNION ALL';
70: const SQL_FROM = 'FROM';
71: const SQL_WHERE = 'WHERE';
72: const SQL_DISTINCT = 'DISTINCT';
73: const SQL_GROUP_BY = 'GROUP BY';
74: const SQL_ORDER_BY = 'ORDER BY';
75: const SQL_HAVING = 'HAVING';
76: const SQL_FOR_UPDATE = 'FOR UPDATE';
77: const SQL_AND = 'AND';
78: const SQL_AS = 'AS';
79: const SQL_OR = 'OR';
80: const SQL_ON = 'ON';
81: const SQL_ASC = 'ASC';
82: const SQL_DESC = 'DESC';
83:
84: /**
85: * Bind variables for query
86: *
87: * @var array
88: */
89: protected $_bind = array();
90:
91: /**
92: * Zend_Db_Adapter_Abstract object.
93: *
94: * @var Zend_Db_Adapter_Abstract
95: */
96: protected $_adapter;
97:
98: /**
99: * The initial values for the $_parts array.
100: * NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure
101: * meximum compatibility with database adapters.
102: *
103: * @var array
104: */
105: protected static $_partsInit = array(
106: self::DISTINCT => false,
107: self::COLUMNS => array(),
108: self::UNION => array(),
109: self::FROM => array(),
110: self::WHERE => array(),
111: self::GROUP => array(),
112: self::HAVING => array(),
113: self::ORDER => array(),
114: self::LIMIT_COUNT => null,
115: self::LIMIT_OFFSET => null,
116: self::FOR_UPDATE => false
117: );
118:
119: /**
120: * Specify legal join types.
121: *
122: * @var array
123: */
124: protected static $_joinTypes = array(
125: self::INNER_JOIN,
126: self::LEFT_JOIN,
127: self::RIGHT_JOIN,
128: self::FULL_JOIN,
129: self::CROSS_JOIN,
130: self::NATURAL_JOIN,
131: );
132:
133: /**
134: * Specify legal union types.
135: *
136: * @var array
137: */
138: protected static $_unionTypes = array(
139: self::SQL_UNION,
140: self::SQL_UNION_ALL
141: );
142:
143: /**
144: * The component parts of a SELECT statement.
145: * Initialized to the $_partsInit array in the constructor.
146: *
147: * @var array
148: */
149: protected $_parts = array();
150:
151: /**
152: * Tracks which columns are being select from each table and join.
153: *
154: * @var array
155: */
156: protected $_tableCols = array();
157:
158: /**
159: * Class constructor
160: *
161: * @param Zend_Db_Adapter_Abstract $adapter
162: */
163: public function __construct(Zend_Db_Adapter_Abstract $adapter)
164: {
165: $this->_adapter = $adapter;
166: $this->_parts = self::$_partsInit;
167: }
168:
169: /**
170: * Get bind variables
171: *
172: * @return array
173: */
174: public function getBind()
175: {
176: return $this->_bind;
177: }
178:
179: /**
180: * Set bind variables
181: *
182: * @param mixed $bind
183: * @return Zend_Db_Select
184: */
185: public function bind($bind)
186: {
187: $this->_bind = $bind;
188:
189: return $this;
190: }
191:
192: /**
193: * Makes the query SELECT DISTINCT.
194: *
195: * @param bool $flag Whether or not the SELECT is DISTINCT (default true).
196: * @return Zend_Db_Select This Zend_Db_Select object.
197: */
198: public function distinct($flag = true)
199: {
200: $this->_parts[self::DISTINCT] = (bool) $flag;
201: return $this;
202: }
203:
204: /**
205: * Adds a FROM table and optional columns to the query.
206: *
207: * The first parameter $name can be a simple string, in which case the
208: * correlation name is generated automatically. If you want to specify
209: * the correlation name, the first parameter must be an associative
210: * array in which the key is the correlation name, and the value is
211: * the physical table name. For example, array('alias' => 'table').
212: * The correlation name is prepended to all columns fetched for this
213: * table.
214: *
215: * The second parameter can be a single string or Zend_Db_Expr object,
216: * or else an array of strings or Zend_Db_Expr objects.
217: *
218: * The first parameter can be null or an empty string, in which case
219: * no correlation name is generated or prepended to the columns named
220: * in the second parameter.
221: *
222: * @param array|string|Zend_Db_Expr $name The table name or an associative array
223: * relating correlation name to table name.
224: * @param array|string|Zend_Db_Expr $cols The columns to select from this table.
225: * @param string $schema The schema name to specify, if any.
226: * @return Zend_Db_Select This Zend_Db_Select object.
227: */
228: public function from($name, $cols = '*', $schema = null)
229: {
230: return $this->_join(self::FROM, $name, null, $cols, $schema);
231: }
232:
233: /**
234: * Specifies the columns used in the FROM clause.
235: *
236: * The parameter can be a single string or Zend_Db_Expr object,
237: * or else an array of strings or Zend_Db_Expr objects.
238: *
239: * @param array|string|Zend_Db_Expr $cols The columns to select from this table.
240: * @param string $correlationName Correlation name of target table. OPTIONAL
241: * @return Zend_Db_Select This Zend_Db_Select object.
242: */
243: public function columns($cols = '*', $correlationName = null)
244: {
245: if ($correlationName === null && count($this->_parts[self::FROM])) {
246: $correlationNameKeys = array_keys($this->_parts[self::FROM]);
247: $correlationName = current($correlationNameKeys);
248: }
249:
250: if (!array_key_exists($correlationName, $this->_parts[self::FROM])) {
251: /**
252: * @see Zend_Db_Select_Exception
253: */
254: require_once 'Zend/Db/Select/Exception.php';
255: throw new Zend_Db_Select_Exception("No table has been specified for the FROM clause");
256: }
257:
258: $this->_tableCols($correlationName, $cols);
259:
260: return $this;
261: }
262:
263: /**
264: * Adds a UNION clause to the query.
265: *
266: * The first parameter has to be an array of Zend_Db_Select or
267: * sql query strings.
268: *
269: * <code>
270: * $sql1 = $db->select();
271: * $sql2 = "SELECT ...";
272: * $select = $db->select()
273: * ->union(array($sql1, $sql2))
274: * ->order("id");
275: * </code>
276: *
277: * @param array $select Array of select clauses for the union.
278: * @return Zend_Db_Select This Zend_Db_Select object.
279: */
280: public function union($select = array(), $type = self::SQL_UNION)
281: {
282: if (!is_array($select)) {
283: require_once 'Zend/Db/Select/Exception.php';
284: throw new Zend_Db_Select_Exception(
285: "union() only accepts an array of Zend_Db_Select instances of sql query strings."
286: );
287: }
288:
289: if (!in_array($type, self::$_unionTypes)) {
290: require_once 'Zend/Db/Select/Exception.php';
291: throw new Zend_Db_Select_Exception("Invalid union type '{$type}'");
292: }
293:
294: foreach ($select as $target) {
295: $this->_parts[self::UNION][] = array($target, $type);
296: }
297:
298: return $this;
299: }
300:
301: /**
302: * Adds a JOIN table and columns to the query.
303: *
304: * The $name and $cols parameters follow the same logic
305: * as described in the from() method.
306: *
307: * @param array|string|Zend_Db_Expr $name The table name.
308: * @param string $cond Join on this condition.
309: * @param array|string $cols The columns to select from the joined table.
310: * @param string $schema The database name to specify, if any.
311: * @return Zend_Db_Select This Zend_Db_Select object.
312: */
313: public function join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
314: {
315: return $this->joinInner($name, $cond, $cols, $schema);
316: }
317:
318: /**
319: * Add an INNER JOIN table and colums to the query
320: * Rows in both tables are matched according to the expression
321: * in the $cond argument. The result set is comprised
322: * of all cases where rows from the left table match
323: * rows from the right table.
324: *
325: * The $name and $cols parameters follow the same logic
326: * as described in the from() method.
327: *
328: * @param array|string|Zend_Db_Expr $name The table name.
329: * @param string $cond Join on this condition.
330: * @param array|string $cols The columns to select from the joined table.
331: * @param string $schema The database name to specify, if any.
332: * @return Zend_Db_Select This Zend_Db_Select object.
333: */
334: public function joinInner($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
335: {
336: return $this->_join(self::INNER_JOIN, $name, $cond, $cols, $schema);
337: }
338:
339: /**
340: * Add a LEFT OUTER JOIN table and colums to the query
341: * All rows from the left operand table are included,
342: * matching rows from the right operand table included,
343: * and the columns from the right operand table are filled
344: * with NULLs if no row exists matching the left table.
345: *
346: * The $name and $cols parameters follow the same logic
347: * as described in the from() method.
348: *
349: * @param array|string|Zend_Db_Expr $name The table name.
350: * @param string $cond Join on this condition.
351: * @param array|string $cols The columns to select from the joined table.
352: * @param string $schema The database name to specify, if any.
353: * @return Zend_Db_Select This Zend_Db_Select object.
354: */
355: public function joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
356: {
357: return $this->_join(self::LEFT_JOIN, $name, $cond, $cols, $schema);
358: }
359:
360: /**
361: * Add a RIGHT OUTER JOIN table and colums to the query.
362: * Right outer join is the complement of left outer join.
363: * All rows from the right operand table are included,
364: * matching rows from the left operand table included,
365: * and the columns from the left operand table are filled
366: * with NULLs if no row exists matching the right table.
367: *
368: * The $name and $cols parameters follow the same logic
369: * as described in the from() method.
370: *
371: * @param array|string|Zend_Db_Expr $name The table name.
372: * @param string $cond Join on this condition.
373: * @param array|string $cols The columns to select from the joined table.
374: * @param string $schema The database name to specify, if any.
375: * @return Zend_Db_Select This Zend_Db_Select object.
376: */
377: public function joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
378: {
379: return $this->_join(self::RIGHT_JOIN, $name, $cond, $cols, $schema);
380: }
381:
382: /**
383: * Add a FULL OUTER JOIN table and colums to the query.
384: * A full outer join is like combining a left outer join
385: * and a right outer join. All rows from both tables are
386: * included, paired with each other on the same row of the
387: * result set if they satisfy the join condition, and otherwise
388: * paired with NULLs in place of columns from the other table.
389: *
390: * The $name and $cols parameters follow the same logic
391: * as described in the from() method.
392: *
393: * @param array|string|Zend_Db_Expr $name The table name.
394: * @param string $cond Join on this condition.
395: * @param array|string $cols The columns to select from the joined table.
396: * @param string $schema The database name to specify, if any.
397: * @return Zend_Db_Select This Zend_Db_Select object.
398: */
399: public function joinFull($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
400: {
401: return $this->_join(self::FULL_JOIN, $name, $cond, $cols, $schema);
402: }
403:
404: /**
405: * Add a CROSS JOIN table and colums to the query.
406: * A cross join is a cartesian product; there is no join condition.
407: *
408: * The $name and $cols parameters follow the same logic
409: * as described in the from() method.
410: *
411: * @param array|string|Zend_Db_Expr $name The table name.
412: * @param array|string $cols The columns to select from the joined table.
413: * @param string $schema The database name to specify, if any.
414: * @return Zend_Db_Select This Zend_Db_Select object.
415: */
416: public function joinCross($name, $cols = self::SQL_WILDCARD, $schema = null)
417: {
418: return $this->_join(self::CROSS_JOIN, $name, null, $cols, $schema);
419: }
420:
421: /**
422: * Add a NATURAL JOIN table and colums to the query.
423: * A natural join assumes an equi-join across any column(s)
424: * that appear with the same name in both tables.
425: * Only natural inner joins are supported by this API,
426: * even though SQL permits natural outer joins as well.
427: *
428: * The $name and $cols parameters follow the same logic
429: * as described in the from() method.
430: *
431: * @param array|string|Zend_Db_Expr $name The table name.
432: * @param array|string $cols The columns to select from the joined table.
433: * @param string $schema The database name to specify, if any.
434: * @return Zend_Db_Select This Zend_Db_Select object.
435: */
436: public function joinNatural($name, $cols = self::SQL_WILDCARD, $schema = null)
437: {
438: return $this->_join(self::NATURAL_JOIN, $name, null, $cols, $schema);
439: }
440:
441: /**
442: * Adds a WHERE condition to the query by AND.
443: *
444: * If a value is passed as the second param, it will be quoted
445: * and replaced into the condition wherever a question-mark
446: * appears. Array values are quoted and comma-separated.
447: *
448: * <code>
449: * // simplest but non-secure
450: * $select->where("id = $id");
451: *
452: * // secure (ID is quoted but matched anyway)
453: * $select->where('id = ?', $id);
454: *
455: * // alternatively, with named binding
456: * $select->where('id = :id');
457: * </code>
458: *
459: * Note that it is more correct to use named bindings in your
460: * queries for values other than strings. When you use named
461: * bindings, don't forget to pass the values when actually
462: * making a query:
463: *
464: * <code>
465: * $db->fetchAll($select, array('id' => 5));
466: * </code>
467: *
468: * @param string $cond The WHERE condition.
469: * @param mixed $value OPTIONAL The value to quote into the condition.
470: * @param int $type OPTIONAL The type of the given value
471: * @return Zend_Db_Select This Zend_Db_Select object.
472: */
473: public function where($cond, $value = null, $type = null)
474: {
475: $this->_parts[self::WHERE][] = $this->_where($cond, $value, $type, true);
476:
477: return $this;
478: }
479:
480: /**
481: * Adds a WHERE condition to the query by OR.
482: *
483: * Otherwise identical to where().
484: *
485: * @param string $cond The WHERE condition.
486: * @param mixed $value OPTIONAL The value to quote into the condition.
487: * @param int $type OPTIONAL The type of the given value
488: * @return Zend_Db_Select This Zend_Db_Select object.
489: *
490: * @see where()
491: */
492: public function orWhere($cond, $value = null, $type = null)
493: {
494: $this->_parts[self::WHERE][] = $this->_where($cond, $value, $type, false);
495:
496: return $this;
497: }
498:
499: /**
500: * Adds grouping to the query.
501: *
502: * @param array|string $spec The column(s) to group by.
503: * @return Zend_Db_Select This Zend_Db_Select object.
504: */
505: public function group($spec)
506: {
507: if (!is_array($spec)) {
508: $spec = array($spec);
509: }
510:
511: foreach ($spec as $val) {
512: if (preg_match('/\(.*\)/', (string) $val)) {
513: $val = new Zend_Db_Expr($val);
514: }
515: $this->_parts[self::GROUP][] = $val;
516: }
517:
518: return $this;
519: }
520:
521: /**
522: * Adds a HAVING condition to the query by AND.
523: *
524: * If a value is passed as the second param, it will be quoted
525: * and replaced into the condition wherever a question-mark
526: * appears. See {@link where()} for an example
527: *
528: * @param string $cond The HAVING condition.
529: * @param mixed $value OPTIONAL The value to quote into the condition.
530: * @param int $type OPTIONAL The type of the given value
531: * @return Zend_Db_Select This Zend_Db_Select object.
532: */
533: public function having($cond, $value = null, $type = null)
534: {
535: if ($value !== null) {
536: $cond = $this->_adapter->quoteInto($cond, $value, $type);
537: }
538:
539: if ($this->_parts[self::HAVING]) {
540: $this->_parts[self::HAVING][] = self::SQL_AND . " ($cond)";
541: } else {
542: $this->_parts[self::HAVING][] = "($cond)";
543: }
544:
545: return $this;
546: }
547:
548: /**
549: * Adds a HAVING condition to the query by OR.
550: *
551: * Otherwise identical to orHaving().
552: *
553: * @param string $cond The HAVING condition.
554: * @param mixed $value OPTIONAL The value to quote into the condition.
555: * @param int $type OPTIONAL The type of the given value
556: * @return Zend_Db_Select This Zend_Db_Select object.
557: *
558: * @see having()
559: */
560: public function orHaving($cond, $value = null, $type = null)
561: {
562: if ($value !== null) {
563: $cond = $this->_adapter->quoteInto($cond, $value, $type);
564: }
565:
566: if ($this->_parts[self::HAVING]) {
567: $this->_parts[self::HAVING][] = self::SQL_OR . " ($cond)";
568: } else {
569: $this->_parts[self::HAVING][] = "($cond)";
570: }
571:
572: return $this;
573: }
574:
575: /**
576: * Adds a row order to the query.
577: *
578: * @param mixed $spec The column(s) and direction to order by.
579: * @return Zend_Db_Select This Zend_Db_Select object.
580: */
581: public function order($spec)
582: {
583: if (!is_array($spec)) {
584: $spec = array($spec);
585: }
586:
587: // force 'ASC' or 'DESC' on each order spec, default is ASC.
588: foreach ($spec as $val) {
589: if ($val instanceof Zend_Db_Expr) {
590: $expr = $val->__toString();
591: if (empty($expr)) {
592: continue;
593: }
594: $this->_parts[self::ORDER][] = $val;
595: } else {
596: if (empty($val)) {
597: continue;
598: }
599: $direction = self::SQL_ASC;
600: if (preg_match('/(.*\W)(' . self::SQL_ASC . '|' . self::SQL_DESC . ')\b/si', $val, $matches)) {
601: $val = trim($matches[1]);
602: $direction = $matches[2];
603: }
604: if (preg_match('/\(.*\)/', $val)) {
605: $val = new Zend_Db_Expr($val);
606: }
607: $this->_parts[self::ORDER][] = array($val, $direction);
608: }
609: }
610:
611: return $this;
612: }
613:
614: /**
615: * Sets a limit count and offset to the query.
616: *
617: * @param int $count OPTIONAL The number of rows to return.
618: * @param int $offset OPTIONAL Start returning after this many rows.
619: * @return Zend_Db_Select This Zend_Db_Select object.
620: */
621: public function limit($count = null, $offset = null)
622: {
623: $this->_parts[self::LIMIT_COUNT] = (int) $count;
624: $this->_parts[self::LIMIT_OFFSET] = (int) $offset;
625: return $this;
626: }
627:
628: /**
629: * Sets the limit and count by page number.
630: *
631: * @param int $page Limit results to this page number.
632: * @param int $rowCount Use this many rows per page.
633: * @return Zend_Db_Select This Zend_Db_Select object.
634: */
635: public function limitPage($page, $rowCount)
636: {
637: $page = ($page > 0) ? $page : 1;
638: $rowCount = ($rowCount > 0) ? $rowCount : 1;
639: $this->_parts[self::LIMIT_COUNT] = (int) $rowCount;
640: $this->_parts[self::LIMIT_OFFSET] = (int) $rowCount * ($page - 1);
641: return $this;
642: }
643:
644: /**
645: * Makes the query SELECT FOR UPDATE.
646: *
647: * @param bool $flag Whether or not the SELECT is FOR UPDATE (default true).
648: * @return Zend_Db_Select This Zend_Db_Select object.
649: */
650: public function forUpdate($flag = true)
651: {
652: $this->_parts[self::FOR_UPDATE] = (bool) $flag;
653: return $this;
654: }
655:
656: /**
657: * Get part of the structured information for the currect query.
658: *
659: * @param string $part
660: * @return mixed
661: * @throws Zend_Db_Select_Exception
662: */
663: public function getPart($part)
664: {
665: $part = strtolower($part);
666: if (!array_key_exists($part, $this->_parts)) {
667: require_once 'Zend/Db/Select/Exception.php';
668: throw new Zend_Db_Select_Exception("Invalid Select part '$part'");
669: }
670: return $this->_parts[$part];
671: }
672:
673: /**
674: * Executes the current select object and returns the result
675: *
676: * @param integer $fetchMode OPTIONAL
677: * @param mixed $bind An array of data to bind to the placeholders.
678: * @return PDO_Statement|Zend_Db_Statement
679: */
680: public function query($fetchMode = null, $bind = array())
681: {
682: if (!empty($bind)) {
683: $this->bind($bind);
684: }
685:
686: $stmt = $this->_adapter->query($this);
687: if ($fetchMode == null) {
688: $fetchMode = $this->_adapter->getFetchMode();
689: }
690: $stmt->setFetchMode($fetchMode);
691: return $stmt;
692: }
693:
694: /**
695: * Converts this object to an SQL SELECT string.
696: *
697: * @return string|null This object as a SELECT string. (or null if a string cannot be produced.)
698: */
699: public function assemble()
700: {
701: $sql = self::SQL_SELECT;
702: foreach (array_keys(self::$_partsInit) as $part) {
703: $method = '_render' . ucfirst($part);
704: if (method_exists($this, $method)) {
705: $sql = $this->$method($sql);
706: }
707: }
708: return $sql;
709: }
710:
711: /**
712: * Clear parts of the Select object, or an individual part.
713: *
714: * @param string $part OPTIONAL
715: * @return Zend_Db_Select
716: */
717: public function reset($part = null)
718: {
719: if ($part == null) {
720: $this->_parts = self::$_partsInit;
721: } else if (array_key_exists($part, self::$_partsInit)) {
722: $this->_parts[$part] = self::$_partsInit[$part];
723: }
724: return $this;
725: }
726:
727: /**
728: * Gets the Zend_Db_Adapter_Abstract for this
729: * particular Zend_Db_Select object.
730: *
731: * @return Zend_Db_Adapter_Abstract
732: */
733: public function getAdapter()
734: {
735: return $this->_adapter;
736: }
737:
738: /**
739: * Populate the {@link $_parts} 'join' key
740: *
741: * Does the dirty work of populating the join key.
742: *
743: * The $name and $cols parameters follow the same logic
744: * as described in the from() method.
745: *
746: * @param null|string $type Type of join; inner, left, and null are currently supported
747: * @param array|string|Zend_Db_Expr $name Table name
748: * @param string $cond Join on this condition
749: * @param array|string $cols The columns to select from the joined table
750: * @param string $schema The database name to specify, if any.
751: * @return Zend_Db_Select This Zend_Db_Select object
752: * @throws Zend_Db_Select_Exception
753: */
754: protected function _join($type, $name, $cond, $cols, $schema = null)
755: {
756: if (!in_array($type, self::$_joinTypes) && $type != self::FROM) {
757: /**
758: * @see Zend_Db_Select_Exception
759: */
760: require_once 'Zend/Db/Select/Exception.php';
761: throw new Zend_Db_Select_Exception("Invalid join type '$type'");
762: }
763:
764: if (count($this->_parts[self::UNION])) {
765: require_once 'Zend/Db/Select/Exception.php';
766: throw new Zend_Db_Select_Exception("Invalid use of table with " . self::SQL_UNION);
767: }
768:
769: if (empty($name)) {
770: $correlationName = $tableName = '';
771: } else if (is_array($name)) {
772: // Must be array($correlationName => $tableName) or array($ident, ...)
773: foreach ($name as $_correlationName => $_tableName) {
774: if (is_string($_correlationName)) {
775: // We assume the key is the correlation name and value is the table name
776: $tableName = $_tableName;
777: $correlationName = $_correlationName;
778: } else {
779: // We assume just an array of identifiers, with no correlation name
780: $tableName = $_tableName;
781: $correlationName = $this->_uniqueCorrelation($tableName);
782: }
783: break;
784: }
785: } else if ($name instanceof Zend_Db_Expr|| $name instanceof Zend_Db_Select) {
786: $tableName = $name;
787: $correlationName = $this->_uniqueCorrelation('t');
788: } else if (preg_match('/^(.+)\s+AS\s+(.+)$/i', $name, $m)) {
789: $tableName = $m[1];
790: $correlationName = $m[2];
791: } else {
792: $tableName = $name;
793: $correlationName = $this->_uniqueCorrelation($tableName);
794: }
795:
796: // Schema from table name overrides schema argument
797: if (!is_object($tableName) && false !== strpos($tableName, '.')) {
798: list($schema, $tableName) = explode('.', $tableName);
799: }
800:
801: $lastFromCorrelationName = null;
802: if (!empty($correlationName)) {
803: if (array_key_exists($correlationName, $this->_parts[self::FROM])) {
804: /**
805: * @see Zend_Db_Select_Exception
806: */
807: require_once 'Zend/Db/Select/Exception.php';
808: throw new Zend_Db_Select_Exception("You cannot define a correlation name '$correlationName' more than once");
809: }
810:
811: if ($type == self::FROM) {
812: // append this from after the last from joinType
813: $tmpFromParts = $this->_parts[self::FROM];
814: $this->_parts[self::FROM] = array();
815: // move all the froms onto the stack
816: while ($tmpFromParts) {
817: $currentCorrelationName = key($tmpFromParts);
818: if ($tmpFromParts[$currentCorrelationName]['joinType'] != self::FROM) {
819: break;
820: }
821: $lastFromCorrelationName = $currentCorrelationName;
822: $this->_parts[self::FROM][$currentCorrelationName] = array_shift($tmpFromParts);
823: }
824: } else {
825: $tmpFromParts = array();
826: }
827: $this->_parts[self::FROM][$correlationName] = array(
828: 'joinType' => $type,
829: 'schema' => $schema,
830: 'tableName' => $tableName,
831: 'joinCondition' => $cond
832: );
833: while ($tmpFromParts) {
834: $currentCorrelationName = key($tmpFromParts);
835: $this->_parts[self::FROM][$currentCorrelationName] = array_shift($tmpFromParts);
836: }
837: }
838:
839: // add to the columns from this joined table
840: if ($type == self::FROM && $lastFromCorrelationName == null) {
841: $lastFromCorrelationName = true;
842: }
843: $this->_tableCols($correlationName, $cols, $lastFromCorrelationName);
844:
845: return $this;
846: }
847:
848: /**
849: * Handle JOIN... USING... syntax
850: *
851: * This is functionality identical to the existing JOIN methods, however
852: * the join condition can be passed as a single column name. This method
853: * then completes the ON condition by using the same field for the FROM
854: * table and the JOIN table.
855: *
856: * <code>
857: * $select = $db->select()->from('table1')
858: * ->joinUsing('table2', 'column1');
859: *
860: * // SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2
861: * </code>
862: *
863: * These joins are called by the developer simply by adding 'Using' to the
864: * method name. E.g.
865: * * joinUsing
866: * * joinInnerUsing
867: * * joinFullUsing
868: * * joinRightUsing
869: * * joinLeftUsing
870: *
871: * @return Zend_Db_Select This Zend_Db_Select object.
872: */
873: public function _joinUsing($type, $name, $cond, $cols = '*', $schema = null)
874: {
875: if (empty($this->_parts[self::FROM])) {
876: require_once 'Zend/Db/Select/Exception.php';
877: throw new Zend_Db_Select_Exception("You can only perform a joinUsing after specifying a FROM table");
878: }
879:
880: $join = $this->_adapter->quoteIdentifier(key($this->_parts[self::FROM]), true);
881: $from = $this->_adapter->quoteIdentifier($this->_uniqueCorrelation($name), true);
882:
883: $cond1 = $from . '.' . $cond;
884: $cond2 = $join . '.' . $cond;
885: $cond = $cond1 . ' = ' . $cond2;
886:
887: return $this->_join($type, $name, $cond, $cols, $schema);
888: }
889:
890: /**
891: * Generate a unique correlation name
892: *
893: * @param string|array $name A qualified identifier.
894: * @return string A unique correlation name.
895: */
896: private function _uniqueCorrelation($name)
897: {
898: if (is_array($name)) {
899: $c = end($name);
900: } else {
901: // Extract just the last name of a qualified table name
902: $dot = strrpos($name,'.');
903: $c = ($dot === false) ? $name : substr($name, $dot+1);
904: }
905: for ($i = 2; array_key_exists($c, $this->_parts[self::FROM]); ++$i) {
906: $c = $name . '_' . (string) $i;
907: }
908: return $c;
909: }
910:
911: /**
912: * Adds to the internal table-to-column mapping array.
913: *
914: * @param string $tbl The table/join the columns come from.
915: * @param array|string $cols The list of columns; preferably as
916: * an array, but possibly as a string containing one column.
917: * @param bool|string True if it should be prepended, a correlation name if it should be inserted
918: * @return void
919: */
920: protected function _tableCols($correlationName, $cols, $afterCorrelationName = null)
921: {
922: if (!is_array($cols)) {
923: $cols = array($cols);
924: }
925:
926: if ($correlationName == null) {
927: $correlationName = '';
928: }
929:
930: $columnValues = array();
931:
932: foreach (array_filter($cols) as $alias => $col) {
933: $currentCorrelationName = $correlationName;
934: if (is_string($col)) {
935: // Check for a column matching "<column> AS <alias>" and extract the alias name
936: if (preg_match('/^(.+)\s+' . self::SQL_AS . '\s+(.+)$/i', $col, $m)) {
937: $col = $m[1];
938: $alias = $m[2];
939: }
940: // Check for columns that look like functions and convert to Zend_Db_Expr
941: if (preg_match('/\(.*\)/', $col)) {
942: $col = new Zend_Db_Expr($col);
943: } elseif (preg_match('/(.+)\.(.+)/', $col, $m)) {
944: $currentCorrelationName = $m[1];
945: $col = $m[2];
946: }
947: }
948: $columnValues[] = array($currentCorrelationName, $col, is_string($alias) ? $alias : null);
949: }
950:
951: if ($columnValues) {
952:
953: // should we attempt to prepend or insert these values?
954: if ($afterCorrelationName === true || is_string($afterCorrelationName)) {
955: $tmpColumns = $this->_parts[self::COLUMNS];
956: $this->_parts[self::COLUMNS] = array();
957: } else {
958: $tmpColumns = array();
959: }
960:
961: // find the correlation name to insert after
962: if (is_string($afterCorrelationName)) {
963: while ($tmpColumns) {
964: $this->_parts[self::COLUMNS][] = $currentColumn = array_shift($tmpColumns);
965: if ($currentColumn[0] == $afterCorrelationName) {
966: break;
967: }
968: }
969: }
970:
971: // apply current values to current stack
972: foreach ($columnValues as $columnValue) {
973: array_push($this->_parts[self::COLUMNS], $columnValue);
974: }
975:
976: // finish ensuring that all previous values are applied (if they exist)
977: while ($tmpColumns) {
978: array_push($this->_parts[self::COLUMNS], array_shift($tmpColumns));
979: }
980: }
981: }
982:
983: /**
984: * Internal function for creating the where clause
985: *
986: * @param string $condition
987: * @param mixed $value optional
988: * @param string $type optional
989: * @param boolean $bool true = AND, false = OR
990: * @return string clause
991: */
992: protected function _where($condition, $value = null, $type = null, $bool = true)
993: {
994: if (count($this->_parts[self::UNION])) {
995: require_once 'Zend/Db/Select/Exception.php';
996: throw new Zend_Db_Select_Exception("Invalid use of where clause with " . self::SQL_UNION);
997: }
998:
999: if ($value !== null) {
1000: $condition = $this->_adapter->quoteInto($condition, $value, $type);
1001: }
1002:
1003: $cond = "";
1004: if ($this->_parts[self::WHERE]) {
1005: if ($bool === true) {
1006: $cond = self::SQL_AND . ' ';
1007: } else {
1008: $cond = self::SQL_OR . ' ';
1009: }
1010: }
1011:
1012: return $cond . "($condition)";
1013: }
1014:
1015: /**
1016: * @return array
1017: */
1018: protected function _getDummyTable()
1019: {
1020: return array();
1021: }
1022:
1023: /**
1024: * Return a quoted schema name
1025: *
1026: * @param string $schema The schema name OPTIONAL
1027: * @return string|null
1028: */
1029: protected function _getQuotedSchema($schema = null)
1030: {
1031: if ($schema === null) {
1032: return null;
1033: }
1034: return $this->_adapter->quoteIdentifier($schema, true) . '.';
1035: }
1036:
1037: /**
1038: * Return a quoted table name
1039: *
1040: * @param string $tableName The table name
1041: * @param string $correlationName The correlation name OPTIONAL
1042: * @return string
1043: */
1044: protected function _getQuotedTable($tableName, $correlationName = null)
1045: {
1046: return $this->_adapter->quoteTableAs($tableName, $correlationName, true);
1047: }
1048:
1049: /**
1050: * Render DISTINCT clause
1051: *
1052: * @param string $sql SQL query
1053: * @return string
1054: */
1055: protected function _renderDistinct($sql)
1056: {
1057: if ($this->_parts[self::DISTINCT]) {
1058: $sql .= ' ' . self::SQL_DISTINCT;
1059: }
1060:
1061: return $sql;
1062: }
1063:
1064: /**
1065: * Render DISTINCT clause
1066: *
1067: * @param string $sql SQL query
1068: * @return string|null
1069: */
1070: protected function _renderColumns($sql)
1071: {
1072: if (!count($this->_parts[self::COLUMNS])) {
1073: return null;
1074: }
1075:
1076: $columns = array();
1077: foreach ($this->_parts[self::COLUMNS] as $columnEntry) {
1078: list($correlationName, $column, $alias) = $columnEntry;
1079: if ($column instanceof Zend_Db_Expr) {
1080: $columns[] = $this->_adapter->quoteColumnAs($column, $alias, true);
1081: } else {
1082: if ($column == self::SQL_WILDCARD) {
1083: $column = new Zend_Db_Expr(self::SQL_WILDCARD);
1084: $alias = null;
1085: }
1086: if (empty($correlationName)) {
1087: $columns[] = $this->_adapter->quoteColumnAs($column, $alias, true);
1088: } else {
1089: $columns[] = $this->_adapter->quoteColumnAs(array($correlationName, $column), $alias, true);
1090: }
1091: }
1092: }
1093:
1094: return $sql .= ' ' . implode(', ', $columns);
1095: }
1096:
1097: /**
1098: * Render FROM clause
1099: *
1100: * @param string $sql SQL query
1101: * @return string
1102: */
1103: protected function _renderFrom($sql)
1104: {
1105: /*
1106: * If no table specified, use RDBMS-dependent solution
1107: * for table-less query. e.g. DUAL in Oracle.
1108: */
1109: if (empty($this->_parts[self::FROM])) {
1110: $this->_parts[self::FROM] = $this->_getDummyTable();
1111: }
1112:
1113: $from = array();
1114:
1115: foreach ($this->_parts[self::FROM] as $correlationName => $table) {
1116: $tmp = '';
1117:
1118: $joinType = ($table['joinType'] == self::FROM) ? self::INNER_JOIN : $table['joinType'];
1119:
1120: // Add join clause (if applicable)
1121: if (! empty($from)) {
1122: $tmp .= ' ' . strtoupper($joinType) . ' ';
1123: }
1124:
1125: $tmp .= $this->_getQuotedSchema($table['schema']);
1126: $tmp .= $this->_getQuotedTable($table['tableName'], $correlationName);
1127:
1128: // Add join conditions (if applicable)
1129: if (!empty($from) && ! empty($table['joinCondition'])) {
1130: $tmp .= ' ' . self::SQL_ON . ' ' . $table['joinCondition'];
1131: }
1132:
1133: // Add the table name and condition add to the list
1134: $from[] = $tmp;
1135: }
1136:
1137: // Add the list of all joins
1138: if (!empty($from)) {
1139: $sql .= ' ' . self::SQL_FROM . ' ' . implode("\n", $from);
1140: }
1141:
1142: return $sql;
1143: }
1144:
1145: /**
1146: * Render UNION query
1147: *
1148: * @param string $sql SQL query
1149: * @return string
1150: */
1151: protected function _renderUnion($sql)
1152: {
1153: if ($this->_parts[self::UNION]) {
1154: $parts = count($this->_parts[self::UNION]);
1155: foreach ($this->_parts[self::UNION] as $cnt => $union) {
1156: list($target, $type) = $union;
1157: if ($target instanceof Zend_Db_Select) {
1158: $target = $target->assemble();
1159: }
1160: $sql .= $target;
1161: if ($cnt < $parts - 1) {
1162: $sql .= ' ' . $type . ' ';
1163: }
1164: }
1165: }
1166:
1167: return $sql;
1168: }
1169:
1170: /**
1171: * Render WHERE clause
1172: *
1173: * @param string $sql SQL query
1174: * @return string
1175: */
1176: protected function _renderWhere($sql)
1177: {
1178: if ($this->_parts[self::FROM] && $this->_parts[self::WHERE]) {
1179: $sql .= ' ' . self::SQL_WHERE . ' ' . implode(' ', $this->_parts[self::WHERE]);
1180: }
1181:
1182: return $sql;
1183: }
1184:
1185: /**
1186: * Render GROUP clause
1187: *
1188: * @param string $sql SQL query
1189: * @return string
1190: */
1191: protected function _renderGroup($sql)
1192: {
1193: if ($this->_parts[self::FROM] && $this->_parts[self::GROUP]) {
1194: $group = array();
1195: foreach ($this->_parts[self::GROUP] as $term) {
1196: $group[] = $this->_adapter->quoteIdentifier($term, true);
1197: }
1198: $sql .= ' ' . self::SQL_GROUP_BY . ' ' . implode(",\n\t", $group);
1199: }
1200:
1201: return $sql;
1202: }
1203:
1204: /**
1205: * Render HAVING clause
1206: *
1207: * @param string $sql SQL query
1208: * @return string
1209: */
1210: protected function _renderHaving($sql)
1211: {
1212: if ($this->_parts[self::FROM] && $this->_parts[self::HAVING]) {
1213: $sql .= ' ' . self::SQL_HAVING . ' ' . implode(' ', $this->_parts[self::HAVING]);
1214: }
1215:
1216: return $sql;
1217: }
1218:
1219: /**
1220: * Render ORDER clause
1221: *
1222: * @param string $sql SQL query
1223: * @return string
1224: */
1225: protected function _renderOrder($sql)
1226: {
1227: if ($this->_parts[self::ORDER]) {
1228: $order = array();
1229: foreach ($this->_parts[self::ORDER] as $term) {
1230: if (is_array($term)) {
1231: if(is_numeric($term[0]) && strval(intval($term[0])) == $term[0]) {
1232: $order[] = (int)trim($term[0]) . ' ' . $term[1];
1233: } else {
1234: $order[] = $this->_adapter->quoteIdentifier($term[0], true) . ' ' . $term[1];
1235: }
1236: } else if (is_numeric($term) && strval(intval($term)) == $term) {
1237: $order[] = (int)trim($term);
1238: } else {
1239: $order[] = $this->_adapter->quoteIdentifier($term, true);
1240: }
1241: }
1242: $sql .= ' ' . self::SQL_ORDER_BY . ' ' . implode(', ', $order);
1243: }
1244:
1245: return $sql;
1246: }
1247:
1248: /**
1249: * Render LIMIT OFFSET clause
1250: *
1251: * @param string $sql SQL query
1252: * @return string
1253: */
1254: protected function _renderLimitoffset($sql)
1255: {
1256: $count = 0;
1257: $offset = 0;
1258:
1259: if (!empty($this->_parts[self::LIMIT_OFFSET])) {
1260: $offset = (int) $this->_parts[self::LIMIT_OFFSET];
1261: $count = PHP_INT_MAX;
1262: }
1263:
1264: if (!empty($this->_parts[self::LIMIT_COUNT])) {
1265: $count = (int) $this->_parts[self::LIMIT_COUNT];
1266: }
1267:
1268: /*
1269: * Add limits clause
1270: */
1271: if ($count > 0) {
1272: $sql = trim($this->_adapter->limit($sql, $count, $offset));
1273: }
1274:
1275: return $sql;
1276: }
1277:
1278: /**
1279: * Render FOR UPDATE clause
1280: *
1281: * @param string $sql SQL query
1282: * @return string
1283: */
1284: protected function _renderForupdate($sql)
1285: {
1286: if ($this->_parts[self::FOR_UPDATE]) {
1287: $sql .= ' ' . self::SQL_FOR_UPDATE;
1288: }
1289:
1290: return $sql;
1291: }
1292:
1293: /**
1294: * Turn magic function calls into non-magic function calls
1295: * for joinUsing syntax
1296: *
1297: * @param string $method
1298: * @param array $args OPTIONAL Zend_Db_Table_Select query modifier
1299: * @return Zend_Db_Select
1300: * @throws Zend_Db_Select_Exception If an invalid method is called.
1301: */
1302: public function __call($method, array $args)
1303: {
1304: $matches = array();
1305:
1306: /**
1307: * Recognize methods for Has-Many cases:
1308: * findParent<Class>()
1309: * findParent<Class>By<Rule>()
1310: * Use the non-greedy pattern repeat modifier e.g. \w+?
1311: */
1312: if (preg_match('/^join([a-zA-Z]*?)Using$/', $method, $matches)) {
1313: $type = strtolower($matches[1]);
1314: if ($type) {
1315: $type .= ' join';
1316: if (!in_array($type, self::$_joinTypes)) {
1317: require_once 'Zend/Db/Select/Exception.php';
1318: throw new Zend_Db_Select_Exception("Unrecognized method '$method()'");
1319: }
1320: if (in_array($type, array(self::CROSS_JOIN, self::NATURAL_JOIN))) {
1321: require_once 'Zend/Db/Select/Exception.php';
1322: throw new Zend_Db_Select_Exception("Cannot perform a joinUsing with method '$method()'");
1323: }
1324: } else {
1325: $type = self::INNER_JOIN;
1326: }
1327: array_unshift($args, $type);
1328: return call_user_func_array(array($this, '_joinUsing'), $args);
1329: }
1330:
1331: require_once 'Zend/Db/Select/Exception.php';
1332: throw new Zend_Db_Select_Exception("Unrecognized method '$method()'");
1333: }
1334:
1335: /**
1336: * Implements magic method.
1337: *
1338: * @return string This object as a SELECT string.
1339: */
1340: public function __toString()
1341: {
1342: try {
1343: $sql = $this->assemble();
1344: } catch (Exception $e) {
1345: trigger_error($e->getMessage(), E_USER_WARNING);
1346: $sql = '';
1347: }
1348: return (string)$sql;
1349: }
1350:
1351: }
1352: