Yii Query Builder menyediakan cara berorientasi objek dalam menulis statement SQL. Fitur ini membantu pengembang untuk menggunakan property dan method kelas untuk menentukan bagian-bagian dari statement SQL yang kemudian menggabungkannya menjasi sebuah statement SQL yang valid yang bisa dieksekusi lebih lanjut oleh method DAO seperti yang dideskripsikan di Data Access Objects. Berikut akan ditampilkan penggunaan umum dari Query Builder untuk membangun sebuah statement SQL SELECT: Show
$user = Yii::app()->db->createCommand()
->select('id, username, profile')
->from('tbl_user u')
->join('tbl_profile p', 'u.id=p.user_id')
->where('id=:id', array(':id'=>$id))
->queryRow(); Query Builder sangat cocok digunakan ketika Anda memerlukan menggabungkan sebuah statement SQL secara prosedural, atau berdasarkan suatu kondisi logis dalam aplikasi Anda. Manfaat utama dalam menggunakan Query Builder termasuk:
Menggunakan Query Builder bukanlah sebuah keharusan. Bahkan, jika query Anda cukup sederhana, akan leih gampang dan cepat menulis SQL-nya langsung.
1. Mempersiapkan Query Builder ¶Query Builder Yii disediakan oleh CDbCommand, kelas query DB utama di Data Access Objects. Untuk menggunakan Query Builder, kita membuat sebuah instance baru dari CDbCommand dengan cara berikut, $command = Yii::app()->db->createCommand(); Begitulah, kita menggunakan Perhatikan bahwa alih-alih kita mem-pass semua statement SQL ke 2. Membangung Query Penarik Data ¶Query penarik data merujuk pada statement SELECT pada SQL. Query builder menyediakan sekumpulan method untuk membangun bagian dari statement SELECT. DIkarenakan semua method ini mengembalikan instance CDbCommand, kita dapat memanggil mereka dengan menggunakan method chaining, seperti pada contoh di awal.
Berikut, kami akan menunjukkan bagaimana menggunakan method-method query builder ini. Supaya sederhana, kami mengasumsi database yang digunakan adalah MySQL. Perhatikan bahwa jika Anda menggunakan DBMS yang lain, quote table/kolom/nilai akan berbeda dengan contoh. select()function select($columns='*') Method select() menentukan bagian Berikut ini merupakan beberapa contoh: // SELECT *
select()
// SELECT `id`, `username`
select('id, username')
// SELECT `tbl_user`.`id`, `username` AS `name`
select('tbl_user.id, username as name')
// SELECT `id`, `username`
select(array('id', 'username'))
// SELECT `id`, count(*) as num
select(array('id', 'count(*) as num')) selectDistinct()function selectDistinct($columns) Method
selectDistinct() mirip dengan select(). Hanya saja selectDistinct mengaktifkan flag SELECT DISTINCT `id`, `username` from()Method
from() menentukan bagian Berikut merupakan beberapa contoh: // FROM `tbl_user`
from('tbl_user')
// FROM `tbl_user` `u`, `public`.`tbl_profile` `p`
from('tbl_user u, public.tbl_profile p')
// FROM `tbl_user`, `tbl_profile`
from(array('tbl_user', 'tbl_profile'))
// FROM `tbl_user`, (select * from tbl_profile) p
from(array('tbl_user', '(select * from tbl_profile) p')) where()function where($conditions, $params=array()) Method where() menetapkan bagian array(operator, operand1, operand2, ...) dengan
Berikut merupakan beberapa contoh yang
menggunakan // WHERE id=1 or id=2
where('id=1 or id=2')
// WHERE id=:id1 or id=:id2
where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))
// WHERE id=1 OR id=2
where(array('or', 'id=1', 'id=2'))
// WHERE id=1 AND (type=2 OR type=3)
where(array('and', 'id=1', array('or', 'type=2', 'type=3')))
// WHERE `id` IN (1, 2)
where(array('in', 'id', array(1, 2))
// WHERE `id` NOT IN (1, 2)
where(array('not in', 'id', array(1,2)))
// WHERE `name` LIKE '%Qiang%'
where(array('like', 'name', '%Qiang%'))
// WHERE `name` LIKE '%Qiang' AND `name` LIKE '%Xue'
where(array('like', 'name', array('%Qiang', '%Xue')))
// WHERE `name` LIKE '%Qiang' OR `name` LIKE '%Xue'
where(array('or like', 'name', array('%Qiang', '%Xue')))
// WHERE `name` NOT LIKE '%Qiang%'
where(array('not like', 'name', '%Qiang%'))
// WHERE `name` NOT LIKE '%Qiang%' OR `name` NOT LIKE '%Xue%'
where(array('or not like', 'name', array('%Qiang%', '%Xue%'))) Perhatikan bahwa ketika menggunakan operator $keyword=$_GET['q'];
// escape % and _ characters
$keyword=strtr($keyword, array('%'=>'\%', '_'=>'\_'));
$command->where(array('like', 'title', '%'.$keyword.'%')); order()Method
order() menentukan bagian Berikut merupakan beberapa contohnya: // ORDER BY `name`, `id` DESC
order('name, id desc')
// ORDER BY `tbl_profile`.`name`, `id` DESC
order(array('tbl_profile.name', 'id desc')) limit() dan offset()function limit($limit, $offset=null)
function offset($offset) Method limit() dan offset() menentukan bagian Berikut merupakan beberapa contoh: // LIMIT 10
limit(10)
// LIMIT 10 OFFSET 20
limit(10, 20)
// OFFSET 20
offset(20) join() dan varian-variannyafunction join($table, $conditions, $params=array())
function leftJoin($table, $conditions, $params=array())
function rightJoin($table, $conditions, $params=array())
function crossJoin($table)
function naturalJoin($table) Method join() dan varian-variannya menentukan bagaimana melakukan join dengan table lain dengan menggunakan Perhatikan bahwa tidak seperti method query builder lainnya, setiap pemanggilan method join akan ditambahkan di belakang sebelumnya. Berikut merupakan beberapa contohnya. // JOIN `tbl_profile` ON user_id=id
join('tbl_profile', 'user_id=id')
// LEFT JOIN `pub`.`tbl_profile` `p` ON p.user_id=id AND type=1
leftJoin('pub.tbl_profile p', 'p.user_id=id AND type=:type', array(':type'=>1)) group()Method group() menetapkan bagian Berikut beberapa contoh: // GROUP BY `name`, `id`
group('name, id')
// GROUP BY `tbl_profile`.`name`, `id`
group(array('tbl_profile.name', 'id') having()function having($conditions, $params=array()) Method
having() menetapkan bagian Berikut contoh-contohnya: // HAVING id=1 or id=2
having('id=1 or id=2')
// HAVING id=1 OR id=2
having(array('or', 'id=1', 'id=2')) union()Method union() menentukan bagian Contoh: // UNION (select * from tbl_profile)
union('select * from tbl_profile') Menjalankan QuerySetelah melakukan pemanggilan method query builder di atas, kita dapat memanggil method DAO seperti yang dijelaskan pada Data Access Objects untuk mengeksekusi query. Misalnya, kita dapat memanggil CDbCommand::queryRow() untuk mendapatkan sebaris hasil atau CDbCommand::queryAll() untuk mendapatkan seluruhnya sekaligus. Berikut beberapa contohnya : $users = Yii::app()->db->createCommand()
->select('*')
->from('tbl_user')
->queryAll(); Mengambil SQL-SQLSelain menjalankan query yang dibuat oleh Query Builder, kita juga dapat menarik statement SQL bersangkutan. Untuk melakukannya gunakan fungsi CDbCommand::getText(). $sql = Yii::app()->db->createCommand()
->select('*')
->from('tbl_user')
->text; Jika terdapat parameter tertentu yang terikat pada query, mereka dapat diambil melalui properti CDbCommand::params. Sintaks Alternatif untuk Membentuk QueryKadangkala, menggunakan method chaining bukanlah pilihan yang tepat. Query Builder Yii memungkinkan kita untuk membuat query dengan menggunakan assignment property object yang sederhana. Pada umumnya, untuk setiap method query builder, terdapat sebuah property yang memiliki nama yang sama. Meng-assign nilai ke property sama saja dengan memanggil method tersebut. Misalnya, berikut merupakan dua statement
yang ekuivalen, dengan asumsi $command->select(array('id', 'username'));
$command->select = array('id', 'username'); Selain itu, method CDbConnection::createCommand() dapat menerima array sebagai parameter. Pasangan nama-nilai di array akan digunakan untuk inisialisasi property instance CDbCommand yang dibuat. Ini artinya, kita dapat menggunakan code berikut untuk membuat sebuah query: $row = Yii::app()->db->createCommand(array(
'select' => array('id', 'username'),
'from' => 'tbl_user',
'where' => 'id=:id',
'params' => array(':id'=>1),
))->queryRow(); Membangun Beberapa QuerySebuah instance CDbCommand dapat dipakai ulang beberapa kali untuk membuat beberapa query. Namun, sebelum membuat query baru, harus memanggil method CDbCommand::reset() terlebih dahulu untuk menghapus query sebelumnya. Misalnya: $command = Yii::app()->db->createCommand();
$users = $command->select('*')->from('tbl_users')->queryAll();
$command->reset(); // clean up the previous query
$posts = $command->select('*')->from('tbl_posts')->queryAll(); 3. Membuat Query Manipulasi Data ¶Query manipulasi data adalah statement SQL untuk melakukan insert, update dan delete data dalam table
database. Query builder menyediakan
Di bawah ini kami akan memaparkan method-method query manipulasi data insert()function insert($table, $columns) Method
insert() membuat dan menjalankan statement SQL Berikut merupakan contohnya:
// buat dan jalankan SQL berikut :
// INSERT INTO `tbl_user` (`name`, `email`) VALUES (:name, :email)
$command->insert('tbl_user', array(
'name'=>'Tester',
'email'=>'',
)); update()function update($table, $columns, $conditions='', $params=array()) Method update() akan membuat dan mengeksekusi statement Berikut merupakan contohnya: // buat dan jalankan SQL berikut:
// UPDATE `tbl_user` SET `name`=:name WHERE id=:id
$command->update('tbl_user', array(
'name'=>'Tester',
), 'id=:id', array(':id'=>1)); delete()function delete($table, $conditions='', $params=array()) Method delete() membuat dan menjalankan
statement SQL Berikut salah satu contoh: //buat dan eksekusi SQL berikut:
// DELETE FROM `tbl_user` WHERE id=:id
$command->delete('tbl_user', 'id=:id', array(':id'=>1)); 4. Membuat Query Manipulasi Schema ¶Selain query manipulasi dan penarikan normal, query builder juga menyediakan sekumpulan method yang digunakan untuk membuat dan menjalankan query SQL untuk manipulasi schema pada database. Query builder mendukung query-query berikut:
Tipe Data AbstrakQuery builder memperkenalkan sekumpulan tipe data abstrak yang dapat digunakan untuk mendefinisikan kolom table. Tidak seperti tipe data physical yang spesifik pada DBMS tertentu dan cukup berbeda di DBMS lainnya, tipe data abstrak bebas dari DBMS. Ketika tipe data abstrak digunakan untuk mendefinisikan kolom table, query builder akan mengubahnya menjadi tipe data physical bersangkutan. Berikut tipe data abstrak yang didukung oleh query builder.
createTable()function createTable($table, $columns, $options=null) Method createTable() akan membuat dan
menjalankan statement SQL untuk menghasilkan sebuah table. Parameter Ketika menentukan definisi sebuah kolom, kita dapat
menggunakan tipe data abstrak seperti yang sudah dijelaskan di atas. Query builder akan mengubah tipe data abstrak tersebut menjadi tipe data physical bersangkuta, sesuai dengan DBMS yang digunakan. Misalnya, Sebuah definisi kolom juga bisa mengandung tipe data non-abstrak atau spesifikasi. Definisi kolom ini akan dmasukkan juga ke dalam SQL tanpa perubahan. Misalnya Berikut contoh bagaimana membuat sebuah table: // CREATE TABLE `tbl_user` (
// `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
// `username` varchar(255) NOT NULL,
// `location` point
// ) ENGINE=InnoDB
createTable('tbl_user', array(
'id' => 'pk',
'username' => 'string NOT NULL',
'location' => 'point',
), 'ENGINE=InnoDB') renameTable()function renameTable($table, $newName) Method renameTable() membuat dan menjalankan statement SQL untuk mengubah nama table. Parameter Berikut contoh bagaimana melakukan perubahan nama pada table : // RENAME TABLE `tbl_users` TO `tbl_user`
renameTable('tbl_users', 'tbl_user') dropTable()function dropTable($table) Method dropTable() membuat dan menjalankan statement SQL untuk menghapus sebuah table. Parameter Berikut contoh bagaimana menghapus sebuah table: // DROP TABLE `tbl_user`
dropTable('tbl_user') truncateTablefunction truncateTable($table) Method truncateTable() membuat dan menjalankan statement SQL untuk menghapus sebuah table. Parameter Berikut contoh bagaimana mengosongkan table: // TRUNCATE TABLE `tbl_user`
truncateTable('tbl_user') addColumn()function addColumn($table, $column, $type) Method addColumn() membuat dan menjalankan statement SQL untuk menambah kolom table baru. Parameter Berikut contoh menambah sebuah kolom table: // ALTER TABLE `tbl_user` ADD `email` varchar(255) NOT NULL
addColumn('tbl_user', 'email', 'string NOT NULL') dropColumn()function dropColumn($table, $column) Method dropColumn() membuat dan menjalankan statement SQL untuk menghapus kolom table. Parameter Berikut contoh bagaimana menghapus sebuah kolom table: // ALTER TABLE `tbl_user` DROP COLUMN `location`
dropColumn('tbl_user', 'location') renameColumn()function renameColumn($table, $name, $newName) Method renameColumn() membuat dan mengeksekusi statement SQL untuk
mengubah nama kolom table. Parameter Berikut contoh mengubah nama kolom table: // ALTER TABLE `tbl_users` CHANGE `name` `username` varchar(255) NOT NULL
renameColumn('tbl_user', 'name', 'username') alterColumn()function alterColumn($table, $column, $type) Method
alterColumn() membuat dan menjalankan statement SQL untuk mengubah kolom table. Parameter Berikut contoh mengubah kolom table: // ALTER TABLE `tbl_user` CHANGE `username` `username` varchar(255) NOT NULL
alterColumn('tbl_user', 'username', 'string NOT NULL') addForeignKey()function addForeignKey($name, $table, $columns,
$refTable, $refColumns, $delete=null, $update=null) Method addForeignKey() akan membuat dan menjalankan statement SQL untuk menambahkan foreign key constraint pada sebuah table. Parameter Berikut contoh menambah foreign key constraint, // ALTER TABLE `tbl_profile` ADD CONSTRAINT `fk_profile_user_id`
// FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`)
// ON DELETE CASCADE ON UPDATE CASCADE
addForeignKey('fk_profile_user_id', 'tbl_profile', 'user_id',
'tbl_user', 'id', 'CASCADE', 'CASCADE') dropForeignKey()function dropForeignKey($name, $table) Method dropForeignKey() membuat dan menjalankan statement SQL untuk menghapus sebuah foreign key constraint. Parameter Berikut contoh menghapus foreign key constraint: // ALTER TABLE `tbl_profile` DROP FOREIGN KEY `fk_profile_user_id`
dropForeignKey('fk_profile_user_id', 'tbl_profile') createIndex()function createIndex($name, $table, $column, $unique=false) Method createIndex() membuat dan menjalankan statement SQL untuk membuat sebuah index. Parameter Berikut contoh pembuatan index: // CREATE INDEX `idx_username` ON `tbl_user` (`username`)
createIndex('idx_username', 'tbl_user') dropIndex()function dropIndex($name, $table) Method
dropIndex() membuat dan mengeksekusi statement SQL untuk menghapus indeks. Parameter Below is an example showing how to drop an index: // DROP INDEX `idx_username` ON `tbl_user`
dropIndex('idx_username', 'tbl_user') |