laravel中DB::select()如何进行分页?

简单的查询和分页代码如下:

    public function manage(Request $request)
    {
        $data  = [];
        $rows  = $request->input('rows', 10);
        $query = Followup::query()
          ->orderBy('created_at', 'desc')
         ->paginate($rows);

        if ($query) {
          $data['rows']  = $query->items();
          $data['total'] = $query->total();
        } else {
          $data['rows']  = [];
          $data['total'] = 0;
        }
    }
    return $this->success($data);

现在有个复杂的报表查询语句:

select  a.d as date,a.user_id as user_id,ifnull(a.bb,0) as 计划回访数量,ifnull(b.bb,0) as 完成回访数量,
ifnull(c.bb,0)  as 创建回访数量 from 
(
select date as d,followup_user as user_id,count(followup_user) as bb  from cy_followup 
where date>='2021-03-10' and date<='2021-03-10' group by date,followup_user
) a left join 
(
select date_format(time,'%Y-%m-%d') as d,EXECUTE_user as user_id,count(EXECUTE_user) as bb  from cy_followup 
where time>='2021-03-10' and time<'2021-03-10' group by date_format(time,'%Y-%m-%d'),EXECUTE_user
) b  on a.d=b.d and a.user_id=b.user_id left join 
(
select date_format(created_at,'%Y-%m-%d') as d,user_id,count(user_id) as bb  from cy_followup 
where  created_at >='2021-03-10' and  created_at <'2021-03-10' group by date_format(created_at,'%Y-%m-%d'),user_id
) c on  a.d=c.d   and a.user_id=c.user_id
union 
select  c.d,c.user_id,ifnull(a.bb,0) as 计划回访数量,ifnull(b.bb,0) as 完成回访数量,ifnull(c.bb,0)  as 创建回访数量 from 
(
select date as d,followup_user as user_id,count(followup_user) as bb  from cy_followup 
where date>='2021-03-10' and date<='2021-03-10' group by date,followup_user
) a right join 
(
select date_format(time,'%Y-%m-%d') as d,EXECUTE_user as user_id,count(EXECUTE_user) as bb  from cy_followup 
where time>='2021-03-10' and time<'2021-03-10' group by date_format(time,'%Y-%m-%d'),EXECUTE_user
) b  on a.d=b.d and a.user_id=b.user_id right join 
(
select date_format(created_at,'%Y-%m-%d') as d,user_id,count(user_id) as bb  from cy_followup 
where  created_at >='2021-03-10' and  created_at <'2021-03-10' group by date_format(created_at,'%Y-%m-%d'),user_id
) c on  a.d=c.d   and a.user_id=c.user_id

这个在laravel中貌似只能用DB::select($sql)来执行了?但是如何分页?

最后用mysql中的view来解决这个问题

CREATE VIEW cy_v_followup_statistic
AS
SELECT *
FROM (
    SELECT a.d AS date, a.user_id AS user_id, ifnull(a.bb, 0) AS followup_count
        , ifnull(b.bb, 0) AS execute_count
        , ifnull(c.bb, 0) AS create_count
    FROM (
        SELECT date AS d, followup_user AS user_id, COUNT(followup_user) AS bb
        FROM cy_followup
        GROUP BY date, followup_user
    ) a
        LEFT JOIN (
            SELECT date_format(created_at, '%Y-%m-%d') AS d, user_id
                , COUNT(user_id) AS bb
            FROM cy_followup
            GROUP BY date_format(created_at, '%Y-%m-%d'), user_id
        ) c
        ON a.d = c.d
            AND a.user_id = c.user_id
        LEFT JOIN (
            SELECT date_format(time, '%Y-%m-%d') AS d, EXECUTE_user AS user_id
                , COUNT(EXECUTE_user) AS bb
            FROM cy_followup
            GROUP BY date_format(time, '%Y-%m-%d'), EXECUTE_user
        ) b
        ON a.d = b.d
            AND a.user_id = b.user_id
) t
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
最佳答案

我有个大胆的想法,把这条语句定义为一个视图,然后使用laravel模型绑定这个视图,再做分页是不是代码量会少一点?

3年前 评论
91it (楼主) 3年前
91it (楼主) 3年前
Tsukasa_Kanzaki (作者) 3年前
LiamHao 3年前
91it (楼主) 3年前
LiamHao 3年前
讨论数量: 13
fatrbaby

这个sql语句生平仅见。敢问目前多少数据量? 跑一次多长时间?

3年前 评论
91it (楼主) 3年前
fatrbaby (作者) 3年前

select语句就只能自己加limit做分页了

3年前 评论

DB::table()->select()->paginate();

3年前 评论
91it (楼主) 3年前
今晚打老虎 (作者) 3年前

用laravel的模型进行左关联不香吗

3年前 评论
91it (楼主) 3年前
我爱大可乐 (作者) 3年前

Db的select方式是直接和底层PDO连接做交互查询,所以需要把SQL用查询构建器提供的方法组装起来最后调用builder的paginate方法就可以咯。

3年前 评论

:neutral_face: 这SQL 我觉得真不妥

3年前 评论

laravel 有个手动分页的函数,把查询结果带进去 就可以

3年前 评论

中间代码是你的sql,外层加一个查询进行分页操作。

SELECT * FROM (

SELECT
    a.d AS date,
    a.user_id AS user_id,
    ifnull( a.bb, 0 ) AS 计划回访数量,
    ifnull( b.bb, 0 ) AS 完成回访数量,
    ifnull( c.bb, 0 ) AS 创建回访数量 
FROM (......)
 UNION
SELECT
    c.d,
    c.user_id,
    ifnull( a.bb, 0 ) AS 计划回访数量,
    ifnull( b.bb, 0 ) AS 完成回访数量,
    ifnull( c.bb, 0 ) AS 创建回访数量 
FROM (......)

) aaa ORDER BY aaa.计划回访数量 DESC LIMIT 0,10

3年前 评论

我有个大胆的想法,把这条语句定义为一个视图,然后使用laravel模型绑定这个视图,再做分页是不是代码量会少一点?

3年前 评论
91it (楼主) 3年前
91it (楼主) 3年前
Tsukasa_Kanzaki (作者) 3年前
LiamHao 3年前
91it (楼主) 3年前
LiamHao 3年前

这么复杂的语句为啥不用视图呢 然后再分页啊

3年前 评论
91it (楼主) 3年前

我觉得用limit就可以了,做一个公共分页方法,视图会麻烦一步

2年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!