使用 Laravel 还原如下 SQL 语句?

你可能可以参考一下这个 Convert Your Legacy SQL to Laravel Builder

$sql = DB::table('order_has_payments')
->select(
[DB::raw('projects.name as p_name')],
[DB::raw('groups.name as g_name')],
['groups.status'],
['orders.group_id'],
[DB::raw('orders.money as o_money')],
[DB::raw('order_has_payments.money as ohp_money')],
['order_has_payments.nt_money'],
[DB::raw(DATE_FORMAT('order_has_payments.updated_at','%Y-%m-%d'),'as','l_time')],
[DB::raw('SUM(money) as all_money')],
[DB::raw('SUM(nt_money) as all_nt_money')],
[DB::raw('SUM(sc_money) as all_sc_money')],
[DB::raw('MAX(DATE_FORMAT(sc_money,%Y-%m-%d)) as updated_at')],
[DB::raw('MAX(DATE_FORMAT(billed_at,%Y-%m-%d)) as billed_at')]
)
->groupBy('order_id')
->leftjoin('orders','order_has_payments.order_id','=','orders.id')
->leftjoin('projects','orders.id','=','projects.order_id')
->leftjoin('groups','orders.group_id','=','groups.id')
->leftjoin('users','projects.owner_id','=','users.id')
->whereNotNull('order_has_payments.nt_money')
->whereNull('orders.deleted_at')
->orderBy('orders.group_id','order_has_payments.billed_at','desc')
->get();//我是才开始接触这个框架 所以无聊试一下 肯定是不对的 只是兴趣

@漫漫长路 和我第一次想法一样，但是这样是有明显的缺陷。所以才考虑用临时表来链表

@xiaoyi 我主要是想在才学习laravel过程当中练习一下当做加深印象，但是我在想如果需要这种查询是不是说明表结构就有些问题？

@漫漫长路 哈哈，这并不是什么实际的业务，只是一个疑问而已，想要解决。

在首页看到了 这一个 SQL 转换工具，然后试了试，好像，有那么些意思2333
虽然这种join巨多的在Laravel里是绝对推荐用模型关联的233

DB::select(`p.name as p_name`,`g.name as g_name`,`p.status`,`o.group_id`,`o.money as o_money`,`ohp.money as ohp_money`,`ohp.nt_money`,`us.name as us_name`)
        ->addSelect(DATE_FORMAT(ohp.updated_at, '%Y-%m-%d'))
        ->addSelect(DATE_FORMAT(ohp.billed_at, '%Y-%m-%d'))
        ->from(`select `order_id`, SUM(`money`) as `all_money`, SUM(`nt_money`) as `all_nt_money`, SUM(`sc_money`) as `all_sc_money`, MAX( DATE_FORMAT(`updated_at`, %Y-%m-%d) ) as `updated_at`, MAX( DATE_FORMAT(`billed_at`, %Y-%m-%d) ) as `billed_at` from `order_has_payments` where `nt_money` is not null group by `order_id` as ohp`)
        ->join(``orders` as o`, function($join) {
            $join->on(`ohp.order_id`, `=`, `o.id`);
            })
        ->join(``projects` as p`, function($join) {
            $join->on(`o.id`, `=`, `p.order_id`);
            })
        ->join(``groups` as g`, function($join) {
            $join->on(`o.group_id`, `=`, `g.id`);
            })
        ->join(``users` as us`, function($join) {
            $join->on(`p.owner_id`, `=`, `us.id`);
            })
        ->where(`o.deleted_at`, `is`, null)
        ->orderBy(`o.group_id`, `ASC`)
        ->orderBy(`ohp.billed_at`, `DESC`)
        ->get();

@Kurisu
要得的，以后可以用，谢谢！