thinkphp中分页paginate和group by一起使用时 代码异常的解决办法

1. paginate和group by报错,代码如下,月份分组
        $page = intval($where['page']);
        $limit = intval($where['limit']);
        $start_time = $where['start_time'];
        $end_time = $where['end_time'];

        $query = Db::table('eb_bonuslistlog')
            ->field('DATE_FORMAT(create_time,"%Y-%m") as date,SUM(money) as total_money,COUNT(*) as total_number')
            ->group('date')
            ->order('date desc');

        if ($start_time && $end_time) {
            $query->whereTime('create_time', 'between', [date('Y-m-01 00:00:00', strtotime($start_time)), date('Y-m-t 23:59:59', strtotime($end_time))]);
        }

  $list = $query->paginate(15);
        return $list;

2. 第一种写法,group by分组后使用page分页, 月份分组
        $page = intval($where['page']);
        $limit = intval($where['limit']);
        $start_time = $where['start_time'];
        $end_time = $where['end_time'];

        $query = Db::table('eb_bonuslistlog')
            ->field('DATE_FORMAT(create_time,"%Y-%m") as date,SUM(money) as total_money,COUNT(*) as total_number')
            ->group('date')
            ->order('date desc');

        if ($start_time && $end_time) {
            $query->whereTime('create_time', 'between', [date('Y-m-01 00:00:00', strtotime($start_time)), date('Y-m-t 23:59:59', strtotime($end_time))]);
        }

        $list = $query->page($page, $limit)
            ->select()
            ->toArray();

        return $list;
 3..第二种写法,group by分组后使用paginate分页,月份分组
        $page = intval($where['page']);
        $limit = intval($where['limit']);
        $start_time = $where['start_time'];
        $end_time = $where['end_time'];

        $query = Db::table('eb_bonuslistlog')
            ->field('DATE_FORMAT(create_time,"%Y-%m") as date,SUM(money) as total_money,COUNT(*) as total_number')
            ->group('date')
            ->order('date desc');

        if ($start_time && $end_time) {
            $query->whereTime('create_time', 'between', [date('Y-m-01 00:00:00', strtotime($start_time)), date('Y-m-t 23:59:59', strtotime($end_time))]);
        }

        $buildSql = $query->buildSql();

        $list = Db::table($buildSql)->alias('bs')->paginate(15);

        return $list->toArray();
4. group by分组后使用paginate分页,按天分组
  public function getZsyjDayReportStat(array $where)
    {
        $page = intval($where['page']);
        $limit = intval($where['limit']);
        $start_time = $where['start_time'];
        $end_time = $where['end_time'];

        // thinkphp中分页paginate和group by一起使用时代码异常,换下面写法
        $query = Db::table('eb_bonuslistlog')
            ->field('DATE_FORMAT(create_time,"%Y-%m-%d") as date,SUM(money) as total_money,COUNT(*) as total_number')
            ->group('date')
            ->order('date desc');

        if ($start_time && $end_time) {
            $query->whereTime('create_time', 'between', [$start_time . ' 00:00:00', $end_time . ' 23:59:59']);
        }

        $buildSql = $query->buildSql();

        $list = Db::table($buildSql)->alias('bs')->paginate(15);

        return $list->toArray();
    }

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值