mysql - 如何将自定义查询转换为 Laravel 5？

Question

我正在从交易表中获取 order_id、created_at date、transaction_amount。我得到了一些包含 order_id、创建日期和交易金额的记录。现在我想要 transaction_amount 列的总和。我尝试直接在 db 上执行以下查询，效果很好，但我无法在 laravel 中编写相同的查询。

select sum(transaction_amount) from (
select order_id, max(created_at), transaction_amount
from transactions
WHERE user_id =100 AND accounting_type = "Debit"
group by order_id
limit 500 ) as transactions

这个方法试过了还是不行

$sql = "select sum(transaction_amount) from 
                ('select order_id, max(created_at), transaction_amount
                from transactions
                WHERE user_id =100 
                group by order_id
                limit 500') as transactions";
        $result = \DB::select( \DB::raw($sql));

Answer 1

首先，让我们分解您的查询:主查询

SELECT
SUM( transaction_amount )
FROM
    (...) AS transactions

这只是用于总结。你的子查询:

        SELECT
            order_id,
            MAX( created_at ),
            transaction_amount
        FROM
            transactions
        WHERE
            user_id = 100
            AND accounting_type = "Debit"
        GROUP BY
            order_id LIMIT 500

对于子查询，Laravel 查询构建器应该是:

use DB;

$result = DB::table('table')
->select(DB::raw("order_id, MAX(created_at), transaction_amount"))
->where("user_id", "=", 100)
->where("accounting_type", "=", "Debit")
->groupBy("order_id")
->limit(500)
->get(); // this will return Laravel Collection (Illuminate\Support\Collection)

Laravel Collection 有 sum 方法所以你可以调用它

$result->sum('transaction_amount');

更多信息请阅读this和 this Laravel 文档。