laravel去重(distinct())后分页(paginate())数据总数还是显示未去重前的总数解决方案

Laravel  /  管理员 发布于 2020-11-13 11:31:31   362

原因：

看源码得知，这个问题一直存在，因为分页先会执行一遍：select *  获取总数，所以你分页上去重并不会影响 他获取总数

    /**
     * Paginate the given query.
     *
     * @param  int  $perPage
     * @param  array  $columns
     * @param  string  $pageName
     * @param  int|null  $page
     * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
     *
     * @throws \InvalidArgumentException
     */
    public function paginate($perPage = null, $columns = ['*'], $pageName = 'page', $page = null)
    {
        $page = $page ?: Paginator::resolveCurrentPage($pageName);
        $perPage = $perPage ?: $this->model->getPerPage();
        $results = ($total = $this->toBase()->getCountForPagination())
                                    ? $this->forPage($page, $perPage)->get($columns)
                                    : $this->model->newCollection();
        return $this->paginator($results, $total, $perPage, $page, [
            'path' => Paginator::resolveCurrentPath(),
            'pageName' => $pageName,
        ]);
    }

案例demo:

1.distinct去重

    $res = DB::table('90_cbb_palmlife')
            ->select('id','tid','title','address','tel','lat','lng',
                DB::raw("round(st_distance_sphere(point(lng,lat),point({$where['lng']},{$where['lat']}))/1000,2) as space"
                )
        )->distinct()
    ->join('92_cbb_palmlife_coupon_relation', '90_cbb_palmlife.tid', '=', '92_cbb_palmlife_coupon_relation.palmlife_id')
            ->whereIn('92_cbb_palmlife_coupon_relation.palmlife_coupon_id',$where['cids'])
            ->orderBy('space','asc')
            ->paginate(10);

效果图：

2.groupBy去重

    $res = DB::table('90_cbb_palmlife')
            ->select('id','tid','title','address','tel','lat','lng',
                DB::raw("round(st_distance_sphere(point(lng,lat),point({$where['lng']},{$where['lat']}))/1000,2) as space"
                )
        )
    ->join('92_cbb_palmlife_coupon_relation', '90_cbb_palmlife.tid', '=', '92_cbb_palmlife_coupon_relation.palmlife_id')
            ->whereIn('92_cbb_palmlife_coupon_relation.palmlife_coupon_id',$where['cids'])
            ->groupBy('id')
    ->orderBy('space','asc')
            ->paginate(10);

效果图：

3.自定义分页 LengthAwarePaginator  (这里我就不举例了)