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!

Solution 1

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.*']);

Solution 2

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

Solution 3

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); 

Comments

[AddWeb Solution Pvt Ltd]

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

[AddWeb Solution Pvt Ltd]

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

[patricus]

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).

[AddWeb Solution Pvt Ltd]

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

[lahwran]

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.