laraveleloquentgroup-bylaravel-pagination

Laravel shows more pages in results


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

Solution

  • 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.