PageRenderTime 51ms CodeModel.GetById 15ms RepoModel.GetById 0ms app.codeStats 0ms

/lib/Zend/Db/Select.php

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