使用 Laravel 还原如下 SQL 语句?

问答 xiaoyi ⋅ 于 5个月前 ⋅ 最后回复由 漫漫长路 5个月前 ⋅ 443 阅读

有这样一条SQL语句

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` , DATE_FORMAT(ohp.`updated_at`, '%Y-%m-%d') as 'l_time' ,  
DATE_FORMAT(ohp.`billed_at`, '%Y-%m-%d') as 'ls_time' , us.`name` as 'us_name' 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
left join `orders`  as  o on  ohp.`order_id` = o.`id`
left join `projects`  as p on o.id = p.`order_id` 
left join `groups` as g on o.group_id = g.id
left join `users`  as us on p.`owner_id`  = us.id
where o.deleted_at is null
order by o.group_id,ohp.billed_at DESC;

使用laravel的构造器来写怎么写,主要是临时表链接查询这一块没找到教程啊,求大佬帮助😂

其实可以简化一下,核心问题是如何使用临时表做连查询,所以可以简化成这样:

SELECT  o.`money` , ohp.`nt_money`  FROM ( select `order_id`,  SUM(`nt_money`) as `nt_money`
from `order_has_payments` where `nt_money` is not null group by `order_id` ) as ohp
left join `orders`  as  o on  ohp.`order_id` = o.`id`;

本文章首发在 Laravel China 社区
成为第一个点赞的人吧 :bowtie:
回复数量: 7
  • 小恪守 1
    5个月前

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

  • 漫漫长路 人丑就要多码代码
    5个月前

    $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
    5个月前

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

  • 漫漫长路 人丑就要多码代码
    5个月前

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

  • xiaoyi
    5个月前

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

  • Kurisu
    5个月前

    在首页看到了 这一个 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();
  • 漫漫长路 人丑就要多码代码
    5个月前

    @Kurisu
    要得的,以后可以用,谢谢!

暂无评论~~

  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!