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.
