Laravel Query Builder重新釐清的地方

Nick Zhang
Sep 1, 2018 · 1 min read
  1. 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

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade