การใช้งาน Query Builder

// เรียกใช้โมเดล
$model = new \Kotchasan\Model;
// เรียกใช้ Query Builder
$query = $model->db()->createQuery();
// SELECT FROM world WHERE id=1000
$query->select();
$query->from('world');
$query->where(array('id', 1000));
// ประมวลผลคำสั่ง SQL สามารถให้ผลลัพท์ได้หลายรายการ
$result = $query->execute();
// ผลลัพท์ 1000
echo $result[0]->id;

$result = $model->db()->createQuery()
  ->select()
  ->from('world')
  ->where(array('id', 1000))
  ->execute();

$query = $model->db()->createQuery()
  ->select()
  ->from('world')
  ->where(array('id', 1000));
foreach($query->execute() as $item) {
  echo $item->id;
}

// SELECT id FROM world WHERE id=1000 LIMIT 1
$result = $model->db()->createQuery()
  ->from('world')
  ->where(array('id', 1000))
  ->first('id');
print_r($result);

// SELECT * FROM `gcms_plus`.`world` WHERE `id` = 1000
$query = $model->db()->createQuery()->select()->from('world')->where(array('id', 1000));
echo $query->text();

/*
SELECT W.`id`,U.`name` FROM `gcms_plus`.`world` AS W
LEFT JOIN `gcms_plus`.`user` AS U ON W.`member_id` = U.`id`
WHERE `id` > 1 AND `id` <= 10000 LIMIT 5
*/
$query = $model->db()->createQuery()
  ->select('W.id', 'U.name')
  ->join('user U', 'LEFT', array('W.member_id', 'U.id'))
  ->from('world W')
  ->where(array(
    array('id', '>', 1),
    array('id', '<=', 10000)
  ))
  ->limit(5);
  $result = $query->execute();

/*
(
  SELECT `id` FROM `gcms_plus`.`world` WHERE `id` IN (1, 2, 3)
) UNION (
  SELECT `id` FROM `gcms_plus`.`world` WHERE `id` IN (3, 4, 5)
)
*/
$q1 = $model->db()->createQuery()
  ->select('id')
  ->from('world')
  ->where(array('id', array(1, 2, 3)));
$q2 = $model->db()->createQuery()
  ->select('id')
  ->from('world')
  ->where(array('id', array(3, 4, 5)));
$result = $model->db()->createQuery()
  ->union($q1, $q2)
  ->execute();

// DELETE FROM `gcms_plus`.`world` WHERE `id` = 1000 LIMIT 1
$model->db()->createQuery()->delete('world', 1000)->first();
// DELETE FROM `gcms_plus`.`world` WHERE `id` = 1000
$model->db()->createQuery()->delete('world', 1000)->execute();

// INSERT INTO `gcms_plus`.`world` (`id`, `created_at`) VALUES (1000, '2016-09-14 20:41:36')
$model->db()->createQuery()->insert('world', array(
    'id' => 1000,
    'created_at' => date('Y-m-d H:i:s')
  ))
  ->execute();

// UPDATE `gcms_plus`.`world` SET `name` = 'ทดสอบ', `user_id` = 1 WHERE `id` = 1000 LIMIT 1
$model->db()->createQuery()
  ->update('world')
  ->where(1000)
  ->set(array(
    'name' => 'ทดสอบ',
    'user_id' => 1
  ))
  ->first();

// SELECT * FROM `gcms_plus`.`world` WHERE `id` NOT IN (1, 2, 3) AND `name` LIKE '%test%'
$result = $model->db()->createQuery()
  ->select()
  ->from('world')
  ->where(array(
    array('id', 'NOT IN', array(1, 2, 3)),
    array('name', 'LIKE', '%test%')
  ))
  ->execute();

// FROM `gcms_plus`.`world`
$query = $model->db()->createQuery()->from('world');
// SELECT COUNT(*) AS `count` FROM `gcms_plus`.`world`
$count = $query->selectCount()->execute();
var_dump($count);
SELECT * FROM `gcms_plus`.`world` LIMIT 1
$result = $query->select()->limit(1)->execute();
var_dump($result);

/*
SELECT `id`,COUNT(*) AS `count` FROM `gcms_plus`.`world`
WHERE `id` NOT IN (1, 2, 3) AND `name` LIKE '%test%'
GROUP BY `id` HAVING `id` > 1
*/
$result = $model->db()->createQuery()
  ->select('id', 'COUNT(*) count')
  ->from('world')
  ->where(array(
    array('id', 'NOT IN', array(1, 2, 3)),
    array('name', 'LIKE', '%test%')
  ))
  ->groupBy('id')
  ->having(array('id', '>', 1))
  ->execute();

// SELECT DISTINCT `id`, `name` FROM `gcms_plus`.`world`
$result = $model->db()->createQuery()
  ->selectDistinct('id', 'name')
  ->from('world')
  ->execute();

/*
SELECT `id`,COUNT(*) AS `count`,
(SELECT `id` FROM `gcms_plus`.`world` WHERE `id` = 1) AS `sub`
FROM `gcms_plus`.`world` WHERE `id` = 1
*/
$q1 = $model->db()->createQuery()
  ->select('id')
  ->from('world')
  ->where(1);
$result = $model->db()->createQuery()
  ->select('id', 'COUNT(*) count', array($q1, 'sub'))
  ->from('world')
  ->where(1)
  ->execute();