Showing 0 to 0 of 0 entries when there are multiple entries

ecomevo

Pagination also doesn't work. All I see is the first page of results.

Using the bllim/datatables ~1.4 package for Laravel.

I've tracked it down the following PHP code in my Laravel site:

    $Items = Item::select([
        'images.image',
        'items.id',
        'items.sku',
        DB::raw("IF(items.enabled, 'Yes', 'No') AS enabled")
    ])
        ->leftJoin('images', function ($j) {
            $j->on('items.id', '=', 'images.imageable_id')
                ->where('images.imageable_type', '=', 'Item');
        })
        ->whereNull('items.deleted_at')
        ->groupBy('items.id');

If I remove the leftJoin and remove the images.image field then pagination works correctly but my images don't show. The image thumbnail shows correctly when I keep the join, but that breaks pagination.

Here is my image config on the JS side:

{
    aTargets: [0],
    bSearchable: false,
    mData: "image",
    mRender: function (data, type, full) {
        return '<img src="' + full[0] + '" alt="thumbnail" class="img-thumbnail" />';
    }
}

What might the problem be with this query?

ecomevo

This was fixed by dropping the leftJoin altogether and changing the select field images.image to:

DB::raw("(select images.image from images where items.id = images.imageable_id and images.imageable_type = 'Item' limit 1) as image")`

Would love if someone could explain why this was necessary. :)

allan

Probably better off asking in a Laravel forum, or even in this case an SQL forum. That isn't a package I've used before.

Allan

ecomevo

Turns out the problem comes in with the way MySQL aggregate functions work. Instead of Datatables getting a count that represented the total # of rows, it got multiple rows with a count representing the number of rows that were aggregated under that group by.