File classes\QueryBuilder.php | PHPLucidFrame 3.3 API

1:	<?php
2:	/**
3:	* This file is part of the PHPLucidFrame library.
4:	* QueryBuilder class is responsible to dynamically create SQL queries.
5:	*
6:	* @package PHPLucidFrame\Core
7:	* @since PHPLucidFrame v 1.9.0
8:	* @copyright Copyright (c), PHPLucidFrame.
9:	* @link http://phplucidframe.com
10:	* @license http://www.opensource.org/licenses/mit-license.php MIT License
11:	*
12:	* This source file is subject to the MIT license that is bundled
13:	* with this source code in the file LICENSE
14:	*/
15:
16:	namespace LucidFrame\Core;
17:
18:	/**
19:	* QueryBuilder class is responsible to dynamically create SQL queries.
20:	*/
21:	class QueryBuilder
22:	{
23:	const EXP_CONDITION = '__QueryBuilder::condition__';
24:	const EXP_RAW = '__QueryBuilder::raw__';
25:
26:	/** @var string The table name */
27:	protected $table;
28:	/** @var string The alias for the table */
29:	protected $alias;
30:	/** @var array Collections of tables to join */
31:	protected $joins;
32:	/** @var array Collections of fields to select */
33:	protected $fields;
34:	/** @var array Collection of conditions */
35:	protected $where;
36:	/** @var array Collection of EXISTS clauses */
37:	protected $exist = array();
38:	/** @var array Collection of NOT EXISTS clauses */
39:	protected $notExist = array();
40:	/** @var array Collection of fields to order */
41:	protected $orderBy;
42:	/** @var array Collection of fields to group by */
43:	protected $groupBy;
44:	/** @var array Collection of fields for having conditions */
45:	protected $having;
46:	/** @var int The offset for LIMIT */
47:	protected $offset;
48:	/** @var int The row count for LIMIT */
49:	protected $limit;
50:	/** @var string The built SQL */
51:	protected $sql;
52:	/** @var array Collection of aggregates */
53:	protected $aggregates = array();
54:	/** @var resource The MySQL result resource */
55:	private $result;
56:	/** @var string AND/OR */
57:	private $whereType = 'AND';
58:	/** @var array The values to sql to bind */
59:	protected static $bindValues = array();
60:	/** @var array Collection of SQL operators allowed */
61:	private static $operators = array(
62:	'=', '>=', '<=', '>', '<', '!=', '<>',
63:	'not', 'in',
64:	'between', 'nbetween',
65:	'like', 'like%%', 'like%~', 'like~%',
66:	'nlike', 'nlike%%', 'nlike%~', 'nlike~%'
67:	);
68:	private static $eqs = array(
69:	'eq' => '=',
70:	'neq' => '!=',
71:	'lt' => '<',
72:	'lte' => '<=',
73:	'gt' => '>',
74:	'gte' => '>=',
75:	);
76:	/** @var array Collection of LIKE expressions */
77:	private static $likes = array(
78:	'like' => 'LIKE CONCAT("%", :placeholder, "%")',
79:	'like%~' => 'LIKE CONCAT("%", :placeholder)',
80:	'like~%' => 'LIKE CONCAT(:placeholder, "%")',
81:	'nlike' => 'NOT LIKE CONCAT("%", :placeholder, "%")',
82:	'nlike%~' => 'NOT LIKE CONCAT("%", :placeholder)',
83:	'nlike~%' => 'NOT LIKE CONCAT(:placeholder, "%")',
84:	);
85:	/** @var array Collection of BETWEEN operator mapping */
86:	private static $betweens = array(
87:	'between' => 'BETWEEN',
88:	'nbetween' => 'NOT BETWEEN',
89:	);
90:	/** @var array Collection of join types allowed */
91:	private static $joinTypes = array('INNER', 'LEFT', 'RIGHT', 'OUTER');
92:	/** @var array Collection of SQL functions allowed */
93:	private static $functions = array(
94:	'ABS', 'ADDDATE', 'ADDTIME', 'AVG',
95:	'CONCAT', 'COUNT', 'CUR_DATE', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP',
96:	'DATE', 'DATE_ADD', 'DATE_FORMAT', 'DATE_SUB', 'DATEDIFF',
97:	'DAY', 'DAYNAME', 'DAYOFMONTH', 'DAYOFWEEK', 'DAYOFYEAR',
98:	'LEFT', 'LENGTH', 'LOCATE', 'LOWER', 'LPAD', 'LTRIM', 'MAX', 'MIN', 'MOD', 'MONTH', 'MONTHNAME', 'NOW',
99:	'RIGHT', 'RPAD', 'RTRIM', 'SIZE', 'SQRT', 'SUBDATE', 'SUBSTR', 'SUBSTRING', 'SUBTIME', 'SUM',
100:	'TRIM', 'TIME', 'TIMEDIFF', 'TIMESTAMP', 'TIMESTAMPADD', 'TIMESTAMPDIFF',
101:	'UPPER', 'WEEK', 'WEEKDAY', 'WEEKOFYEAR', 'YEAR'
102:	);
103:
104:	/**
105:	* Constructor
106:	*
107:	* @param string $table The base table to select from
108:	* @param string $alias The alias for the table
109:	* @return void
110:	*/
111:	public function __construct($table = null, $alias = null)
112:	{
113:	self::clearBindValues();
114:
115:	$this->from($table, $alias);
116:	}
117:
118:	/**
119:	* Table to SELECT
120:	*
121:	* @param string $table The table name
122:	* @param string $alias The table alias
123:	*
124:	* @return object QueryBuilder
125:	*/
126:	public function from($table, $alias = null)
127:	{
128:	if (self::validateName($table) === false) {
129:	return $this;
130:	}
131:
132:	if ($this->alias && self::validateName($alias) === false) {
133:	$alias = $table;
134:	}
135:
136:	if ($alias === null) {
137:	$alias = $table;
138:	}
139:
140:	$this->table = db_table($table);
141:	$this->alias = $alias;
142:
143:	return $this;
144:	}
145:
146:	/**
147:	* Add fields to SELECT
148:	*
149:	* @param string $alias The table alias
150:	* @param array $fields Array of field names
151:	*
152:	* @return object QueryBuilder
153:	*/
154:	public function fields($alias, array $fields = array())
155:	{
156:	if (!$fields \|\| count($fields) === 0) {
157:	$fields = array('*');
158:	}
159:	$this->fields[$alias] = $fields;
160:
161:	return $this;
162:	}
163:
164:	/**
165:	* Add field to SELECT
166:	*
167:	* @param string $field The field name
168:	* @param array $alias The alias for the field name
169:	*
170:	* @return object QueryBuilder
171:	*/
172:	public function field($field, $alias = null)
173:	{
174:	$this->fields['*'][] = $alias ? array($field, $alias) : $field;
175:
176:	return $this;
177:	}
178:
179:	/**
180:	* Prepare field name ready for SELECT
181:	*
182:	* @param string $table The table alias
183:	* @param string $field The field name or array of field name and field alias
184:	*
185:	* @return string\|null
186:	*/
187:	private function prepareField($table, $field)
188:	{
189:	if ($table === '*') {
190:	return is_array($field) ? $field[0] . ' ' . $field[1] : $field;
191:	}
192:
193:	if ($field === '*') {
194:	return self::quote($table) . '.' . $field;
195:	} else {
196:	if (is_array($field)) {
197:	if (count($field) != 2) {
198:	return null;
199:	}
200:
201:	# field with alias
202:	list($fieldName, $alias) = $field;
203:
204:	$f = self::quote($fieldName);
205:	if (substr($f, 0, 1) !== '`') {
206:	if (self::isRawExp($f)) {
207:	$f = self::parseFromRawExp($f);
208:	}
209:
210:	return $f . ' AS ' . $alias;
211:	} else {
212:	if (self::isRawExp($f)) {
213:	return self::parseFromRawExp($f) . ' AS ' . self::quote($alias);
214:	}
215:
216:	return self::quote($table) . '.' . $f . ' AS ' . self::quote($alias);
217:	}
218:	} else {
219:	# field without alias
220:	$f = self::quote($field);
221:	if (substr($f, 0, 1) !== '`') {
222:	return self::isRawExp($f) ? self::parseFromRawExp($f) : $f;
223:	} else {
224:	if (self::isRawExp($f)) {
225:	return self::parseFromRawExp($f);
226:	}
227:
228:	return self::quote($table) . '.' . $f;
229:	}
230:	}
231:	}
232:	}
233:
234:	/**
235:	* Add a table to join
236:	*
237:	* @param string $table The table name
238:	* @param string $alias The alias for the table
239:	* @param string $condition The join condition e.g., t1.pk = t2.fk
240:	* @param string $type INNER, LEFT, RIGHT or OUTER
241:	*
242:	* @return object QueryBuilder
243:	*/
244:	public function join($table, $alias, $condition, $type = 'INNER')
245:	{
246:	if (self::validateName($table) === false \|\| self::validateName($alias) === false) {
247:	return $this;
248:	}
249:
250:	$type = strtoupper($type);
251:
252:	if (!in_array($type, self::$joinTypes)) {
253:	$type = 'INNER';
254:	}
255:
256:	$this->joins[] = array(
257:	'table' => db_table($table),
258:	'alias' => $alias === null ? $table : $alias,
259:	'condition' => $condition,
260:	'type' => $type
261:	);
262:
263:	return $this;
264:	}
265:
266:	/**
267:	* Add a table to perform left join
268:	*
269:	* @param string $table The table name
270:	* @param string $alias The alias for the table
271:	* @param string $condition The join condition e.g., t1.pk = t2.fk
272:	*
273:	* @return object QueryBuilder
274:	*/
275:	public function leftJoin($table, $alias, $condition)
276:	{
277:	$this->join($table, $alias, $condition, 'left');
278:
279:	return $this;
280:	}
281:
282:	/**
283:	* Add a table to perform right join
284:	*
285:	* @param string $table The table name
286:	* @param string $alias The alias for the table
287:	* @param string $condition The join condition e.g., t1.pk = t2.fk
288:	*
289:	* @return object QueryBuilder
290:	*/
291:	public function rightJoin($table, $alias, $condition)
292:	{
293:	$this->join($table, $alias, $condition, 'right');
294:
295:	return $this;
296:	}
297:
298:	/**
299:	* Add a table to perform outer join
300:	*
301:	* @param string $table The table name
302:	* @param string $alias The alias for the table
303:	* @param string $condition The join condition e.g., t1.pk = t2.fk
304:	*
305:	* @return object QueryBuilder
306:	*/
307:	public function outerJoin($table, $alias, $condition)
308:	{
309:	$this->join($table, $alias, $condition, 'outer');
310:
311:	return $this;
312:	}
313:
314:	/**
315:	* Alias of `andWhere()`
316:	* Create WHERE ... AND condition
317:	*
318:	* @param array\|null $condition The array of conditions
319:	* @return object QueryBuilder
320:	*/
321:	public function where($condition = null)
322:	{
323:	return $this->andWhere($condition);
324:	}
325:
326:	/**
327:	* Create WHERE ... AND condition
328:	*
329:	* @param array\|null $condition The array of conditions
330:	*
331:	* array(
332:	* 'fieldName1' => $value1,
333:	* 'fieldName2 >=' => $value2,
334:	* 'fieldName3 => NULL
335:	* )
336:	*
337:	* OR
338:	*
339:	* array(
340:	* 'fieldName1' => $value1,
341:	* 'fieldName2 >=' => $value2,
342:	* 'fieldName3 => NULL,
343:	* '$or' => array(
344:	* 'fieldName4' => array(1, 2, 3)
345:	* 'fieldName4 <' => 10
346:	* )
347:	* )
348:	*
349:	* @return object QueryBuilder
350:	*/
351:	public function andWhere($condition = null)
352:	{
353:	if (is_array($condition) && count($condition) == 0) {
354:	return $this;
355:	}
356:
357:	if ($condition === null) {
358:	$this->where['AND'] = array();
359:	} else {
360:	$this->where['AND'][] = self::buildCondition($condition, 'AND');
361:	}
362:	$this->whereType = 'AND';
363:
364:	return $this;
365:	}
366:
367:	/**
368:	* Create WHERE ... OR condition
369:	*
370:	* @param array\|null $condition The array of conditions
371:	*
372:	* array(
373:	* 'fieldName1' => $value1,
374:	* 'fieldName2 >=' => $value2,
375:	* 'fieldName3 => NULL
376:	* )
377:	*
378:	* OR
379:	*
380:	* array(
381:	* 'fieldName1' => $value1,
382:	* 'fieldName2 >=' => $value2,
383:	* 'fieldName3 => NULL,
384:	* '$and' => array(
385:	* 'fieldName4' => array(1, 2, 3)
386:	* 'fieldName4 <' => 10
387:	* )
388:	* )
389:	*
390:	* @return object QueryBuilder
391:	*/
392:	public function orWhere($condition = null)
393:	{
394:	if (is_array($condition) && count($condition) == 0) {
395:	return $this;
396:	}
397:
398:	if ($condition === null) {
399:	$this->where['OR'] = array();
400:	} else {
401:	$this->where['OR'][] = self::buildCondition($condition, 'OR');
402:	}
403:	$this->whereType = 'OR';
404:
405:	return $this;
406:	}
407:
408:	/**
409:	* Create simple WHERE condition with field/value assignment
410:	*
411:	* @param string $field The field name
412:	* @param mixed $value The value to check against the field name
413:	*
414:	* $qb = db_select('post', 'p')
415:	* ->orWhere()
416:	* ->condition('catId', 1)
417:	* ->condition('catId', 2);
418:	*
419:	* @return object QueryBuilder
420:	*/
421:	public function condition($field, $value)
422:	{
423:	if (isset($this->where[$this->whereType][$field])) {
424:	$field .= uniqid('__' . trim(__METHOD__, 'LucidFrame\Core') . '__');
425:	}
426:	$this->where[$this->whereType][$field] = $value;
427:
428:	return $this;
429:	}
430:
431:	/**
432:	* Add EXISTS clause to WHERE condition
433:	* @param string $subquery The sub-query statement
434:	* @param string $type AND\|OR
435:	* @return object QueryBuilder
436:	*/
437:	public function exists($subquery, $type = 'AND')
438:	{
439:	$this->exist[] = array(
440:	'query' => self::raw($subquery),
441:	'type' => strtoupper($type)
442:	);
443:
444:	return $this;
445:	}
446:
447:	/**
448:	* Add NOT EXISTS clause to WHERE condition
449:	* @param string $subquery The sub-query statement
450:	* @param string $type AND\|OR
451:	* @return object QueryBuilder
452:	*/
453:	public function notExists($subquery, $type = 'AND')
454:	{
455:	$this->notExist[] = array(
456:	'query' => self::raw($subquery),
457:	'type' => strtoupper($type)
458:	);
459:
460:	return $this;
461:	}
462:
463:	/**
464:	* Add `OR EXISTS` clause to WHERE condition
465:	* @param string $subquery The sub-query statement
466:	* @return object QueryBuilder
467:	*/
468:	public function orExists($subquery)
469:	{
470:	return $this->exists($subquery, 'OR');
471:	}
472:
473:	/**
474:	* Add `OR NOT EXISTS` clause to WHERE condition
475:	* @param string $subquery The sub-query statement
476:	* @return object QueryBuilder
477:	*/
478:	public function orNotExists($subquery)
479:	{
480:	return $this->notExists($subquery, 'OR');
481:	}
482:
483:	/**
484:	* Add ORDER BY clause
485:	*
486:	* @param string $field The field name to sort
487:	* @param string $sort ASC or DESC
488:	*
489:	* @return object QueryBuilder
490:	*/
491:	public function orderBy($field, $sort = 'ASC')
492:	{
493:	$sort = strtoupper($sort);
494:	if (!in_array($sort, array('ASC', 'DESC'))) {
495:	$sort = 'ASC';
496:	}
497:	$this->orderBy[$field] = $sort;
498:
499:	return $this;
500:	}
501:
502:	/**
503:	* Add GROUP BY clause
504:	*
505:	* @param string $field The field name
506:	*
507:	* @return object QueryBuilder
508:	*/
509:	public function groupBy($field)
510:	{
511:	$this->groupBy[] = $field;
512:	$this->groupBy = array_unique($this->groupBy);
513:
514:	return $this;
515:	}
516:
517:	/**
518:	* Create HAVING ... condition
519:	*
520:	* @param array $condition The array of conditions
521:	*
522:	* array(
523:	* 'fieldName1' => $value1,
524:	* 'fieldName2 >=' => $value2,
525:	* 'fieldName3 => NULL
526:	* )
527:	*
528:	* OR
529:	*
530:	* array(
531:	* 'fieldName1' => $value1,
532:	* 'fieldName2 >=' => $value2,
533:	* 'fieldName3 => NULL,
534:	* '$or' => array(
535:	* 'fieldName4' => array(1, 2, 3)
536:	* 'fieldName4 <' => 10
537:	* )
538:	* )
539:	*
540:	* @return object QueryBuilder
541:	*/
542:	public function having(array $condition)
543:	{
544:	return $this->andHaving($condition);
545:	}
546:
547:	/**
548:	* Create AND HAVING ... condition
549:	* @param array $condition The array of conditions
550:	* @return object QueryBuilder
551:	* @see having()
552:	*/
553:	public function andHaving(array $condition)
554:	{
555:	return $this->addHaving($condition, 'AND');
556:	}
557:
558:	/**
559:	* Create OR HAVING ... condition
560:	* @param array $condition The array of conditions
561:	* @return object QueryBuilder
562:	* @see having()
563:	*/
564:	public function orHaving(array $condition = array())
565:	{
566:	return $this->addHaving($condition, 'OR');
567:	}
568:
569:	/**
570:	* @internal
571:	* Create AND/OR HAVING ... condition
572:	* @param array $condition The array of conditions
573:	* @param string $type AND\|OR
574:	* @return object QueryBuilder
575:	*/
576:	private function addHaving(array $condition, $type)
577:	{
578:	list($clause, $values) = self::buildCondition($condition, $type);
579:
580:	$this->having = $clause;
581:	self::addBindValues($values);
582:
583:	return $this;
584:	}
585:
586:	/**
587:	* Add LIMIT clause
588:	* @param int argument1 The offset
589:	* @param int argument2 The row count
590:	*
591:	* OR
592:	*
593:	* @param int argument1 The row count
594:	* @return object QueryBuilder
595:	*/
596:	public function limit()
597:	{
598:	$args = func_get_args();
599:	if (count($args) === 2 && is_numeric($args[0]) && is_numeric($args[1])) {
600:	$this->offset = $args[0];
601:	$this->limit = $args[1];
602:	} elseif (count($args) === 1 && is_numeric($args[0])) {
603:	$this->limit = $args[0];
604:	}
605:
606:	return $this;
607:	}
608:
609:	/**
610:	* Add COUNT(*) or COUNT(field)
611:	*
612:	* @param string $field The field name
613:	* @param string $alias The alias field name to retrieve
614:	*
615:	* @return object QueryBuilder
616:	*/
617:	public function count($field = null, $alias = null)
618:	{
619:	$this->setAggregate('count', $field, $alias);
620:
621:	return $this;
622:	}
623:
624:	/**
625:	* Add MAX(field)
626:	*
627:	* @param string $field The field name
628:	* @param string $alias The alias field name to retrieve
629:	*
630:	* @return object QueryBuilder
631:	*/
632:	public function max($field, $alias = null)
633:	{
634:	$this->setAggregate('max', $field, $alias);
635:
636:	return $this;
637:	}
638:
639:	/**
640:	* Add MIN(field)
641:	*
642:	* @param string $field The field name
643:	* @param string $alias The alias field name to retrieve
644:	*
645:	* @return object QueryBuilder
646:	*/
647:	public function min($field, $alias = null)
648:	{
649:	$this->setAggregate('min', $field, $alias);
650:
651:	return $this;
652:	}
653:
654:	/**
655:	* Add SUM(field)
656:	*
657:	* @param string $field The field name
658:	* @param string $alias The alias field name to retrieve
659:	*
660:	* @return object QueryBuilder
661:	*/
662:	public function sum($field, $alias = null)
663:	{
664:	$this->setAggregate('sum', $field, $alias);
665:
666:	return $this;
667:	}
668:
669:	/**
670:	* Add AVG(field)
671:	*
672:	* @param string $field The field name
673:	* @param string $alias The alias field name to retrieve
674:	*
675:	* @return object QueryBuilder
676:	*/
677:	public function avg($field, $alias = null)
678:	{
679:	$this->setAggregate('avg', $field, $alias);
680:
681:	return $this;
682:	}
683:
684:	/**
685:	* Aggregation
686:	*
687:	* @param string $name The function name COUNT, MAX, MIN, SUM, AVG, etc.
688:	* @param string $field The field name
689:	* @param string $alias The alias field name to retrieve
690:	*
691:	* @return object QueryBuilder
692:	*/
693:	protected function setAggregate($name, $field = null, $alias = null)
694:	{
695:	if (!isset($this->aggregates[$name])) {
696:	$this->aggregates[$name] = array();
697:	}
698:	$field = ($field === null) ? '*' : $field;
699:	$this->aggregates[$name][$field] = ($alias === null) ? $field : array($field, $alias);
700:
701:	return $this;
702:	}
703:
704:	/**
705:	* Build SQL
706:	*
707:	* @return object QueryBuilder
708:	*/
709:	protected function buildSQL()
710:	{
711:	$sql = 'SELECT ';
712:	# SELECT fields
713:	$select = array();
714:	if ($this->fields) {
715:	foreach ($this->fields as $tableAlias => $field) {
716:	foreach ($field as $f) {
717:	$readyField = $this->prepareField($tableAlias, $f);
718:	if ($readyField) {
719:	$select[] = $readyField;
720:	}
721:	}
722:	}
723:	}
724:
725:	if (count($this->aggregates)) {
726:	foreach ($this->aggregates as $func => $fields) {
727:	$func = strtoupper($func);
728:	foreach ($fields as $field) {
729:	if (is_array($field)) {
730:	$select[] = $func . '(' . self::quote($field[0]) . ') ' . self::quote($field[1]);
731:	} else {
732:	$select[] = $func . '(' . self::quote($field) . ')';
733:	}
734:	}
735:	}
736:	}
737:
738:	if (count($select) === 0) {
739:	$select = array(self::quote($this->alias) . '.*');
740:	}
741:
742:	$sql .= implode(', ', $select);
743:
744:	# FROM clause
745:	$sql .= ' FROM ' . self::quote($this->table) . ' ' . self::quote($this->alias);
746:
747:	# JOIN clause
748:	if ($this->joins) {
749:	$joins = array();
750:	foreach ($this->joins as $join) {
751:	$join = (object)$join;
752:	if (preg_match_all('/([a-z0-9_]+\.[a-z0-9_]+)/i', $join->condition, $matches)) {
753:	$matchedFields = array_unique($matches[0]);
754:	foreach ($matchedFields as $field) {
755:	$join->condition = str_replace($field, self::quote($field), $join->condition);
756:	}
757:	}
758:	$joins[] = $join->type . ' JOIN '
759:	. self::quote($join->table) . ' ' . self::quote($join->alias)
760:	. ' ON ' . $join->condition;
761:	}
762:	$sql .= ' ' . implode(' ', $joins);
763:	}
764:
765:	# WHERE clause
766:	if ($this->where) {
767:	if (is_array($this->where)) {
768:	if (array_key_exists('AND', $this->where)) {
769:	list($clause, $values) = self::buildCondition($this->where['AND'], 'AND');
770:	$sql .= ' WHERE ' . $clause;
771:	self::addBindValues($values);
772:	} elseif (array_key_exists('OR', $this->where)) {
773:	list($clause, $values) = self::buildCondition($this->where['OR'], 'OR');
774:	$sql .= ' WHERE ' . $clause;
775:	self::addBindValues($values);
776:	} elseif (array_key_exists('NOT', $this->where)) {
777:	list($clause, $values) = self::buildCondition($this->where['NOT'], 'NOT');
778:	$sql .= ' WHERE ' . $clause;
779:	self::addBindValues($values);
780:	}
781:	} else {
782:	$sql .= ' WHERE ' . $this->where;
783:	}
784:	}
785:
786:	# EXISTS clause
787:	$exists = array();
788:	if (!empty($this->exist)) {
789:	foreach ($this->exist as $exist) {
790:	$subquery = self::isRawExp($exist['query']) ? self::parseFromRawExp($exist['query']) : $exist['query'];
791:	$exists[] = " $exist[type] EXISTS ($subquery)";
792:	}
793:	}
794:
795:	# NOT EXISTS clause
796:	if (!empty($this->notExist)) {
797:	foreach ($this->notExist as $exist) {
798:	$subquery = self::isRawExp($exist['query']) ? self::parseFromRawExp($exist['query']) : $exist['query'];
799:	$exists[] = " $exist[type] NOT EXISTS ($subquery)";
800:	}
801:	}
802:
803:	$sql = $this->appendExistClauses($exists, $sql);
804:
805:	# GROUP BY clause
806:	if ($this->groupBy) {
807:	$groupBy = array();
808:	foreach ($this->groupBy as $field) {
809:	if (self::isRawExp($field)) {
810:	$groupBy[] = self::parseFromRawExp($field);
811:	continue;
812:	}
813:
814:	$groupBy[] = self::quote($field);
815:	}
816:	$sql .= ' GROUP BY ' . implode(', ', $groupBy);
817:	}
818:
819:	# HAVING clause
820:	if ($this->having) {
821:	$sql .= ' HAVING ' . $this->having;
822:	}
823:
824:	# ORDER BY clause
825:	if ($this->orderBy) {
826:	$orderBy = array();
827:	foreach ($this->orderBy as $field => $sort) {
828:	if (self::isRawExp($field)) {
829:	$orderBy[] = self::parseFromRawExp($field);
830:	continue;
831:	}
832:
833:	$orderBy[] = self::quote($field) . ' ' . $sort;
834:	}
835:	$sql .= ' ORDER BY ' . implode(', ', $orderBy);
836:	}
837:
838:	# LIMIT clause
839:	if ($this->offset !== null && $this->limit) {
840:	$sql .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
841:	} elseif ($this->limit && $this->offset === null) {
842:	$sql .= ' LIMIT ' . $this->limit;
843:	}
844:
845:	$this->sql = $sql;
846:
847:	return $this;
848:	}
849:
850:	/**
851:	* Append EXISTS clauses to the SQL statement building
852:	* @param array $exists Array of exists clauses
853:	* @param string $sql The original SQL statement to be appended
854:	* @return string
855:	*/
856:	protected function appendExistClauses(array $exists, $sql)
857:	{
858:	if (!count($exists)) {
859:	return $sql;
860:	}
861:
862:	$clause = implode('', $exists);
863:	if (!empty($this->where)) {
864:	// if there is already WHERE clause in the statement
865:	$sql .= $clause;
866:	} else {
867:	// if there is no WHERE clause in the statement
868:	$clause = preg_replace('/^(AND\|OR)\s+/', '', trim($clause));
869:	$sql .= ' WHERE ' . $clause;
870:	}
871:
872:	return $sql;
873:	}
874:
875:	/**
876:	* Execute the query
877:	*
878:	* @return bool\|resource The result
879:	*/
880:	public function execute()
881:	{
882:	$this->buildSQL();
883:
884:	if ($this->sql) {
885:	$this->result = db_query($this->sql, self::$bindValues);
886:	}
887:
888:	self::clearBindValues();
889:
890:	return $this->result;
891:	}
892:
893:	/**
894:	* Get the number of rows in the query result
895:	* @return int Returns the number of rows in the result set.
896:	*/
897:	public function getNumRows()
898:	{
899:	if ($this->result === null) {
900:	$this->execute();
901:	}
902:
903:	if ($this->result) {
904:	return db_numRows($this->result);
905:	}
906:
907:	return 0;
908:	}
909:
910:	/**
911:	* Fetch a query result row
912:	*
913:	* @param int $resultType The optional constant indicating what type of array should be produced.
914:	* The possible values for this parameter are the constants
915:	* LC_FETCH_OBJECT, LC_FETCH_ASSOC, or LC_FETCH_ARRAY.
916:	* Default to LC_FETCH_OBJECT.
917:	*
918:	* @return mixed
919:	*/
920:	public function fetchRow($resultType = LC_FETCH_OBJECT)
921:	{
922:	if ($this->result === null) {
923:	$this->execute();
924:	}
925:
926:	if ($this->result) {
927:	if ($row = db_fetchAssoc($this->result)) {
928:	if ($resultType === LC_FETCH_ARRAY) {
929:	return array_values($row);
930:	} elseif ($resultType === LC_FETCH_OBJECT) {
931:	return (object)$row;
932:	} else {
933:	return $row;
934:	}
935:	}
936:	}
937:
938:	return null;
939:	}
940:
941:	/**
942:	* Perform a query on the database and return the array of all results
943:	*
944:	* @return array The result array of objects.
945:	* If the result not found, return null.
946:	*/
947:	public function getResult()
948:	{
949:	if ($this->result === null) {
950:	$this->execute();
951:	}
952:
953:	$data = array();
954:	if ($this->result) {
955:	while ($row = db_fetchObject($this->result)) {
956:	$data[] = $row;
957:	}
958:	}
959:
960:	return $data;
961:	}
962:
963:	/**
964:	* Perform a query on the database and return the result object
965:	*
966:	* @return object\|null The result object
967:	* If the result not found, return null.
968:	*/
969:	public function getSingleResult()
970:	{
971:	$this->limit(1);
972:
973:	if ($this->result === null) {
974:	$this->execute();
975:	}
976:
977:	if ($row = db_fetchObject($this->result)) {
978:	return $row;
979:	}
980:
981:	return null;
982:	}
983:
984:	/**
985:	* Perform a query on the database and fetch one field only
986:	*
987:	* @return mixed The result
988:	* If the result not found, return null.
989:	*/
990:	public function fetch()
991:	{
992:	$this->limit(1);
993:
994:	if ($this->result === null) {
995:	$this->execute();
996:	}
997:
998:	if ($this->result && $row = db_fetchArray($this->result)) {
999:	return $row[0];
1000:	}
1001:
1002:	return null;
1003:	}
1004:
1005:	/**
1006:	* Get the built SQL
1007:	*
1008:	* @return string
1009:	*/
1010:	public function getSQL()
1011:	{
1012:	if ($this->sql === null) {
1013:	$this->buildSQL();
1014:	}
1015:
1016:	return $this->sql;
1017:	}
1018:
1019:	/**
1020:	* Get the built SQL with the values replaced
1021:	* @return string
1022:	*/
1023:	public function getReadySQL()
1024:	{
1025:	$sql = $this->getSQL();
1026:
1027:	foreach (QueryBuilder::getBindValues() as $key => $value) {
1028:	$sql = preg_replace('/' . $key . '\b/', $value, $sql);
1029:	}
1030:
1031:	return $sql;
1032:	}
1033:
1034:	/**
1035:	* Validate table name or field name
1036:	*
1037:	* @param string $name The table name or field name to be validated
1038:	* @return boolean
1039:	*/
1040:	public static function validateName($name)
1041:	{
1042:	if (!is_string($name)) {
1043:	return false;
1044:	}
1045:
1046:	return preg_match('/^[A-Za-z0-9_]+$/', $name);
1047:	}
1048:
1049:	/**
1050:	* Quote table name and field name
1051:	*
1052:	* @param string $name The table name or field name or table.field
1053:	* @return string
1054:	*/
1055:	public static function quote($name)
1056:	{
1057:	$name = trim($name);
1058:
1059:	if ($name === '*' \|\| self::isRawExp($name)) {
1060:	return $name;
1061:	}
1062:
1063:	foreach (self::$functions as $func) {
1064:	if (stripos($name, $func) === 0) {
1065:	return $name;
1066:	}
1067:	}
1068:
1069:	if (strpos($name, '.') !== false) {
1070:	$name = str_replace('.', '`.`', $name);
1071:	}
1072:
1073:	return '`' . $name . '`';
1074:	}
1075:
1076:	/**
1077:	* Create raw expression string
1078:	* @param string $expression
1079:	* @param array $values The values to be replaced with specifier in $expression. See vsprintf.
1080:	* @return string
1081:	*/
1082:	public static function raw($expression, array $values = array())
1083:	{
1084:	return vsprintf(self::EXP_RAW . $expression, $values);
1085:	}
1086:
1087:	/**
1088:	* Check if field is raw expression
1089:	* @param string $field
1090:	* @return bool
1091:	*/
1092:	private static function isRawExp($field)
1093:	{
1094:	return is_string($field) && strpos($field, self::EXP_RAW) !== false;
1095:	}
1096:
1097:	/**
1098:	* Parse field from raw expression
1099:	* @param string $field
1100:	* @return false\|string
1101:	*/
1102:	private static function parseFromRawExp($field)
1103:	{
1104:	if (self::isRawExp($field)) {
1105:	return substr($field, strlen(self::EXP_RAW));
1106:	}
1107:
1108:	return $field;
1109:	}
1110:
1111:	/**
1112:	* Build the SQL WHERE clause from the various condition arrays
1113:	*
1114:	* @param array $cond The condition array, for example
1115:	*
1116:	* array(
1117:	* 'fieldName1' => $value1,
1118:	* 'fieldName2 >=' => $value2,
1119:	* 'fieldName3 => NULL
1120:	* )
1121:	*
1122:	* @param string $type The condition type "AND" or "OR"; Default is "AND"
1123:	*
1124:	* @return array The built condition WHERE AND/OR
1125:	* [0] string The built condition WHERE AND/OR clause
1126:	* [1] array The values to bind in the condition
1127:	*/
1128:	public static function buildCondition($cond = array(), $type = 'AND')
1129:	{
1130:	if (!is_array($cond)) {
1131:	return $cond;
1132:	}
1133:
1134:	if (empty($cond)) {
1135:	return array('', array());
1136:	}
1137:
1138:	$type = strtoupper($type);
1139:	$condition = array();
1140:
1141:	foreach ($cond as $field => $value) {
1142:	$field = trim($field);
1143:	if (self::isRawExp($value)) {
1144:	$value = self::quote(self::parseFromRawExp($value));
1145:	}
1146:
1147:	if (in_array(strtolower($field), array('$and', '$or', '$not'))) {
1148:	$field = substr($field, 1);
1149:	if (strtoupper($field) == 'NOT') {
1150:	list($nestedClause, $values) = self::buildCondition($value, 'AND');
1151:	$condition[] = 'NOT (' . $nestedClause . ')';
1152:	} else {
1153:	list($nestedClause, $values) = self::buildCondition($value, $field);
1154:	$condition[] = '(' . $nestedClause . ')';
1155:	}
1156:	self::addBindValues($values);
1157:	continue;
1158:	}
1159:
1160:	$fieldOpr = explode(' ', $field);
1161:	$field = trim($fieldOpr[0]);
1162:
1163:	if (strpos($field, self::EXP_CONDITION) !== false) {
1164:	$field = substr($field, 0, strpos($field, self::EXP_CONDITION));
1165:	}
1166:
1167:	$opr = count($fieldOpr) === 2 ? trim($fieldOpr[1]) : '=';
1168:	$opr = strtolower($opr);
1169:
1170:	# check if any operator is given in the field
1171:	if (!in_array($opr, self::$operators)) {
1172:	$opr = '=';
1173:	}
1174:
1175:	if (is_numeric($field)) {
1176:	# if the field is array index,
1177:	# assuming that is a condition built by db_or() or db_and();
1178:	list($nestedClause, $values) = $value;
1179:	$condition[] = '( ' . $nestedClause . ' )';
1180:	self::addBindValues($values);
1181:	} else {
1182:	# if the operator is "between", the value must be array
1183:	# otherwise force to "="
1184:	if (in_array($opr, array('between', 'nbetween')) && !is_array($value)) {
1185:	$opr = '=';
1186:	}
1187:
1188:	$key = $field;
1189:	$placeholder = self::getPlaceholder($key, self::$bindValues);
1190:	$field = self::quote($field);
1191:
1192:	if ($opr == 'in') {
1193:	if (self::isRawExp($value)) {
1194:	$condition[] = $field . ' IN (' . self::parseFromRawExp($value) . ')';
1195:	} else {
1196:	$condition[] = $field . ' IN (' . $placeholder . ')';
1197:	self::setBindValue($placeholder, is_array($value) ? implode(', ', $value) : $value);
1198:	}
1199:	continue;
1200:	}
1201:
1202:	if (array_key_exists($opr, self::$likes)) {
1203:	$condition[] = $field . ' ' . str_replace(':placeholder', $placeholder, self::$likes[$opr]);
1204:	self::setBindValue($placeholder, $value);
1205:	continue;
1206:	}
1207:
1208:	if (is_null($value)) {
1209:	if (in_array($opr, array('!=', '<>'))) {
1210:	$condition[] = $field . ' IS NOT NULL';
1211:	} else {
1212:	$condition[] = $field . ' IS NULL';
1213:	}
1214:	continue;
1215:	}
1216:
1217:	if (is_array($value) && count($value)) {
1218:	if ($opr === 'between' \|\| $opr === 'nbetween') {
1219:	$condition[] = sprintf(
1220:	'(%s %s :%s_from AND :%s_to)',
1221:	$field,
1222:	self::$betweens[$opr],
1223:	$key,
1224:	$key
1225:	);
1226:
1227:	self::setBindValue($placeholder . '_from', $value[0]);
1228:	self::setBindValue($placeholder . '_to', $value[1]);
1229:	} else {
1230:	$inPlaceholders = array();
1231:	foreach ($value as $i => $val) {
1232:	$holder = preg_replace('/(\d)*/', '', $placeholder);
1233:	$holder = $holder . $i;
1234:	$inPlaceholders[] = $holder;
1235:	self::setBindValue($holder, $val);
1236:	}
1237:
1238:	$condition[] = sprintf(
1239:	'%s%sIN (%s)',
1240:	$field,
1241:	$opr === '!=' ? ' NOT ' : ' ',
1242:	implode(', ', $inPlaceholders)
1243:	);
1244:	}
1245:	continue;
1246:	}
1247:
1248:	$condition[] = "{$field} {$opr} {$placeholder}";
1249:	self::setBindValue($placeholder, $value);
1250:	}
1251:	}
1252:
1253:	if (count($condition)) {
1254:	return array(
1255:	implode(" {$type} ", $condition),
1256:	self::$bindValues,
1257:	);
1258:	}
1259:
1260:	return array('', array());
1261:	}
1262:
1263:	private static function getPlaceholder($key, $values = array())
1264:	{
1265:	$specChars = array(
1266:	'`','~','!','@','#','$','%','\^','&',
1267:	'*','(',')','=','+','{','}','[',']',
1268:	':',';',"'",'"','<','>','\\','\|','?','/',',','.'
1269:	);
1270:
1271:	$key = str_replace($specChars, '_', $key);
1272:
1273:	$placeholders = array_filter($values, function ($placeholder) use ($key) {
1274:	return stripos($placeholder, $key) === 1;
1275:	}, ARRAY_FILTER_USE_KEY);
1276:
1277:	if (!count($placeholders)) {
1278:	return ':' . $key;
1279:	}
1280:
1281:	$placeholders = array_keys($placeholders);
1282:	rsort($placeholders);
1283:
1284:	$index = '';
1285:	if (preg_match('/:' . $key . '(\d)*/', $placeholders[0], $matches)) {
1286:	$index = isset($matches[1]) ? $matches[1] + 1 : 0;
1287:	}
1288:
1289:	return ':' . $key . $index;
1290:	}
1291:
1292:	/**
1293:	* Bind values for query arguments
1294:	* @param array $values
1295:	*/
1296:	private static function addBindValues(array $values)
1297:	{
1298:	self::$bindValues = array_merge(self::$bindValues, $values);
1299:	}
1300:
1301:	/**
1302:	* Bind value for query argument by key
1303:	* @param string $key
1304:	* @param mixed $value
1305:	*/
1306:	private static function setBindValue($key, $value)
1307:	{
1308:	self::$bindValues[$key] = $value;
1309:	}
1310:
1311:	/**
1312:	* Clear bind values
1313:	*/
1314:	public static function clearBindValues()
1315:	{
1316:	self::$bindValues = array();
1317:	}
1318:
1319:	/**
1320:	* Get bind values
1321:	* @return array
1322:	*/
1323:	public static function getBindValues()
1324:	{
1325:	return self::$bindValues;
1326:	}
1327:	}
1328:

Namespaces

Classes