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

9.5k views Asked by At

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.

2

There are 2 answers

4
Gayan On BEST ANSWER

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.

0
Serg Chernata On

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