CODEWITHSUNDEEP
phpmysqlpaginationlaravel-5
adam78
adam78

Reputation: 10078

Laravel 5.2 - Incorrect Pagination Total When using Distinct Clause

I have the following query where I want to select distinct rows. However the count query generated by the paginator does not seem to add the distinct clause and so I'm getting an incorrect total?

DB::enableQueryLog();

$jobs =  Job::join('locations', 'locations.id', '=', 'jobs.location_id')
            ->join('job_industry', 'job_industry.job_id', '=', 'jobs.id')
            ->select('jobs.*', 'locations.name')
            ->distinct()
            ->paginate(5, 'jobs.id');

 dd(DB::getQueryLog());

This is the query log:

"query" => """
    select count(*) as aggregate from `jobs`
     inner join `locations` on `locations`.`id` = `jobs`.`location_id`
     inner join `job_industry` on `job_industry`.`job_id` = `jobs`.`id`

  """


"query" => """
    select distinct `jobs`.*, `locations`.`name` from `jobs`
     inner join `locations` on `locations`.`id` = `jobs`.`location_id`
     inner join `job_industry` on `job_industry`.`job_id` = `jobs`.`id`
    limit 5 offset 0
  """

As you can see the count query generated by the paginator method is incorrect - it should be COUNT(DISTINCT jobs.id) as aggregate...

I followed the instruction from the following post (distinct() with pagination() in laravel 5.2 not working) but I cant find the following code referenced in the above post inside my copy of builder.php - this can be confirmed by checking the master repo: https://github.com/laravel/framework/blob/5.2/src/Illuminate/Database/Eloquent/Builder.php#L484

 //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 = ['*'];
    }
}

I tried to add the above code to my own paginate method inside the builder.php file but it makes no difference - I still get the same behavior?

How do I fix this. The only way I can overcome this is by using a group by clause but then that has its own performance issues.

Upvotes: 0

Views: 2494

Answers (1)

exceptione
exceptione

Reputation: 101

I encountered similar problem before. I fixed it by doing this :

->distinct('jobs.id')
->paginate(5, 'jobs.id');

Upvotes: 4

Related Questions