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');