· 6 years ago · Mar 26, 2019, 08:06 AM
1<?php
2/**
3* This file is part of Batflat ~ the lightweight, fast and easy CMS
4*
5* @author Paweł Klockiewicz <klockiewicz@sruu.pl>
6* @author Wojciech Król <krol@sruu.pl>
7* @copyright 2017 Paweł Klockiewicz, Wojciech Król <Sruu.pl>
8* @license https://batflat.org/license
9* @link https://batflat.org
10*/
11
12namespace Inc\Core\Lib;
13
14/**
15 * Batflat QueryBuilder class
16 */
17class QueryBuilder
18{
19 protected static $db = null;
20
21 protected static $last_sqls = [];
22
23 protected static $options = [];
24
25 protected $table = null;
26
27 protected $columns = [];
28
29 protected $joins = [];
30
31 protected $conditions = [];
32
33 protected $condition_binds = [];
34
35 protected $sets = [];
36
37 protected $set_binds = [];
38
39 protected $orders = [];
40
41 protected $group_by = [];
42
43 protected $having = [];
44
45 protected $limit = '';
46
47 protected $offset = '';
48
49 /**
50 * constructor
51 *
52 * @param string $table
53 */
54 public function __construct($table = null)
55 {
56 if ($table) {
57 $this->table = $table;
58 }
59 }
60
61 /**
62 * PDO instance
63 *
64 * @return PDO
65 */
66 public static function pdo()
67 {
68 return static::$db;
69 }
70
71 /**
72 * last SQL queries
73 *
74 * @return array SQLs array
75 */
76 public static function lastSqls()
77 {
78 return static::$last_sqls;
79 }
80
81 /**
82 * creates connection with database
83 *
84 * Qb::connect($dsn); // default user, password and options
85 * Qb::connect($dsn, $user); // default password and options
86 * Qb::connect($dsn, $user, $pass); // default options
87 * Qb::connect($dsn, $user, $pass, $options);
88 * Qb::connect($dsn, $options);
89 * Qb::connect($dsn, $user, $options);
90 *
91 * @param string $dsn
92 * @param string $user
93 * @param string $pass
94 * @param array $options
95 * primary_key: primary column name, default: 'id'
96 * error_mode: default: \PDO::ERRMODE_EXCEPTION
97 * json_options: default: JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_AMP | JSON_HEX_QUOT
98 */
99 public static function connect($dsn, $user = '', $pass = '', $options = [])
100 {
101 if (is_array($user)) {
102 $options = $user;
103 $user = '';
104 $pass = '';
105 } elseif (is_array($pass)) {
106 $options = $pass;
107 $pass = '';
108 }
109 static::$options = array_merge([
110 'primary_key' => 'id',
111 'error_mode' => \PDO::ERRMODE_EXCEPTION,
112 'json_options' => JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_AMP | JSON_HEX_QUOT,
113 ], $options);
114 static::$db = new \PDO($dsn, $user, $pass);
115 static::$db->setAttribute(\PDO::ATTR_ERRMODE, static::$options['error_mode']);
116 if (strpos($dsn, 'sqlite') !== false) {
117 static::$db->exec("pragma synchronous = off;");
118 }
119 }
120
121 /**
122 * close connection with database
123 */
124 public static function close()
125 {
126 static::$db = null;
127 }
128
129 /**
130 * get or set options
131 *
132 * @param string $name
133 * @param mixed $value
134 */
135 public static function config($name, $value = null)
136 {
137 if ($value === null) {
138 return static::$options[$name];
139 } else {
140 static::$options[$name] = $value;
141 }
142 }
143
144 /**
145 * SELECT
146 *
147 * select('column1')->select('column2') // SELECT column1, column2
148 * select(['column1', 'column2', ...]) // SELECT column1, column2, ...
149 * select(['alias1' => 'column1', 'column2', ...]) // SELECT column1 AS alias1, column2, ...
150 *
151 * @param string|array $columns
152 *
153 * @return \Inc\Core\Lib\QueryBuilder
154 */
155 public function select($columns)
156 {
157 if (!is_array($columns)) {
158 $columns = array($columns);
159 }
160 foreach ($columns as $alias => $column) {
161 if (!is_numeric($alias)) {
162 $column .= " AS $alias";
163 }
164 array_push($this->columns, $column);
165 }
166 return $this;
167 }
168
169 /**
170 * INNER JOIN
171 *
172 * @param string $table
173 * @param string $condition
174 *
175 * @return \Inc\Core\Lib\QueryBuilder
176 */
177 public function join($table, $condition)
178 {
179 array_push($this->joins, "INNER JOIN $table ON $condition");
180 return $this;
181 }
182
183 /**
184 * LEFT OUTER JOIN
185 *
186 * @param string $table
187 * @param string $condition
188 *
189 * @return \Inc\Core\Lib\QueryBuilder
190 */
191 public function leftJoin($table, $condition)
192 {
193 array_push($this->joins, "LEFT JOIN $table ON $condition");
194 return $this;
195 }
196
197 /**
198 * HAVING
199 *
200 * having(aggregate_function, operator, value) // HAVING aggregate_function (=, <, >, <=, >=, <>) value
201 * having(aggregate_function, value) // HAVING aggregate_function = value
202 *
203 * @param string $aggregate_function
204 * @param mixed $value
205 *
206 * @return \Inc\Core\Lib\QueryBuilder
207 */
208 public function having($aggregate_function, $operator, $value = null, $ao = 'AND')
209 {
210 if ($value === null) {
211 $value = $operator;
212 $operator = '=';
213 }
214
215 if (is_array($value)) {
216 $qs = '(' . implode(',', array_fill(0, count($value), '?')) . ')';
217 if (empty($this->having)) {
218 array_push($this->having, "$aggregate_function $operator $qs");
219 } else {
220 array_push($this->having, "$ao $aggregate_function $operator $qs");
221 }
222 foreach ($value as $v) {
223 array_push($this->condition_binds, $v);
224 }
225 } else {
226 if (empty($this->having)) {
227 array_push($this->having, "$aggregate_function $operator ?");
228 } else {
229 array_push($this->having, "$ao $aggregate_function $operator ?");
230 }
231 array_push($this->condition_binds, $value);
232 }
233 return $this;
234 }
235
236 public function orHaving($aggregate_function, $operator, $value = null)
237 {
238 return $this->having($aggregate_function, $operator, $value, 'OR');
239 }
240
241 /**
242 * WHERE
243 *
244 * where(column, operator, value) // WHERE column (=, <, >, <=, >=, <>) value
245 * where(column, value) // WHERE column = value
246 * where(value) // WHERE id = value
247 * where(function($st) {
248 * $st->where()...
249 * })
250 *
251 * @param mixed $column
252 * @param mixed $value
253 *
254 * @return \Inc\Core\Lib\QueryBuilder
255 */
256 public function where($column, $operator = null, $value = null, $ao = 'AND')
257 {
258 // Where group
259 if (!is_string($column) && is_callable($column)) {
260 if (empty($this->conditions) || strpos(end($this->conditions), '(') !== false) {
261 array_push($this->conditions, '(');
262 } else {
263 array_push($this->conditions, $ao.' (');
264 }
265
266 call_user_func($column, $this);
267 array_push($this->conditions, ')');
268
269 return $this;
270 }
271
272 if ($operator === null) {
273 $value = $column;
274 $column = static::$options['primary_key'];
275 $operator = '=';
276 } elseif ($value === null) {
277 $value = $operator;
278 $operator = '=';
279 }
280
281 if (is_array($value)) {
282 foreach ($value as $v) {
283 array_push($this->condition_binds, $v);
284 }
285 $value = '(' . implode(',', array_fill(0, count($value), '?')) . ')';
286 } else {
287 array_push($this->condition_binds, $value);
288 }
289
290 if (empty($this->conditions) || strpos(end($this->conditions), '(') !== false) {
291 array_push($this->conditions, "$column $operator ?");
292 } else {
293 array_push($this->conditions, "$ao $column $operator ?");
294 }
295
296 return $this;
297 }
298
299 /**
300 * OR WHERE
301 *
302 * orWhere(column, operator, value) // WHERE column (=, <, >, <=, >=, <>) value
303 * orWhere(column, value) // WHERE column = value
304 * orWhere(value) // WHERE id = value
305 * orWhere(function($st) {
306 * $st->where()...
307 * })
308 *
309 * @param mixed $column
310 * @param mixed $value
311 *
312 * @return \Inc\Core\Lib\QueryBuilder
313 */
314 public function orWhere($column, $operator = null, $value = null)
315 {
316 return $this->where($column, $operator, $value, 'OR');
317 }
318
319 /**
320 * WHERE IS NULL
321 *
322 * @param string $column
323 * @param string $ao
324 * @return \Inc\Core\Lib\QueryBuilder
325 */
326 public function isNull($column, $ao = 'AND')
327 {
328 if (is_array($column)) {
329 foreach ($column as $c) {
330 $this->isNull($c, $ao);
331 }
332
333 return $this;
334 }
335
336 if (empty($this->conditions) || strpos(end($this->conditions), '(') !== false) {
337 array_push($this->conditions, "$column IS NULL");
338 } else {
339 array_push($this->conditions, "$ao $column IS NULL");
340 }
341
342 return $this;
343 }
344
345 /**
346 * WHERE IS NOT NULL
347 *
348 * @param string $column
349 * @param string $ao
350 * @return \Inc\Core\Lib\QueryBuilder
351 */
352 public function isNotNull($column, $ao = 'AND')
353 {
354 if (is_array($column)) {
355 foreach ($column as $c) {
356 $this->isNotNull($c, $ao);
357 }
358
359 return $this;
360 }
361
362 if (empty($this->conditions) || strpos(end($this->conditions), '(') !== false) {
363 array_push($this->conditions, "$column IS NOT NULL");
364 } else {
365 array_push($this->conditions, "$ao $column IS NOT NULL");
366 }
367
368 return $this;
369 }
370
371 /**
372 * OR WHERE IS NULL
373 *
374 * @param string $column
375 * @return \Inc\Core\Lib\QueryBuilder
376 */
377 public function orIsNull($column)
378 {
379 return $this->isNull($column, 'OR');
380 }
381
382 /**
383 * OR WHERE IS NOT NULL
384 *
385 * @param string $column
386 * @return \Inc\Core\Lib\QueryBuilder
387 */
388 public function orIsNotNull($column)
389 {
390 return $this->isNotNull($column, 'OR');
391 }
392
393 /**
394 * WHERE LIKE
395 *
396 * @param string $column
397 * @param mixed $value
398 *
399 * @return \Inc\Core\Lib\QueryBuilder
400 */
401 public function like($column, $value)
402 {
403 $this->where($column, 'LIKE', $value);
404 return $this;
405 }
406
407 /**
408 * WHERE OR LIKE
409 *
410 * @param string $column
411 * @param mixed $value
412 *
413 * @return \Inc\Core\Lib\QueryBuilder
414 */
415 public function orLike($column, $value)
416 {
417 $this->where($column, 'LIKE', $value, 'OR');
418 return $this;
419 }
420
421 /**
422 * WHERE NOT LIKE
423 *
424 * @param string $column
425 * @param mixed $value
426 *
427 * @return \Inc\Core\Lib\QueryBuilder
428 */
429 public function notLike($column, $value)
430 {
431 $this->where($column, 'NOT LIKE', $value);
432 return $this;
433 }
434
435 /**
436 * WHERE OR NOT LIKE
437 *
438 * @param string $column
439 * @param mixed $value
440 *
441 * @return \Inc\Core\Lib\QueryBuilder
442 */
443 public function orNotLike($column, $value)
444 {
445 $this->where($column, 'NOT LIKE', $value, 'OR');
446 return $this;
447 }
448
449 /**
450 * WHERE IN
451 *
452 * @param string $column
453 * @param array $values
454 *
455 * @return \Inc\Core\Lib\QueryBuilder
456 */
457 public function in($column, $values)
458 {
459 $this->where($column, 'IN', $values);
460 return $this;
461 }
462
463 /**
464 * WHERE OR IN
465 *
466 * @param string $column
467 * @param array $values
468 *
469 * @return \Inc\Core\Lib\QueryBuilder
470 */
471 public function orIn($column, $values)
472 {
473 $this->where($column, 'IN', $values, 'OR');
474 return $this;
475 }
476
477 /**
478 * WHERE NOT IN
479 *
480 * @param string $column
481 * @param array $values
482 *
483 * @return \Inc\Core\Lib\QueryBuilder
484 */
485 public function notIn($column, $values)
486 {
487 $this->where($column, 'NOT IN', $values);
488 return $this;
489 }
490
491 /**
492 * WHERE OR NOT IN
493 *
494 * @param string $column
495 * @param array $values
496 *
497 * @return \Inc\Core\Lib\QueryBuilder
498 */
499 public function orNotIn($column, $values)
500 {
501 $this->where($column, 'NOT IN', $values, 'OR');
502 return $this;
503 }
504
505 /**
506 * get or set column value
507 *
508 * @param string $column
509 * @param mixed $value
510 *
511 * @return \Inc\Core\Lib\QueryBuilder
512 */
513 public function set($column, $value = null)
514 {
515 if (is_array($column)) {
516 $sets = $column;
517 } else {
518 $sets = [$column => $value];
519 }
520 $this->sets += $sets;
521 return $this;
522 }
523
524 /**
525 * UPDATE or INSERT
526 *
527 * @param string $column
528 * @param mixed $value
529 *
530 * @return integer / boolean
531 */
532 public function save($column = null, $value = null)
533 {
534 if ($column) {
535 $this->set($column, $value);
536 }
537 $st = $this->_build();
538 if ($lid = static::$db->lastInsertId()) {
539 return $lid;
540 } else {
541 return $st;
542 }
543 }
544
545 /**
546 * UPDATE
547 *
548 * @param string $column
549 * @param mixed $value
550 *
551 * @return boolean
552 */
553 public function update($column = null, $value = null)
554 {
555 if ($column) {
556 $this->set($column, $value);
557 }
558 return $this->_build(['only_update' => true]);
559 }
560
561 /**
562 * ORDER BY ASC
563 *
564 * @param string $column
565 *
566 * @return \Inc\Core\Lib\QueryBuilder
567 */
568 public function asc($column)
569 {
570 array_push($this->orders, "$column ASC");
571 return $this;
572 }
573
574 /**
575 * ORDER BY DESC
576 *
577 * @param string $column
578 *
579 * @return \Inc\Core\Lib\QueryBuilder
580 */
581 public function desc($column)
582 {
583 array_push($this->orders, "$column DESC");
584 return $this;
585 }
586
587 /**
588 * GROUP BY
589 *
590 * @param mixed $column
591 *
592 * @return \Inc\Core\Lib\QueryBuilder
593 */
594 public function group($columns)
595 {
596 if (is_array($columns)) {
597 foreach ($columns as $column) {
598 array_push($this->group_by, "$column");
599 }
600 } else {
601 array_push($this->group_by, "$columns");
602 }
603 return $this;
604 }
605
606 /**
607 * LIMIT
608 *
609 * @param integer $num
610 *
611 * @return \Inc\Core\Lib\QueryBuilder
612 */
613 public function limit($num)
614 {
615 $this->limit = " LIMIT $num";
616 return $this;
617 }
618
619 /**
620 * OFFSET
621 *
622 * @param integer $num
623 *
624 * @return \Inc\Core\Lib\QueryBuilder
625 */
626 public function offset($num)
627 {
628 $this->offset = " OFFSET $num";
629 return $this;
630 }
631
632 /**
633 * create array with all rows
634 *
635 * @return array
636 */
637 public function toArray()
638 {
639 $st = $this->_build();
640 return $st->fetchAll(\PDO::FETCH_ASSOC);
641 }
642
643 /**
644 * create object with all rows
645 *
646 * @return \stdObject[]
647 */
648 public function toObject()
649 {
650 $st = $this->_build();
651 return $st->fetchAll(\PDO::FETCH_OBJ);
652 }
653
654 /**
655 * create JSON array with all rows
656 *
657 * @return string
658 */
659 public function toJson()
660 {
661 $rows = $this->toArray();
662 return json_encode($rows, static::$options['json_options']);
663 }
664
665 /**
666 * create array with one row
667 *
668 * @param string $column
669 * @param mixed $value
670 *
671 * @return array
672 */
673 public function oneArray($column = null, $value = null)
674 {
675 if ($column !== null) {
676 $this->where($column, $value);
677 }
678 $st = $this->_build();
679 return $st->fetch(\PDO::FETCH_ASSOC);
680 }
681
682 /**
683 * create object with one row
684 *
685 * @param string $column
686 * @param mixed $value
687 *
688 * @return \stdObject
689 */
690 public function oneObject($column = null, $value = null)
691 {
692 if ($column !== null) {
693 $this->where($column, $value);
694 }
695 $st = $this->_build();
696 return $st->fetch(\PDO::FETCH_OBJ);
697 }
698
699 /**
700 * create JSON array with one row
701 *
702 * @param string $column
703 * @param mixed $value
704 *
705 * @return string
706 */
707 public function oneJson($column = null, $value = null)
708 {
709 if ($column !== null) {
710 $this->where($column, $value);
711 }
712 $row = $this->oneArray();
713 return json_encode($row, static::$options['json_options']);
714 }
715
716 /**
717 * returns rows count
718 *
719 * @return integer
720 */
721 public function count()
722 {
723 $st = $this->_build('count');
724 return $st->fetchColumn();
725 }
726
727 /**
728 * Last inserted id
729 *
730 * @return integer
731 */
732 public function lastInsertId()
733 {
734 return static::$db->lastInsertId();
735 }
736
737 /**
738 * DELETE
739 *
740 * @param string $column
741 * @param mixed $value
742 */
743 public function delete($column = null, $value = null)
744 {
745 if ($column !== null) {
746 $this->where($column, $value);
747 }
748 $st = $this->_build('delete');
749 return $st->rowCount();
750 }
751
752 /**
753 * Create SQL query
754 *
755 * @param $type `default`, `delete`, `count`
756 *
757 * @return string
758 */
759 public function toSql($type = 'default')
760 {
761 $sql = '';
762 $sql_where = '';
763 $sql_having = '';
764
765 // build conditions
766 $conditions = implode(' ', $this->conditions);
767 $conditions = str_replace(['( ', ' )'], ['(', ')'], $conditions);
768 if ($conditions) {
769 $sql_where .= " WHERE $conditions";
770 }
771
772 // build having
773 $having = implode(' ', $this->having);
774 if ($having) {
775 $sql_having .= " HAVING $having";
776 }
777
778 // if some columns have set value then UPDATE or INSERT
779 if ($this->sets) {
780 // get table columns
781 $table_cols = $this->_getColumns();
782
783 // Update updated_at column if exists
784 if (in_array('updated_at', $table_cols) && !array_key_exists('updated_at', $this->sets)) {
785 $this->set('updated_at', time());
786 }
787
788 // if there are some conditions then UPDATE
789 if (!empty($this->conditions)) {
790 $insert = false;
791 $columns = implode('=?,', array_keys($this->sets)) . '=?';
792 $this->set_binds = array_values($this->sets);
793 $sql = "UPDATE $this->table SET $columns";
794 $sql .= $sql_where;
795
796 return $sql;
797 }
798 // if there aren't conditions, then INSERT
799 else {
800 // Update created_at column if exists
801 if (in_array('created_at', $table_cols) && !array_key_exists('created_at', $this->sets)) {
802 $this->set('created_at', time());
803 }
804
805 $columns = implode(',', array_keys($this->sets));
806 $this->set_binds = array_values($this->sets);
807 $qs = implode(',', array_fill(0, count($this->sets), '?'));
808 $sql = "INSERT INTO $this->table($columns) VALUES($qs)";
809 $this->condition_binds = array();
810
811 return $sql;
812 }
813 } else {
814 if ($type == 'delete') {
815 // DELETE
816 $sql = "DELETE FROM $this->table";
817 $sql .= $sql_where;
818
819 return $sql;
820 } else {
821 // SELECT
822 $columns = implode(',', $this->columns);
823 if (!$columns) {
824 $columns = '*';
825 }
826 if ($type == 'count') {
827 $columns = "COUNT($columns) AS count";
828 }
829 $sql = "SELECT $columns FROM $this->table";
830 $joins = implode(' ', $this->joins);
831 if ($joins) {
832 $sql .= " $joins";
833 }
834 $order = '';
835 if (count($this->orders) > 0) {
836 $order = ' ORDER BY ' . implode(',', $this->orders);
837 }
838
839 $group_by = '';
840 if (count($this->group_by) > 0) {
841 $group_by = ' GROUP BY ' . implode(',', $this->group_by);
842 }
843
844 $sql .= $sql_where . $group_by . $order . $sql_having . $this->limit . $this->offset;
845
846 return $sql;
847 }
848 }
849
850 return null;
851 }
852 /**
853 * build SQL query
854 *
855 * @param array $type `default`, `delete`, `count`
856 *
857 * @return PDOStatement
858 */
859 protected function _build($type = 'default')
860 {
861 return $this->_query($this->toSql($type));
862 }
863
864 /**
865 * execute SQL query
866 *
867 * @param string $sql
868 *
869 * @return PDOStatement
870 */
871 protected function _query($sql)
872 {
873 $binds = array_merge($this->set_binds, $this->condition_binds);
874 $st = static::$db->prepare($sql);
875 foreach ($binds as $key => $bind) {
876 $pdo_param = \PDO::PARAM_STR;
877 if (is_int($bind)) {
878 $pdo_param = \PDO::PARAM_INT;
879 }
880 $st->bindValue($key+1, $bind, $pdo_param);
881 }
882 $st->execute();
883 static::$last_sqls[] = $sql;
884 return $st;
885 }
886
887 /**
888 * Get current table columns
889 *
890 * @return array
891 */
892 protected function _getColumns()
893 {
894 $q = $this->pdo()->query("PRAGMA table_info(".$this->table.")")->fetchAll();
895 return array_column($q, 'name');
896 }
897}