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 StatementParamerContainer 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 PreparableSqlInterfaceSqlInterface 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:

Phương thức Diễn giải
Hằng số
    JOIN_INNER = 'inner'; JOIN_OUTER = 'outer';
    JOIN_LEFT = 'left';
    JOIN_RIGHT = 'right';
    SQL_STAR = '*';
    ORDER_ASCENDING = 'ASC';
    ORDER_DESCENDING = 'DESC';
from($table) $table = string|array|TableIdentifier chọn tên bảng
    $select->from('foo');
    $select->from(['t' => 'table']); //SELECT "t".* FROM "table" AS "t"
    $select->from(['t' => new TableIdentifier('table')]);
columns($columns) Chọn các cột, $colums là mảng tên cột.
    $select->columns(['CategoryID', 'namec' => 'CategoryName'], false);
    KQ: Select "CategoryID" AS "CategoryID", "CategoryName" AS "namec" ...
join()
$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
);
where(), having() Cập nhật đối tượng Where, Having lưu trong thuộc tính wherehaving (cú pháp tương đồng, nên chỉ trình bày phần Where)
//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

Sử dụng tham số chuỗi

Zend\Db\Sql\Predicate\Expression sẽ dùng chuỗi khởi biểu thức where
$select->where("CategoryName like '%abc%'");
//WHERE CategoryName like '%abc%'

Sử dụng mảng chỉ số số nguyên

Các giá trị trong mảng được dùng để xây dựng nên Expression

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

Sử dụng mảng liên kết

Tùy thuộc vào giá trị phần tử, nó tự động chuyển như sau:

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

Ví dụ

$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

Xây dựng biểu thức where phức tạp với thuộc tính where

Cách này truy vấn trực tiếp tới thuộc tính where là đối tượng Where của Select (không phải phương thức where ở trên)

Ví dụ, xây dựng câu lệnh SELECT * WHERE (column1 is null or column1 = 2) AND (column2 = 3)

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

Sử dụng Closure

Cách này bạn cung cấp hàm callback theo đúng cấu trúc ở dưới, nó sẽ nhận đối tượng Where của Select, và callback xử lý theo đối tượng này

$select->where(function (Where $where) {
    $where->like('username', 'ralph%');
});
from($table)
$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
order
$select->order('id DESC'); // -> 'id' DESC
$select->order(array('name ASC', 'age DESC')); // -> 'name' ASC, 'age' DESC
limit(), offset()
$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)
);
Đăng ký theo dõi ủng hộ kênh