[KOHANA] Bài 6 Lệnh thao tác truy vấn cơ sở dữ liệu (tt) - dùng Query Buider

2. Dùng query dựng sẳn (Query Builder)

chú ý tất cả các lệnh chỉ thực thi sau khi gọi $result = $query->execute(); như bài trước đã đề cập

 2.1 Select

Sử dụng  DB::select();

Chọn từ bảng  $query = DB::select()->from('users');

có điều kiện $query = DB::select()->from('users')->where('username', '=', 'john');

nhiều điều kiện $query = DB::select()->from('users')->where('username', '=', 'john')->or_where('username', '=', 'jane');

ngoài ra có thể sử dụng các toán tử  IN, BETWEEN, >, =<, !=, etc. Ví dụ thêm:

$query = DB::select()->from('users')->where('logins', '<=', 1);

 $query = DB::select()->from('users')->where('logins', '>', 50);

 $query = DB::select()->from('users')->where('username', 'IN', array('john','mark','matt'));

 $query = DB::select()->from('users')->where('joindate', 'BETWEEN', array($then, $now));

Chọn cột chi tiết

$query = DB::select('username', 'password')->from('users')->where('username', '=', 'john');

Nếu kiểm tra thì dùng 

echo Kohana::debug((string) $query);

// kết quả là :

// SELECT `username`, `password` FROM `users` WHERE `username` = 'john'

Tạo bí danh cho cột Select - AS (column aliases) bằng cách chuyển một mảng thông số cho  DB::select:

$query = DB::select(array('username', 'u'), array('password', 'p'))->from('users');

Câu lệnh trên tương tự như câu lệnh chuẩn SELECT `username` AS `u`, `password` AS `p` FROM `users`

 chọn với distinct (Select - DISTINCT)  một cột duy nhất có thể set TRUE hay FALSE cho phương thức distinct() như ví dụ sa u:

$query = DB::select('username')->distinct(TRUE)->from('posts');

Câu lệnh query tương ứng là 

SELECT DISTINCT `username` FROM `posts`

Select - LIMIT & OFFSET

Sử dụng  limit() và offset() cho yêu cầu tương ứng .

 $query = DB::select()->from(`posts`)->limit(10)->offset(30);

Câu lệnh tương ứng là 

SELECT * FROM `posts` LIMIT 10 OFFSET 30

Select - ORDER BY

Thực hiện query select với yêu cầu sắp xếp

$query = DB::select()->from(`posts`)->order_by(`published`, `DESC`);

tương đương:

SELECT * FROM `posts` ORDER BY `published` DESC

 

2.2 Insert

để thêm record vào CSDL dùng  DB::insert để tạo câu lệnh INSERT với giá trị được chuyển vào xem ví dụ sau 

$query = DB::insert('users', array('username', 'password'))->values(array('fred', 'p@5sW0Rd'));

Câu lệnh tương ứng là 

INSERT INTO `users` (`username`, `password`) VALUES ('fred', 'p@5sW0Rd')

2.3 Update

Để thay đổi một record thì dùng DB::update để thực hiện caou6 lệnh  truy vấn UPDATE :

 $query = DB::update('users')->set(array('username' => 'jane'))->where('username', '=', 'john');

câu lệnh tương ứng 

UPDATE `users` SET `username` = 'jane' WHERE `username` = 'john'

2.4 Delete

xóa 1  record, use DB::delete để tạo truy vấn DELETE như sau:

 $query = DB::delete('users')->where('username', 'IN', array('john', 'jane'));

câu lệnh tương ứng 

DELETE FROM `users` WHERE `username` IN ('john', 'jane')

3. Truy vấn nâng cao

 

3.1 Joins

hãy xem cách thực hiện thông qua các ví dụ sau

// This query will find all the posts related to "smith" with JOIN

$query = DB::select('authors.name', 'posts.content')->from('authors')->join('posts')->on('authors.id', '=', 'posts.author_id')->where('authors.name', '=', 'smith');

câu lệnh tương ứng 

SELECT `authors`.`name`, `posts`.`content` FROM `authors` JOIN `posts` ON (`authors`.`id` = `posts`.`author_id`) WHERE `authors`.`name` = 'smith'

Bạn cũng có thể dụng LEFT, RIGHT or INNER JOIN nếu thích với kiểu  join('colum_name', 'type_of_join'):

 xem ví dụ

