همانطور که می دانید در فریمورک Yii برای استفاده از دیتابیس سه راه وجود دارد:

1. استفاده از Activr Record کلاس CActiveRecord.
2. استفاده از دستورات داخلی سازنده فرامین دیتابیس معروف به Query Builder (کلاس CDbCommandBuilder).
3. استفاده و اجرای مستقیم دستورات sql که معروف به DAO – Database Access Objects (همان کلاس CDbCommandBuilder).

وقتی از ابزار Gii استفاده می کنید به طور معمولو به صورت پیش فرض خود فریمورک کدهای تولیدی را بر مبنای Active Record ایجاد می کند. ما در این نوشته قصد داریم تا با حالت دوم دسترسی به دیتابیس یعنی Query Builder را توضیح بدیم.

در این حالت فریمورک برای شما بستری را برای دسترسی و استفاده از دیتابیس را به صورت شئ گرا در برنامه ایجاد می کند. به طوری که دستورات و فرامین قابل اجرا در دیتابیس، داخل خود فریمورک شبیه سازی شده و به شما این امکان را می دهد تا با استفاده از یک سری متدها دستورات sql را ایجاد کنید. در نهایت فریمورک آنها را سر جمع کرده و با حالت سوم DAO اجرا می کند. این حالت بهتر است زمانی به کار گرفته شود که شما قصد اجرای دستورات sql را به صورت رویه ای دارید و یا یک سری شرط ها و قید های پیچیده را می خواهید پیاده سازی کنید. مزیت های استفاده از این روش:

• امکان ایجاد دستورات بسیار پیچیده sql در فریمورک.
• آماده سازی خودکار نام جداول و فیلدها برای جلوگیری از تداخل با کلمات کلیدی در sql.
• امکان استفاده از پارامترها و مقید کردن آنها در شرط ها که موجب افزایش امنیت برنامه شما در مقابل Sql Injection می شود.
• این روش یک لایه ساده و نزدیک به sql را برای شما فراهم می کند که هم درک آن ساده است و هم اجرای آن آسان.

آماده سازی این روش

این روش توسط کلاس CDbCommand تأمین می شود. برای استفاده از این روش یک نمونه از کلاس CDbCommand را ایجاد می کینم:

$command = Yii::app()->db->createCommand();

در کد بالا با پراپتری Yii::app()->db کانکشن به دیتابیس مربوطه را گرفته و با متد createCommand یک نمونه از کلاس CDbCommand را ایجاد یم کنیم.

تولید فرامین واکشی داده SQL

در این قسمت به توضیح دستورات SELECT در sql می پردازیم. کلاس CDbCommand که در بالا ایجادش کردیم یکسری متد را برای تولید دستور SELECT ارائه می دهید که هرکدام یک نمونه از آن شئ را بر می گردانند. در زیر به توضیح بیشتر این متد ها می پردازیم. قبل از آن فرض بر آن است که دیتابیس مورد استفاده MySql است و قابل اشاره است که در صورتی که برنامه دیتابیس مورد استفاده شما متفاوت باشد، کدهای تولید با مثال های زیر فرق خواهد داشت.

select

function select($columns='*')

این متد قسمت select دستور را به همراه فیلدهای آن، در صورت وجود، تولید می کند. فیلدها در قالب رشته متنی و یا آرایه به صورت پارامتر به این متد داده می شوند. اگر فیلدی تعیین نشود به طور پیش فرض * در نظر گرفته می شود که به معنای تمامی فیلدها است.

// 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)

این متد نیز همانند select  است با این تفاوت که کلمه DISTINCT را هم به دستور select اضافه می کندک

selectDistinct('id, username');
//SELECT DISTINCT `id`, `username`

from

function from($tables)

متد from مشخص کنند جداول مورد استفاده در دستور select است. این متد نام جدول یا جداول را بصورت پارامتر و در قالب رشته متنی یا آرایه می گیرد. هم چنین می توان نام دیتابیس یا نام مستعار برای جداول را در آن متن قرار داد.

// 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())

متد where مشخص کننده قسمت مربوط به شرطها و قیود مربوط به select را تولید می کند. شرط ها به صورت یک پارامتر در قالب متنی یا آرایه به این متد داده می شوند. همچنین پارامترهای مربوط به شرط ها که باید به آنها مقید شوند را نیز در یک پارامتر دیگر در قالب آرایه به این متد داده می شوند.

// 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%')))

دقت داشته باشید که برای استفاده از شرط like در select باید در پارامتر ارسالی از علامات % و _ استفاده شود.

andWhere

function andWhere($conditions, $params=array())

عملکرد متد andWhere نیز شبیه به متد where است با این تفاوت که یک شرط را با قید AND در انتهای شروط اضافه می کند.

orWhere

function orWhere($conditions, $params=array())

عملکرد متد orWhere نیز شبیه به متد where است با این تفاوت که یک شرط را با قید OR در انتهای شروط اضافه می کند.

order

function order($columns)

متد order مشخص کننده بخش ORDER BY در دستور select که برای مرتب سازی است می باشد. پارامتر ورودی آن که در قالب متنی و آرایه می تواند باشد، تعیین کننده فیلد یا فیلدهایی است که قصد مرتب سازی بر اساس آنها را دارید، می باشد.

