I'm working with a query where the results finish on page 21, but I'm getting 29 pages of links. My problem might be a groupBy()
problem, but I don't know how to do it.
$afiliates = DB::table('ad_afiliado as af')
->select('af.logo_url', 'af.NombreComercial', 'af.Destacado',
'af.id_afiliado', 'af.Clave')
->join('af_promocion as promo', function ($join) {
$join->on('af.Clave', '=', 'promo.id_afiliado');
})
->where('promo.v_fin', '>', $FechaActual)
->where('af.Activo', '=', 'S')
->where('af.Categoria', 'like', $categoryStr)
->orderBy('af.NombreComercial')
->orderBy(DB::raw('RAND()'))
->distinct()
->paginate(9);
I found the answer. It seems to be an issue with laravel distinct() and pagination, especialy when making joins.
The thread is here: distinct() with pagination() in laravel 5.2 not working
I had to add the field name was causing repetition of results, to the distinct() and paginate(). In my case 'promo.id_afiliado'
as you'll see in the code next
$afiliates = DB::table('ad_afiliado as af')
->join('af_promocion as promo', 'af.Clave', '=', 'promo.id_afiliado')
->select('af.logo_url', 'af.NombreComercial', 'af.Destacado', 'af.id_afiliado', 'af.Clave')
->where('promo.v_fin','>',$FechaActual)
->where('af.Activo','=', 'S')
->distinct('promo.id_afiliado')
->orderBy('af.Destacado', 'desc')
->orderBy('af.NombreComercial')
->paginate(9, 'promo.id_afiliado');
Thaks @TimLewis for caring, hope this will usefull to others.