原始查询转换为Eloquent(Raw query convert to Eloquent)

原始查询转换为Eloquent(Raw query convert to Eloquent)

需要帮助将此查询转换为雄辩，以便使用paginate，groupby和orderby方法

SELECT DATE(created_at), SUM(CASE WHEN `type` = 'withdraw' THEN 1 ELSE 0 END) AS 'withdraw_count', SUM(CASE WHEN `type` = 'withdraw' THEN amount ELSE 0 END) AS 'withdraw_amount', SUM(CASE WHEN `type` = 'deposit' THEN 1 ELSE 0 END) AS 'deposit_count', SUM(CASE WHEN `type` = 'deposit' THEN amount ELSE 0 END) AS 'deposit_amount' FROM ( SELECT created_at, 'withdraw' as `type`, amount FROM withdraw UNION ALL SELECT created_at, 'deposit' as `type`, amount FROM deposit ) t GROUP by DATE(created_at)

Need help in converting this query to eloquent for me to use the paginate, groupby and orderby method

SELECT DATE(created_at), SUM(CASE WHEN `type` = 'withdraw' THEN 1 ELSE 0 END) AS 'withdraw_count', SUM(CASE WHEN `type` = 'withdraw' THEN amount ELSE 0 END) AS 'withdraw_amount', SUM(CASE WHEN `type` = 'deposit' THEN 1 ELSE 0 END) AS 'deposit_count', SUM(CASE WHEN `type` = 'deposit' THEN amount ELSE 0 END) AS 'deposit_amount' FROM ( SELECT created_at, 'withdraw' as `type`, amount FROM withdraw UNION ALL SELECT created_at, 'deposit' as `type`, amount FROM deposit ) t GROUP by DATE(created_at)

最满意答案

$from = DB::table('withdraw')
    ->select('created_at', DB::raw("'withdraw' as `type`"), 'amount')
    ->unionAll(
        DB::table('deposit')
            ->select('created_at', DB::raw("'deposit' as `type`"), 'amount')
    );
DB::query()
    ->selectRaw('DATE(created_at)')
    ->selectRaw("SUM(CASE WHEN `type` = 'withdraw' THEN 1 ELSE 0 END) AS 'withdraw_count'")
    ->selectRaw("SUM(CASE WHEN `type` = 'withdraw' THEN amount ELSE 0 END) AS 'withdraw_amount'")
    ->selectRaw("SUM(CASE WHEN `type` = 'deposit' THEN 1 ELSE 0 END) AS 'deposit_count'")
    ->selectRaw("SUM(CASE WHEN `type` = 'deposit' THEN amount ELSE 0 END) AS 'deposit_amount'")
    ->fromSub($from, 't')
    ->groupBy(DB::raw('DATE(created_at)'))
    ->get();
$from = DB::table('withdraw')
    ->select('created_at', DB::raw("'withdraw' as `type`"), 'amount')
    ->unionAll(
        DB::table('deposit')
            ->select('created_at', DB::raw("'deposit' as `type`"), 'amount')
    );
DB::query()
    ->selectRaw('DATE(created_at)')
    ->selectRaw("SUM(CASE WHEN `type` = 'withdraw' THEN 1 ELSE 0 END) AS 'withdraw_count'")
    ->selectRaw("SUM(CASE WHEN `type` = 'withdraw' THEN amount ELSE 0 END) AS 'withdraw_amount'")
    ->selectRaw("SUM(CASE WHEN `type` = 'deposit' THEN 1 ELSE 0 END) AS 'deposit_count'")
    ->selectRaw("SUM(CASE WHEN `type` = 'deposit' THEN amount ELSE 0 END) AS 'deposit_amount'")
    ->fromSub($from, 't')
    ->groupBy(DB::raw('DATE(created_at)'))
    ->get();