PageRenderTime 45ms CodeModel.GetById 18ms RepoModel.GetById 1ms app.codeStats 0ms

/library/Maniple/Model/Db/Select.php

https://github.com/maniple/maniple
PHP | 389 lines | 251 code | 48 blank | 90 comment | 36 complexity | 5202662dda611f253717136167e3312a MD5 | raw file
  1. <?php
  2. /**
  3. * @package Maniple_Model_Db
  4. * @uses Zend_Db
  5. * @uses Zefram_Db
  6. * @version 2013-12-01
  7. * @author xemlock
  8. */
  9. class Maniple_Model_Db_Select extends Zefram_Db_Select
  10. {
  11. const OP_GT = '>';
  12. const OP_LT = '<';
  13. const OP_GTE = '>=';
  14. const OP_LTE = '<=';
  15. const OP_NOT = '<>';
  16. const OP_EQ = '=';
  17. const OP_LIKE = 'LIKE';
  18. const OP_NOT_LIKE = 'NOT LIKE';
  19. const SORT_ASC = 'ASC';
  20. const SORT_DESC = 'DESC';
  21. /**
  22. * @var string
  23. */
  24. protected $_defaultCorrelation;
  25. /**
  26. * @deprecated
  27. */
  28. public function criteria(array $criteria, $defaultCorrelation = null) // {{{
  29. {
  30. return $this->conditions($criteria, $defaultCorrelation);
  31. } // }}}
  32. /**
  33. * Add criteria to WHERE clause.
  34. *
  35. * @param array $conditions
  36. * @param string $defaultCorrelation OPTIONAL
  37. * @return Maniple_Model_Db_Select
  38. */
  39. public function conditions(array $conditions, $defaultCorrelation = null) // {{{
  40. {
  41. if (is_null($defaultCorrelation)) {
  42. $defaultCorrelation = $this->_defaultCorrelation;
  43. }
  44. $where = $this->translateConditions($this->getAdapter(), $conditions, $defaultCorrelation);
  45. return $this->where($where);
  46. } // }}}
  47. /**
  48. * @param string|array $sort
  49. * @param string $defaultCorrelation OPTIONAL
  50. * @return Maniple_Model_Db_Select
  51. */
  52. public function sort($sort, $defaultCorrelation = null) // {{{
  53. {
  54. if (is_null($defaultCorrelation)) {
  55. $defaultCorrelation = $this->_defaultCorrelation;
  56. }
  57. $order = self::translateSort($this->getAdapter(), $sort, $defaultCorrelation);
  58. return $this->order($order);
  59. } // }}}
  60. /**
  61. * Set default correlation name.
  62. *
  63. * @param string|null $defaultCorrelation
  64. * @return Maniple_Model_Db_Select
  65. */
  66. public function defaultCorrelation($defaultCorrelation = null) // {{{
  67. {
  68. if (null !== $defaultCorrelation) {
  69. $defaultCorrelation = (string) $defaultCorrelation;
  70. }
  71. $this->_defaultCorrelation = $defaultCorrelation;
  72. return $this;
  73. } // }}}
  74. /**
  75. * Set record retrieval modifiers.
  76. *
  77. * Currently supported modifiers:
  78. * lock, sort, order, limit, offset, page, rows_per_page
  79. *
  80. * @param array $modifiers
  81. * @return Maniple_Model_Db_Select
  82. */
  83. public function modifiers(array $modifiers) // {{{
  84. {
  85. foreach ($modifiers as $key => $value) {
  86. switch ($key) {
  87. case 'lock':
  88. // lock selected rows using FOR UPDATE clause
  89. $this->forUpdate($value);
  90. break;
  91. case 'sort':
  92. $this->sort($value);
  93. break;
  94. case 'order':
  95. $this->order($value);
  96. break;
  97. case 'limit':
  98. if (isset($modifiers['offset'])) {
  99. $this->limit($value, $modifiers['offset']);
  100. } else {
  101. $this->limit($value);
  102. }
  103. break;
  104. case 'page':
  105. if (isset($modifiers['rows_per_page'])) {
  106. $this->limitPage($value, $modifiers['rows_per_page']);
  107. } else {
  108. $this->limitPage($value, 1);
  109. }
  110. break;
  111. }
  112. }
  113. return $this;
  114. } // }}}
  115. /**
  116. * Translate mapper conditions to WHERE clause understood by Zend_Db_Select.
  117. *
  118. * @param Zend_Db_Adapter_Abstract $db
  119. * @param array $conditions
  120. * @param string $defaultCorrelation OPTIONAL
  121. * @return array
  122. */
  123. public static function translateConditions(Zend_Db_Adapter_Abstract $db, array $conditions, $defaultCorrelation = null) // {{{
  124. {
  125. $where = array();
  126. foreach ($conditions as $column => $value) {
  127. $symbol = strrchr($column, '.');
  128. switch ($symbol) {
  129. case '.gt':
  130. $op = self::OP_GT;
  131. break;
  132. case '.lt':
  133. $op = self::OP_LT;
  134. break;
  135. case '.ge':
  136. case '.gte':
  137. $op = self::OP_GTE;
  138. break;
  139. case '.le':
  140. case '.lte':
  141. $op = self::OP_LTE;
  142. break;
  143. case '.not':
  144. case '.neq':
  145. $op = self::OP_NOT;
  146. break;
  147. case '.eq':
  148. case '.eql':
  149. $op = self::OP_EQ;
  150. break;
  151. case '.like':
  152. $op = self::OP_LIKE;
  153. break;
  154. case '.ilike':
  155. $op = self::OP_ILIKE;
  156. break;
  157. case '.not_like':
  158. $op = self::OP_NOT_LIKE;
  159. break;
  160. default:
  161. $symbol = null;
  162. $op = self::OP_EQ;
  163. break;
  164. }
  165. // strip off op symbol from column name
  166. if (null !== $symbol) {
  167. $column = substr($column, 0, -strlen($symbol));
  168. }
  169. // check for .nocase suffix that initializes case-insensitive match
  170. // In case-insensitive mode both operands are lowercased.
  171. // Fortunately LOWER() function is available in all major DBMS,
  172. // unfortunately though, some implementations (namely, PostgreSQL)
  173. // are strict in terms of parameter types, which may result in
  174. // a database error if LOWER() is applied to a non-string value.
  175. // In other words, use with caution.
  176. if ('.nocase' === substr($column, -7)) {
  177. $column = substr($column, 0, -7);
  178. $nocase = true;
  179. } else {
  180. $nocase = false;
  181. }
  182. // if default correlation name was given prepend it to the column
  183. // name if it does not already contain a correlation name
  184. if (false === strpos($column, '.') && null !== $defaultCorrelation) {
  185. $column = $defaultCorrelation . '.' . $column;
  186. }
  187. $quoted_column = $db->quoteIdentifier($column);
  188. if ($nocase) {
  189. $quoted_column = 'LOWER(' . $quoted_column . ')';
  190. }
  191. if (null === $value) {
  192. // NULL matching
  193. switch ($op) {
  194. case self::OP_EQ:
  195. $where[] = $quoted_column . ' IS NULL';
  196. break;
  197. case self::OP_NOT:
  198. $where[] = $quoted_column . ' IS NOT NULL';
  199. break;
  200. default:
  201. throw new Maniple_Model_Db_Exception_InvalidArgument(
  202. 'NULL value can be tested for equality/inequality only'
  203. );
  204. }
  205. } elseif (is_array($value)) {
  206. // filter-out NULLs as Zend_Db_Adapter_Abstract::quote() handles
  207. // them improperly (renders empty string instead of 'NULL').
  208. // Moreover, NULL values will not be matched if it occurs in
  209. // a list of values (using IN operator). To handle such case
  210. // properly, remember if a NULL value is also to be matched.
  211. $null = false;
  212. foreach ($value as $key => $val) {
  213. if (null === $val) {
  214. $null = true;
  215. unset($value[$key]);
  216. }
  217. }
  218. if ($nocase) {
  219. foreach ($value as $key => $val) {
  220. $value[$key] = new Zend_Db_Expr(sprintf(
  221. 'LOWER(%s)', $db->quote(strval($val))
  222. ));
  223. }
  224. }
  225. switch ($op) {
  226. case self::OP_EQ:
  227. case self::OP_NOT:
  228. break;
  229. default:
  230. throw new Maniple_Model_Db_Exception_InvalidArgument(
  231. 'A list of values can be tested for inclusion/exclusion only'
  232. );
  233. }
  234. if (count($value)) {
  235. if ($null) {
  236. if (self::OP_EQ === $op) {
  237. $key = sprintf("%s IN (?) OR %s IS NULL",
  238. $quoted_column,
  239. $quoted_column
  240. );
  241. } else {
  242. $key = sprintf("%s NOT IN (?) AND %s IS NOT NULL",
  243. $quoted_column,
  244. $quoted_column
  245. );
  246. }
  247. } else {
  248. $key = sprintf("%s %s (?)",
  249. $quoted_column,
  250. $op === self::OP_EQ ? 'IN' : 'NOT IN'
  251. );
  252. }
  253. $where[$key] = $value;
  254. } else {
  255. if ($null) {
  256. // match NULL only as list of non-NULL values is empty
  257. $where[] = sprintf("%s %s NULL",
  258. $quoted_column,
  259. $op === self::OP_EQ ? 'IS' : 'IS NOT'
  260. );
  261. } else {
  262. // this should not match anything regardless whether
  263. // exclusion or inclusion operator is used, i.e. both
  264. // expressions: NULL IN (NULL) and NULL NOT IN (NULL)
  265. // evaluate to NULL
  266. $where[] = sprintf("%s %s (NULL)",
  267. $quoted_column,
  268. $op === self::OP_EQ ? 'IN' : 'NOT IN'
  269. );
  270. }
  271. }
  272. } elseif ($nocase) {
  273. $where[$quoted_column . ' ' . $op . ' LOWER(?)'] = $value;
  274. } else {
  275. $where[$quoted_column . ' ' . $op . ' ?'] = $value;
  276. }
  277. }
  278. return $where;
  279. } // }}}
  280. /**
  281. * Translate mapper ordering to a ORDER clause understood by Zend_Db_Select.
  282. *
  283. * @param Zend_Db_Adapter_Abstract $db
  284. * @param string|array $sort
  285. * @param string $defaultCorrelation OPTIONAL
  286. * @return array
  287. */
  288. public static function translateSort(Zend_Db_Adapter_Abstract $db, $sort, $defaultCorrelation = null) // {{{
  289. {
  290. $order = array();
  291. foreach ((array) $sort as $column) {
  292. $nocase = false;
  293. $column = (string) $column;
  294. $symbol = strrchr($column, '.');
  295. switch ($symbol) {
  296. case '.asc':
  297. $direction = self::SORT_ASC;
  298. break;
  299. case '.desc':
  300. $direction = self::SORT_DESC;
  301. break;
  302. default:
  303. $direction = self::SORT_ASC;
  304. $symbol = null;
  305. break;
  306. }
  307. if (null !== $symbol) {
  308. $column = substr($column, 0, -strlen($symbol));
  309. }
  310. if ('.nocase' === substr($column, -7)) {
  311. $nocase = true;
  312. $column = substr($column, 0, -7);
  313. }
  314. // if default correlation name was given, prepend it to the
  315. // column name, provided that the latter does not already
  316. // contain a correlation name
  317. if (false === strpos($column, '.') && null !== $defaultCorrelation) {
  318. $column = $defaultCorrelation . '.' . $column;
  319. }
  320. if ($nocase) {
  321. // If a column contains parentheses, Zend_Db_Select recognizes
  322. // it as an expression. See:
  323. // http://framework.zend.com/manual/1.12/en/zend.db.select.html
  324. $order[] = 'LOWER(' . $db->quoteIdentifier($column) . ') ' . $direction;
  325. } else {
  326. // do not quote identifier, as it is done in a crude
  327. // way by Zend_Db_Select::_renderOrder()
  328. $order[] = $column . ' ' . $direction;
  329. }
  330. }
  331. return $order;
  332. } // }}}
  333. /**
  334. * @param Zend_Db_Adapter_Abstract $db
  335. * @return Maniple_Model_Db_Select
  336. */
  337. public static function factory(Zend_Db_Adapter_Abstract $db) // {{{
  338. {
  339. return new self($db);
  340. } // }}}
  341. }