[L4.2] distinct() and pagination returns total number of results not distinct total

@appnorth Show example and what relation it is. It's working just fine.

I am experiencing the problem in a number of places throughout the application so here is a more complete example of one of those.

In the Controller

// if dd($clients); total is 7 when in fact the distinct number of clients is 5 - this causes an extra pagination page which is empy

public function index()
{
   // get the signed in user's company
  $company = Company::findOrFail(Auth::user()->company->id);
        
   $clients = $company->clients()
                      ->distinct()
                      ->orderby('lastname', 'ASC')
                      ->paginate();

   return View::make('clients.index')->with('clients', $clients);
}

In the View

<section class="small-10 medium-12 top-padding">

  @if( $clients->count() )

    <table class="responsive">
          <thead>
              <tr>
                  <th>Name</th>
                  <th>Telephone</th>
                  <th>Email</th>
                  <th class="small-1"></th>
                  <th class="small-1"></th>
              </tr>
          </thead>
          <tbody>
              @foreach( $clients as $client)

                    <tr>
                        <td>{{ $client->title . ' ' .  $client->firstname . ' ' . $client->lastname }}</td>
                        <td>{{ $client->telephone }}</td>
                        <td>{{ $client->email }}</td>
                            <td> {{ link_to("/clients/{$client->id}", 'View', ['class' => 'button']) }}</td>
                    <td> {{ link_to("/clients/{$client->id}/edit", 'Edit', ['class' => 'button secondary']) }}</td>
                        </tr>   

              @endforeach
          </tbody>
      </table>

      {{ $clients->links() }}

  @else

      <p>No clients to display</p>

  @endif

</section>

The DB table looks along the lines of this:

id  | client_id | project_id
------------------------------------
1   | 101           | 11
------------------------------------
2  | 101           | 16
------------------------------------
3  | 102          | 32
------------------------------------
4  | 103          | 31
------------------------------------
5  | 104           | 19
------------------------------------
6  | 101           | 60
------------------------------------
7  | 106           | 37

The total returns 7 where-by the distinct total should be 5 - this means that the pagination links think there are 7 items to to display when in fact there are only 5 so an extra pagination link is included which doesn't have any content - it falls back to the 'No clients to display'.

@appnorth First off a suggestion:

// this query is redundant and findOrFail makes no sense, 
// since in case of FAIL you would first get Trying to get property of non-object on ->id part
$company = Company::findOrFail( // 2nd query for company
    Auth::user()->company->id // 1st query for company
);

// If you want OrFail, then you may use this:
$company = Auth::user()->company()->firstOrFail(); // single query

Then, I suppose pasted data is irrelevant - client_id, project_id? Anyway, try this:

$company->clients()->groupBy('clients.id')->paginate();

@JarekTkaczyk Thanks for the suggestions.

I used your suggestion of ->groupBy('clients.id') like so and it works great.

$clients = $company->clients()
                   ->groupBy('clients.id')
                   ->orderby('lastname', 'ASC')
                   ->paginate(5);

Thanks.

The "groupBy" solution from @JarekTkaczyk works perfectly!

Thank you, bro!

groupBy is not going to be a good solution when you are having lots of rows.

$clients = $company->clients()
                   ->groupBy('clients.id')
                   ->orderby('lastname', 'ASC')
                   ->paginate(5);

will give the following query

select count(*) from company . . . . group by clients.id

so if you have 100 unique clients.id then 100 rows will return. And the laravel iterate that array to calculate the count. if you have lakhs of unique ids then it will return a big result. That will use lots of runtime memory, when calculating the result.

In my case i have nearly 2 lakhs of rows and the php took 300 mb of runtime memory. If your server RAM is low then it will cause error. you can check this by using laravel debugger.

Please Suggest me a good solution to overcome this issue.

At first, docs says "Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually" https://laravel.com/docs/5.5/pagination Database Builder can do like this

$query->paginate(15, ['table.id']);

But Eloquent Builder cannot. I solved it like this

return (new EloquentBuilderDecorator($this->buildQuery()))->paginate(null, ['table.id']);

Decorator code:

<?php

namespace App\Decorators\Paginator;

use Illuminate\Pagination\Paginator;
use Illuminate\Pagination\LengthAwarePaginator;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Relations\Relation;

class EloquentBuilderDecorator
{
    /**
     * @param \Illuminate\Database\Eloquent\Builder $query
     */
    public function __construct($query)
    {
        $this->query = $query;
        $this->model = $this->query->getModel();
    }

    /**
    * to use columns when getting count
    */
    public function paginate($perPage = null, $columns = ['*'], $pageName = 'page', $page = null)
    {
        $page = $page ?: Paginator::resolveCurrentPage($pageName);

        $perPage = $perPage ?: $this->model->getPerPage();

        $total = $this->query->toBase()->getCountForPagination($columns);

        if ($total) {
            if ($this->query instanceof Builder) {
                $distinct = $this->query->getQuery()->distinct;
            }
            if ($this->query instanceof Relation) {
                $distinct = $this->query->getBaseQuery()->distinct;
            }
            if ($distinct) {
                $this->query->groupBy($columns);
            }
            $results = $this->query->forPage($page, $perPage)->get($columns);
        } else {
            $results = $this->model->newCollection();
        }

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

In Laravel Eloquent builder it looks like

getCountForPagination()

Instead of

getCountForPagination($columns)