File classes\QueryBuilder.php | PHPLucidFrame 3.4 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 (is_array($this->where)) {
767:	$sql .= ' WHERE 1 = 1';
768:	foreach ($this->where as $key => $where) {
769:	if ($key == 'AND') {
770:	list($clause, $values) = self::buildCondition($where, 'AND');
771:	$sql .= ' AND ' . $clause;
772:	self::addBindValues($values);
773:	} elseif ($key == 'OR') {
774:	list($clause, $values) = self::buildCondition($where, 'OR');
775:	$sql .= ' AND ' . $clause;
776:	self::addBindValues($values);
777:	} elseif ($key == 'NOT') {
778:	list($clause, $values) = self::buildCondition($where, 'NOT');
779:	$sql .= ' AND ' . $clause;
780:	self::addBindValues($values);
781:	}
782:	}
783:	} elseif (is_string($this->where)) {
784:	$sql .= ' WHERE ' . $this->where;
785:	}
786:
787:	# EXISTS clause
788:	$exists = array();
789:	if (!empty($this->exist)) {
790:	foreach ($this->exist as $exist) {
791:	$subquery = self::isRawExp($exist['query']) ? self::parseFromRawExp($exist['query']) : $exist['query'];
792:	$exists[] = " $exist[type] EXISTS ($subquery)";
793:	}
794:	}
795:
796:	# NOT EXISTS clause
797:	if (!empty($this->notExist)) {
798:	foreach ($this->notExist as $exist) {
799:	$subquery = self::isRawExp($exist['query']) ? self::parseFromRawExp($exist['query']) : $exist['query'];
800:	$exists[] = " $exist[type] NOT EXISTS ($subquery)";
801:	}
802:	}
803:
804:	$sql = $this->appendExistClauses($exists, $sql);
805:
806:	# GROUP BY clause
807:	if ($this->groupBy) {
808:	$groupBy = array();
809:	foreach ($this->groupBy as $field) {
810:	if (self::isRawExp($field)) {
811:	$groupBy[] = self::parseFromRawExp($field);
812:	continue;
813:	}
814:
815:	$groupBy[] = self::quote($field);
816:	}
817:	$sql .= ' GROUP BY ' . implode(', ', $groupBy);
818:	}
819:
820:	# HAVING clause
821:	if ($this->having) {
822:	$sql .= ' HAVING ' . $this->having;
823:	}
824:
825:	# ORDER BY clause
826:	if ($this->orderBy) {
827:	$orderBy = array();
828:	foreach ($this->orderBy as $field => $sort) {
829:	if (self::isRawExp($field)) {
830:	$orderBy[] = self::parseFromRawExp($field);
831:	continue;
832:	}
833:
834:	$orderBy[] = self::quote($field) . ' ' . $sort;
835:	}
836:	$sql .= ' ORDER BY ' . implode(', ', $orderBy);
837:	}
838:
839:	# LIMIT clause
840:	if ($this->offset !== null && $this->limit) {
841:	$sql .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
842:	} elseif ($this->limit && $this->offset === null) {
843:	$sql .= ' LIMIT ' . $this->limit;
844:	}
845:
846:	$this->sql = $sql;
847:
848:	return $this;
849:	}
850:
851:	/**
852:	* Append EXISTS clauses to the SQL statement building
853:	* @param array $exists Array of exists clauses
854:	* @param string $sql The original SQL statement to be appended
855:	* @return string
856:	*/
857:	protected function appendExistClauses(array $exists, $sql)
858:	{
859:	if (!count($exists)) {
860:	return $sql;
861:	}
862:
863:	$clause = implode('', $exists);
864:	if (!empty($this->where)) {
865:	// if there is already WHERE clause in the statement
866:	$sql .= $clause;
867:	} else {
868:	// if there is no WHERE clause in the statement
869:	$clause = preg_replace('/^(AND\|OR)\s+/', '', trim($clause));
870:	$sql .= ' WHERE ' . $clause;
871:	}
872:
873:	return $sql;
874:	}
875:
876:	/**
877:	* Execute the query
878:	*
879:	* @return bool\|resource The result
880:	*/
881:	public function execute()
882:	{
883:	$this->buildSQL();
884:
885:	if ($this->sql) {
886:	$this->result = db_query($this->sql, self::$bindValues);
887:	}
888:
889:	self::clearBindValues();
890:
891:	return $this->result;
892:	}
893:
894:	/**
895:	* Get the number of rows in the query result
896:	* @return int Returns the number of rows in the result set.
897:	*/
898:	public function getNumRows()
899:	{
900:	if ($this->result === null) {
901:	$this->execute();
902:	}
903:
904:	if ($this->result) {
905:	return db_numRows($this->result);
906:	}
907:
908:	return 0;
909:	}
910:
911:	/**
912:	* Fetch a query result row
913:	*
914:	* @param int $resultType The optional constant indicating what type of array should be produced.
915:	* The possible values for this parameter are the constants
916:	* LC_FETCH_OBJECT, LC_FETCH_ASSOC, or LC_FETCH_ARRAY.
917:	* Default to LC_FETCH_OBJECT.
918:	*
919:	* @return mixed
920:	*/
921:	public function fetchRow($resultType = LC_FETCH_OBJECT)
922:	{
923:	if ($this->result === null) {
924:	$this->execute();
925:	}
926:
927:	if ($this->result) {
928:	if ($row = db_fetchAssoc($this->result)) {
929:	if ($resultType === LC_FETCH_ARRAY) {
930:	return array_values($row);
931:	} elseif ($resultType === LC_FETCH_OBJECT) {
932:	return (object)$row;
933:	} else {
934:	return $row;
935:	}
936:	}
937:	}
938:
939:	return null;
940:	}
941:
942:	/**
943:	* Perform a query on the database and return the array of all results
944:	*
945:	* @return array The result array of objects.
946:	* If the result not found, return null.
947:	*/
948:	public function getResult()
949:	{
950:	if ($this->result === null) {
951:	$this->execute();
952:	}
953:
954:	$data = array();
955:	if ($this->result) {
956:	while ($row = db_fetchObject($this->result)) {
957:	$data[] = $row;
958:	}
959:	}
960:
961:	return $data;
962:	}
963:
964:	/**
965:	* Perform a query on the database and return the array of all results in associate array
966:	*
967:	* @return array The result array of objects.
968:	* If the result not found, return null.
969:	*/
970:	public function getResultArray()
971:	{
972:	if ($this->result === null) {
973:	$this->execute();
974:	}
975:
976:	$data = array();
977:	if ($this->result) {
978:	while ($row = db_fetchAssoc($this->result)) {
979:	$data[] = $row;
980:	}
981:	}
982:
983:	return $data;
984:	}
985:
986:	/**
987:	* Perform a query on the database and return the key/value array of all results
988:	*
989:	* @param string $keyField The field name for the array key; default is `id`
990:	* @param string $valueField The field name for the array value; default is `name`
991:	* @return array
992:	*/
993:	public function getList($keyField = 'id', $valueField = 'name')
994:	{
995:	if ($this->result === null) {
996:	$this->execute();
997:	}
998:
999:	$data = array();
1000:	if ($this->result) {
1001:	while ($row = db_fetchObject($this->result)) {
1002:	$data[$row->{$keyField}] = $row->{$valueField};
1003:	}
1004:	}
1005:
1006:	return $data;
1007:	}
1008:
1009:	/**
1010:	* Perform a query on the database and return the result object
1011:	*
1012:	* @return object\|null The result object
1013:	* If the result not found, return null.
1014:	*/
1015:	public function getSingleResult()
1016:	{
1017:	$this->limit(1);
1018:
1019:	if ($this->result === null) {
1020:	$this->execute();
1021:	}
1022:
1023:	if ($row = db_fetchObject($this->result)) {
1024:	return $row;
1025:	}
1026:
1027:	return null;
1028:	}
1029:
1030:	/**
1031:	* Perform a query on the database and fetch one field only
1032:	*
1033:	* @return mixed The result
1034:	* If the result not found, return null.
1035:	*/
1036:	public function fetch()
1037:	{
1038:	$this->limit(1);
1039:
1040:	if ($this->result === null) {
1041:	$this->execute();
1042:	}
1043:
1044:	if ($this->result && $row = db_fetchArray($this->result)) {
1045:	return $row[0];
1046:	}
1047:
1048:	return null;
1049:	}
1050:
1051:	/**
1052:	* Get the built SQL
1053:	*
1054:	* @return string
1055:	*/
1056:	public function getSQL()
1057:	{
1058:	if ($this->sql === null) {
1059:	$this->buildSQL();
1060:	}
1061:
1062:	return $this->sql;
1063:	}
1064:
1065:	/**
1066:	* Get the built SQL with the values replaced
1067:	* @return string
1068:	*/
1069:	public function getReadySQL()
1070:	{
1071:	$sql = $this->getSQL();
1072:
1073:	foreach (QueryBuilder::getBindValues() as $key => $value) {
1074:	if (is_string($value) && !self::hasQuote($value)) {
1075:	$value = '"' . $value . '"';
1076:	}
1077:
1078:	$sql = preg_replace('/' . $key . '\b/', $value, $sql);
1079:	}
1080:
1081:	return $sql;
1082:	}
1083:
1084:	/**
1085:	* Validate table name or field name
1086:	*
1087:	* @param string $name The table name or field name to be validated
1088:	* @return boolean
1089:	*/
1090:	public static function validateName($name)
1091:	{
1092:	if (!is_string($name)) {
1093:	return false;
1094:	}
1095:
1096:	return preg_match('/^[A-Za-z0-9_]+$/', $name);
1097:	}
1098:
1099:	/**
1100:	* Quote table name and field name
1101:	*
1102:	* @param string $name The table name or field name or table.field
1103:	* @return string
1104:	*/
1105:	public static function quote($name)
1106:	{
1107:	$name = trim($name);
1108:
1109:	if ($name === '*' \|\| self::isRawExp($name)) {
1110:	return $name;
1111:	}
1112:
1113:	foreach (self::$functions as $func) {
1114:	if (stripos($name, $func) === 0) {
1115:	return $name;
1116:	}
1117:	}
1118:
1119:	if (strpos($name, '.') !== false) {
1120:	$name = str_replace('.', '`.`', $name);
1121:	}
1122:
1123:	return '`' . $name . '`';
1124:	}
1125:
1126:	/**
1127:	* Check if the value has quoted table name and field name
1128:	* @param mixed $value
1129:	* @return false\|int\|null
1130:	*/
1131:	public static function hasQuote($value)
1132:	{
1133:	return preg_match_all('/(`[a-z0-9_-]+`\.`[a-z0-9_-]+`)/i', $value);
1134:	}
1135:
1136:	/**
1137:	* Create raw expression string
1138:	* @param string $expression
1139:	* @param array $values The values to be replaced with specifier in $expression. See vsprintf.
1140:	* @return string
1141:	*/
1142:	public static function raw($expression, array $values = array())
1143:	{
1144:	return vsprintf(self::EXP_RAW . $expression, $values);
1145:	}
1146:
1147:	/**
1148:	* Check if field is raw expression
1149:	* @param string $field
1150:	* @return bool
1151:	*/
1152:	private static function isRawExp($field)
1153:	{
1154:	return is_string($field) && strpos($field, self::EXP_RAW) !== false;
1155:	}
1156:
1157:	/**
1158:	* Parse field from raw expression
1159:	* @param string $field
1160:	* @return false\|string
1161:	*/
1162:	private static function parseFromRawExp($field)
1163:	{
1164:	if (self::isRawExp($field)) {
1165:	return substr($field, strlen(self::EXP_RAW));
1166:	}
1167:
1168:	return $field;
1169:	}
1170:
1171:	/**
1172:	* Build the SQL WHERE clause from the various condition arrays
1173:	*
1174:	* @param array $cond The condition array, for example
1175:	*
1176:	* array(
1177:	* 'fieldName1' => $value1,
1178:	* 'fieldName2 >=' => $value2,
1179:	* 'fieldName3 => NULL
1180:	* )
1181:	*
1182:	* @param string $type The condition type "AND" or "OR"; Default is "AND"
1183:	*
1184:	* @return array The built condition WHERE AND/OR
1185:	* [0] string The built condition WHERE AND/OR clause
1186:	* [1] array The values to bind in the condition
1187:	*/
1188:	public static function buildCondition($cond = array(), $type = 'AND')
1189:	{
1190:	if (!is_array($cond)) {
1191:	return $cond;
1192:	}
1193:
1194:	if (empty($cond)) {
1195:	return array('', array());
1196:	}
1197:
1198:	$type = strtoupper($type);
1199:	$condition = array();
1200:
1201:	foreach ($cond as $field => $value) {
1202:	$field = trim($field);
1203:	if (self::isRawExp($value)) {
1204:	$value = self::quote(self::parseFromRawExp($value));
1205:	}
1206:
1207:	if (in_array(strtolower($field), array('$and', '$or', '$not'))) {
1208:	$field = substr($field, 1);
1209:	if (strtoupper($field) == 'NOT') {
1210:	list($nestedClause, $values) = self::buildCondition($value, 'AND');
1211:	$condition[] = 'NOT (' . $nestedClause . ')';
1212:	} else {
1213:	list($nestedClause, $values) = self::buildCondition($value, $field);
1214:	$condition[] = '(' . $nestedClause . ')';
1215:	}
1216:	self::addBindValues($values);
1217:	continue;
1218:	}
1219:
1220:	$fieldOpr = explode(' ', $field);
1221:	$field = trim($fieldOpr[0]);
1222:
1223:	if (strpos($field, self::EXP_CONDITION) !== false) {
1224:	$field = substr($field, 0, strpos($field, self::EXP_CONDITION));
1225:	}
1226:
1227:	$opr = count($fieldOpr) === 2 ? trim($fieldOpr[1]) : '=';
1228:	$opr = strtolower($opr);
1229:
1230:	# check if any operator is given in the field
1231:	if (!in_array($opr, self::$operators)) {
1232:	$opr = '=';
1233:	}
1234:
1235:	if (is_numeric($field)) {
1236:	# if the field is array index,
1237:	# assuming that is a condition built by db_or() or db_and();
1238:	list($nestedClause, $values) = $value;
1239:	$condition[] = '( ' . $nestedClause . ' )';
1240:	self::addBindValues($values);
1241:	} else {
1242:	# if the operator is "between", the value must be array
1243:	# otherwise force to "="
1244:	if (in_array($opr, array('between', 'nbetween')) && !is_array($value)) {
1245:	$opr = '=';
1246:	}
1247:
1248:	$key = $field;
1249:	$placeholder = self::getPlaceholder($key, self::$bindValues);
1250:	$field = self::quote($field);
1251:
1252:	if ($opr == 'in') {
1253:	if (self::isRawExp($value)) {
1254:	$condition[] = $field . ' IN (' . self::parseFromRawExp($value) . ')';
1255:	} else {
1256:	$condition[] = $field . ' IN (' . $placeholder . ')';
1257:	self::setBindValue($placeholder, is_array($value) ? implode(', ', $value) : $value);
1258:	}
1259:	continue;
1260:	}
1261:
1262:	if (array_key_exists($opr, self::$likes)) {
1263:	$condition[] = $field . ' ' . str_replace(':placeholder', $placeholder, self::$likes[$opr]);
1264:	self::setBindValue($placeholder, $value);
1265:	continue;
1266:	}
1267:
1268:	if (is_null($value)) {
1269:	if (in_array($opr, array('!=', '<>'))) {
1270:	$condition[] = $field . ' IS NOT NULL';
1271:	} else {
1272:	$condition[] = $field . ' IS NULL';
1273:	}
1274:	continue;
1275:	}
1276:
1277:	if (is_array($value) && count($value)) {
1278:	if ($opr === 'between' \|\| $opr === 'nbetween') {
1279:	$condition[] = sprintf(
1280:	'(%s %s :%s_from AND :%s_to)',
1281:	$field,
1282:	self::$betweens[$opr],
1283:	$key,
1284:	$key
1285:	);
1286:
1287:	self::setBindValue($placeholder . '_from', $value[0]);
1288:	self::setBindValue($placeholder . '_to', $value[1]);
1289:	} else {
1290:	$inPlaceholders = array();
1291:	foreach ($value as $i => $val) {
1292:	$holder = preg_replace('/(\d)*/', '', $placeholder);
1293:	$holder = $holder . $i;
1294:	$inPlaceholders[] = $holder;
1295:	self::setBindValue($holder, $val);
1296:	}
1297:
1298:	$condition[] = sprintf(
1299:	'%s%sIN (%s)',
1300:	$field,
1301:	$opr === '!=' ? ' NOT ' : ' ',
1302:	implode(', ', $inPlaceholders)
1303:	);
1304:	}
1305:	continue;
1306:	}
1307:
1308:	$condition[] = "{$field} {$opr} {$placeholder}";
1309:	self::setBindValue($placeholder, $value);
1310:	}
1311:	}
1312:
1313:	if (count($condition)) {
1314:	return array(
1315:	implode(" {$type} ", $condition),
1316:	self::$bindValues,
1317:	);
1318:	}
1319:
1320:	return array('', array());
1321:	}
1322:
1323:	private static function getPlaceholder($key, $values = array())
1324:	{
1325:	$specChars = array(
1326:	'`','~','!','@','#','$','%','\^','&',
1327:	'*','(',')','=','+','{','}','[',']',
1328:	':',';',"'",'"','<','>','\\','\|','?','/',',','.'
1329:	);
1330:
1331:	$key = str_replace($specChars, '_', $key);
1332:
1333:	$placeholders = array_filter($values, function ($placeholder) use ($key) {
1334:	return stripos($placeholder, $key) === 1;
1335:	}, ARRAY_FILTER_USE_KEY);
1336:
1337:	if (!count($placeholders)) {
1338:	return ':' . $key;
1339:	}
1340:
1341:	$placeholders = array_keys($placeholders);
1342:	rsort($placeholders);
1343:
1344:	$index = '';
1345:	if (preg_match('/:' . $key . '(\d)*/', $placeholders[0], $matches)) {
1346:	$index = isset($matches[1]) ? $matches[1] + 1 : 0;
1347:	}
1348:
1349:	return ':' . $key . $index;
1350:	}
1351:
1352:	/**
1353:	* Bind values for query arguments
1354:	* @param array $values
1355:	*/
1356:	private static function addBindValues(array $values)
1357:	{
1358:	self::$bindValues = array_merge(self::$bindValues, $values);
1359:	}
1360:
1361:	/**
1362:	* Bind value for query argument by key
1363:	* @param string $key
1364:	* @param mixed $value
1365:	*/
1366:	private static function setBindValue($key, $value)
1367:	{
1368:	self::$bindValues[$key] = $value;
1369:	}
1370:
1371:	/**
1372:	* Clear bind values
1373:	*/
1374:	public static function clearBindValues()
1375:	{
1376:	self::$bindValues = array();
1377:	}
1378:
1379:	/**
1380:	* Get bind values
1381:	* @return array
1382:	*/
1383:	public static function getBindValues()
1384:	{
1385:	return self::$bindValues;
1386:	}
1387:	}
1388:

Namespaces

Classes