PageRenderTime 46ms CodeModel.GetById 20ms app.highlight 18ms RepoModel.GetById 1ms app.codeStats 0ms

/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/**
 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}