การใช้งาน Query Builder ใน CodeIgniter 4

<?php namespace App\Controllers; 

use CodeIgniter\Controller; // เรียกใช้งาน Controller class

class Helloworld extends Controller 
{
    public function index() 
    {	
			$db = db_connect(); 
			$builder = $db->table('tbl_provinces');		
    }
}

$builder = $db->table('tbl_provinces');		
$query   = $builder->get();  // SELECT * FROM `tbl_provinces`
// $query   = $builder->get(5);  // SELECT * FROM `tbl_provinces` LIMIT 5
// $query = $builder->get(10, 20); // SELECT * FROM `tbl_provinces` LIMIT 20, 10

// ดูเกี่ยวกับการใช้งาน Query Result หรือผลลัพธ์ได้ที่  http://niik.in/1002
$result = $query->getResultArray();

$builder = $db->table('tbl_provinces');		
$sql = $builder->getCompiledSelect();
echo $sql;	  // SELECT * FROM `tbl_provinces`

$builder = $db->table('tbl_provinces');		
$sql = $builder->limit(10,20)->getCompiledSelect(false);
echo $sql;	 // SELECT * FROM `tbl_provinces` LIMIT 20, 10

$builder = $db->table('tbl_provinces');		
$builder->limit(10,20)->getCompiledSelect(false);
$sql = $builder->select('province_id, province_name')->getCompiledSelect();
echo $sql; // SELECT `province_id`, `province_name` FROM `tbl_provinces` LIMIT 20, 10

$query = $builder->getWhere(['province_id' => 1]); 
// SELECT * FROM `tbl_provinces` WHERE `province_id` = 1
//$query = $builder->getWhere(['province_id < ' => 5], 2); 
// SELECT * FROM `tbl_provinces` WHERE `province_id` < 5 LIMIT 2
// $query = $builder->getWhere(['province_id < ' => 5], 2,1); 
// SELECT * FROM `tbl_provinces` WHERE `province_id` < 5 LIMIT 1, 2	

$builder->select('province_id, province_name as name');
$query   = $builder->get();  
// SELECT `province_id`, `province_name` as `name` FROM `tbl_provinces`

$builder->select('
	province_id, 
	province_name as name'
);

