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
.
As the error states this issue occurs when
name
column not being inGROUP BY
clause.To solve this change
In
mysql
configuration underconnections
in yourconfig/database.php
file toAdditionally I would highly encourage to use polymorphic relations over left join with eloquent.