Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 472
Kingsley Avatar asked Jan 05 '17 18:01

Kingsley


2 Answers

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.

like image 194
Gayan Avatar answered Nov 14 '22 22:11

Gayan


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');
like image 34
Serg Chernata Avatar answered Nov 14 '22 22:11

Serg Chernata