کوئری ساز
- مقدمه
- دستورات select
- Joins
- دستورات شرطی پیشرفته
- اجرای توابع جمعی (Aggregates)
- بکارگیری کوئری های توکار (Raw Expression)
- ایجاد رکورد Insert
- دستور Update
- حذف رکورد
- ادغام رکورد ها Union
- قفل Pessimistic
مقدمه
کوئری ساز لاراول یک رابط روان و راحت و ساده برای تولید و اجرای کوئری ها می باشد که میتواند در بیشتر عملیات کار با دیتابیس نیز استفاده شود و برروی تمامی بانکهایی اطلاعاتی که توسط لاراول پشتیبانی میشود قابل اجرا می باشد .
نکته : کوئری ساز برای نشاندن مقادیر در کوئری ها ، بطور کامل از
PDO parameter binding
استفاده میکند تا وب سایت شما را درمقابل حملاتSQL injection
حفظ کند و دیگر نیازی نیست که پارامتر های ارسالی به کوئری ساز را از نظر کدهای مخرب چک کنید .
دستورات Select
گرفتن تمامی رکورد های جدول
$users = DB::table('users')->get();
foreach ($users as $user)
{
var_dump($user->name);
}
تکه تکه کردن نتایج حاصل از اجرای کوئری select
DB::table('users')->chunk(100, function($users)
{
foreach ($users as $user)
{
//
}
});
میتوانید با ارسال مقدار false
از درون closure
به اجرای ادامه عملیات خاتمه دهید :
DB::table('users')->chunk(100, function($users)
{
//
return false;
});
دریافت یک رکورد از کل نتایج
$user = DB::table('users')->where('name', 'John')->first();
var_dump($user->name);
دریافت یک ستون از کل نتایج
$name = DB::table('users')->where('name', 'John')->pluck('name');
دریافت نتایج بصورت لیست (آرایه)
$roles = DB::table('roles')->lists('title');
این متد یک آرایه از Title ها را برمیگرداند ، همچنین شما میتوانید مقادیر اندیس ها در آرایه را از طریق پارامتر دوم متد lists
مشخص کنید :
$roles = DB::table('roles')->lists('title', 'name');
دستور select جهت انتخاب ستون ها
$users = DB::table('users')->select('name', 'email')->get();
$users = DB::table('users')->distinct()->get();
$users = DB::table('users')->select('name as user_name')->get();
اضافه کردن یک ستون دیگر با addSelect
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
دستورات شرطی
$users = DB::table('users')->where('votes', '>', 100)->get();
دستور Or
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
جستجو رکورد های که مقدار فیلد x آنها بین دامنه مقادیر داده شده باشد :
$users = DB::table('users')
->whereBetween('votes', [1, 100])->get();
جستجو رکورد های که مقدار فیلد x آنها بین دامنه مقادیر داده شده نباشند :
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])->get();
جستجو رکورد های که مقدار فیلد x آنها محدود به آرایه داده شده باشد :
$users = DB::table('users')
->whereIn('id', [1, 2, 3])->get();
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])->get();
جستجوی رکوردها با مقادیر null .
$users = DB::table('users')
->whereNull('updated_at')->get();
دستورات شرط پویا
میتوانید برای تولید دستورات شرطی از عبارات خاصی استفاده کنید تا بتوانید نوع شرط را به روش مفهومی تری بیان نمایید :
$admin = DB::table('users')->whereId(1)->first();
$john = DB::table('users')
->whereIdAndEmail(2, 'john@doe.com')
->first();
$jane = DB::table('users')
->whereNameOrAge('Jane', 22)
->first();
دستورات Order by , Group by, Having
$users = DB::table('users')
->orderBy('name', 'desc')
->groupBy('count')
->having('count', '>', 100)
->get();
دستور Limit
$users = DB::table('users')->skip(10)->take(5)->get();
عملیات Join و ضرب دوجدول
برای ضرب دو جدول و Join کردن آنها به شکل زیر عمل کنید :
نمونه ساده Join چند جدول با یکدیگر
DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.id', 'contacts.phone', 'orders.price')
->get();
عملیات Left Join
DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
همچنین میتوانید عملیات Join پیشرفته تری ایجاد کنید :
DB::table('users')
->join('contacts', function($join)
{
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
میتوانید از دستورات "where" یا "orWhere" برای شرط Join خود استفاده کنید، به عنوان مثال علاوه بر شرط همسان بودن دو فیلد ، فیلد مورد نظر را با یک عدد نیز مقایسه نمایید :
DB::table('users')
->join('contacts', function($join)
{
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
دستور Where پیشرفته
تولید کوئری های تو در تو
ممکن است بخواهید دستورات شرطی پیچیده تری را ایجاد کنید یا مثلا از دستور "where exist" برای بررسی موجودیت یک کوئری استفاده نمایید . لاراول برای اجرای اینگونه کوئری ها ، یک راه حل جالب و مفهومی ارائه کرده است :
DB::table('users')
->where('name', '=', 'John')
->orWhere(function($query)
{
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();
دستورات بالا نمونه کوئری زیر را تولید میکنند :
select * from users where name = 'John' or (votes > 100 and title <> 'Admin')
بررسی موجودیت یک کوئری (Exists Statements)
DB::table('users')
->whereExists(function($query)
{
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
دستورات بالا نمونه کوئری زیر را تولید میکنند :
select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)
اجرای توابع جمعی (Aggregates)
میتوانید نتیجه دستورات count
, max
, min
, avg
, و sum
را توسط کوئری ساز (Query Builder) استخراج نمایید .
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->min('price');
$price = DB::table('orders')->avg('price');
$total = DB::table('users')->sum('votes');
بکارگیری از کوئری های توکار (Raw Expression)
برای استفاده از یک کوئری توکار در کوئری ساز (Query Builder) از متد DB::raw
استفاده میکنیم . حواستان باشد تنها کاربرد این متد بکارگیری یک کوئری در دل کوئری دیگر است ، و این دستور هرگز کوئری ها را از کد های مخرب (Sql Injection) پاک نمیکند .:
نمونه تزریق یک کوئری در یک دستور select
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
دستورات Insert (ایجاد رکود)
ایجاد رکورد جدید در جدول
DB::table('users')->insert(
['email' => 'john@example.com', 'votes' => 0]
);
اضافه کردن یک رکورد به جدول همراه با Auto-Incrementing
اگر جدول شما دارای یک فیلد Auto-Increment باشد ، میتوانید توسط متد insertGetId
برای ایجاد رکورد و دریافت مقدار ID رکورد ایجاد شده استفاده نمایید :
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
نکته : اگر از بانک اطلاعاتی PostgreSQL استفاده میکنید ، برای استفاده از متد
insertGetId
، نام فیلد کلید اصلی باید برابرid
باشد .
اضافه کردن چند رکورد به صورت همزمان
DB::table('users')->insert([
['email' => 'taylor@example.com', 'votes' => 0],
['email' => 'dayle@example.com', 'votes' => 0]
]);
دستور Update
ویرایش یک رکود در جدول
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
افزایش و کاهش مقدار یک فیلد در جدول
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
میتوانید همزمان فیلد های دیگری را نیز درجدول Update کنید :
DB::table('users')->increment('votes', 1, ['name' => 'John']);
حذف رکورد (Delete)
حذف یک رکورد از جدول
DB::table('users')->where('votes', '<', 100)->delete();
حذف تمامی رکورد های جدول
DB::table('users')->delete();
خالی کردن یک جدول (Truncating)
DB::table('users')->truncate();
دستورات Union (ادغام رکورد ها)
لاراول یک راه سریع برای ادغام دو نتیجه کوئری با یکدیگر ارائه کرده است :
$first = DB::table('users')->whereNull('first_name');
$users = DB::table('users')->whereNull('last_name')->union($first)->get();
متد unionAll
با روشی مشابه با متد union
نیز در دسترس می باشد.
قفل Pessimistic
کوئری ساز ، قابلیت صدور قفل "pessimistic" بر روی دستورات Select
را دارد.
توسط متد sharedLock
برای کوئری SELECT قفل "pessimistic" ایجاد کنید :
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
و توسط متد lockForUpdate ، هنگام اجرای دستور Select از اجرای Update بر روی آنها جلوگیری نمایید :
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();