1: <?php
2: /**
3: * @filesource Kotchasan/Database/QueryBuilder.php
4: *
5: * @copyright 2016 Goragod.com
6: * @license http://www.kotchasan.com/license/
7: *
8: * @see http://www.kotchasan.com/
9: */
10:
11: namespace Kotchasan\Database;
12:
13: use Kotchasan\ArrayTool;
14:
15: /**
16: * SQL Query builder.
17: *
18: * @setup $driver = new PdoMysqlDriver;
19: * @setup $this = $driver->createQuery();
20: *
21: * @author Goragod Wiriya <admin@goragod.com>
22: *
23: * @since 1.0
24: */
25: class QueryBuilder extends \Kotchasan\Database\Query
26: {
27: /**
28: * ส่งออกผลลัพท์เป็น Array.
29: *
30: * @var bool
31: */
32: protected $toArray = false;
33: /**
34: * ตัวแปรเก็บพารามิเตอร์สำหรับการ bind.
35: *
36: * @var array
37: */
38: protected $values;
39:
40: /**
41: * Class constructor.
42: *
43: * @param object $db database driver
44: */
45: public function __construct(Driver $db)
46: {
47: $this->db = $db;
48: $this->values = array();
49: }
50:
51: /**
52: * ฟังก์ชั่นสร้างคำสั่ง WHERE ถ้ามีข้อมูล Where ก่อนหน้าจะ AND กับข้อมูลก่อนหน้า.
53: *
54: * @assert where(array('U.id', 1))->andWhere(array())->text() [==] " WHERE U.`id` = 1"
55: * @assert where(array('U.id', 1))->andWhere(array('U.id', 2))->text() [==] " WHERE (U.`id` = 1) AND (U.`id` = 2)"
56: * @assert where(array('U.id', 1))->andWhere(array(Sql::BETWEEN('id', 0, 1), Sql::BETWEEN('id', 0, 1)), 'OR')->text() [==] " WHERE (U.`id` = 1) AND (`id` BETWEEN 0 AND 1 OR `id` BETWEEN 0 AND 1)"
57: *
58: * @param mixed $condition query string หรือ array
59: * @param string $oprator defaul AND
60: * @param string $id Primary Key เช่น id (default)
61: *
62: * @return \static
63: */
64: public function andWhere($condition, $oprator = 'AND', $id = 'id')
65: {
66: if (!empty($condition)) {
67: $ret = $this->buildWhere($condition, $oprator, $id);
68: if (is_array($ret)) {
69: $this->sqls['where'] = empty($this->sqls['where']) ? $ret[0] : '('.$this->sqls['where'].') AND ('.$ret[0].')';
70: $this->values = ArrayTool::replace($this->values, $ret[1]);
71: } else {
72: $this->sqls['where'] = empty($this->sqls['where']) ? $ret : '('.$this->sqls['where'].') AND ('.$ret.')';
73: }
74: }
75:
76: return $this;
77: }
78:
79: /**
80: * นำเข้า property จาก Class อื่น.
81: *
82: * @param \Kotchasan\Orm\Recordset $src
83: *
84: * @return \static
85: */
86: public function assignment($src)
87: {
88: $this->sqls = array(
89: 'function' => 'customQuery',
90: 'select' => '*',
91: );
92: if ($src instanceof \Kotchasan\Orm\Recordset) {
93: $this->sqls['from'] = $src->getField()->getTableWithAlias();
94: }
95: foreach ($src->sqls as $k => $v) {
96: $this->sqls[$k] = $v;
97: }
98: $this->values = $src->getValues();
99:
100: return $this;
101: }
102:
103: /**
104: * เปิดการใช้งานแคช
105: * จะมีการตรวจสอบจากแคชก่อนการสอบถามข้อมูล.
106: *
107: * @param bool $auto_save (options) true (default) บันทึกผลลัพท์อัตโนมัติ, false ต้องบันทึกแคชเอง
108: *
109: * @return \static
110: */
111: public function cacheOn($auto_save = true)
112: {
113: $this->db()->cacheOn($auto_save);
114:
115: return $this;
116: }
117:
118: /**
119: * สำเนา Class เป็นอันใหม่.
120: *
121: * @return \static
122: */
123: public function copy()
124: {
125: return clone $this;
126: }
127:
128: /**
129: * ประมวลผลคำสั่ง SQL และคืนค่าจำนวนแถวของผลลัพท์
130: * คืนค่า จำนวนแถว.
131: *
132: * @return int
133: */
134: public function count()
135: {
136: if (!isset($this->sqls['select'])) {
137: $this->selectCount('* count');
138: }
139: $result = $this->toArray()->execute();
140:
141: return count($result) == 1 ? (int) $result[0]['count'] : 0;
142: }
143:
144: /**
145: * ฟังก์ชั่นสร้างคำสั่ง DELETE.
146: *
147: * @assert delete('user', array(array('id', 1), array('name', 'test')))->text() [==] "DELETE FROM `user` WHERE `id` = 1 AND `name` = 'test'"
148: *
149: * @param string $table
150: * @param mixed $condition query string หรือ array
151: *
152: * @return \static
153: */
154: public function delete($table, $condition)
155: {
156: $this->sqls['function'] = 'query';
157: $this->sqls['delete'] = $this->quoteTableName($table);
158: $this->where($condition);
159:
160: return $this;
161: }
162:
163: /**
164: * ประมวลผลคำสั่ง SQL
165: * คืนค่า แอเรย์ของผลลัพท์ ไม่พบข้อมูล คืนค่าแอเรย์ว่าง.
166: *
167: * @return array
168: */
169: public function execute()
170: {
171: $result = $this->db->execQuery($this->sqls, $this->values, $this->debugger);
172: if ($this->toArray) {
173: $this->toArray = false;
174: } elseif (is_array($result)) {
175: foreach ($result as $i => $items) {
176: $result[$i] = (object) $items;
177: }
178: }
179:
180: return $result;
181: }
182:
183: /**
184: * ฟังก์ชั่นสร้าง SQL EXISTS.
185: *
186: * @param string $table ชื่อตาราง
187: * @param mixed $condition query WHERE
188: *
189: * @assert select()->from('user U')->exists('useronline', array('member_id', 'U.id'))->text() [==] 'SELECT * FROM `user` AS U WHERE EXISTS (SELECT * FROM `useronline` WHERE `member_id` = U.`id`)'
190: * @assert select()->from('user U')->where(array('U.id', 1))->exists('useronline', array('member_id', 'U.id'))->text() [==] 'SELECT * FROM `user` AS U WHERE U.`id` = 1 AND EXISTS (SELECT * FROM `useronline` WHERE `member_id` = U.`id`)'
191: *
192: * @return \static
193: */
194: public function exists($table, $condition)
195: {
196: $ret = $this->buildWhere($condition);
197: if (is_array($ret)) {
198: $this->values = ArrayTool::replace($this->values, $ret[1]);
199: $ret = $ret[0];
200: }
201: if (empty($this->sqls['where'])) {
202: $this->sqls['where'] = '';
203: } else {
204: $this->sqls['where'] .= ' AND';
205: }
206: $this->sqls['where'] .= ' EXISTS (SELECT * FROM '.$this->quoteTableName($table).' WHERE '.$ret.')';
207:
208: return $this;
209: }
210:
211: /**
212: * คำสั่งสำหรับดูรายละเอียดการ Query.
213: *
214: * @return \static
215: */
216: public function explain()
217: {
218: $this->sqls['explain'] = true;
219:
220: return $this;
221: }
222:
223: /**
224: * ฟังก์ชั่นประมวลผลคำสั่ง SQL ข้อมูลต้องการผลลัพท์เพียงรายการเดียว
225: * คืนค่าผลลัพท์ที่พบเพียงรายการเดียว ไม่พบข้อมูลคืนค่า false.
226: *
227: * @param string $fields (option) รายชื่อฟิลด์ field1, field2, field3, ....
228: *
229: * @return object|array|bool
230: */
231: public function first($fields = '*')
232: {
233: if (func_num_args() > 1) {
234: $fields = func_get_args();
235: }
236: if (!empty($fields)) {
237: // ถ้ามีการระบุฟิลด์มา
238: call_user_func(array($this, 'select'), $fields);
239: }
240: if (empty($this->sqls['select'])) {
241: // เลือกทุกฟิลด์ ถ้ายังไม่มีฟิลด์ที่ถูกเลือก
242: call_user_func(array($this, 'select'), '*');
243: }
244: $this->sqls['limit'] = 1;
245: $result = $this->execute();
246:
247: return empty($result) ? false : $result[0];
248: }
249:
250: /**
251: * ฟังก์ชั่นสร้างคำสั่ง FROM.
252: *
253: * @assert select()->from('user')->text() [==] "SELECT * FROM `user`"
254: * @assert select()->from('user a', 'user b')->text() [==] "SELECT * FROM `user` AS `a`, `user` AS `b`"
255: *
256: * @param string $tables ชื่อตาราง table1, table2, table3, ....
257: *
258: * @return \static
259: */
260: public function from($tables)
261: {
262: $qs = array();
263: foreach (func_get_args() as $table) {
264: $qs[] = $this->quoteTableName($table);
265: }
266: if (count($qs) > 0) {
267: $this->sqls['from'] = implode(', ', $qs);
268: }
269:
270: return $this;
271: }
272:
273: /**
274: * คืนค่าแอเร์ยเก็บพารามิเตอร์สำหรับการ bind รวมกับ $values.
275: *
276: * @param array $values
277: *
278: * @return array
279: */
280: public function getValues($values = array())
281: {
282: if (empty($values)) {
283: return $this->values;
284: }
285: foreach ($this->values as $key => $value) {
286: $values[$key] = $value;
287: }
288:
289: return $values;
290: }
291:
292: /**
293: * GROUP BY.
294: *
295: * @assert select()->from('user')->groupBy('MONTH(`date`)', 'YEAR(`date`)')->text() [==] 'SELECT * FROM `user` GROUP BY MONTH(`date`), YEAR(`date`)'
296: * @assert select()->from('user')->groupBy('U.id')->text() [==] 'SELECT * FROM `user` GROUP BY U.`id`'
297: * @assert select()->from('user')->groupBy(array('id', 'username'))->text() [==] 'SELECT * FROM `user` GROUP BY `id`, `username`'
298: *
299: * @param string $fields รายชื่อฟิล์ด เช่น field1, field2, ...
300: *
301: * @return \static
302: */
303: public function groupBy($fields)
304: {
305: $args = is_array($fields) ? $fields : func_get_args();
306: $sqls = array();
307: foreach ($args as $item) {
308: if ($item instanceof Sql) {
309: $sqls[] = $item->text();
310: } elseif (strpos($item, '(') !== false) {
311: $sqls[] = $item;
312: } elseif (preg_match('/^(([a-z0-9]+)\.)?([a-z0-9_]+)?$/i', $item, $match)) {
313: $sqls[] = "$match[1]`$match[3]`";
314: }
315: }
316: if (count($sqls) > 0) {
317: $this->sqls['group'] = implode(', ', $sqls);
318: }
319:
320: return $this;
321: }
322:
323: /**
324: * HAVING.
325: *
326: * @param mixed $condition query string หรือ array
327: * @param string $oprator defaul AND
328: *
329: * @return \static
330: */
331: public function having($condition, $oprator = 'AND')
332: {
333: if (!empty($condition)) {
334: $ret = $this->buildWhere($condition, $oprator);
335: if (is_array($ret)) {
336: $this->sqls['having'] = $ret[0];
337: $this->values = ArrayTool::replace($this->values, $ret[1]);
338: } else {
339: $this->sqls['having'] = $ret;
340: }
341: }
342:
343: return $this;
344: }
345:
346: /**
347: * ฟังก์ชั่นสร้างคำสั่ง INSERT INTO
348: * สามารถกำหนดค่า value เป็น query string ได้.
349: *
350: * @assert insert('user', array('id' => 1, 'name' => 'test'))->text() [==] "INSERT INTO `user` (`id`, `name`) VALUES (1, 'test')"
351: *
352: * @param string $table ชื่อตาราง
353: * @param array $datas รูปแบบ array(key1=>value1, key2=>value2)
354: *
355: * @return \static
356: */
357: public function insert($table, $datas)
358: {
359: $this->sqls['function'] = 'query';
360: $this->sqls['insert'] = $this->getFullTableName($table);
361: foreach ($datas as $key => $value) {
362: if ($value[0] == '(' && $value[strlen($value) - 1] == ')') {
363: $this->sqls['keys'][$key] = $value;
364: } else {
365: $this->sqls['keys'][$key] = ':'.$key;
366: $this->values[':'.$key] = $value;
367: }
368: }
369:
370: return $this;
371: }
372:
373: /**
374: * ฟังก์ชั่นสร้างคำสั่ง INSERT INTO
375: * โดยทำการตรวจสอบ KEY ถ้ามีอยู่แล้วจะเป็นการ UPDATE ข้อมูล.
376: *
377: * @assert insertOrUpdate('user', array('id' => 1, 'name' => 'test'))->text() [==] "INSERT INTO `user` (`id`, `name`) VALUES (1, 'test') ON DUPLICATE KEY UPDATE `id`=VALUES(`id`), `name`=VALUES(`name`)"
378: *
379: * @param string $table ชื่อตาราง
380: * @param array $datas รูปแบบ array(key1=>value1, key2=>value2)
381: *
382: * @return \static
383: */
384: public function insertOrUpdate($table, $datas)
385: {
386: $this->insert($table, $datas);
387: $this->sqls['orupdate'] = array();
388: foreach ($datas as $key => $value) {
389: $this->sqls['orupdate'][] = "`$key`=VALUES(`$key`)";
390: }
391:
392: return $this;
393: }
394:
395: /**
396: * สร้างคำสั่ง JOIN.
397: *
398: * @assert join('user U', 'INNER', 1)->text() [==] " INNER JOIN `user` AS U ON `id` = 1"
399: * @assert join('user U', 'INNER', array('U.id', 'A.id'))->text() [==] " INNER JOIN `user` AS U ON U.`id` = A.`id`"
400: * @assert join('user U', 'INNER', array('U.id', '=', 'A.id'))->text() [==] " INNER JOIN `user` AS U ON U.`id` = A.`id`"
401: * @assert join('user U', 'INNER', array('id', '=', 1))->text() [==] " INNER JOIN `user` AS U ON `id` = 1"
402: * @assert join('user U', 'INNER', array(array('U.id', 'A.id'), array('U.id', 'A.id')))->text() [==] " INNER JOIN `user` AS U ON U.`id` = A.`id` AND U.`id` = A.`id`"
403: *
404: * @param string $table ชื่อตารางที่ต้องการ join เช่น table alias
405: * @param string|array $table ชื่อตารางที่ต้องการ join เช่น table alias หรือ (QueryBuilder, alias)
406: * @param string $type เข่น INNER OUTER LEFT RIGHT
407: * @param mixed $on query string หรือ array
408: *
409: * @return \static
410: */
411: public function join($table, $type, $on)
412: {
413: $ret = $this->buildJoin($table, $type, $on);
414: if (is_array($ret)) {
415: $this->sqls['join'][] = $ret[0];
416: $this->values = ArrayTool::replace($this->values, $ret[1]);
417: } else {
418: $this->sqls['join'][] = $ret;
419: }
420:
421: return $this;
422: }
423:
424: /**
425: * จำกัดผลลัพท์ และกำหนดรายการเริ่มต้น.
426: *
427: * @assert limit(10)->text() [==] " LIMIT 10"
428: * @assert limit(10, 1)->text() [==] " LIMIT 1,10"
429: *
430: * @param int $count จำนวนผลลัท์ที่ต้องการ
431: * @param int $start รายการเริ่มต้น
432: *
433: * @return \static
434: */
435: public function limit($count, $start = 0)
436: {
437: if (!empty($start)) {
438: $this->sqls['start'] = (int) $start;
439: }
440: if (!empty($count)) {
441: $this->sqls['limit'] = (int) $count;
442: }
443:
444: return $this;
445: }
446:
447: /**
448: * ฟังก์ชั่นสร้าง SQL NOT EXISTS.
449: *
450: * @param string $table ชื่อตาราง
451: * @param mixed $condition query WHERE
452: *
453: * @return \static
454: */
455: public function notExists($table, $condition)
456: {
457: $ret = $this->buildWhere($condition);
458: if (is_array($ret)) {
459: $this->values = ArrayTool::replace($this->values, $ret[1]);
460: $ret = $ret[0];
461: }
462: $this->sqls['where'] .= (empty($this->sqls['where']) ? ' ' : ' AND ').'NOT EXISTS (SELECT * FROM '.$this->quoteTableName($table).' WHERE '.$ret.')';
463:
464: return $this;
465: }
466:
467: /**
468: * ฟังก์ชั่นสร้างคำสั่ง WHERE ถ้ามีข้อมูล Where ก่อนหน้าจะ OR กับข้อมูลก่อนหน้า.
469: *
470: * @assert where(array('U.id', 1))->orWhere(array())->text() [==] " WHERE U.`id` = 1"
471: * @assert where(array('U.id', 1))->orWhere(array('U.id', 2))->text() [==] " WHERE (U.`id` = 1) OR (U.`id` = 2)"
472: * @assert where(array('U.id', 1))->orWhere(array(Sql::BETWEEN('id', 0, 1), Sql::BETWEEN('id', 0, 1)), 'OR')->text() [==] " WHERE (U.`id` = 1) OR (`id` BETWEEN 0 AND 1 OR `id` BETWEEN 0 AND 1)"
473: *
474: * @param mixed $condition query string หรือ array
475: * @param string $oprator defaul AND
476: * @param string $id Primary Key เช่น id (default)
477: *
478: * @return \static
479: */
480: public function orWhere($condition, $oprator = 'AND', $id = 'id')
481: {
482: if (!empty($condition)) {
483: $ret = $this->buildWhere($condition, $oprator, $id);
484: if (is_array($ret)) {
485: $this->sqls['where'] = empty($this->sqls['where']) ? $ret[0] : '('.$this->sqls['where'].') OR ('.$ret[0].')';
486: $this->values = ArrayTool::replace($this->values, $ret[1]);
487: } else {
488: $this->sqls['where'] = empty($this->sqls['where']) ? $ret : '('.$this->sqls['where'].') OR ('.$ret.')';
489: }
490: }
491:
492: return $this;
493: }
494:
495: /**
496: * สร้าง query เรียงลำดับ.
497: *
498: * @assert order('id', 'id ASC')->text() [==] " ORDER BY `id`, `id` ASC"
499: * @assert order('id ASC')->text() [==] " ORDER BY `id` ASC"
500: * @assert order('user.id DESC')->text() [==] " ORDER BY `user`.`id` DESC"
501: * @assert order('id ASCD')->text() [==] ""
502: *
503: * @param mixed $sorts array('field ASC','field DESC') หรือ 'field ASC', 'field DESC', ....
504: *
505: * @return \static
506: */
507: public function order($sorts)
508: {
509: $sorts = is_array($sorts) ? $sorts : func_get_args();
510: $ret = $this->buildOrder($sorts);
511: if (!empty($ret)) {
512: $this->sqls['order'] = $ret;
513: }
514:
515: return $this;
516: }
517:
518: /**
519: * SELECT `field1`, `field2`, `field3`, ....
520: *
521: * @assert select('U.id', 'email name', 'module')->text() [==] "SELECT U.`id`,`email` AS `name`,`module`"
522: * @assert select('"email" name', '0 id', '0 `ไอดี`')->text() [==] "SELECT 'email' AS `name`,0 AS `id`,0 AS `ไอดี`"
523: * @assert select("'email' name", '0 AS id', '0 AS ไอดี')->text() [==] "SELECT 'email' AS `name`,0 AS `id`,0 AS `ไอดี`"
524: * @assert select()->text() [==] "SELECT *"
525: * @assert select()->where(array('domain', Sql::strValue('kotchasan.com')))->text() [==] "SELECT * WHERE `domain` = 'kotchasan.com'"
526: * @assert select('name `ชื่อ นามสกุล`', 'U.`idcard` AS `เลขประชาชน`')->text() [==] "SELECT `name` AS `ชื่อ นามสกุล`,U.`idcard` AS `เลขประชาชน`"
527: * @assert select('table.field', '`table`.`field`')->text() [==] "SELECT `table`.`field`,`table`.`field`"
528: * @assert select('table.field field', '`table`.`field` `field`')->text() [==] "SELECT `table`.`field` AS `field`,`table`.`field` AS `field`"
529: * @assert select('table.field AS field', '`table`.`field` AS `field`')->text() [==] "SELECT `table`.`field` AS `field`,`table`.`field` AS `field`"
530: * @assert select('U.field', 'U1.`field`', 'NULL id')->text() [==] "SELECT U.`field`,U1.`field`,NULL AS `id`"
531: * @assert select('U.field field', 'U1.`field` `field`')->text() [==] "SELECT U.`field` AS `field`,U1.`field` AS `field`"
532: * @assert select('U.field AS field', 'U1.`field` AS `field`')->text() [==] "SELECT U.`field` AS `field`,U1.`field` AS `field`"
533: * @assert select(Sql::YEAR('create_date', 'year'), Sql::MONTH('create_date', 'month'))->text() [==] "SELECT YEAR(`create_date`) AS `year`,MONTH(`create_date`) AS `month`"
534: * @assert select(array(Sql::YEAR('create_date', 'year'), Sql::MONTH('create_date', 'month')))->text() [==] "SELECT YEAR(`create_date`) AS `year`,MONTH(`create_date`) AS `month`"
535: *
536: * @param string $fields (option) รายชื่อฟิลด์ field1, field2, field3, ....
537: *
538: * @return \static
539: */
540: public function select($fields = '*')
541: {
542: $qs = array();
543: if ($fields == '*') {
544: $qs[] = '*';
545: } else {
546: foreach (func_get_args() as $item) {
547: if (!empty($item)) {
548: $qs[] = $this->buildSelect($item);
549: }
550: }
551: }
552: if (count($qs) > 0) {
553: $this->sqls['function'] = 'customQuery';
554: $this->sqls['select'] = implode(',', $qs);
555: }
556:
557: return $this;
558: }
559:
560: /**
561: * สร้าง query สำหรับการนับจำนวน record.
562: *
563: * @assert selectCount()->from('user')->text() [==] "SELECT COUNT(*) AS `count` FROM `user`"
564: * @assert selectCount('id ids')->from('user')->text() [==] "SELECT COUNT(`id`) AS `ids` FROM `user`"
565: * @assert selectCount('id ids', 'field alias')->from('user')->text() [==] "SELECT COUNT(`id`) AS `ids`, COUNT(`field`) AS `alias` FROM `user`"
566: *
567: * @param mixed $fileds (option) 'field alias'
568: *
569: * @return \static
570: */
571: public function selectCount($fileds = '* count')
572: {
573: $args = func_num_args() == 0 ? array($fileds) : func_get_args();
574: $sqls = array();
575: foreach ($args as $item) {
576: if (preg_match('/^([a-z0-9_\*]+)([\s]+([a-z0-9_]+))?$/', trim($item), $match)) {
577: $sqls[] = 'COUNT('.($match[1] == '*' ? '*' : '`'.$match[1].'`').')'.(isset($match[3]) ? ' AS `'.$match[3].'`' : '');
578: }
579: }
580: if (count($sqls) > 0) {
581: $this->sqls['function'] = 'customQuery';
582: $this->sqls['select'] = implode(', ', $sqls);
583: }
584:
585: return $this;
586: }
587:
588: /**
589: * SELECT DISTINCT `field1`, `field2`, `field3`, ....
590: *
591: * @assert selectDistinct('id')->from('user')->text() [==] "SELECT DISTINCT `id` FROM `user`"
592: *
593: * @param string $fields (option) รายชื่อฟิลด์ field1, field2, field3, ....
594: *
595: * @return \static
596: */
597: public function selectDistinct($fields = '*')
598: {
599: call_user_func(array($this, 'select'), func_get_args());
600: $this->sqls['select'] = 'DISTINCT '.$this->sqls['select'];
601:
602: return $this;
603: }
604:
605: /**
606: * UPDATE ..... SET.
607: *
608: * @assert update('user')->set(array('key1' => 'value1', 'key2' => 2))->where(1)->text() [==] "UPDATE `user` SET `key1`=:Skey1, `key2`=:Skey2 WHERE `id` = 1"
609: * @assert update('user U')->set(array('U.key1' => 'value1', 'U.key2' => 2))->where(array('U.id', 1))->text() [==] "UPDATE `user` AS U SET U.`key1`=:SUkey1, U.`key2`=:SUkey2 WHERE U.`id` = 1"
610: * @assert update('user')->set(array('key1' => '(...)'))->text() [==] "UPDATE `user` SET `key1`=(...)"
611: * @assert update('user')->set(array('key1' => 'test (...)'))->text() [==] "UPDATE `user` SET `key1`=:Skey1"
612: * @assert update('user')->set('`reply`=`reply`+1')->text() [==] "UPDATE `user` SET `reply`=`reply`+1"
613: * @assert update('user')->set(array('id' => 1, '`reply`=`reply`+1'))->text() [==] "UPDATE `user` SET `id`=:Sid, `reply`=`reply`+1"
614: * @assert update('user')->set(array('create_date' => Sql::NOW()))->text() [==] "UPDATE `user` SET `create_date`=NOW()"
615: * @assert update('user')->set(array('create_date' => Sql::create('SELECT * FROM `a`')))->text() [==] "UPDATE `user` SET `create_date`=SELECT * FROM `a`"
616: * @assert update('user')->set(array('create_date' => 'U.id'))->text() [==] "UPDATE `user` SET `create_date`=U.`id`"
617: * @assert update('user')->set(array('create_date' => '111.11'))->text() [==] "UPDATE `user` SET `create_date`=:Screatedate"
618: * @assert update('user')->set(array('create_date' => 'user.user'))->text() [==] "UPDATE `user` SET `create_date`=:Screatedate"
619: *
620: * @param array|string $datas รูปแบบ array(key1 => value1, query_string) หรือ query_string
621: *
622: * @return \static
623: */
624: public function set($datas)
625: {
626: if (is_array($datas) || is_object($datas)) {
627: foreach ($datas as $key => $value) {
628: if (is_int($key)) {
629: $this->sqls['set'][$value] = $value;
630: } else {
631: $field = $this->fieldName($key);
632: $key = $this->aliasName($key, 'S');
633: if ($value instanceof QueryBuilder) {
634: $this->sqls['set'][$key] = $field.'=('.$value->text().')';
635: } elseif ($value instanceof Sql) {
636: $this->sqls['set'][$key] = $field.'='.$value->text();
637: } elseif (is_string($value)) {
638: if (preg_match('/^([A-Z][0-9]?)\.`?([A-Za-z0-9_]+)`?$/', $value, $match)) {
639: $this->sqls['set'][$key] = $field.'='.$match[1].'.`'.$match[2].'`';
640: } elseif (mb_strlen($value) > 2 && $value[0] === '(' && $value[mb_strlen($value) - 1] === ')') {
641: $this->sqls['set'][$key] = $field.'='.$value;
642: } else {
643: $this->sqls['set'][$key] = $field.'='.$key;
644: $this->sqls['values'][$key] = $value;
645: }
646: } else {
647: $this->sqls['set'][$key] = $field.'='.$key;
648: $this->sqls['values'][$key] = $value;
649: }
650: }
651: }
652: } else {
653: $this->sqls['set'][$datas] = $datas;
654: }
655:
656: return $this;
657: }
658:
659: /**
660: * คืนค่าข้อมูลเป็น Array
661: * ฟังก์ชั่นนี้ใช้เรียกก่อนการสอบถามข้อมูล.
662: *
663: * @return \static
664: */
665: public function toArray()
666: {
667: $this->toArray = true;
668:
669: return $this;
670: }
671:
672: /**
673: * UNION.
674: *
675: * @assert (Sql::create('SELECT * FROM `a`'), Sql::create('SELECT * FROM `b`'))->text() [==] "(SELECT * FROM `a`) UNION (SELECT * FROM `b`)"
676: * @assert (array(Sql::create('SELECT * FROM `a`'), Sql::create('SELECT * FROM `b`')))->text() [==] "(SELECT * FROM `a`) UNION (SELECT * FROM `b`)"
677: *
678: * @param array $querys แอเรย์ของ QueryBuilder หรือ Query String ที่จะนำม่า UNION
679: *
680: * @return \static
681: */
682: public function union($querys)
683: {
684: $this->sqls['union'] = array();
685: $querys = is_array($querys) ? $querys : func_get_args();
686: foreach ($querys as $item) {
687: if ($item instanceof QueryBuilder || $item instanceof Sql) {
688: $this->sqls['union'][] = $item->text();
689: } elseif (is_string($item)) {
690: $this->sqls['union'][] = $item;
691: } else {
692: throw new \InvalidArgumentException('Invalid arguments in union');
693: }
694: }
695: $this->sqls['function'] = 'customQuery';
696:
697: return $this;
698: }
699:
700: /**
701: * UNION ALL.
702: *
703: * @assert (Sql::create('SELECT * FROM `a`'), Sql::create('SELECT * FROM `b`'))->text() [==] "(SELECT * FROM `a`) UNION ALL (SELECT * FROM `b`)"
704: * @assert (array(Sql::create('SELECT * FROM `a`'), Sql::create('SELECT * FROM `b`')))->text() [==] "(SELECT * FROM `a`) UNION ALL (SELECT * FROM `b`)"
705: *
706: * @param array $querys แอเรย์ของ QueryBuilder หรือ Query String ที่จะนำม่า UNION ALL
707: *
708: * @return \static
709: */
710: public function unionAll($querys)
711: {
712: $this->sqls['unionAll'] = array();
713: $querys = is_array($querys) ? $querys : func_get_args();
714: foreach ($querys as $item) {
715: if ($item instanceof QueryBuilder || $item instanceof Sql) {
716: $this->sqls['unionAll'][] = $item->text();
717: } elseif (is_string($item)) {
718: $this->sqls['unionAll'][] = $item;
719: } else {
720: throw new \InvalidArgumentException('Invalid arguments in unionAll');
721: }
722: }
723: $this->sqls['function'] = 'customQuery';
724:
725: return $this;
726: }
727:
728: /**
729: * UPDATE
730: *
731: * @assert update('user')->set(array('key1'=>'value1', 'key2'=>2))->where(array(array('id', 1), array('id', 1)))->text() [==] "UPDATE `user` SET `key1`=:Skey1, `key2`=:Skey2 WHERE `id` = 1 AND `id` = 1"
732: *
733: * @param string $table [$table1, $table2, ....] ชื่อตาราง
734: *
735: * @return \static
736: */
737: public function update($table)
738: {
739: $this->sqls['function'] = 'query';
740: $updates = array();
741: foreach (func_get_args() as $tbl) {
742: $updates[] = $this->quoteTableName($tbl);
743: }
744: $this->sqls['update'] = implode(',', $updates);
745:
746: return $this;
747: }
748:
749: /**
750: * ฟังก์ชั่นสร้างคำสั่ง WHERE.
751: *
752: * @assert where(array())->text() [==] ""
753: * @assert where(1)->text() [==] " WHERE `id` = 1"
754: * @assert where(array('id', 1))->text() [==] " WHERE `id` = 1"
755: * @assert where(array('id', '1'))->text() [==] " WHERE `id` = '1'"
756: * @assert where(array('domain', 'domain.tld'))->text() [==] " WHERE `domain` = 'domain.tld'"
757: * @assert where(array(1, 1))->text() [==] " WHERE 1 = 1"
758: * @assert where(array('U.id', 'G.id'))->text() [==] " WHERE U.`id` = G.`id`"
759: * @assert where(array('date', '2016-1-1 30:30'))->text() [==] " WHERE `date` = '2016-1-1 30:30'"
760: * @assert where(array('id', '=', 1))->text() [==] " WHERE `id` = 1"
761: * @assert where(Sql::create('`id`=1 OR (SELECT ....)'))->text() [==] " WHERE `id`=1 OR (SELECT ....)"
762: * @assert where(array('id', '=', 1))->text() [==] " WHERE `id` = 1"
763: * @assert where(array('id', 'IN', array(1, 2, '3')))->text() [==] " WHERE `id` IN (1, 2, '3')"
764: * @assert where(array(array('social', '0'), Sql::create('(...)')))->text() [==] " WHERE `social` = '0' AND (...)"
765: * @assert where(array(array(Sql::MONTH('create_date'), 1), array(Sql::YEAR('create_date'), 1)))->text() [==] " WHERE MONTH(`create_date`) = 1 AND YEAR(`create_date`) = 1"
766: * @assert where(array(array('id', array(1, 'a')), array('id', array('G.id', 'G.`id2`'))))->text() [==] " WHERE `id` IN (1, 'a') AND `id` IN (G.`id`, G.`id2`)"
767: * @assert where(array(array('id', array(1, 'a')), array('id', array('', 'th'))))->text() [==] " WHERE `id` IN (1, 'a') AND `id` IN ('', 'th')"
768: * @assert where(array('ip', 'NOT IN', array('', '192.168.1.104')))->text() [==] " WHERE `ip` NOT IN ('', '192.168.1.104')"
769: * @assert where(array('U.id', '(SELECT CASE END)'))->text() [==] " WHERE U.`id` = '(SELECT CASE END)'"
770: * @assert where(array(array(Sql::YEAR('create_date'), Sql::YEAR('S.`create_date`'))))->text() [==] " WHERE YEAR(`create_date`) = YEAR(S.`create_date`)"
771: * @assert where(array('U.id', Sql::strValue('G.id')))->text() [==] " WHERE U.`id` = 'G.id'"
772: * @assert where(Sql::ISNULL('U.id'))->text() [==] " WHERE U.`id` IS NULL"
773: * @assert where(array(array('create_date', 'A'), Sql::BETWEEN('id', 'ทดสอบ', 'ทดสอบ')))->text() [==] " WHERE `create_date` = 'A' AND `id` BETWEEN 'ทดสอบ' AND 'ทดสอบ'"
774: * @assert where(array(array(Sql::BETWEEN('id', 0, 1), 'OR', Sql::BETWEEN('id', 0, 1)), array(Sql::BETWEEN('id', 0, 1), 'OR', Sql::BETWEEN('id', 0, 1))), 'OR')->text() [==] " WHERE `id` BETWEEN 0 AND 1 OR `id` BETWEEN 0 AND 1 OR `id` BETWEEN 0 AND 1 OR `id` BETWEEN 0 AND 1"
775: *
776: * @param mixed $condition query string หรือ array
777: * @param string $oprator defaul AND
778: * @param string $id Primary Key เช่น id (default)
779: *
780: * @return \static
781: */
782: public function where($condition, $oprator = 'AND', $id = 'id')
783: {
784: if (!empty($condition)) {
785: $sql = Sql::WHERE($condition, $oprator, $id);
786: $this->sqls['where'] = $sql->text();
787: $this->values = $sql->getValues($this->values);
788: }
789:
790: return $this;
791: }
792: }
793: