framework: [5.6.27] Pagination pages count incorrect

paginate() doesn’t support distinct(). Use groupBy() instead:

Product::select('products.*')
    ->join('categories', 'categories.id', '=', 'products.category_id')
    ->where($filters)
    ->groupBy('products.id', ...)
    ->paginate(10);

Since you are using MariaDB, you have to add all columns from select() to groupBy().

I have faced same issue having next configuration:

• Laravel Version: 5.6.28
• PHP Version: 7.2.8

Pagination provide empty pages when is used with join and distinct. My query is someting like

 Package::query()
    ->select("packages.*")
    ->leftJoin("barcodes", "barcodes.scan_id", "=", "packages.scan_id");
    ->orderBy("scan_id", "desc")
    ->distinct()
    ->paginate(20, ["packages.scan_id"]);

The problem is at this line https://github.com/laravel/framework/blob/5.6/src/Illuminate/Database/Eloquent/Builder.php#L710. Instead of:

    $results = ($total = $this->toBase()->getCountForPagination())

should be:

    $results = ($total = $this->toBase()->getCountForPagination($columns))

This issue was fixed already for Database/Query/Builder https://github.com/laravel/framework/blob/5.6/src/Illuminate/Database/Query/Builder.php#L1980.

I have fixed this for my project creating Custom Eloquent Builder

class CustomEloquentBuilder extends Builder {

    /**
     * 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($columns))
            ? $this->forPage($page, $perPage)->get($columns)
            : $this->model->newCollection();

        return $this->paginator($results, $total, $perPage, $page, [
            'path' => Paginator::resolveCurrentPath(),
            'pageName' => $pageName,
        ]);
    }
}

and in my model I have added:

/**
     * Create a new Eloquent query builder for the model.
     *
     * @param QueryBuilder $query Query Builder
     *
     * @return CustomEloquentBuilder|static
     */
    public function newEloquentBuilder($query)
    {
        return new CustomEloquentBuilder($query);
    }