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