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

You need to self-debug this; we don't have access to your database, so there's no way for us to verify that there are 29 pages (9 * 29 records) vs 21 pages (9 * 21 records). Replace `->paginate(9)` with `->count()`, to see how many records you're working with. — Tim Lewis, Oct 21 '22 at 15:31
Hi @TimLewis , I notice that if I take out distinct(), the results are 261 (29 pages), and with distinct() results are 178 (20 pages) but always shows 29 pages (9 empty) — Pepe F., Oct 21 '22 at 16:41
Makes sense, but I don't know why `distinct()` wouldn't play well with `paginate()` I'll have to do some testing and see if I can recreate this. — Tim Lewis, Oct 21 '22 at 17:52

score 1 · Accepted Answer · answered Oct 21 '22 at 22:10

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.

Laravel shows more pages in results

1 Answers1