CODEWITHSUNDEEP
phplaraveleloquent
GalahadXVI
GalahadXVI

Reputation: 839

Grouping items from a pivot table using Eloquent

I have a relationship between two models. The user has the relationship belongsToMany to the inventory model. I've done it like this.

public function inventory_items()
{
    return $this->belongsToMany(
        'App\Item',                             // joined model
        'inventory',                        // pivot table name
        'userid',                           // column name in pivot table
        'item_id'                            // column name in pivot table
    )->withPivot('equipped','id');
}

I'm currently getting the data like this

  $items = $user->inventory_items()->orderBy('inventory.id', 'DESC')->paginate(25);

And that lists the items in this way.

 +-------+-------------------+
 |  name |item_id|description|
 ----------------------------|
 | item 1|    1  | lorem...  |
 | item 1|    1  | lorem...  |
 | item 1|    1  | lorem...  |
 | item 2|    2  | lorem...  |
 | item 2|    2  | lorem...  |
 | item 3|    3  | lorem...  |
 ----------------------------

How can I group the data by item_id and show the count with it? I want it something like this 

 +-----------+-------+------------+
 |  name     |item_id|description|
 --------------------------------|
 |item 1 (x3)|    1  | lorem...  |
 |item 2 (x2)|    2  | lorem...  |
 |item 3 (x1)|    3  | lorem...  |
 ----------------------------------

I've tried different variations of 

  $items = $user->inventory_items()->orderBy('inventory.id', 'DESC')->groupBy("inventory.item_id")->paginate(25);

But it's returning with 

 This is incompatible with sql_mode=only_full_group_by

How can I do this? Preferably without changing the sql_mode

Upvotes: 1

Views: 622

Answers (1)

JCPHPDev
JCPHPDev

Reputation: 169

I think you have to use query builder:

$items = Item::join('inventory', 'inventory.item_id', 'items.id')
    ->where('inventory.userid', '=', $user->id)
    ->select('items.id', 'items.name', \DB::raw('count(*)'))
    ->groupBy('items.id', 'items.name')
    ->get();

Upvotes: 1

Related Questions