$builder = $db->table('tbl_provinces');		
$builder->select('
	(SELECT province_name FROM tbl_provinces 
	WHERE province_name LIKE "%บุรี" LIMIT 1) as myprovince
', false);
$query   = $builder->get();  
// SELECT 
//		(SELECT province_name FROM tbl_provinces 
// 		WHERE province_name LIKE "%บุรี" LIMIT 1) as myprovince 
// FROM `tbl_provinces`

$builder = $db->table('tbl_provinces');		
$builder->selectMax('province_id');
// SELECT MAX(`province_id`) AS `province_id` FROM `tbl_provinces`
$builder->selectMax('province_id','maxID');
// SELECT MAX(`province_id`) AS `maxID` FROM `tbl_provinces`

$builder->selectCount('province_id','region');
$builder->groupBy('geo_id');
// SELECT COUNT(`province_id`) AS `region` FROM `tbl_provinces` GROUP BY `geo_id`

$builder = $db->table('tbl_provinces a');		
$builder->select('a.province_id,b.province_name');
$builder->from('tbl_provinces b');
// SELECT `a`.`province_id`, `b`.`province_name` FROM `tbl_provinces` `a`, `tbl_provinces` `b`

$builder = $db->table('tbl_provinces a');		
$builder->select('a.province_id,b.province_name');
$builder->from('tbl_provinces b');
$builder->from('tbl_provinces c');

$builder = $db->table('tbl_provinces a');		
$builder->select('a.province_id,b.province_name');
$builder->join('tbl_provinces b','a.province_id=b.province_id','inner');
// SELECT `a`.`province_id`, `b`.`province_name` 
// FROM `tbl_provinces` `a` 
// INNER JOIN `tbl_provinces` `b` 
// ON `a`.`province_id`=`b`.`province_id`

$builder = $db->table('tbl_provinces a');		
$builder->select('a.province_id,b.province_name');
$builder->join('tbl_provinces b','a.province_id=b.province_id','left');
$builder->join('tbl_provinces c','a.province_id=c.province_id','left');
// SELECT `a`.`province_id`, `b`.`province_name` 
// FROM `tbl_provinces` `a` 
// LEFT JOIN `tbl_provinces` `b` ON `a`.`province_id`=`b`.`province_id` 
// LEFT JOIN `tbl_provinces` `c` ON `a`.`province_id`=`c`.`province_id`

$builder->where('name', $name);
$builder->where('title', $title);
$builder->where('status', $status);
// WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

$builder->where('name !=', $name);
$builder->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45

$array = ['name' => $name, 'title' => $title, 'status' => $status];
$builder->where($array);
// WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

$array = ['name !=' => $name, 'id <' => $id, 'date >' => $date];
$builder->where($array);

$where = "name='Joe' AND status='boss' OR status='active'";
$builder->where($where);

$builder->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

$builder->where('advance_amount <', function(BaseBuilder $builder) {
    return $builder->select('MAX(advance_amount)', false)->from('orders')->where('id >', 2);
});
// WHERE "advance_amount" < (SELECT MAX(advance_amount) FROM "orders" WHERE "id" > 2)

$builder->where('name !=', $name);
$builder->orWhere('id >', $id);  // WHERE name != 'Joe' OR id > 50

$names = ['Frank', 'Todd', 'James'];
$builder->whereIn('username', $names);
// WHERE username IN ('Frank', 'Todd', 'James')
// มีค่าเหมือน WHERE username = 'Frank' OR username = 'Todd' OR username = 'James'

$builder->where('name !=', $name);
$names = ['Frank', 'Todd', 'James'];
$builder->whereIn('username', $names);
// WHERE name =! 'Joe' AND username IN ('Frank', 'Todd', 'James')

$builder->where('name !=', $name);
$names = ['Frank', 'Todd', 'James'];
$builder->orWhereIn('username', $names);
// WHERE name =! 'Joe' OR username IN ('Frank', 'Todd', 'James')

$names = ['Frank', 'Todd', 'James'];
$builder->whereNotIn('username', $names);
// WHERE username NOT IN ('Frank', 'Todd', 'James')

$names = ['Frank', 'Todd', 'James'];
$builder->orWhereNotIn('username', $names);
// OR username NOT IN ('Frank', 'Todd', 'James')

$builder->like('title', 'match');
// WHERE `title` LIKE '%match%' ESCAPE '!'

$builder->like('title', '20% match');
// WHERE `title` LIKE '%20!%match%' ESCAPE '!'

$builder->like('title', 'match');
$builder->like('body', 'match');
// WHERE `title` LIKE '%match%' ESCAPE '!' AND  `body` LIKE '%match%' ESCAPE '!'

$builder->like('title', 'match', 'before'); // WHERE `title` LIKE '%match' ESCAPE '!'
$builder->like('title', 'match', 'after');  // WHERE `title` LIKE 'match%' ESCAPE '!'
$builder->like('title', 'match', 'both');   // WHERE `title` LIKE '%match%' ESCAPE '!'
$builder->like('title', 'match');           // WHERE `title` LIKE '%match%' ESCAPE '!'

$array = ['title' => $match, 'page1' => $match, 'page2' => $match];
$builder->like($array);
// WHERE `title` LIKE '%match%' ESCAPE '!' AND  `page1` LIKE '%match%' ESCAPE '!' 
// AND  `page2` LIKE '%match%' ESCAPE '!'

$builder->groupBy("title"); // GROUP BY title

$builder->groupBy(["title", "date"]);  // GROUP BY title, date

$builder->distinct();
$builder->get(); // SELECT DISTINCT * FROM mytable

$builder->having('user_id = 45');  // HAVING user_id = 45
$builder->having('user_id',  45);  // HAVING user_id = 45

$builder->having(['title =' => 'My Title', 'id <' => $id]);
// HAVING title = 'My Title', id < 45

$builder->having('user_id',  45);  // HAVING `user_id` = 45 
$builder->having('user_id',  45, FALSE);  // HAVING user_id = 45

$groups = [1, 2, 3];
$builder->havingIn('group_id', $groups);
// HAVING group_id IN (1, 2, 3)

$groups = [1, 2, 3];
$builder->orHavingIn('group_id', $groups);
// OR group_id IN (1, 2, 3)

$groups = [1, 2, 3];
$builder->havingNotIn('group_id', $groups);
// HAVING group_id NOT IN (1, 2, 3)

$groups = [1, 2, 3];
$builder->havingNotIn('group_id', $groups);
// OR group_id NOT IN (1, 2, 3)

$builder->havingLike('title', 'match');
// HAVING `title` LIKE '%match%' ESCAPE '!'

$builder->havingLike('title', 'match'); $builder->orHavingLike('body', $match);
// HAVING `title` LIKE '%match%' ESCAPE '!' OR  `body` LIKE '%match%' ESCAPE '!'

$builder->notHavingLike('title', 'match');      // HAVING `title` NOT LIKE '%match% ESCAPE '!'

$builder->havingLike('title', 'match');
$builder->orNotHavingLike('body', 'match');
// HAVING `title` LIKE '%match% OR  `body` NOT LIKE '%match%' ESCAPE '!'

$builder->orderBy('title', 'DESC');
// ORDER BY `title` DESC

$builder->orderBy('title DESC, name ASC');
// ORDER BY `title` DESC, `name` ASC

$builder->orderBy('title', 'DESC');
$builder->orderBy('name', 'ASC');
// ORDER BY `title` DESC, `name` ASC

$builder->orderBy('title', 'RANDOM'); // ไม่สนใจฟิลด์ title
// ORDER BY RAND()

$builder->orderBy(42, 'RANDOM'); // ค่า 42 คือ seed value
// ORDER BY RAND(42)

$builder->limit(10);  //  LIMIT 10

$builder->limit(10, 20);  // LIMIT 20, 10 

$builder = $db->table('tbl_provinces');		
echo $builder->countAllResults();    //  แสดง 77 รายการ เพราะยังไม่มีเงื่อนไขการกรอง
$builder->select('province_id,province_name');
$builder->where('province_id > 60');

// เมื่อมีเงื่อนการกรองข้อมูลเข้ามา จำนวนรายการก็ลดลง เหลือเฉพาะจำนวนรายการตามเงื่อนไข
// แต่กรณีนี้คำสั่ง select() และเงื่อนไขต่างในคำสั่ง SQL จะถูกรีเซ็ต
echo $builder->countAllResults();  // แสดง 17 รายการ
$query   = $builder->get();  
echo $db->getLastQuery();
// SELECT * FROM `tbl_provinces`

$builder = $db->table('tbl_provinces');		
echo $builder->countAllResults();    //  แสดง 77 รายการ
$builder->select('province_id,province_name');
$builder->where('province_id > 60');

// กรณีนี้คำสั่ง select() จะไม่ถูกรีเซ็ตค่า และคำสั่ง SQL จะยังคงเงื่อนไขตามที่กำหนดเดิม
echo $builder->countAllResults(false);  // แสดง 17 รายการ
$query   = $builder->get();  
echo $db->getLastQuery();
// SELECT `province_id`, `province_name` FROM `tbl_provinces` WHERE `province_id` > 60

$builder = $db->table('tbl_provinces');		
echo $builder->countAll();    //  แสดง 77 รายการ 
$builder->select('province_id,province_name');
$builder->where('province_id > 60');

// กรณีนี้คำสั่ง select() จะไม่ถูกรีเซ็ตค่า และคำสั่ง SQL จะยังคงเงื่อนไขตามที่กำหนดเดิม
echo $builder->countAll(false);  // แสดง 77 รายการ
$query   = $builder->get();  
echo $db->getLastQuery();
// SELECT `province_id`, `province_name` FROM `tbl_provinces` WHERE `province_id` > 60
// ถ้าไม่กำหนดเป็น false คำสั่ง SQL จะเป็น SELECT * FROM `tbl_provinces`

$query = $builder->select('title')
                 ->where('id', $id)
                 ->limit(10, 20)
                 ->get();

$query = $builder->select('title')->where('id', $id)->limit(10, 20)->get();

$builder->select('title');
$builder->where('id', $id);
$builder->limit(10, 20);
$query = $builder->get();

$builder->select('*')->from('my_table')
        ->groupStart()
                ->where('a', 'a')
                ->orGroupStart()
                        ->where('b', 'b')
                        ->where('c', 'c')
                ->groupEnd()
        ->groupEnd()
        ->where('d', 'd')
->get();

// ผลลัพธ์คำสั่ง SQL 
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'

$data = [
        'title' => 'My title',
        'name'  => 'My Name',
        'date'  => 'My date'
];

$builder->insert($data);
// INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')
echo $db->insertID();

class Myclass {
        public $title   = 'My Title';
        public $content = 'My Content';
        public $date    = 'My Date';
}

$object = new Myclass;
$builder->insert($object);
// INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date')
echo $db->insertID();

$data = [
        'title' => 'My title',
        'name'  => 'My Name',
        'date'  => 'My date'
];

$builder->ignore(true)->insert($data);
// INSERT OR IGNORE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

$data = [
        'title' => 'My title',
        'name'  => 'My Name',
        'date'  => 'My date'
];

$sql = $builder->set($data)->getCompiledInsert('mytable');
echo $sql;
// INSERT INTO mytable (`title`, `name`, `date`) VALUES ('My title', 'My name', 'My date')

echo $builder->set('title', 'My Title')->getCompiledInsert('mytable', FALSE);
// INSERT INTO mytable (`title`) VALUES ('My Title')

// คำสั่งเดิมยังอยู่ หากเราเพิ่มการกำหนดค่าเพิ่มเติม ก็จะไปต่อจากค่าเดิม
echo $builder->set('content', 'My Content')->getCompiledInsert();
// INSERT INTO mytable (`title`, `content`) VALUES ('My Title', 'My Content')

$data = [
        [
                'title' => 'My title',
                'name'  => 'My Name',
                'date'  => 'My date'
        ],
        [
                'title' => 'Another title',
                'name'  => 'Another Name',
                'date'  => 'Another date'
        ]
];

$builder->insertBatch($data);
// INSERT INTO mytable (title, name, date) VALUES 
// ('My title', 'My name', 'My date'),  ('Another title', 'Another name', 'Another date')

$data = [
        'title' => 'My title',
        'name'  => 'My Name',
        'date'  => 'My date'
];

$builder->replace($data);
// REPLACE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

$data = [
        'title' => 'My title',
        'name'  => 'My Name',
        'date'  => 'My date'
];
$builder->set($data)->replace();

$builder->set('name', $name);
$builder->insert();  // INSERT INTO mytable (`name`) VALUES ('{$name}')

$builder->set('name', $name);
$builder->set('title', $title);
$builder->set('status', $status);
$builder->insert();

$builder->set('field', 'field+1', FALSE);
$builder->where('id', 2);
$builder->update(); // UPDATE mytable SET field = field+1 WHERE `id` = 2

$builder->set('field', 'field+1');
$builder->where('id', 2);
$builder->update(); //  UPDATE `mytable` SET `field` = 'field+1' WHERE `id` = 2

$array = [
        'name'   => $name,
        'title'  => $title,
        'status' => $status
];

$builder->set($array);
$builder->insert();

class Myclass {
        public $title   = 'My Title';
        public $content = 'My Content';
        public $date    = 'My Date';
}

$object = new Myclass;
$builder->set($object);
$builder->insert();

// แบบที่ 1
$builder->where('id', $id);
$builder->update($data);

// แบบที่ 2
$builder->update($data, "id = 4");
// แบบที่ 3
$builder->update($data, ['id' => $id]);

$data = [
        'title' => $title,
        'name'  => $name,
        'date'  => $date
];

$builder->where('id', $id);
$builder->update($data);
//      UPDATE mytable
//      SET title = '{$title}', name = '{$name}', date = '{$date}'
//      WHERE id = $id

class Myclass {
        public $title   = 'My Title';
        public $content = 'My Content';
        public $date    = 'My Date';
}

$object = new Myclass;
$builder->where('id', $id);
$builder->update($object);
// UPDATE `mytable`
// SET `title` = '{$title}', `name` = '{$name}', `date` = '{$date}'
// WHERE id = `$id`

$data = [
   [
      'title' => 'My title' ,
      'name'  => 'My Name 2' ,
      'date'  => 'My date 2'
   ],
   [
      'title' => 'Another title' ,
      'name'  => 'Another Name 2' ,
      'date'  => 'Another date 2'
   ]
];

$builder->updateBatch($data, 'title');

// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
// `date` = CASE
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
// WHERE `title` IN ('My title','Another title')

$builder->delete(['id' => $id]);  // DELETE FROM mytable  // WHERE id = $id

$builder->where('id', $id);
$builder->delete();

// DELETE FROM mytable
// WHERE id = $id

$builder->emptyTable('mytable'); // DELETE FROM mytable

$builder->truncate();

// TRUNCATE mytable

// ปกติคำสั่ง getCompiledSelect() จะรีเซ็ตค่าให้แต่พอดีเรากำหนดให้การรีเซ็ตเป็น false 
$sql = $builder->select(['field1','field2'])
               ->where('field3',5)
               ->getCompiledSelect(false);

// ...
// นำคำสั่ง SQL ที่ได้ไปใช้งานบางอย่าง
// ...

$builder->resetQuery();

$builder = $db->table('tbl_provinces');		
$builder->select('province_id,province_name');
$builder->where('province_id > 60');
echo $builder->getCompiledSelect(false);
// ถึงตรงนี้คำสั่ง SQL จะประมาณ 
// SELECT `province_id`, `province_name` FROM `tbl_provinces` WHERE `province_id` > 60
$builder->resetQuery();
// แต่เมื่อรีเซ็ตไป ค่าก็จะไปเริ่มที่ SELECT * FROM tbl_provinces
$query   = $builder->get();