Laravel Query Builder重新釐清的地方
- Join:
inner join:
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
users table inner join contacts and orders table
left join:
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();users table left join posts table
cross join (full join):
$users = DB::table('sizes')
->crossJoin('colours')
->get();sizes table full join colours table
advanced join clause:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();users table join contacts table which on clause is true
sub-query join:
$latestPosts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('user_id');
$users = DB::table('users')
->joinSub($latestPosts, 'latest_posts', function($join) {
$join->on('users.id', '=', 'latest_posts.user_id');
})->get();2. Union
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();first query union with users query
3. Where Exists Clauses
DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();in SQL like
select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)其實跟eloquent的whereHas有點像
4.where() 與 having() 的不同:
where() 不能在query 做 aggregate 後做 篩選,但是having() 可以
所以一般 where() 會寫在 groupBy() 之前,having() 會寫在groupBy()之後
5. Conditional Clauses:
有時候我們需要先判斷某值是否存在,才進行Query,這時可以使用when()
$role = $request->input('role');
$users = DB::table('users')
->when($role, function ($query, $role) {
return $query->where('role_id', $role);
})
->get();上面意旨當$role有值時才進行以下的query。
如果值不存在時,我們也可以指派query給他進行
$sortBy = null;
$users = DB::table('users')
->when($sortBy, function ($query, $sortBy) {
return $query->orderBy($sortBy);
}, function ($query) {
return $query->orderBy('name');
})
->get();當$sortBy為null時,就會進行第二個function
