使用 Laravel 还原如下 SQL 语句?
1

有这样一条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`;
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
讨论数量: 7

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

10个月前

$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();//我是才开始接触这个框架 所以无聊试一下 肯定是不对的 只是兴趣

10个月前

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

10个月前

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

10个月前

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

10个月前
Kurisu

在首页看到了 这一个 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();
10个月前

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

10个月前

  • 请注意单词拼写,以及中英文排版,参考此页
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 Emoji 自动补全来咯,可用的 Emoji 请见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!