// ORDER BY `name`, `id` DESC
order('name, id desc')
// ORDER BY `tbl_profile`.`name`, `id` DESC
order(array('tbl_profile.name', 'id desc'))

limit و offset

function limit($limit, $offset=null)
function offset($offset)

متدهای limit و offset مشخص کنند بخش LIMIT و OFFSET در کوئری هستند. توجه داشته باشید که ممکن است برخی از DBMS ها از این بخش ها در کوئری پشتیبانی نکنند.

// LIMIT 10
limit(10)
// LIMIT 10 OFFSET 20
limit(10, 20)
// OFFSET 20
offset(20)

انواع join ها

function join($table, $conditions, $params=array())
function leftJoin($table, $conditions, $params=array())
function rightJoin($table, $conditions, $params=array())
function crossJoin($table)
function naturalJoin($table)

متد join و انواع آن تعیین کننده انواع ارتباط جدول با دیگر جداول در کوئری است. پارامترهای موجود در این متدها مشخص کننده نام جدولی است که قرار است با آن رابطعه برقرار شود، شرایطی که ارتباط بر مبنای آن برقرار می شود و ارگومانهای مورد نیاز آن ارتباط می باشند.

// 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

متد group مشخص کنند بخش GROUP BY در دستور select است که یک پارامتر در قالب های متنی و آرایه برای تعیین نام فیلد یا فیلدهایی که باید گروه بندی شوند دارد.

// 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())

متد having تعیین کننده بخش HAVING در دستور select  است. پارامتر آن شبیه به پارامترهای متد where عمل می کند.

// 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

function union($sql)

متد union امکان اضافه کردن دستورات select متفاوت در یک کوئری را برای شما فراهم می کند.

// UNION (select * from tbl_profile)
union('select * from tbl_profile')

اجرای کوئری

برای اجرای کوئری تولید شده با متدها بالا و مشاهده نتیجه، باید از یک سری متدها مربوط به شئ DAO استفاده بشه از جمله queryAll که تمامی نتیجه ها را بر می گرداند و یا queryRow که فقط اولین رکورد را به عنوان نتیجه باز می گرداند.

$users = Yii::app()->db->createCommand()
    ->select('*')
    ->from('tbl_user')
    ->queryAll();

مشاهده دستور نهایی

در پشت اجرای یک کوئری تولیدی با Query Builder، یک رشته دستور sql وجود دارد که با استفاده از متد text می توان به آن رشته متنی دستور نهایی دسترسی پیدا کرد و از مشکلات احتمالی در syntax آگاه شد.

$sql = Yii::app()->db->createCommand()
    ->select('*')
    ->from('tbl_user')
    ->text;

 اجرای چند کوئری با یک QueryBuilder

گاهی وقتها لازم است تا با یک شئ ایجاد شده Query Builder چند کوئری را اجرا کنید. ولی برای انجام آن لازم است تا قبل از اجرای کوئری ها بعدی آن شئ را با متد reset از نتایج قبلی پاک کنید.

$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();

تولید کوئری های دستکاری داده ها

کوئری های بخش دستکاری داده ها همان دستورات inser، update و delete از جداول دیتابیس است. برخلاف دستور select که در بالا بیان شد، این نوع دستورات بلافصله بعد از تولید کوئری نهایی، آن را به دتیابیس اعمال کرده و تغییرات در همان لحظه ایجاد می شوند.

inser

function insert($table, $columns)

متد insert دستور INSERT INTO را در sql ایجاد می کند. این متد دارای دو پارامتر است. اولی نام جدول مورد نظر را گرفته و دومی آرایه ای به صورت کلید و مقدار است که فیلدها و مقادیر مربوطه برای عمل insert در دیتابیس را مشخص می کند.

// build and execute the following SQL:
// INSERT INTO `tbl_user` (`name`, `email`) VALUES (:name, :email)
$command->insert('tbl_user', array(
    'name'=>'Tester',
    'email'=>'[email protected]',
));

update

function update($table, $columns, $conditions='', $params=array())

متد update دستور UPDATE در دستورات SQL را تولید می کند. این متد دارای چهار پارامتر است. اولی نام جدول مورد نظر را گرفته و دومی آرایه ای به صورت کلید و مقدار است که فیلدها و مقادیر مربوطه برای عمل update در دیتابیس را مشخص می کند. پارامتر سوم شرط های مربوطه جهت update را تعیین کرده و پارامتر چهارم آرگومانهایی که باید در شرطها مقید شوند را مشخص می کند. دو پارامتر آخر عملکردی همانند متد where در بخش قبلی دارد.

// build and execute the following SQL:
// 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())

متد delete دستور DELETE در SQL را تولید می کند. این متد دارای سه پارامتر است. اولی نام جدول مورد نظر را گرفته و دومی شرط های مربوطه جهت delete را تعیین کرده و پارامتر چهارم آرگومانهایی که باید در شرطها مقید شوند را مشخص می کند. دو پارامتر آخر عملکردی همانند متد where در بخش قبلی دارد.

// build and execute the following SQL:
// DELETE FROM `tbl_user` WHERE id=:id
$command->delete('tbl_user', 'id=:id', array(':id'=>1));

این نوشته ادامه دارد…..

امیدوارم که مفید بوده باشد.

The post نحوه استفاده از Query Builder در Yii 1.1 appeared first on دست نوشته های یک تازه کار.