การใช้งาน Query Builder

Query Builder เป็นเครื่องมือสำหรับจัดการกับฐานข้อมูลรูปแบบหนึ่ง ที่ออกแบบมาเพื่อลดข้อแตกต่างระหว่างฐานข้อมูล ซึ่งโดยทั่วๆไปแล้ว คำสั่งของ SQL บางตัว สำหรับฐานข้อมูลแต่ละประเภท เช่น MySQL หรือ MSSQL มันแตกต่างกัน ทำให้เวลาที่จะเปลี่ยนฐานข้อมูลทีเราจะต้องมาเขียนคำสั่งเพื่อจัดการกับฐานข้อมูลใหม่ แต่ถ้าเราเขียนคำสั่ง SQL ผ่าน Query Builder เราจะไม่ต้องกังวลกับข้อแตกต่างนี้ เพราะ Query Builder จะทำการแปลงคำสั่ง SQL ให้เหมาะสมกับฐานข้อมูลที่เลือกใช้เอง

ตัวอย่างการเรียกใช้ 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;

การใช้งาน Query Builder เริ่มต้นขึ้นเมื่อมีการเรียกคำสั่ง createQuery()
คำสั่งถัดมาก็จะเป็นคำสั่งที่เกี่ยวกับ Query ทั้งหมด ซึ่งถ้าสังเกตุจะเห็นว่าคำสั่งของ Query Builder จะมีลักษณะคล้ายคลึงกับคำสั่งของ SQL ปกติ
ในการเรียกใช้คำสั่งของ Query Builder อาจเรียกใช้ต่อๆกันไป เหมือนการต่อคำสั่ง SQL เลยก็ได้ เช่น

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

ซึ่งคำสั่งสุดท้ายคือ execute() เป็นคำสั่งในการประมวลผลคำสั่งที่เขียนขึ้นก่อนหน้าทั้งหมด และส่งผลลัพท์ออกมาเป็นข้อมูลที่ Query ได้ ซึ่งการใช้คำสั่ง execute() สามารถให้ผลลัพท์ของการ Query ได้หลายรายการ เช่น

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

หากต้องการ Query ข้อมูลที่ต้องการผลลัพท์เพียงรายการเดียว เราจะใช้ first() แทน

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

ผลลัพท์

stdClass Object ( [id] => 1000 )

ในกรณีที่ต้องการดูผลลัพท์คำสั่ง SQL ก่อนการประมวลผล สามารถใช้คำสั่ง text() เพื่อดูผลลัพท์ได้

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

การ JOIN ข้อมูล

/*
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();

UNION

/*
(
  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);

การนับจำนวน ร่วมกับการใช้ GROUP BY และ HAVING

/*
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();

การจำกัดผลลัพท์ไม่ให้ซ้ำกันด้วย DISTINCT

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

หมายเหตุ
การจำกัดจำนวนผลลัพท์ใช้คำสั่ง limit() เช่น limit(1) แปลงเป็น SQL หมายถึง LIMIT 1 หรือ limit(5, 10) แปลงเป็น SQL จะหมายถึง LIMIT 10, 5 (ต้องการข้อมูลจำนวน 5 รายการ ตั้งแต่รายการที่ 10 -15)
คำสั่ง where() ของ Query Builder สามารถระบุค่าได้หลายรูปแบบ เช่น

• where(1) หมายถึง WHERE id=1 ซึ่ง id ก็คือฟิลด์ที่เป็น Key และเป็น Auto Increment ซึ่งต้องมีอยู่ในทุกตารางอยู่แล้ว หรืออาจใช้เป็น where(array('id', 1)) ก็ได้
• where(array('id', array(1, 2, 3))) หรือ where(array('id', 'IN', array(1, 2, 3))) หมายถึง WHERE id IN (1, 2, 3)
• where(array(array('id', 1), array('id', 2)), 'OR') หมายถึง WHERE `id` = 1 OR `id` = 2
• where(array('id', 'NOT IN', '(SELECT id FROM x) Q')) หมายถึง WHERE `id` NOT IN (SELECT id FROM x) AS `Q`

คำสั่ง insert ของ Query Builder คืนค่าว่าสำเร็จหรือไม่เท่านั้น ถ้าต้องการผลลัพท์เป็น id ที่เพิ่มใหม่ให้ใช้คำสั่งของ CRUD
คำสั่ง select และ first สามารถระบุพารามิเตอร์เป็น ชื่อฟิลด์ Query String หรือ Query Builder ก็ได้ด้วย

/*
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();

ค่าเริ่มต้นของ Query Builder จะคืนค่าผลลัพท์เป็น Object หรือ แอเรย์ของ Object (หากผลลัพท์มีหลายรายการ) หากต้องการให้ผลลัพท์เป็นแอเรย์สามารถใช้ร่วมกับเมธอด toArray() ได้