laravel - 如何用 Laravel elequoent 编写这个 SQL 查询

Question

我在laravel中试过这个，但它不起作用。当我直接在 PHPMyAdmin 上使用它时，我无法让它工作

SELECT service,
count(*) as total_count,
sum(if(status ='Successful',1,0)) as successful_count,
sum(if(status ='Failed',1,0)) as failed_count
FROM `wallet_ledgers`
WHERE operator = "-"
AND user_id = 5
group by service

这是所需的输出表格的外观

标签： laraveleloquent

Answer 1

您可以使用DB::raw

链接：Laravel 原始表达式

$result = DB::table('wallet_ledgers')
                     ->select(DB::raw("service, count(*) as total_count, sum(if(status ='Successful',1,0)) as successful_count, sum(if(status ='Failed',1,0)) as failed_count"))
                     ->where([
                             [ 'operator', '=', '-'],
                             ['user_id', '=', '5'],
                             ])
                     ->groupBy('service')
                     ->get();