Zend\Db\Sql tương tác cơ sở dữ liệu với các lệnh SQL

Zend\Db\Sql

Zend\Db\Sql là một lớp trừu tượng hóa SQL dùng để truy vấn hệ quản trị CSDL thông qua một hệ thống các API hướng đối tượng. Kết quả của Zend\Db\Sql sẽ cho bạn một Statement và ParamerContainer biểu diễn mục tiêu truy vấn hoặc một chuỗi biểu diễn đầy đủ câu lệnh SQL để bạn truy vấn trực tiếp đến CSDL. Và tất nhiên Zend\Db\Sql làm việc cần đến một đối tượng Zend\Db\Adapter\Adapter như trình bày ở phần trước: sử dụng Adapter kết nối dữ liệu

Để sử dụng Sql luôn cần có một Adapter, ở trong các phần trình bày ở đây biến $adapter biểu diễn đối tượng Zend\Db\Adapter\Adapter đã có

Tương tác với một hệ quản trị CSDL như bạn đã biết cần các thao tác: select, insert, update, delete. Với Zend\Db\Sql nó sẽ giúp bạn tạo ra bốn loại đối tượng cho bốn câu lệnh đó để sử dụng. Ví dụ:

use Zend\Db\Sql\Sql;


$sql    = new Sql($adapter);
$select = $sql->select(); // trả về Zend\Db\Sql\Select
$insert = $sql->insert(); // trả về Zend\Db\Sql\Insert
$update = $sql->update(); // trả về Zend\Db\Sql\Update
$delete = $sql->delete(); // trả về Zend\Db\Sql\Delete

Có được các đối tượng Select, Insert, Update, Delete bạn sẽ tương tác trên đối tượng đó để thiết lập tham số chi tiết các truy vấn và nó đã sẵn sàng để prepare hoặc thi hành.

Ví dụ thực hiện câu lệnh Select có Prepare tạo ra Statement trước

Xem thêm: Query Preparation

use Zend\Db\Sql\Sql;

$sql    = new Sql($adapter);
$select = $sql->select();

$select->from('foo');
$select->where(['id' => 2]);

$statement = $sql->prepareStatementForSqlObject($select);

$results = $statement->execute();

Ví dụ thực hiện câu lệnh Select không cần Prepare

use Zend\Db\Sql\Sql;

$sql    = new Sql($adapter);
$select = $sql->select();
$select->from('foo');
$select->where(['id' => 2]);

$selectString = $sql->buildSqlString($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);

Khởi tạo Sql có thể chi ra luôn bảng mà nó tương tác, lúc đấy các đối tượng Select, Update ... tạo ra cũng đã chứa tên bảng tương tác

$sql    = new Sql($adapter, 'foo');

$select = $sql->select();
$select->where(['id' => 2]); // $select coi như đã có ->from('foo')

Các đối tượng Select, Update, Delete, Insert đều triển khai từ giao diện PreparableSqlInterface và SqlInterface nên nó các các phương thức chung: prepareStatement($adapter, $statement) để preparing và getSqlString($adapter = null) để lấy chuỗi câu lệnh SQL.

Select

Zend\Db\Sql\Select dùng để xây dựng câu lệnh truy vấn Select, bạn có thể khởi tạo trực tiếp hoặc từ đối tượng Sql như trên.

Nó có các hằng số và phương thức hay dùng sau:

    JOIN_INNER = 'inner'; JOIN_OUTER = 'outer';
    JOIN_LEFT = 'left';
    JOIN_RIGHT = 'right';
    SQL_STAR = '*';
    ORDER_ASCENDING = 'ASC';
    ORDER_DESCENDING = 'DESC';

    $select->from('foo');
    $select->from(['t' => 'table']); //SELECT "t".* FROM "table" AS "t"
    $select->from(['t' => new TableIdentifier('table')]);

    $select->columns(['CategoryID', 'namec' => 'CategoryName'], false);
    KQ: Select "CategoryID" AS "CategoryID", "CategoryName" AS "namec" ...

$select->join(
    'foo',              // tên bảng join
    'id = bar.id',      // biểu thức join on
    ['bar', 'baz'],     // (tùy chọn) chọn các cột tử bảng join
    $select::JOIN_OUTER // (tùy chọn) kiểu join, xem JOIN
);

//Cú pháp đầy đủ
where($predicate, $combination = Predicate\PredicateSet::OP_AND)
//$predicate có thể là Closure|string|array|Predicate
//$combination cách nối vào biểu thức như AND, OR

$select->where("CategoryName like '%abc%'");
//WHERE CategoryName like '%abc%'

$select->where(['express1', 'express2']);
//WHERE express1 AND express2

null => Predicate\IsNull
array => Predicate\In
string => Predicate\Operator

$select->from('foo')->where([
    'c1' => null,
    'c2' => [1, 2, 3],
    new \Zend\Db\Sql\Predicate\IsNotNull('c3'),
]);
//WHERE "c1" IS NULL AND "c2" IN (?, ?, ?) AND "c3" IS NOT NULL

    $select->where->nest() // dấu ngoặc (
    ->isNull('column1')
    ->or
    ->equalTo('column1', '2')
    ->unnest();  // đóng ngoặc )
    ->equalTo('column2', '3');

