Expr.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647
  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 MIT license. For more information, see
  17. * <http://www.doctrine-project.org>.
  18. */
  19. namespace Doctrine\ORM\Query;
  20. /**
  21. * This class is used to generate DQL expressions via a set of PHP static functions.
  22. * @link www.doctrine-project.org
  23. * @since 2.0
  24. * @author Guilherme Blanco <guilhermeblanco@hotmail.com>
  25. * @author Jonathan Wage <jonwage@gmail.com>
  26. * @author Roman Borschel <roman@code-factory.org>
  27. * @author Benjamin Eberlei <kontakt@beberlei.de>
  28. * @todo Rename: ExpressionBuilder
  29. */
  30. class Expr
  31. {
  32. /**
  33. * Creates a conjunction of the given boolean expressions.
  34. *
  35. * Example:
  36. *
  37. * [php]
  38. * // (u.type = ?1) AND (u.role = ?2)
  39. * $expr->andX($expr->eq('u.type', ':1'), $expr->eq('u.role', ':2'));
  40. *
  41. * @param \Doctrine\ORM\Query\Expr\Comparison |
  42. * \Doctrine\ORM\Query\Expr\Func |
  43. * \Doctrine\ORM\Query\Expr\Orx
  44. * $x Optional clause. Defaults to null, but requires at least one defined when converting to string.
  45. *
  46. * @return Expr\Andx
  47. */
  48. public function andX($x = null)
  49. {
  50. return new Expr\Andx(func_get_args());
  51. }
  52. /**
  53. * Creates a disjunction of the given boolean expressions.
  54. *
  55. * Example:
  56. *
  57. * [php]
  58. * // (u.type = ?1) OR (u.role = ?2)
  59. * $q->where($q->expr()->orX('u.type = ?1', 'u.role = ?2'));
  60. *
  61. * @param mixed $x Optional clause. Defaults to null, but requires
  62. * at least one defined when converting to string.
  63. *
  64. * @return Expr\Orx
  65. */
  66. public function orX($x = null)
  67. {
  68. return new Expr\Orx(func_get_args());
  69. }
  70. /**
  71. * Creates an ASCending order expression.
  72. *
  73. * @param mixed $expr
  74. *
  75. * @return Expr\OrderBy
  76. */
  77. public function asc($expr)
  78. {
  79. return new Expr\OrderBy($expr, 'ASC');
  80. }
  81. /**
  82. * Creates a DESCending order expression.
  83. *
  84. * @param mixed $expr
  85. *
  86. * @return Expr\OrderBy
  87. */
  88. public function desc($expr)
  89. {
  90. return new Expr\OrderBy($expr, 'DESC');
  91. }
  92. /**
  93. * Creates an equality comparison expression with the given arguments.
  94. *
  95. * First argument is considered the left expression and the second is the right expression.
  96. * When converted to string, it will generated a <left expr> = <right expr>. Example:
  97. *
  98. * [php]
  99. * // u.id = ?1
  100. * $expr->eq('u.id', '?1');
  101. *
  102. * @param mixed $x Left expression.
  103. * @param mixed $y Right expression.
  104. *
  105. * @return Expr\Comparison
  106. */
  107. public function eq($x, $y)
  108. {
  109. return new Expr\Comparison($x, Expr\Comparison::EQ, $y);
  110. }
  111. /**
  112. * Creates an instance of Expr\Comparison, with the given arguments.
  113. * First argument is considered the left expression and the second is the right expression.
  114. * When converted to string, it will generated a <left expr> <> <right expr>. Example:
  115. *
  116. * [php]
  117. * // u.id <> ?1
  118. * $q->where($q->expr()->neq('u.id', '?1'));
  119. *
  120. * @param mixed $x Left expression.
  121. * @param mixed $y Right expression.
  122. *
  123. * @return Expr\Comparison
  124. */
  125. public function neq($x, $y)
  126. {
  127. return new Expr\Comparison($x, Expr\Comparison::NEQ, $y);
  128. }
  129. /**
  130. * Creates an instance of Expr\Comparison, with the given arguments.
  131. * First argument is considered the left expression and the second is the right expression.
  132. * When converted to string, it will generated a <left expr> < <right expr>. Example:
  133. *
  134. * [php]
  135. * // u.id < ?1
  136. * $q->where($q->expr()->lt('u.id', '?1'));
  137. *
  138. * @param mixed $x Left expression.
  139. * @param mixed $y Right expression.
  140. *
  141. * @return Expr\Comparison
  142. */
  143. public function lt($x, $y)
  144. {
  145. return new Expr\Comparison($x, Expr\Comparison::LT, $y);
  146. }
  147. /**
  148. * Creates an instance of Expr\Comparison, with the given arguments.
  149. * First argument is considered the left expression and the second is the right expression.
  150. * When converted to string, it will generated a <left expr> <= <right expr>. Example:
  151. *
  152. * [php]
  153. * // u.id <= ?1
  154. * $q->where($q->expr()->lte('u.id', '?1'));
  155. *
  156. * @param mixed $x Left expression.
  157. * @param mixed $y Right expression.
  158. *
  159. * @return Expr\Comparison
  160. */
  161. public function lte($x, $y)
  162. {
  163. return new Expr\Comparison($x, Expr\Comparison::LTE, $y);
  164. }
  165. /**
  166. * Creates an instance of Expr\Comparison, with the given arguments.
  167. * First argument is considered the left expression and the second is the right expression.
  168. * When converted to string, it will generated a <left expr> > <right expr>. Example:
  169. *
  170. * [php]
  171. * // u.id > ?1
  172. * $q->where($q->expr()->gt('u.id', '?1'));
  173. *
  174. * @param mixed $x Left expression.
  175. * @param mixed $y Right expression.
  176. *
  177. * @return Expr\Comparison
  178. */
  179. public function gt($x, $y)
  180. {
  181. return new Expr\Comparison($x, Expr\Comparison::GT, $y);
  182. }
  183. /**
  184. * Creates an instance of Expr\Comparison, with the given arguments.
  185. * First argument is considered the left expression and the second is the right expression.
  186. * When converted to string, it will generated a <left expr> >= <right expr>. Example:
  187. *
  188. * [php]
  189. * // u.id >= ?1
  190. * $q->where($q->expr()->gte('u.id', '?1'));
  191. *
  192. * @param mixed $x Left expression.
  193. * @param mixed $y Right expression.
  194. *
  195. * @return Expr\Comparison
  196. */
  197. public function gte($x, $y)
  198. {
  199. return new Expr\Comparison($x, Expr\Comparison::GTE, $y);
  200. }
  201. /**
  202. * Creates an instance of AVG() function, with the given argument.
  203. *
  204. * @param mixed $x Argument to be used in AVG() function.
  205. *
  206. * @return Expr\Func
  207. */
  208. public function avg($x)
  209. {
  210. return new Expr\Func('AVG', array($x));
  211. }
  212. /**
  213. * Creates an instance of MAX() function, with the given argument.
  214. *
  215. * @param mixed $x Argument to be used in MAX() function.
  216. *
  217. * @return Expr\Func
  218. */
  219. public function max($x)
  220. {
  221. return new Expr\Func('MAX', array($x));
  222. }
  223. /**
  224. * Creates an instance of MIN() function, with the given argument.
  225. *
  226. * @param mixed $x Argument to be used in MIN() function.
  227. *
  228. * @return Expr\Func
  229. */
  230. public function min($x)
  231. {
  232. return new Expr\Func('MIN', array($x));
  233. }
  234. /**
  235. * Creates an instance of COUNT() function, with the given argument.
  236. *
  237. * @param mixed $x Argument to be used in COUNT() function.
  238. *
  239. * @return Expr\Func
  240. */
  241. public function count($x)
  242. {
  243. return new Expr\Func('COUNT', array($x));
  244. }
  245. /**
  246. * Creates an instance of COUNT(DISTINCT) function, with the given argument.
  247. *
  248. * @param mixed $x Argument to be used in COUNT(DISTINCT) function.
  249. *
  250. * @return string
  251. */
  252. public function countDistinct($x)
  253. {
  254. return 'COUNT(DISTINCT ' . implode(', ', func_get_args()) . ')';
  255. }
  256. /**
  257. * Creates an instance of EXISTS() function, with the given DQL Subquery.
  258. *
  259. * @param mixed $subquery DQL Subquery to be used in EXISTS() function.
  260. *
  261. * @return Expr\Func
  262. */
  263. public function exists($subquery)
  264. {
  265. return new Expr\Func('EXISTS', array($subquery));
  266. }
  267. /**
  268. * Creates an instance of ALL() function, with the given DQL Subquery.
  269. *
  270. * @param mixed $subquery DQL Subquery to be used in ALL() function.
  271. *
  272. * @return Expr\Func
  273. */
  274. public function all($subquery)
  275. {
  276. return new Expr\Func('ALL', array($subquery));
  277. }
  278. /**
  279. * Creates a SOME() function expression with the given DQL subquery.
  280. *
  281. * @param mixed $subquery DQL Subquery to be used in SOME() function.
  282. *
  283. * @return Expr\Func
  284. */
  285. public function some($subquery)
  286. {
  287. return new Expr\Func('SOME', array($subquery));
  288. }
  289. /**
  290. * Creates an ANY() function expression with the given DQL subquery.
  291. *
  292. * @param mixed $subquery DQL Subquery to be used in ANY() function.
  293. *
  294. * @return Expr\Func
  295. */
  296. public function any($subquery)
  297. {
  298. return new Expr\Func('ANY', array($subquery));
  299. }
  300. /**
  301. * Creates a negation expression of the given restriction.
  302. *
  303. * @param mixed $restriction Restriction to be used in NOT() function.
  304. *
  305. * @return Expr\Func
  306. */
  307. public function not($restriction)
  308. {
  309. return new Expr\Func('NOT', array($restriction));
  310. }
  311. /**
  312. * Creates an ABS() function expression with the given argument.
  313. *
  314. * @param mixed $x Argument to be used in ABS() function.
  315. *
  316. * @return Expr\Func
  317. */
  318. public function abs($x)
  319. {
  320. return new Expr\Func('ABS', array($x));
  321. }
  322. /**
  323. * Creates a product mathematical expression with the given arguments.
  324. *
  325. * First argument is considered the left expression and the second is the right expression.
  326. * When converted to string, it will generated a <left expr> * <right expr>. Example:
  327. *
  328. * [php]
  329. * // u.salary * u.percentAnnualSalaryIncrease
  330. * $q->expr()->prod('u.salary', 'u.percentAnnualSalaryIncrease')
  331. *
  332. * @param mixed $x Left expression.
  333. * @param mixed $y Right expression.
  334. *
  335. * @return Expr\Math
  336. */
  337. public function prod($x, $y)
  338. {
  339. return new Expr\Math($x, '*', $y);
  340. }
  341. /**
  342. * Creates a difference mathematical expression with the given arguments.
  343. * First argument is considered the left expression and the second is the right expression.
  344. * When converted to string, it will generated a <left expr> - <right expr>. Example:
  345. *
  346. * [php]
  347. * // u.monthlySubscriptionCount - 1
  348. * $q->expr()->diff('u.monthlySubscriptionCount', '1')
  349. *
  350. * @param mixed $x Left expression.
  351. * @param mixed $y Right expression.
  352. *
  353. * @return Expr\Math
  354. */
  355. public function diff($x, $y)
  356. {
  357. return new Expr\Math($x, '-', $y);
  358. }
  359. /**
  360. * Creates a sum mathematical expression with the given arguments.
  361. * First argument is considered the left expression and the second is the right expression.
  362. * When converted to string, it will generated a <left expr> + <right expr>. Example:
  363. *
  364. * [php]
  365. * // u.numChildren + 1
  366. * $q->expr()->diff('u.numChildren', '1')
  367. *
  368. * @param mixed $x Left expression.
  369. * @param mixed $y Right expression.
  370. *
  371. * @return Expr\Math
  372. */
  373. public function sum($x, $y)
  374. {
  375. return new Expr\Math($x, '+', $y);
  376. }
  377. /**
  378. * Creates a quotient mathematical expression with the given arguments.
  379. * First argument is considered the left expression and the second is the right expression.
  380. * When converted to string, it will generated a <left expr> / <right expr>. Example:
  381. *
  382. * [php]
  383. * // u.total / u.period
  384. * $expr->quot('u.total', 'u.period')
  385. *
  386. * @param mixed $x Left expression.
  387. * @param mixed $y Right expression.
  388. *
  389. * @return Expr\Math
  390. */
  391. public function quot($x, $y)
  392. {
  393. return new Expr\Math($x, '/', $y);
  394. }
  395. /**
  396. * Creates a SQRT() function expression with the given argument.
  397. *
  398. * @param mixed $x Argument to be used in SQRT() function.
  399. *
  400. * @return Expr\Func
  401. */
  402. public function sqrt($x)
  403. {
  404. return new Expr\Func('SQRT', array($x));
  405. }
  406. /**
  407. * Creates an IN() expression with the given arguments.
  408. *
  409. * @param string $x Field in string format to be restricted by IN() function.
  410. * @param mixed $y Argument to be used in IN() function.
  411. *
  412. * @return Expr\Func
  413. */
  414. public function in($x, $y)
  415. {
  416. if (is_array($y)) {
  417. foreach ($y as &$literal) {
  418. if ( ! ($literal instanceof Expr\Literal)) {
  419. $literal = $this->_quoteLiteral($literal);
  420. }
  421. }
  422. }
  423. return new Expr\Func($x . ' IN', (array) $y);
  424. }
  425. /**
  426. * Creates a NOT IN() expression with the given arguments.
  427. *
  428. * @param string $x Field in string format to be restricted by NOT IN() function.
  429. * @param mixed $y Argument to be used in NOT IN() function.
  430. *
  431. * @return Expr\Func
  432. */
  433. public function notIn($x, $y)
  434. {
  435. if (is_array($y)) {
  436. foreach ($y as &$literal) {
  437. if ( ! ($literal instanceof Expr\Literal)) {
  438. $literal = $this->_quoteLiteral($literal);
  439. }
  440. }
  441. }
  442. return new Expr\Func($x . ' NOT IN', (array) $y);
  443. }
  444. /**
  445. * Creates an IS NULL expression with the given arguments.
  446. *
  447. * @param string $x Field in string format to be restricted by IS NULL.
  448. *
  449. * @return string
  450. */
  451. public function isNull($x)
  452. {
  453. return $x . ' IS NULL';
  454. }
  455. /**
  456. * Creates an IS NOT NULL expression with the given arguments.
  457. *
  458. * @param string $x Field in string format to be restricted by IS NOT NULL.
  459. *
  460. * @return string
  461. */
  462. public function isNotNull($x)
  463. {
  464. return $x . ' IS NOT NULL';
  465. }
  466. /**
  467. * Creates a LIKE() comparison expression with the given arguments.
  468. *
  469. * @param string $x Field in string format to be inspected by LIKE() comparison.
  470. * @param mixed $y Argument to be used in LIKE() comparison.
  471. *
  472. * @return Expr\Comparison
  473. */
  474. public function like($x, $y)
  475. {
  476. return new Expr\Comparison($x, 'LIKE', $y);
  477. }
  478. /**
  479. * Creates a NOT LIKE() comparison expression with the given arguments.
  480. *
  481. * @param string $x Field in string format to be inspected by LIKE() comparison.
  482. * @param mixed $y Argument to be used in LIKE() comparison.
  483. *
  484. * @return Expr\Comparison
  485. */
  486. public function notLike($x, $y)
  487. {
  488. return new Expr\Comparison($x, 'NOT LIKE', $y);
  489. }
  490. /**
  491. * Creates a CONCAT() function expression with the given arguments.
  492. *
  493. * @param mixed $x First argument to be used in CONCAT() function.
  494. * @param mixed $y Second argument to be used in CONCAT() function.
  495. *
  496. * @return Expr\Func
  497. */
  498. public function concat($x, $y)
  499. {
  500. return new Expr\Func('CONCAT', array($x, $y));
  501. }
  502. /**
  503. * Creates a SUBSTRING() function expression with the given arguments.
  504. *
  505. * @param mixed $x Argument to be used as string to be cropped by SUBSTRING() function.
  506. * @param int $from Initial offset to start cropping string. May accept negative values.
  507. * @param int|null $len Length of crop. May accept negative values.
  508. *
  509. * @return Expr\Func
  510. */
  511. public function substring($x, $from, $len = null)
  512. {
  513. $args = array($x, $from);
  514. if (null !== $len) {
  515. $args[] = $len;
  516. }
  517. return new Expr\Func('SUBSTRING', $args);
  518. }
  519. /**
  520. * Creates a LOWER() function expression with the given argument.
  521. *
  522. * @param mixed $x Argument to be used in LOWER() function.
  523. *
  524. * @return Expr\Func A LOWER function expression.
  525. */
  526. public function lower($x)
  527. {
  528. return new Expr\Func('LOWER', array($x));
  529. }
  530. /**
  531. * Creates an UPPER() function expression with the given argument.
  532. *
  533. * @param mixed $x Argument to be used in UPPER() function.
  534. *
  535. * @return Expr\Func An UPPER function expression.
  536. */
  537. public function upper($x)
  538. {
  539. return new Expr\Func('UPPER', array($x));
  540. }
  541. /**
  542. * Creates a LENGTH() function expression with the given argument.
  543. *
  544. * @param mixed $x Argument to be used as argument of LENGTH() function.
  545. *
  546. * @return Expr\Func A LENGTH function expression.
  547. */
  548. public function length($x)
  549. {
  550. return new Expr\Func('LENGTH', array($x));
  551. }
  552. /**
  553. * Creates a literal expression of the given argument.
  554. *
  555. * @param mixed $literal Argument to be converted to literal.
  556. *
  557. * @return Expr\Literal
  558. */
  559. public function literal($literal)
  560. {
  561. return new Expr\Literal($this->_quoteLiteral($literal));
  562. }
  563. /**
  564. * Quotes a literal value, if necessary, according to the DQL syntax.
  565. *
  566. * @param mixed $literal The literal value.
  567. *
  568. * @return string
  569. */
  570. private function _quoteLiteral($literal)
  571. {
  572. if (is_numeric($literal) && !is_string($literal)) {
  573. return (string) $literal;
  574. } else if (is_bool($literal)) {
  575. return $literal ? "true" : "false";
  576. } else {
  577. return "'" . str_replace("'", "''", $literal) . "'";
  578. }
  579. }
  580. /**
  581. * Creates an instance of BETWEEN() function, with the given argument.
  582. *
  583. * @param mixed $val Valued to be inspected by range values.
  584. * @param integer $x Starting range value to be used in BETWEEN() function.
  585. * @param integer $y End point value to be used in BETWEEN() function.
  586. *
  587. * @return Expr\Func A BETWEEN expression.
  588. */
  589. public function between($val, $x, $y)
  590. {
  591. return $val . ' BETWEEN ' . $x . ' AND ' . $y;
  592. }
  593. /**
  594. * Creates an instance of TRIM() function, with the given argument.
  595. *
  596. * @param mixed $x Argument to be used as argument of TRIM() function.
  597. *
  598. * @return Expr\Func a TRIM expression.
  599. */
  600. public function trim($x)
  601. {
  602. return new Expr\Func('TRIM', $x);
  603. }
  604. }