Laravel shows more pages in results

Question

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

Answer 1

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.