1、数据库的原生使用
文档:https://learnku.com/docs/laravel/5.8/queries/3926,DB:row
需要在查询中使用原生表达式,selectRaw
方法可以代替 select(DB::raw(...))
。该方法的第二个参数是可选项,值是一个绑定参数的数组:
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.0825])
->get();
eg:
$data = $recoveryOrder->leftJoin('user_wechats', 'recovery_orders.user_id', '=', 'user_wechats.user_id')
->leftJoin('recovery_order_expresses', 'recovery_orders.id', '=', 'recovery_order_expresses.order_id')
->leftJoin('recovery_order_senders', 'recovery_orders.id', '=', 'recovery_order_senders.order_id')
->leftJoin('recovery_order_receivers', 'recovery_orders.id', '=', 'recovery_order_receivers.order_id')
->select( 'recovery_orders.order_number',
'recovery_orders.created_at',
'recovery_orders.cashback_money',
'recovery_order_senders.sender_name',
'recovery_order_senders.sender_phone',
'recovery_order_senders.sender_province_name',
'recovery_order_senders.sender_city_name',
'recovery_order_senders.sender_county_name',
'recovery_order_senders.sender_detail_address',
'recovery_orders.meet_at',
'recovery_orders.remark_from_user',
'recovery_orders.cargo_name',
'recovery_orders.estimated_weight',
DB::raw("CASE recovery_orders.is_cashback WHEN 'Yes' THEN '已返现' WHEN 'No' THEN '未返现' ELSE '未知' END as is_cashback"),
DB::raw("CASE recovery_orders.order_status WHEN 'CREATED' THEN '创建成功' WHEN 'ASSIGNED' THEN '等待上门' WHEN 'RECEIVED' THEN '已取货' WHEN 'SIGNED' THEN '已签收' WHEN 'CANCELLED' THEN '已取消' ELSE '未知状态' END as order_status"),
'recovery_order_expresses.express_company_name',
'recovery_order_expresses.express_number',
'recovery_order_expresses.weight_from_express',
'recovery_orders.user_id',
'recovery_order_expresses.remark_from_express',
'recovery_orders.cancelled_reason',
'recovery_orders.remark_from_admin',
'user_wechats.open_id'
)
->selectRaw('(SELECT
GROUP_CONCAT(recovery_order_waybills.tracking_number)
FROM
recovery_order_waybills
WHERE
recovery_order_expresses.id = recovery_order_waybills.order_express_id
) AS tracking_number'
)
->distinct()
->orderBy('recovery_orders.created_at','desc')
->paginate($request->limit);