// This query will find all the posts related to "smith" with LEFT JOIN

$query = DB::select()->from('authors')->join('posts', 'LEFT')->on('authors.id', '=', 'posts.author_id')->where('authors.name', '=', 'smith');

câu lệnh tương ứng 

SELECT `authors`.`name`, `posts`.`content` FROM `authors` LEFT JOIN `posts` ON (`authors`.`id` = `posts`.`author_id`) WHERE `authors`.`name` = 'smith'

4. Các hàm CSDL (Database Functions)

Sử dụng Count

$query = DB::select(array('COUNT("username")', 'total_users'))->from('users');

Câu lệnh tương ứng 

SELECT COUNT(`username`) AS `total_users` FROM `users`

chú ý : Khi xây dụng những truy vấn phức tạp. bạn cần lấy tổng dòng trả về Muốn vậy thì phải xây dụng 1 danh sách các cột rỗng trước. Rồi tạo ra một truy vấn nhái và thêm hàm COUNT vào sao chép của một cột và danh sách cột vào những cái khác. Với cách này thì sẽ làm giảm tổng số dòng của lệnh và sẽ dể điều chỉnh truy vấn hơn . xem ví dụ sau 

$query = DB::select()->from('users')

    ->join('posts')->on('posts.username', '=', 'users.username')

    ->where('users.active', '=', TRUE)

    ->where('posts.created', '>=', $yesterday);

 

$total = clone $query;

$total->select(array('COUNT( DISTINCT "username")', 'unique_users'));

$query->select('posts.username')->distinct();

Các hàm tính toán nâng cao như  COUNT(), SUM(), AVG(), v,v . sẽ sử dụng với  group_by()  và having() 

xem ví dụ

$query = DB::select('username', array('COUNT("id")', 'total_posts')

    ->from('posts')->group_by('username')->having('total_posts', '>=', 10);

câu lệnh tương đượng

SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10

Lồng query (subquery)

$sub = DB::select('username', array('COUNT("id")', 'total_posts')

    ->from('posts')->group_by('username')->having('total_posts', '>=', 10);

 $query = DB::select('profiles.*', 'posts.total_posts')->from('profiles')

    ->join(array($sub, 'posts'), 'INNER')->on('profiles.username', '=', 'posts.username');

Câu lệnh tương đương

SELECT `profiles`.*, `posts`.`total_posts` FROM `profiles` INNER JOIN

( SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10 ) AS posts

ON `profiles`.`username` = `posts`.`username`

Truy vấn Insert queries sử dụng  select query cho giá trị chèn 

 $sub = DB::select('username', array('COUNT("id")', 'total_posts')

    ->from('posts')->group_by('username')->having('total_posts', '>=', 10);

 $query = DB::insert('post_totals', array('username', 'posts'))->select($sub);

câu ;lệnh tương đương

INSERT INTO `post_totals` (`username`, `posts`) 

SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10 

Boolean Operators and Nested Clauses

xem thông qua các ví dụ 

?

$query = DB::select()->from('users')

    ->where_open()

        ->or_where('id', 'IN', $expired)

        ->and_where_open()

            ->where('last_login', '<=', $last_month)

            ->or_where('last_login', 'IS', NULL)

        ->and_where_close()

    ->where_close()

    ->and_where('removed','IS', NULL);

tương ứng 

SELECT * FROM `users` WHERE ( `id` IN (1, 2, 3, 5) OR ( `last_login` <= 1276020805 OR `last_login` IS NULL ) ) AND `removed` IS NULL 

Sử dụng biểu thức 

xem ví dụ

?

$query = DB::update('users')->set(array('login_count' => DB::expr('login_count + 1')))->where('id', '=', $id);

nếu với  $id = 45:

 thì câu lệnh tương đương là 

UPDATE `users` SET `login_count` = `login_count` + 1 WHERE `id` = 45

Đây là một ví dụ tính toán khoảng cách địa lý 

$query = DB::select(array(DB::expr('degrees(acos(sin(radians('.$lat.')) * sin(radians(`latitude`)) + cos(radians('.$lat.')) * cos(radians(`latitude`)) * cos(radians(abs('.$lng.' - `longitude`))))) * 69.172'), 'distance'))->from('locations');