distinct() with pagination() in laravel 5.2 not working

Question

I'm trying to use distinct() with pagination() in laravel 5.2 with fluent and it's given result proper but pagination remain same(Like without apply distinct).

I have already reviewed and tested below answers with mine code
- laravel 5 - paginate total() of a query with distinct
- Paginate & Distinct
- Query Builder paginate method count number wrong when using distinct

My code is something like:

DB::table('myTable1 AS T1')
->select('T1.*')
->join('myTable2 AS T2','T2.T1_id','=','T1.id')
->distinct()
->paginate(5);

EXAMPLE
- I have result with three records(i.e. POST1, POST2, POST3 and POST1) so I apply distinct().
- Now my result is POST1, POST2 and POST3 but pagination still display like 4 records(As result before applied distinct()).

Any suggestion would be appreciated!

Sucy

Can you tell us specifically what's not working? What do you expect to see, and what do you see instead? – Hominoid

Thanks for reply! See EXAMPLE added at my question.. – Sucy

I still can't recreate your scenario. Can you upload a DB schema and some rows? – Hominoid

Maybe the join is giving you the duplicated row, but with some extra fields in the select, so the distinct it's not excluding it? Try to remove the join, or call ->select() to specify which columns you will use. – Boomer

@DiogoSgrillo Yes, join return duplicated set of rows and that's why I used distinct() but unfortunately it does not affected on pagination result. Why? – Sucy

Answer 1

Pass the column name in distinct().

I have multiple tables joined and need distinct data. So listings work perfectly with distinct but the total records count during pagination was wrong and I landed on this thread. later, I was able to solve this by passing an argument in the distinct method.

DB::table('myTable1 AS T1')
    ->select('T1.*')
    ->join('myTable2 AS T2','T2.T1_id','=','T1.id')
    ->distinct(['T1.id'])
    ->paginate(5);

Nelrsa

This worked for me in Laravel 9 using Livewire. I couldn't get @patricus answer to work. – Perdure

Answer 2

There seems to be some ongoing issues with Laravel and using distinct with pagination.

In this case, when pagination is determining the total number of records, it is ignoring the fields you have specified in your select() clause. Since it ignores your columns, the distinct functionality is ignored as well. So, the count query becomes select count(*) as aggregate from ...

To resolve the issue, you need to tell the paginate function about your columns. Pass your array of columns to select as the second parameter, and it will take them into account for the total count. So, if you do:

/*DB::stuff*/->paginate(5, ['T1.*']);

This will run the count query of:

select count(distinct T1.*) as aggregate from

So, your query should look like:

DB::table('myTable1 AS T1')
    ->select('T1.*')
    ->join('myTable2 AS T2','T2.T1_id','=','T1.id')
    ->distinct()
    ->paginate(5, ['T1.*']);

Obtrusive

Yes, this is the working solution and found it from it's origin..Thanks man..+1! – Sucy

Further I place my codejack(into my answer) to bypass the passing second param. Could you please suggest? – Sucy

Answer 3

If it is possible, you can change distinct() with groupBy().

Pedant

Answer 4

I found the problem at my code, that is I need to pass $column as second parameter into paginate($perPage, $columns, $pageName, $page). I got the solution from laravel git issue.

So my working code:

DB::table('myTable1 AS T1')
->select('T1.*')
->join('myTable2 AS T2','T2.T1_id','=','T1.id')
->distinct()
->paginate(5, ['T1.*']);

Need suggestion
What if, I hack the code for Builder.php to bypass passing the second parameter $column. I mean this is the good thing OR any batter option?

/**
 * Paginate the given query into a simple paginator.
 *
 * @param  int  $perPage
 * @param  array  $columns
 * @param  string  $pageName
 * @param  int|null  $page
 * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
 */
public function paginate($perPage = 15, $columns = null, $pageName = 'page', $page = null)
{

    //To solved paginator issue with distinct...
    if(is_null($columns) && strpos($this->toSql(), 'distinct') !== FALSE){
        $columns = $this->columns; 
        $columns = array_filter($columns, function($value) {
            return (is_string($value) && !empty($value));
        });
    }
    else {
        //If null $column, set with default one
        if(is_null($columns)){
            $columns = ['*'];
        }
    }

    $page = $page ?: Paginator::resolveCurrentPage($pageName);

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

    $results = $total ? $this->forPage($page, $perPage)->get($columns) : [];

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

Sucy

You can't safely modify code inside the vendor directory. The next time you do a composer update, your change will get overwritten. This also adds extremely complexity to the ability to automate deployments, use CI, or even just simply manually moving your code from one place to another (you'll have to remember what custom changes you've made). – Obtrusive

@patricus: Yes I understand. Is there any way something like patch OR alter function(In Drupal/WP) which will prevent to discard my change? – Sucy

Answer 5

While other answers explain the issue and workarounds well, doing a join to accomplish an existance check does not really make sense. Do a whereIn instead - this avoids the issue and makes it obvious at a glance what you are doing in the query. As per your example:

DB::table('myTable1 AS T1')
    ->whereIn('T1.id', fn(Builder $q) => $q->
        select('T2.id')->from('T2'))
    ->paginate(5);

You can add any where condition you might need to T2.

Holmic

Answer 6

I had the same issue and I solved it by using ->groupby->paginate()

Parenteral

Answer 7

Pagination with multiple distinct.

composer require larahook/distinct-on-pagination

Usage

SomeModel::select(['*'])
    ->distinct(['field_a', 'field_b'])
    ->orderBy('field_a')
    ->orderBy('field_b')
    ->paginate($perPage)

Catawba

Answer 8

Use groupBy() instead of distinct(). I haven't tested but it will work. I was facing same issue in my query.

$author = Author::select('author_name')->groupBy('author_name')->paginate(15); 

Pear

Answer 9

use groupby with paginate to remove duplicacy

Mcclure

Hi Azwar! I think your answer's content is already covered by other answers. If you'd like to elaborate on those answers, I'd suggest doing so in a comment on one of them. – Snip

Answer 10

Disticnt with Laravel is malfunctioning. I solved this problem using groupBy.

Plenty

Answer 11

The distinct is working.... When you make a join, it checks for distinct entries in all selected columns and here.. the column names you get after the join are also included in the join...

To run this please add select() to your builder instance and it should work :)

DB::table('myTable1 AS T1')
  ->join('myTable2 AS T2','T2.T1_id','=','T1.id')
  ->select('T1.*')
  ->distinct()
  ->paginate(5);

Let me know if this doesn't work for you :)

Leavy

Thanks for answer....! I already did this select(), not resolved. The actual issue is like apply on distinct() resultset return proper distinct set of data but pagination react same as before use distinct. I tried every thing mentioned some of the links at my question but not solved my issue. – Sucy