/packages/Db/QueryBuilder/Objects/Expr/Expr.php

https://bitbucket.org/alexamiryan/stingle · PHP · 553 lines · 173 code · 41 blank · 339 comment · 7 complexity · f86ec98e03dc86b772affa03c8ac4ece MD5 · raw file

  1. <?php
  2. /*
  3. * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  4. * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  5. * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  6. * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  7. * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  8. * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  9. * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  10. * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  11. * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  12. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  13. * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  14. *
  15. * This software consists of voluntary contributions made by many individuals
  16. * and is licensed under the LGPL. For more information, see
  17. * <http://www.doctrine-project.org>.
  18. */
  19. /**
  20. * This class is used to generate SQL expressions via a set of PHP static functions
  21. *
  22. * @license http://www.opensource.org/licenses/lgpl-license.php LGPL
  23. * @link www.doctrine-project.org
  24. * @since 2.0
  25. * @author Guilherme Blanco <guilhermeblanco@hotmail.com>
  26. * @author Jonathan Wage <jonwage@gmail.com>
  27. * @author Roman Borschel <roman@code-factory.org>
  28. * @author Benjamin Eberlei <kontakt@beberlei.de>
  29. */
  30. class Expr
  31. {
  32. /**
  33. * Creates an ASCending order expression.
  34. *
  35. * @param $sort
  36. * @return OrderBy
  37. */
  38. public function asc($expr)
  39. {
  40. return new OrderBy($expr, OrderBy::ASC);
  41. }
  42. /**
  43. * Creates a DESCending order expression.
  44. *
  45. * @param $sort
  46. * @return OrderBy
  47. */
  48. public function desc($expr)
  49. {
  50. return new OrderBy($expr, OrderBy::DESC);
  51. }
  52. /**
  53. * Creates an equality comparison expression with the given arguments.
  54. *
  55. * First argument is considered the left expression and the second is the right expression.
  56. * When converted to string, it will generated a <left expr> = <right expr>. Example:
  57. *
  58. * [php]
  59. * // u.id = ?1
  60. * $expr->equal('u.id', '?1');
  61. *
  62. * @param mixed $x Left expression
  63. * @param mixed $y Right expression
  64. * @return Comparison
  65. */
  66. public function equal($x, $y)
  67. {
  68. return new Comparison($x, Comparison::EQ, $y);
  69. }
  70. /**
  71. * Creates an instance of Comparison, with the given arguments.
  72. * First argument is considered the left expression and the second is the right expression.
  73. * When converted to string, it will generated a <left expr> <> <right expr>. Example:
  74. *
  75. * [php]
  76. * // u.id <> ?1
  77. * $q->where($q->expr()->notEqual('u.id', '?1'));
  78. *
  79. * @param mixed $x Left expression
  80. * @param mixed $y Right expression
  81. * @return Comparison
  82. */
  83. public function notEqual($x, $y)
  84. {
  85. return new Comparison($x, Comparison::NEQ, $y);
  86. }
  87. /**
  88. * Creates an instance of Comparison, with the given arguments.
  89. * First argument is considered the left expression and the second is the right expression.
  90. * When converted to string, it will generated a <left expr> < <right expr>. Example:
  91. *
  92. * [php]
  93. * // u.id < ?1
  94. * $q->where($q->expr()->less('u.id', '?1'));
  95. *
  96. * @param mixed $x Left expression
  97. * @param mixed $y Right expression
  98. * @return Comparison
  99. */
  100. public function less($x, $y)
  101. {
  102. return new Comparison($x, Comparison::LT, $y);
  103. }
  104. /**
  105. * Creates an instance of Comparison, with the given arguments.
  106. * First argument is considered the left expression and the second is the right expression.
  107. * When converted to string, it will generated a <left expr> <= <right expr>. Example:
  108. *
  109. * [php]
  110. * // u.id <= ?1
  111. * $q->where($q->expr()->lessEqual('u.id', '?1'));
  112. *
  113. * @param mixed $x Left expression
  114. * @param mixed $y Right expression
  115. * @return Comparison
  116. */
  117. public function lessEqual($x, $y)
  118. {
  119. return new Comparison($x, Comparison::LTE, $y);
  120. }
  121. /**
  122. * Creates an instance of Comparison, with the given arguments.
  123. * First argument is considered the left expression and the second is the right expression.
  124. * When converted to string, it will generated a <left expr> > <right expr>. Example:
  125. *
  126. * [php]
  127. * // u.id > ?1
  128. * $q->where($q->expr()->greater('u.id', '?1'));
  129. *
  130. * @param mixed $x Left expression
  131. * @param mixed $y Right expression
  132. * @return Comparison
  133. */
  134. public function greater($x, $y)
  135. {
  136. return new Comparison($x, Comparison::GT, $y);
  137. }
  138. /**
  139. * Creates an instance of Comparison, with the given arguments.
  140. * First argument is considered the left expression and the second is the right expression.
  141. * When converted to string, it will generated a <left expr> >= <right expr>. Example:
  142. *
  143. * [php]
  144. * // u.id >= ?1
  145. * $q->where($q->expr()->greaterEqual('u.id', '?1'));
  146. *
  147. * @param mixed $x Left expression
  148. * @param mixed $y Right expression
  149. * @return Comparison
  150. */
  151. public function greaterEqual($x, $y)
  152. {
  153. return new Comparison($x, Comparison::GTE, $y);
  154. }
  155. /**
  156. * Creates an instance of AVG() function, with the given argument.
  157. *
  158. * @param mixed $x Argument to be used in AVG() function.
  159. * @return Func
  160. */
  161. public function avg($x)
  162. {
  163. return new Func('AVG', array($x));
  164. }
  165. /**
  166. * Creates an instance of MAX() function, with the given argument.
  167. *
  168. * @param mixed $x Argument to be used in MAX() function.
  169. * @return Func
  170. */
  171. public function max($x, $alias = null)
  172. {
  173. return new Func('MAX', array($x), $alias);
  174. }
  175. /**
  176. * Creates an instance of MIN() function, with the given argument.
  177. *
  178. * @param mixed $x Argument to be used in MIN() function.
  179. * @return Func
  180. */
  181. public function min($x, $alias = null)
  182. {
  183. return new Func('MIN', array($x), $alias);
  184. }
  185. /**
  186. * Creates an instance of COUNT() function, with the given argument.
  187. *
  188. * @param mixed $x Argument to be used in COUNT() function.
  189. * @return Func
  190. */
  191. public function count($x, $alias = null)
  192. {
  193. return new Func('COUNT', array($x), $alias);
  194. }
  195. /**
  196. * Creates an instance of COUNT(DISTINCT) function, with the given argument.
  197. *
  198. * @param mixed $x Argument to be used in COUNT(DISTINCT) function.
  199. * @return string
  200. */
  201. public function countDistinct($x, $alias = null)
  202. {
  203. $params = func_get_args();
  204. foreach($params as &$param){
  205. $param = Expr::quoteLiteral($param);
  206. }
  207. $returnStr = 'COUNT(DISTINCT ' . implode(', ', $params) . ')';
  208. if($alias != null){
  209. $returnStr .= "as `$alias`";
  210. }
  211. return $returnStr;
  212. }
  213. /**
  214. * Creates an instance of EXISTS() function, with the given SQL Subquery.
  215. *
  216. * @param mixed $subquery SQL Subquery to be used in EXISTS() function.
  217. * @return Func
  218. */
  219. public function exists($subquery)
  220. {
  221. return new Func('EXISTS', array($subquery));
  222. }
  223. /**
  224. * Creates an instance of ALL() function, with the given SQL Subquery.
  225. *
  226. * @param mixed $subquery SQL Subquery to be used in ALL() function.
  227. * @return Func
  228. */
  229. public function all($subquery)
  230. {
  231. return new Func('ALL', array($subquery));
  232. }
  233. /**
  234. * Creates a SOME() function expression with the given SQL subquery.
  235. *
  236. * @param mixed $subquery SQL Subquery to be used in SOME() function.
  237. * @return Func
  238. */
  239. public function some($subquery)
  240. {
  241. return new Func('SOME', array($subquery));
  242. }
  243. /**
  244. * Creates an ANY() function expression with the given SQL subquery.
  245. *
  246. * @param mixed $subquery SQL Subquery to be used in ANY() function.
  247. * @return Func
  248. */
  249. public function any($subquery)
  250. {
  251. return new Func('ANY', array($subquery));
  252. }
  253. /**
  254. * Creates a negation expression of the given restriction.
  255. *
  256. * @param mixed $restriction Restriction to be used in NOT() function.
  257. * @return Func
  258. */
  259. public function not($restriction)
  260. {
  261. return new Func('NOT', array($restriction));
  262. }
  263. /**
  264. * Creates an ABS() function expression with the given argument.
  265. *
  266. * @param mixed $x Argument to be used in ABS() function.
  267. * @return Func
  268. */
  269. public function abs($x)
  270. {
  271. return new Func('ABS', array($x));
  272. }
  273. /**
  274. * Creates a product mathematical expression with the given arguments.
  275. *
  276. * First argument is considered the left expression and the second is the right expression.
  277. * When converted to string, it will generated a <left expr> * <right expr>. Example:
  278. *
  279. * [php]
  280. * // u.salary * u.percentAnualSalaryIncrease
  281. * $q->expr()->prod('u.salary', 'u.percentAnualSalaryIncrease')
  282. *
  283. * @param mixed $x Left expression
  284. * @param mixed $y Right expression
  285. * @return Math
  286. */
  287. public function prod($x, $y)
  288. {
  289. return new Math($x, '*', $y);
  290. }
  291. /**
  292. * Creates a difference mathematical expression with the given arguments.
  293. * First argument is considered the left expression and the second is the right expression.
  294. * When converted to string, it will generated a <left expr> - <right expr>. Example:
  295. *
  296. * [php]
  297. * // u.monthlySubscriptionCount - 1
  298. * $q->expr()->diff('u.monthlySubscriptionCount', '1')
  299. *
  300. * @param mixed $x Left expression
  301. * @param mixed $y Right expression
  302. * @return Math
  303. */
  304. public function diff($x, $y)
  305. {
  306. return new Math($x, '-', $y);
  307. }
  308. /**
  309. * Creates a sum mathematical expression with the given arguments.
  310. * First argument is considered the left expression and the second is the right expression.
  311. * When converted to string, it will generated a <left expr> + <right expr>. Example:
  312. *
  313. * [php]
  314. * // u.numChildren + 1
  315. * $q->expr()->diff('u.numChildren', '1')
  316. *
  317. * @param mixed $x Left expression
  318. * @param mixed $y Right expression
  319. * @return Math
  320. */
  321. public function sum($x, $y)
  322. {
  323. return new Math($x, '+', $y);
  324. }
  325. /**
  326. * Creates a quotient mathematical expression with the given arguments.
  327. * First argument is considered the left expression and the second is the right expression.
  328. * When converted to string, it will generated a <left expr> / <right expr>. Example:
  329. *
  330. * [php]
  331. * // u.total / u.period
  332. * $expr->quot('u.total', 'u.period')
  333. *
  334. * @param mixed $x Left expression
  335. * @param mixed $y Right expression
  336. * @return Math
  337. */
  338. public function quot($x, $y)
  339. {
  340. return new Math($x, '/', $y);
  341. }
  342. /**
  343. * Creates a SQRT() function expression with the given argument.
  344. *
  345. * @param mixed $x Argument to be used in SQRT() function.
  346. * @return Func
  347. */
  348. public function sqrt($x)
  349. {
  350. return new Func('SQRT', array($x));
  351. }
  352. /**
  353. * Creates an IN() expression with the given arguments.
  354. *
  355. * @param string $x Field in string format to be restricted by IN() function
  356. * @param mixed $y Argument to be used in IN() function.
  357. * @return Func
  358. */
  359. public function in($x, $y)
  360. {
  361. /*if($y instanceof QueryBuilder){
  362. $y = array($y->getSQL());
  363. }
  364. elseif($y instanceof Unionx){
  365. $y = array($y);
  366. }*/
  367. return new Func($x . ' IN', $y);
  368. }
  369. /**
  370. * Creates a NOT IN() expression with the given arguments.
  371. *
  372. * @param string $x Field in string format to be restricted by NOT IN() function
  373. * @param mixed $y Argument to be used in NOT IN() function.
  374. * @return Func
  375. */
  376. public function notIn($x, $y)
  377. {
  378. /*if($y instanceof QueryBuilder){
  379. $y = array($y->getSQL());
  380. }*/
  381. return new Func($x . ' NOT IN', $y);
  382. }
  383. /**
  384. * Creates an IS NULL expression with the given arguments.
  385. *
  386. * @param string $x Field in string format to be restricted by IS NULL
  387. * @return string
  388. */
  389. public function isNull($x)
  390. {
  391. return $x . ' IS NULL';
  392. }
  393. /**
  394. * Creates an IS NOT NULL expression with the given arguments.
  395. *
  396. * @param string $x Field in string format to be restricted by IS NOT NULL
  397. * @return string
  398. */
  399. public function isNotNull($x)
  400. {
  401. return $x . ' IS NOT NULL';
  402. }
  403. /**
  404. * Creates a LIKE() comparison expression with the given arguments.
  405. *
  406. * @param string $x Field in string format to be inspected by LIKE() comparison.
  407. * @param mixed $y Argument to be used in LIKE() comparison.
  408. * @return Comparison
  409. */
  410. public function like($x, $y)
  411. {
  412. return new Comparison($x, 'LIKE', $y);
  413. }
  414. /**
  415. * Creates a CONCAT() function expression with the given arguments.
  416. *
  417. * @param mixed $x First argument to be used in CONCAT() function.
  418. * @param mixed $x Second argument to be used in CONCAT() function.
  419. * @return Func
  420. */
  421. public function concat($x, $y)
  422. {
  423. return new Func('CONCAT', array($x, $y));
  424. }
  425. /**
  426. * Creates a SUBSTRING() function expression with the given arguments.
  427. *
  428. * @param mixed $x Argument to be used as string to be cropped by SUBSTRING() function.
  429. * @param integer $from Initial offset to start cropping string. May accept negative values.
  430. * @param integer $len Length of crop. May accept negative values.
  431. * @return Func
  432. */
  433. public function substring($x, $from, $len = null)
  434. {
  435. $args = array($x, $from);
  436. if (null !== $len) {
  437. $args[] = $len;
  438. }
  439. return new Func('SUBSTRING', $args);
  440. }
  441. /**
  442. * Creates a LOWER() function expression with the given argument.
  443. *
  444. * @param mixed $x Argument to be used in LOWER() function.
  445. * @return Func A LOWER function expression.
  446. */
  447. public function lower($x)
  448. {
  449. return new Func('LOWER', array($x));
  450. }
  451. /**
  452. * Creates an UPPER() function expression with the given argument.
  453. *
  454. * @param mixed $x Argument to be used in UPPER() function.
  455. * @return Func An UPPER function expression.
  456. */
  457. public function upper($x)
  458. {
  459. return new Func('UPPER', array($x));
  460. }
  461. /**
  462. * Creates a LENGTH() function expression with the given argument.
  463. *
  464. * @param mixed $x Argument to be used as argument of LENGTH() function.
  465. * @return Func A LENGTH function expression.
  466. */
  467. public function length($x)
  468. {
  469. return new Func('LENGTH', array($x));
  470. }
  471. /**
  472. * Quotes a literal value, if necessary, according to the SQL syntax.
  473. *
  474. * @param mixed $literal The literal value.
  475. * @return string
  476. */
  477. public static function quoteLiteral($literal){
  478. if (($literal instanceof QBpart) or ($literal instanceof QueryBuilder)) {
  479. return $literal;
  480. }
  481. else{
  482. if (is_numeric($literal) && !is_string($literal)) {
  483. return (string) $literal;
  484. }
  485. else {
  486. return "'" . MySqlDbManager::getQueryObject()->escapeString($literal) . "'";
  487. }
  488. }
  489. }
  490. /**
  491. * Creates an instance of BETWEEN() function, with the given argument.
  492. *
  493. * @param mixed $val Valued to be inspected by range values.
  494. * @param integer $x Starting range value to be used in BETWEEN() function.
  495. * @param integer $y End point value to be used in BETWEEN() function.
  496. * @return Func A BETWEEN expression.
  497. */
  498. public function between($val, $x, $y)
  499. {
  500. return Expr::quoteLiteral($val) . ' BETWEEN ' . Expr::quoteLiteral($x) . ' AND ' . Expr::quoteLiteral($y);
  501. }
  502. /**
  503. * Creates an instance of TRIM() function, with the given argument.
  504. *
  505. * @param mixed $x Argument to be used as argument of TRIM() function.
  506. * @return Func a TRIM expression.
  507. */
  508. public function trim($x)
  509. {
  510. return new Func('TRIM', $x);
  511. }
  512. }