Wrong total pagination with select distinct in Laravel 6

Question

I use Laravel 6.12, I have this request :

$queryJob = DB::table('jobs as j')->join('job_translations as jt', 'j.id', 'jt.job_id')
        ->whereNull('j.deleted_at')
        ->whereNull('jt.deleted_at')
        ->select('j.id', 'j.short_name', 'j.status', DB::raw("case when j.short_name = '{$request->short_name}' then 0 else 1 end"))
        ->distinct();

$jobs = $queryJob->paginate($qtyItemsPerPage);

The results displays an error for the total :

The total = 3, but as you can see the data contains only 2 elements.

I read here that when using a distinct, I must be clear on which column the total must be calculated: distinct() with pagination() in laravel 5.2 not working

So I modified my query like that:

$jobs = $queryJob->paginate($qtyItemsPerPage, ['j.*']);

But without success, the total is still wrong.

Is there a reason for using such a complex query structure instead of using the built-in functionality of Eloquent (like relations and soft deletions)? — Edwin Krause, Feb 12 '20 at 09:49
The simple reason I use DB facade is that ... I am beginner and do not feel confortable with Eloquent...But I think this request is not complex at all..... — Dom, Feb 12 '20 at 10:04
can you change `distinct()` with `groupBy('j.id')` as mentioned in the article's last answer, I'm not sure if it will take the punctuation in the argument — Edwin Krause, Feb 12 '20 at 10:11
Yes it works with a groupBy instead of a distinct. But I read in the doc that the pagination do not work with groupBy, strange.... : https://laravel.com/docs/6.x/pagination#paginating-query-builder-results . So is it recommanded or not to use a groupBy in my case ? — Dom, Feb 12 '20 at 10:16

Edwin Krause · Answer 1 · 2020-02-12T10:28:34.593

0

Hoping that I don't misunderstand your DB and relations structure and purpose of your query perhaps this will avoid using distinct or groupBy altogether?

$shortname = $request->input('short_name'); 
$queryJob = Job::with('job_translations')->select('id','short_name',
    'status', DB::raw("case when short_name = '" . $shortname . "' 
    then 0 else 1 end")
    ->paginate($qtyItemsPerPage);

Pagination can be easily manually added with skip and take in case you need to use groupBy

 $queryJob->skip(($page - 1) * $qtyItemsPerPage)->take($qtyItemsPerPage)->get();

edited Feb 12 '20 at 10:28

answered Feb 12 '20 at 10:20

Edwin Krause

1,766
1
16
33

I appreciate your help Edwin. I am waiting a little, but I think I will keep the _groupBy_ instead of the _distinct_ . Thanks again. – Dom Feb 12 '20 at 10:38

score 0 · Answer 2 · answered Sep 05 '22 at 13:59

The solution for me was to pass a field name to the distinct() method.

With your example:

$queryJob = DB::table('jobs as j')
    // joins, where and other chained methods go here
    ->distinct('j.id')

Solution taken from https://stackoverflow.com/a/69073801/3503615

Wrong total pagination with select distinct in Laravel 6

2 Answers2