Laravel - query builder - left join polymorphic relationship, distinct only

Question

So I'm using Vue 2.0 and Laravel 5.3 to create an application.

I've implemented my own sortable table with Vue, using the built-in pagination provided by Laravel.

Everything's working perfect - except, I'm trying to left join a "media" polymorphic table, so I can show an image in my table.

To enable sorting, I've had to use the query builder, as you can't sort on a relationship using Eloquent (AFAIK).

I define my relationships like:

$inventories = $inventories->leftjoin('users as inventory_user', 'inventories.user_id', '=', 'inventory_user.id');
$inventories = $inventories->leftjoin('categories as category', 'inventories.category_id', '=', 'category.id');
$inventories = $inventories->leftjoin('inventories as parent', 'inventories.parent_id', '=', 'parent.id');

Which work great. However, how exactly do I left join a polymorphic relationship, without repeating (duplicating) any of the rows?

I've got this:

$inventories = $inventories->leftJoin('media', function($q) {
    $q->on('media.model_id', '=', 'inventories.id');
    $q->where('media.model_type', '=', 'App\Models\Inventory');
});

Which does show media (if it has a relation in the polymorphic table). However, if for instance I have 5 images (media) for 1 particular inventory item, my query repeats the inventory for however media there are, which in my case it's repeated 5 times.

This is my select query:

    $inventories = $inventories->select(DB::raw("
            inventories.id as inventory_id,
            inventories.name as inventory_name,
            inventories.order_column as inventory_order_column,
            category.id as category_id,
            category.name as category_name,
            parent.name as parent_name,
            parent.id as parent_id,
            inventories.updated_at as inventory_updated_at,
            media.name
    "));

I think I need to use a groupBy, but I'm not sure where I define it. If I do

$inventories = $inventories->groupBy('inventories.id');

I get SQLSTATE[42000]: Syntax error or access violation: 1055 'test.inventories.name' isn't in GROUP BY...

Has anyone been in a similar situation or know where to put my groupBy to show 1/distinct inventory items?

EDIT:

I was able to fix by using:

$inventories = $inventories->leftJoin('media', function($q) {
    $q->on('media.model_id', '=', 'inventories.id');
    $q->where('media.model_type', '=', 'App\Models\Inventory');
    $q->orderBy('media.order_column', 'asc');
    $q->groupBy('model.model_id');
});

and then setting strict => false in my database.php.

Hippocras

The issue is with the grouping. See the answer I added below – Ne

FWIW, I would not call it a fix, a workaround at best. Add the column name ('model.mode_id' from your example) you are grouping by to SELECT statement and the error will be gone and you can/should keep the strict setting on. – Borkowski

If I ever look at that codebase again, I'll have to check that out! I remember feeling queazy about disabling strict but workaround fixed it, so I forgot about it. – Hippocras

Answer 1

As the error states this issue occurs when name column not being in GROUP BY clause.

To solve this change

'strict' => true, 

In mysql configuration under connections in your config/database.php file to

'strict' => false,

Additionally I would highly encourage to use polymorphic relations over left join with eloquent.

Ne

Thanks, Gayan! I originally found that fix here: github.com/laravel/framework/issues/15232 but didn't want to disable strict mode, as I didn't know what the repercussions were. I have since disabled it and it's working. I am indeed using polymorphic relations, which do work fine. However, I'm using the query builder, so I have to manually connect my tables via joins. Thanks for your help! – Hippocras

Sorry, I meant this issue: github.com/laravel/framework/issues/… – Hippocras

Glad you found it helpfull. More on mysql strict mode could found here mattstauffer.co/blog/… – Ne

Ah, that explains so much! Thanks Gayan! – Hippocras

Answer 2

I think you'll add it here:

$categories = $categories->select(DB::raw("
        inventories.id as inventory_id,
        inventories.name as inventory_name,
        inventories.order_column as inventory_order_column,
        category.id as category_id,
        category.name as category_name,
        parent.name as parent_name,
        parent.id as parent_id,
        inventories.updated_at as inventory_updated_at,
        media.name
"))->groupBy('inventories.id');

Baugher