File classes\QueryBuilder.php | PHPLucidFrame 3.5 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 ORDER BY RAND() clause
504:	*
505:	* @return object QueryBuilder
506:	*/
507:	public function orderRand()
508:	{
509:	return $this->orderBy(db_raw('RAND()'));
510:	}
511:
512:	/**
513:	* Add GROUP BY clause
514:	*
515:	* @param string $field The field name
516:	*
517:	* @return object QueryBuilder
518:	*/
519:	public function groupBy($field)
520:	{
521:	$this->groupBy[] = $field;
522:	$this->groupBy = array_unique($this->groupBy);
523:
524:	return $this;
525:	}
526:
527:	/**
528:	* Create HAVING ... condition
529:	*
530:	* @param array $condition The array of conditions
531:	*
532:	* array(
533:	* 'fieldName1' => $value1,
534:	* 'fieldName2 >=' => $value2,
535:	* 'fieldName3 => NULL
536:	* )
537:	*
538:	* OR
539:	*
540:	* array(
541:	* 'fieldName1' => $value1,
542:	* 'fieldName2 >=' => $value2,
543:	* 'fieldName3 => NULL,
544:	* '$or' => array(
545:	* 'fieldName4' => array(1, 2, 3)
546:	* 'fieldName4 <' => 10
547:	* )
548:	* )
549:	*
550:	* @return object QueryBuilder
551:	*/
552:	public function having(array $condition)
553:	{
554:	return $this->andHaving($condition);
555:	}
556:
557:	/**
558:	* Create AND HAVING ... condition
559:	* @param array $condition The array of conditions
560:	* @return object QueryBuilder
561:	* @see having()
562:	*/
563:	public function andHaving(array $condition)
564:	{
565:	return $this->addHaving($condition, 'AND');
566:	}
567:
568:	/**
569:	* Create OR HAVING ... condition
570:	* @param array $condition The array of conditions
571:	* @return object QueryBuilder
572:	* @see having()
573:	*/
574:	public function orHaving(array $condition = array())
575:	{
576:	return $this->addHaving($condition, 'OR');
577:	}
578:
579:	/**
580:	* @internal
581:	* Create AND/OR HAVING ... condition
582:	* @param array $condition The array of conditions
583:	* @param string $type AND\|OR
584:	* @return object QueryBuilder
585:	*/
586:	private function addHaving(array $condition, $type)
587:	{
588:	list($clause, $values) = self::buildCondition($condition, $type);
589:
590:	$this->having = $clause;
591:	self::addBindValues($values);
592:
593:	return $this;
594:	}
595:
596:	/**
597:	* Add LIMIT clause
598:	* @param int argument1 The offset
599:	* @param int argument2 The row count
600:	*
601:	* OR
602:	*
603:	* @param int argument1 The row count
604:	* @return object QueryBuilder
605:	*/
606:	public function limit()
607:	{
608:	$args = func_get_args();
609:	if (count($args) === 2 && is_numeric($args[0]) && is_numeric($args[1])) {
610:	$this->offset = $args[0];
611:	$this->limit = $args[1];
612:	} elseif (count($args) === 1 && is_numeric($args[0])) {
613:	$this->limit = $args[0];
614:	}
615:
616:	return $this;
617:	}
618:
619:	/**
620:	* Add COUNT(*) or COUNT(field)
621:	*
622:	* @param string $field The field name
623:	* @param string $alias The alias field name to retrieve
624:	*
625:	* @return object QueryBuilder
626:	*/
627:	public function count($field = null, $alias = null)
628:	{
629:	$this->setAggregate('count', $field, $alias);
630:
631:	return $this;
632:	}
633:
634:	/**
635:	* Add MAX(field)
636:	*
637:	* @param string $field The field name
638:	* @param string $alias The alias field name to retrieve
639:	*
640:	* @return object QueryBuilder
641:	*/
642:	public function max($field, $alias = null)
643:	{
644:	$this->setAggregate('max', $field, $alias);
645:
646:	return $this;
647:	}
648:
649:	/**
650:	* Add MIN(field)
651:	*
652:	* @param string $field The field name
653:	* @param string $alias The alias field name to retrieve
654:	*
655:	* @return object QueryBuilder
656:	*/
657:	public function min($field, $alias = null)
658:	{
659:	$this->setAggregate('min', $field, $alias);
660:
661:	return $this;
662:	}
663:
664:	/**
665:	* Add SUM(field)
666:	*
667:	* @param string $field The field name
668:	* @param string $alias The alias field name to retrieve
669:	*
670:	* @return object QueryBuilder
671:	*/
672:	public function sum($field, $alias = null)
673:	{
674:	$this->setAggregate('sum', $field, $alias);
675:
676:	return $this;
677:	}
678:
679:	/**
680:	* Add AVG(field)
681:	*
682:	* @param string $field The field name
683:	* @param string $alias The alias field name to retrieve
684:	*
685:	* @return object QueryBuilder
686:	*/
687:	public function avg($field, $alias = null)
688:	{
689:	$this->setAggregate('avg', $field, $alias);
690:
691:	return $this;
692:	}
693:
694:	/**
695:	* Aggregation
696:	*
697:	* @param string $name The function name COUNT, MAX, MIN, SUM, AVG, etc.
698:	* @param string $field The field name
699:	* @param string $alias The alias field name to retrieve
700:	*
701:	* @return object QueryBuilder
702:	*/
703:	protected function setAggregate($name, $field = null, $alias = null)
704:	{
705:	if (!isset($this->aggregates[$name])) {
706:	$this->aggregates[$name] = array();
707:	}
708:	$field = ($field === null) ? '*' : $field;
709:	$this->aggregates[$name][$field] = ($alias === null) ? $field : array($field, $alias);
710:
711:	return $this;
712:	}
713:
714:	/**
715:	* Build SQL
716:	*
717:	* @return object QueryBuilder
718:	*/
719:	protected function buildSQL()
720:	{
721:	$sql = 'SELECT ';
722:	# SELECT fields
723:	$select = array();
724:	if ($this->fields) {
725:	foreach ($this->fields as $tableAlias => $field) {
726:	foreach ($field as $f) {
727:	$readyField = $this->prepareField($tableAlias, $f);
728:	if ($readyField) {
729:	$select[] = $readyField;
730:	}
731:	}
732:	}
733:	}
734:
735:	if (count($this->aggregates)) {
736:	foreach ($this->aggregates as $func => $fields) {
737:	$func = strtoupper($func);
738:	foreach ($fields as $field) {
739:	if (is_array($field)) {
740:	$select[] = $func . '(' . self::quote($field[0]) . ') ' . self::quote($field[1]);
741:	} else {
742:	$select[] = $func . '(' . self::quote($field) . ')';
743:	}
744:	}
745:	}
746:	}
747:
748:	if (count($select) === 0) {
749:	$select = array(self::quote($this->alias) . '.*');
750:	}
751:
752:	$sql .= implode(', ', $select);
753:
754:	# FROM clause
755:	$sql .= ' FROM ' . self::quote($this->table) . ' ' . self::quote($this->alias);
756:
757:	# JOIN clause
758:	if ($this->joins) {
759:	$joins = array();
760:	foreach ($this->joins as $join) {
761:	$join = (object)$join;
762:	if (preg_match_all('/([a-z0-9_]+\.[a-z0-9_]+)/i', $join->condition, $matches)) {
763:	$matchedFields = array_unique($matches[0]);
764:	foreach ($matchedFields as $field) {
765:	$join->condition = str_replace($field, self::quote($field), $join->condition);
766:	}
767:	}
768:	$joins[] = $join->type . ' JOIN '
769:	. self::quote($join->table) . ' ' . self::quote($join->alias)
770:	. ' ON ' . $join->condition;
771:	}
772:	$sql .= ' ' . implode(' ', $joins);
773:	}
774:
775:	# WHERE clause
776:	if (is_array($this->where)) {
777:	$sql .= ' WHERE 1 = 1';
778:	foreach ($this->where as $key => $where) {
779:	if ($key == 'AND') {
780:	list($clause, $values) = self::buildCondition($where, 'AND');
781:	$sql .= ' AND ' . $clause;
782:	self::addBindValues($values);
783:	} elseif ($key == 'OR') {
784:	list($clause, $values) = self::buildCondition($where, 'OR');
785:	$sql .= ' AND ' . $clause;
786:	self::addBindValues($values);
787:	} elseif ($key == 'NOT') {
788:	list($clause, $values) = self::buildCondition($where, 'NOT');
789:	$sql .= ' AND ' . $clause;
790:	self::addBindValues($values);
791:	}
792:	}
793:	} elseif (is_string($this->where)) {
794:	$sql .= ' WHERE ' . $this->where;
795:	}
796:
797:	# EXISTS clause
798:	$exists = array();
799:	if (!empty($this->exist)) {
800:	foreach ($this->exist as $exist) {
801:	$subquery = self::isRawExp($exist['query']) ? self::parseFromRawExp($exist['query']) : $exist['query'];
802:	$exists[] = " $exist[type] EXISTS ($subquery)";
803:	}
804:	}
805:
806:	# NOT EXISTS clause
807:	if (!empty($this->notExist)) {
808:	foreach ($this->notExist as $exist) {
809:	$subquery = self::isRawExp($exist['query']) ? self::parseFromRawExp($exist['query']) : $exist['query'];
810:	$exists[] = " $exist[type] NOT EXISTS ($subquery)";
811:	}
812:	}
813:
814:	$sql = $this->appendExistClauses($exists, $sql);
815:
816:	# GROUP BY clause
817:	if ($this->groupBy) {
818:	$groupBy = array();
819:	foreach ($this->groupBy as $field) {
820:	if (self::isRawExp($field)) {
821:	$groupBy[] = self::parseFromRawExp($field);
822:	continue;
823:	}
824:
825:	$groupBy[] = self::quote($field);
826:	}
827:	$sql .= ' GROUP BY ' . implode(', ', $groupBy);
828:	}
829:
830:	# HAVING clause
831:	if ($this->having) {
832:	$sql .= ' HAVING ' . $this->having;
833:	}
834:
835:	# ORDER BY clause
836:	if ($this->orderBy) {
837:	$orderBy = array();
838:	foreach ($this->orderBy as $field => $sort) {
839:	if (self::isRawExp($field)) {
840:	$orderBy[] = self::parseFromRawExp($field);
841:	continue;
842:	}
843:
844:	$orderBy[] = self::quote($field) . ' ' . $sort;
845:	}
846:	$sql .= ' ORDER BY ' . implode(', ', $orderBy);
847:	}
848:
849:	# LIMIT clause
850:	if ($this->offset !== null && $this->limit) {
851:	$sql .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
852:	} elseif ($this->limit && $this->offset === null) {
853:	$sql .= ' LIMIT ' . $this->limit;
854:	}
855:
856:	$this->sql = $sql;
857:
858:	return $this;
859:	}
860:
861:	/**
862:	* Append EXISTS clauses to the SQL statement building
863:	* @param array $exists Array of exists clauses
864:	* @param string $sql The original SQL statement to be appended
865:	* @return string
866:	*/
867:	protected function appendExistClauses(array $exists, $sql)
868:	{
869:	if (!count($exists)) {
870:	return $sql;
871:	}
872:
873:	$clause = implode('', $exists);
874:	if (!empty($this->where)) {
875:	// if there is already WHERE clause in the statement
876:	$sql .= $clause;
877:	} else {
878:	// if there is no WHERE clause in the statement
879:	$clause = preg_replace('/^(AND\|OR)\s+/', '', trim($clause));
880:	$sql .= ' WHERE ' . $clause;
881:	}
882:
883:	return $sql;
884:	}
885:
886:	/**
887:	* Execute the query
888:	*
889:	* @return bool\|resource The result
890:	*/
891:	public function execute()
892:	{
893:	$this->buildSQL();
894:
895:	if ($this->sql) {
896:	$this->result = db_query($this->sql, self::$bindValues);
897:	}
898:
899:	self::clearBindValues();
900:
901:	return $this->result;
902:	}
903:
904:	/**
905:	* Get the number of rows in the query result
906:	* @return int Returns the number of rows in the result set.
907:	*/
908:	public function getNumRows()
909:	{
910:	if ($this->result === null) {
911:	$this->execute();
912:	}
913:
914:	if ($this->result) {
915:	return db_numRows($this->result);
916:	}
917:
918:	return 0;
919:	}
920:
921:	/**
922:	* Fetch a query result row
923:	*
924:	* @param int $resultType The optional constant indicating what type of array should be produced.
925:	* The possible values for this parameter are the constants
926:	* LC_FETCH_OBJECT, LC_FETCH_ASSOC, or LC_FETCH_ARRAY.
927:	* Default to LC_FETCH_OBJECT.
928:	*
929:	* @return mixed
930:	*/
931:	public function fetchRow($resultType = LC_FETCH_OBJECT)
932:	{
933:	if ($this->result === null) {
934:	$this->execute();
935:	}
936:
937:	if ($this->result) {
938:	if ($row = db_fetchAssoc($this->result)) {
939:	if ($resultType === LC_FETCH_ARRAY) {
940:	return array_values($row);
941:	} elseif ($resultType === LC_FETCH_OBJECT) {
942:	return (object)$row;
943:	} else {
944:	return $row;
945:	}
946:	}
947:	}
948:
949:	return null;
950:	}
951:
952:	/**
953:	* Perform a query on the database and return the array of all results
954:	*
955:	* @return array The result array of objects.
956:	* If the result not found, return null.
957:	*/
958:	public function getResult()
959:	{
960:	if ($this->result === null) {
961:	$this->execute();
962:	}
963:
964:	$data = array();
965:	if ($this->result) {
966:	while ($row = db_fetchObject($this->result)) {
967:	$data[] = $row;
968:	}
969:	}
970:
971:	return $data;
972:	}
973:
974:	/**
975:	* Perform a query on the database and return the array of all results in associate array
976:	*
977:	* @return array The result array of objects.
978:	* If the result not found, return null.
979:	*/
980:	public function getResultArray()
981:	{
982:	if ($this->result === null) {
983:	$this->execute();
984:	}
985:
986:	$data = array();
987:	if ($this->result) {
988:	while ($row = db_fetchAssoc($this->result)) {
989:	$data[] = $row;
990:	}
991:	}
992:
993:	return $data;
994:	}
995:
996:	/**
997:	* Perform a query on the database and return the key/value array of all results
998:	*
999:	* @param string $keyField The field name for the array key; default is `id`
1000:	* @param string $valueField The field name for the array value; default is `name`
1001:	* @return array
1002:	*/
1003:	public function getList($keyField = 'id', $valueField = 'name')
1004:	{
1005:	if ($this->result === null) {
1006:	$this->execute();
1007:	}
1008:
1009:	$data = array();
1010:	if ($this->result) {
1011:	while ($row = db_fetchObject($this->result)) {
1012:	$data[$row->{$keyField}] = $row->{$valueField};
1013:	}
1014:	}
1015:
1016:	return $data;
1017:	}
1018:
1019:	/**
1020:	* Perform a query on the database and return the result object
1021:	*
1022:	* @return object\|null The result object
1023:	* If the result not found, return null.
1024:	*/
1025:	public function getSingleResult()
1026:	{
1027:	$this->limit(1);
1028:
1029:	if ($this->result === null) {
1030:	$this->execute();
1031:	}
1032:
1033:	if ($row = db_fetchObject($this->result)) {
1034:	return $row;
1035:	}
1036:
1037:	return null;
1038:	}
1039:
1040:	/**
1041:	* Perform a query on the database and fetch one field only
1042:	*
1043:	* @return mixed The result
1044:	* If the result not found, return null.
1045:	*/
1046:	public function fetch()
1047:	{
1048:	$this->limit(1);
1049:
1050:	if ($this->result === null) {
1051:	$this->execute();
1052:	}
1053:
1054:	if ($this->result && $row = db_fetchArray($this->result)) {
1055:	return $row[0];
1056:	}
1057:
1058:	return null;
1059:	}
1060:
1061:	/**
1062:	* Get the built SQL
1063:	*
1064:	* @return string
1065:	*/
1066:	public function getSQL()
1067:	{
1068:	if ($this->sql === null) {
1069:	$this->buildSQL();
1070:	}
1071:
1072:	return $this->sql;
1073:	}
1074:
1075:	/**
1076:	* Get the built SQL with the values replaced
1077:	* @return string
1078:	*/
1079:	public function getReadySQL()
1080:	{
1081:	$sql = $this->getSQL();
1082:
1083:	foreach (QueryBuilder::getBindValues() as $key => $value) {
1084:	if (is_string($value) && !self::hasQuote($value)) {
1085:	$value = '"' . $value . '"';
1086:	}
1087:
1088:	$sql = preg_replace('/' . $key . '\b/', $value, $sql);
1089:	}
1090:
1091:	return $sql;
1092:	}
1093:
1094:	/**
1095:	* Validate table name or field name
1096:	*
1097:	* @param string $name The table name or field name to be validated
1098:	* @return boolean
1099:	*/
1100:	public static function validateName($name)
1101:	{
1102:	if (!is_string($name)) {
1103:	return false;
1104:	}
1105:
1106:	return preg_match('/^[A-Za-z0-9_]+$/', $name);
1107:	}
1108:
1109:	/**
1110:	* Quote table name and field name
1111:	*
1112:	* @param string $name The table name or field name or table.field
1113:	* @return string
1114:	*/
1115:	public static function quote($name)
1116:	{
1117:	$name = trim($name);
1118:
1119:	if ($name === '*' \|\| self::isRawExp($name)) {
1120:	return $name;
1121:	}
1122:
1123:	foreach (self::$functions as $func) {
1124:	if (stripos($name, $func) === 0) {
1125:	return $name;
1126:	}
1127:	}
1128:
1129:	if (strpos($name, '.') !== false) {
1130:	$name = str_replace('.', '`.`', $name);
1131:	}
1132:
1133:	return '`' . $name . '`';
1134:	}
1135:
1136:	/**
1137:	* Check if the value has quoted table name and field name
1138:	* @param mixed $value
1139:	* @return false\|int\|null
1140:	*/
1141:	public static function hasQuote($value)
1142:	{
1143:	return preg_match_all('/(`[a-z0-9_-]+`\.`[a-z0-9_-]+`)/i', $value);
1144:	}
1145:
1146:	/**
1147:	* Create raw expression string
1148:	* @param string $expression
1149:	* @param array $values The values to be replaced with specifier in $expression. See vsprintf.
1150:	* @return string
1151:	*/
1152:	public static function raw($expression, array $values = array())
1153:	{
1154:	return vsprintf(self::EXP_RAW . $expression, $values);
1155:	}
1156:
1157:	/**
1158:	* Check if field is raw expression
1159:	* @param string $field
1160:	* @return bool
1161:	*/
1162:	private static function isRawExp($field)
1163:	{
1164:	return is_string($field) && strpos($field, self::EXP_RAW) !== false;
1165:	}
1166:
1167:	/**
1168:	* Parse field from raw expression
1169:	* @param string $field
1170:	* @return false\|string
1171:	*/
1172:	private static function parseFromRawExp($field)
1173:	{
1174:	if (self::isRawExp($field)) {
1175:	return substr($field, strlen(self::EXP_RAW));
1176:	}
1177:
1178:	return $field;
1179:	}
1180:
1181:	/**
1182:	* Build the SQL WHERE clause from the various condition arrays
1183:	*
1184:	* @param array $cond The condition array, for example
1185:	*
1186:	* array(
1187:	* 'fieldName1' => $value1,
1188:	* 'fieldName2 >=' => $value2,
1189:	* 'fieldName3 => NULL
1190:	* )
1191:	*
1192:	* @param string $type The condition type "AND" or "OR"; Default is "AND"
1193:	*
1194:	* @return array The built condition WHERE AND/OR
1195:	* [0] string The built condition WHERE AND/OR clause
1196:	* [1] array The values to bind in the condition
1197:	*/
1198:	public static function buildCondition($cond = array(), $type = 'AND')
1199:	{
1200:	if (!is_array($cond)) {
1201:	return $cond;
1202:	}
1203:
1204:	if (empty($cond)) {
1205:	return array('', array());
1206:	}
1207:
1208:	$type = strtoupper($type);
1209:	$condition = array();
1210:
1211:	foreach ($cond as $field => $value) {
1212:	$field = trim($field);
1213:	if (self::isRawExp($value)) {
1214:	$value = self::quote(self::parseFromRawExp($value));
1215:	}
1216:
1217:	if (in_array(strtolower($field), array('$and', '$or', '$not'))) {
1218:	$field = substr($field, 1);
1219:	if (strtoupper($field) == 'NOT') {
1220:	list($nestedClause, $values) = self::buildCondition($value, 'AND');
1221:	$condition[] = 'NOT (' . $nestedClause . ')';
1222:	} else {
1223:	list($nestedClause, $values) = self::buildCondition($value, $field);
1224:	$condition[] = '(' . $nestedClause . ')';
1225:	}
1226:	self::addBindValues($values);
1227:	continue;
1228:	}
1229:
1230:	$fieldOpr = explode(' ', $field);
1231:	$field = trim($fieldOpr[0]);
1232:
1233:	if (strpos($field, self::EXP_CONDITION) !== false) {
1234:	$field = substr($field, 0, strpos($field, self::EXP_CONDITION));
1235:	}
1236:
1237:	$opr = count($fieldOpr) === 2 ? trim($fieldOpr[1]) : '=';
1238:	$opr = strtolower($opr);
1239:
1240:	# check if any operator is given in the field
1241:	if (!in_array($opr, self::$operators)) {
1242:	$opr = '=';
1243:	}
1244:
1245:	if (is_numeric($field)) {
1246:	# if the field is array index,
1247:	# assuming that is a condition built by db_or() or db_and();
1248:	list($nestedClause, $values) = $value;
1249:	$condition[] = '( ' . $nestedClause . ' )';
1250:	self::addBindValues($values);
1251:	} else {
1252:	# if the operator is "between", the value must be array
1253:	# otherwise force to "="
1254:	if (in_array($opr, array('between', 'nbetween')) && !is_array($value)) {
1255:	$opr = '=';
1256:	}
1257:
1258:	$key = $field;
1259:	$placeholder = self::getPlaceholder($key, self::$bindValues);
1260:	$field = self::quote($field);
1261:
1262:	if ($opr == 'in') {
1263:	if (self::isRawExp($value)) {
1264:	$condition[] = $field . ' IN (' . self::parseFromRawExp($value) . ')';
1265:	} else {
1266:	$condition[] = $field . ' IN (' . $placeholder . ')';
1267:	self::setBindValue($placeholder, is_array($value) ? implode(', ', $value) : $value);
1268:	}
1269:	continue;
1270:	}
1271:
1272:	if (array_key_exists($opr, self::$likes)) {
1273:	$condition[] = $field . ' ' . str_replace(':placeholder', $placeholder, self::$likes[$opr]);
1274:	self::setBindValue($placeholder, $value);
1275:	continue;
1276:	}
1277:
1278:	if (is_null($value)) {
1279:	if (in_array($opr, array('!=', '<>'))) {
1280:	$condition[] = $field . ' IS NOT NULL';
1281:	} else {
1282:	$condition[] = $field . ' IS NULL';
1283:	}
1284:	continue;
1285:	}
1286:
1287:	if (is_array($value) && count($value)) {
1288:	if ($opr === 'between' \|\| $opr === 'nbetween') {
1289:	$condition[] = sprintf(
1290:	'(%s %s :%s_from AND :%s_to)',
1291:	$field,
1292:	self::$betweens[$opr],
1293:	$key,
1294:	$key
1295:	);
1296:
1297:	self::setBindValue($placeholder . '_from', $value[0]);
1298:	self::setBindValue($placeholder . '_to', $value[1]);
1299:	} else {
1300:	$inPlaceholders = array();
1301:	foreach ($value as $i => $val) {
1302:	$holder = preg_replace('/(\d)*/', '', $placeholder);
1303:	$holder = $holder . $i;
1304:	$inPlaceholders[] = $holder;
1305:	self::setBindValue($holder, $val);
1306:	}
1307:
1308:	$condition[] = sprintf(
1309:	'%s%sIN (%s)',
1310:	$field,
1311:	$opr === '!=' ? ' NOT ' : ' ',
1312:	implode(', ', $inPlaceholders)
1313:	);
1314:	}
1315:	continue;
1316:	}
1317:
1318:	$condition[] = "{$field} {$opr} {$placeholder}";
1319:	self::setBindValue($placeholder, $value);
1320:	}
1321:	}
1322:
1323:	if (count($condition)) {
1324:	return array(
1325:	implode(" {$type} ", $condition),
1326:	self::$bindValues,
1327:	);
1328:	}
1329:
1330:	return array('', array());
1331:	}
1332:
1333:	private static function getPlaceholder($key, $values = array())
1334:	{
1335:	$specChars = array(
1336:	'`','~','!','@','#','$','%','\^','&',
1337:	'*','(',')','=','+','{','}','[',']',
1338:	':',';',"'",'"','<','>','\\','\|','?','/',',','.'
1339:	);
1340:
1341:	$key = str_replace($specChars, '_', $key);
1342:
1343:	$placeholders = array_filter($values, function ($placeholder) use ($key) {
1344:	return stripos($placeholder, $key) === 1;
1345:	}, ARRAY_FILTER_USE_KEY);
1346:
1347:	if (!count($placeholders)) {
1348:	return ':' . $key;
1349:	}
1350:
1351:	$placeholders = array_keys($placeholders);
1352:	rsort($placeholders);
1353:
1354:	$index = '';
1355:	if (preg_match('/:' . $key . '(\d)*/', $placeholders[0], $matches)) {
1356:	$index = isset($matches[1]) ? $matches[1] + 1 : 0;
1357:	}
1358:
1359:	return ':' . $key . $index;
1360:	}
1361:
1362:	/**
1363:	* Bind values for query arguments
1364:	* @param array $values
1365:	*/
1366:	private static function addBindValues(array $values)
1367:	{
1368:	self::$bindValues = array_merge(self::$bindValues, $values);
1369:	}
1370:
1371:	/**
1372:	* Bind value for query argument by key
1373:	* @param string $key
1374:	* @param mixed $value
1375:	*/
1376:	private static function setBindValue($key, $value)
1377:	{
1378:	self::$bindValues[$key] = $value;
1379:	}
1380:
1381:	/**
1382:	* Clear bind values
1383:	*/
1384:	public static function clearBindValues()
1385:	{
1386:	self::$bindValues = array();
1387:	}
1388:
1389:	/**
1390:	* Get bind values
1391:	* @return array
1392:	*/
1393:	public static function getBindValues()
1394:	{
1395:	return self::$bindValues;
1396:	}
1397:	}
1398:

Namespaces

Classes