$select->where(function (Where $where) {
    $where->like('username', 'ralph%');
});

$select->order('id DESC'); // -> 'id' DESC
$select->order(['name ASC', 'age DESC']); // -> 'name' ASC, 'age' DESC
$select
    ->order('id DESC')
    ->order('name ASC, age DESC'); // -> 'id' DESC, 'name' ASC, 'age' DESC

$select->order('id DESC'); // -> 'id' DESC
$select->order(array('name ASC', 'age DESC')); // -> 'name' ASC, 'age' DESC

$select->limit(5); // LIMIT '5'
$select->offset(10); // OFFSET '10'

Insert

Đối tượng Zend\Db\Sql\Insert để thực hiện câu lệnh SQL Insert

Cách dùng như sau:

//(1)Khởi tạo với new hoặc từ $sql
$insert = new Insert('Categories');
//$insert = $sql->insert('Categories');
//$insert->into('Categories');

//(2) Các cột chèn dữ liệu
$insert->columns(['CategoryName', 'Description']);

//(3) Thiết lập tập dữ liệu theo thứ tự
$insert->values(
    [
        'New Name',
        'New Description'
    ]
);

//(4) Nếu cần bổ xung, cập nhật
$insert->values(
    [
        'CategoryName' => 'New NameCate',
    ],
    $insert::VALUES_MERGE
);

//(5) Thi hành lệnh Insert
$stmt = $sql->prepareStatementForSqlObject($insert);
$result = $stmt->execute();

//(6) Lấy giá trị tự phát sinh (ví dụ ID)
$id = $result->getGeneratedValue();

Insert có thể lấy giá trị từ một câu lệnh Select bằng cách $insert->values($select)

Update

Đối tượng Zend\Db\Sql\Update để thực hiện câu lệnh SQL Update

Cách dùng như sau:

//(1) Khởi tạo
$update = new Update('Categories');

//(2) Cập nhật mảng cột/giá trị cập nhật
$update->set([
    'CategoryName' => 'Category Update Value',
    'Description' => 'Description Value ...',
]);

//(3) Biểu thích where xác định các dòng được cập nhật
$update->where(['CategoryID' => 100]);


//(4) Thi hành lệnh
$stmt = $sql->prepareStatementForSqlObject($update);
$stmt->execute();

Delete

Đối tượng Zend\Db\Sql\Delete để thực hiện câu lệnh SQL Delete

Cách dùng như sau:

$delete = new Delete('Categories');
$delete->where(['CategoryID' => 100]);

$stmt = $sql->prepareStatementForSqlObject($delete);
$stmt->execute();

Nhớ khi dùng Update, Delete luôn cần có where, nếu thiếu có thể dẫn đền toàn bộ các dòng của bảng được cập nhật hay tất cả các dòng bị xóa

Where, Having

Trong các đối tượng dùng để xây dựng lên các câu lệnh SQL ở trên, luôn có phương thức where() và thuộc tính tính ->where; để xây dựng nên câu lệnh. Trong Zend Framework đối tượng đó biểu diễn bởi Where (đối tượng Having tương tự). Having và Where thực chất là một, gần như là tên tắt của Zend\Db\Sql\Predicate

Bạn có thể tạo và thiết lập Where, Having sau đó cập nhật vào Select, Insert, Update ...

Where, Having có nhiều phương thức tiện dụng giúp xây dựng lên phần Where của câu lệnh SQL

Để tạo ra đối tượng Where và Having đơn giản

$where  = new Where();
//$having = new Having();


//Cập nhật vào câu sệnh $select, $update, $delete ...
$delete->where($where);

equalTo(), lessThan(), greaterThan(), lessThanOrEqualTo(), greaterThanOrEqualTo()

$where->equalTo('CategoryID', 100); //WHERE `CategoryID` = '100'

/*Tương đương
$where->addPredicate(
    new Operator('CategoryID',Operator::OPERATOR_EQUAL_TO,100 )
    );*/

like, notLike

$where->notLike('CategoryName','%ev%'); // `CategoryName` NOT LIKE '%ev%'

/*Tương đương
$where->addPredicate(
    new NotLike('CategoryName','%ev%')
);

literal

Thêm chuỗi cố định vào Where

$where->literal('CategoryID = 4');//WHERE CategoryID = 4

/*Tương đương
$where->addPredicate(
    new Literal('CategoryID = 4')
);

expression

Thêm biểu thức

$where->expression('COUNT(id)=?',1); // COUNT(id)='1'
$where->addPredicate(
    new \Zend\Db\Sql\Predicate\Expression('COUNT(id)=?',1)
);

isNull,isNotNull

$where->isNull('Description'); //WHERE `Description` IS NULL
$where->addPredicate(
    new IsNull('Description')
);

in,notIn

$where->in('CategoryID',[10,20]);//WHERE `CategoryID` IN ('10', '20') 
$where->addPredicate(
    new In('CategoryID',[10,20])
);

between,notBetween

$where->between('CategoryID',10,20);//WHERE `CategoryID` BETWEEN '10' AND '20'
$where->addPredicate(
    new Between('CategoryID',10,20)
);