iT邦幫忙

2021 iThome 鐵人賽

DAY 1
0
Modern Web

工作後才知道的後端 30 件小事系列 第 1

SQL 的括號怎麼寫成 Laravel Query?

前言

之前工作遇到一個情境需要撈出取消訂單狀態為 0,1 或沒有取消單的訂單,然後要再加上其他條件,像是訂單狀態、時間和供應商名稱。

SQL 大概是長這樣,OR的條件用括號刮起來

SELECT * FROM orders
WHERE status = 1
AND (/* 括號 */
    EXISTS (
        /* 取消訂單狀態為 0 或 1 */
        SELECT * FROM cancellations 
        WHERE orders.id = cancellations.order_id
        AND cancellations.status in (0, 1)
    ) OR NOT EXISTS (
        /* 或沒有取消訂單 */
        SELECT * FROM cancellations 
        WHERE orders.id = cancellations.order_id
    )
)/* 括號 */ 
AND DATE (created_at) >= "2021-07-01 00:00:00"
AND DATE (created_at) <= "2021-07-31 23:59:59"
AND supplier = "appple";

所以寫成 Laravel query 長這樣

Order::where('status', 1)
whereHas('cancellation', function($query) {
    return $query->whereIn('status', [0, 1]);
})
->orWhereDoesntHave('cancellation')
->where('created_at', '>=', '2021-07-01 00:00:00')
->where('created_at', '<=', '2021-07-31 23:59:59')
->where('supplier', 'apple');

問題來了!我要怎麼加那個「括號」呢!?

各種嘗試

爬了很多文和嘗試,得出的結論是,以這個 case ,最簡單的方式就是不加!只是把 or 條件移到最後,像這樣。結果會是我要的。

Order::where('status', 1)
->where('created_at', '>=', '2021-07-01 00:00:00')
->where('created_at', '<=', '2021-07-31 23:59:59')
->where('supplier', 'apple')
/* 下面這段放最後面 */
whereHas('cancellation', function($query) {
    return $query->whereIn('status', [0, 1]);
})
->orWhereDoesntHave('cancellation');

但這樣的缺點是可能比較難維護,例如之後的人要再加一個條件,他不知道這個情形,就直接在最後面接上一個新條件就 GG 了!而且如果 OR 的條件不只一個,這個寫法也不行。

後來我查了 Laravel query ExistsNot Exists 的寫法,拼出了以下的寫法,結果也是對的!只是比較醜...

邏輯基本上就是想辦法把 SQL 用 Laravel query 的語法硬寫出來。

Order::where('status', 1)
whereExists(function($query) {
    return $query->from('cancellations')
    ->whereColumn('orders.id', 'cancellations.order_id')
    ->whereIn('cancellations.status', [0, 1])
    ->orWhereNotExists(function ($query){
      return $query->from('cancellations')
      ->whereColumn('orders.id', 'cancellations.order_id');
    });
})
->where('created_at', '>=', '2021-07-01 00:00:00')
->where('created_at', '<=', '2021-07-31 23:59:59')
->where('supplier', 'apple');

成功是成功了,但我還是不是很滿意,覺得這樣的寫法很不優雅。後來跟同事討論才知道原來可以把 where 當括號來用!知道 Laravel query 的括號怎麼寫後就單純很多了!

Order::where('status', 1)
->where(function ($query) {/* 括號! */
    return $query->whereHas('cancellation', function($query) {
        return $query->whereIn('status', [0, 1]);
    })->orWhereDoesntHave('cancellation');
})/* 括號! */
->where('created_at', '>=', '2021-07-01 00:00:00')
->where('created_at', '<=', '2021-07-31 23:59:59')
->where('supplier', 'apple');

所以就算之後裡面還要多個括號就再加 where 就好~滿足~

最後補充

如果條件是變數,可以用 use 把變數傳進 where

$status = [0, 1]; /* 狀態變數 */

Order::where('status', 1)
->where(function ($query) use ($status) {/* 傳 $status 進去 */
    return $query->whereHas('cancellation', function($query) {
        return $query->whereIn('status', $status);/* 用在這 */
    })->orWhereDoesntHave('cancellation');
})
->where('created_at', '>=', '2021-07-01 00:00:00')
->where('created_at', '<=', '2021-07-31 23:59:59')
->where('supplier', 'apple');

下一篇
如何用 SQL 去除重複資料 : Distint
系列文
工作後才知道的後端 30 件小事20

尚未有邦友留言

立即登入留言