Laravel - Get additional column value from pivot table

user_13353845 0
Viewed 82

I have Stores and Medicines tables with many to many relationship. Storing and updating work fine with attach and sync. The table structure is like below. I want to retrieve value of extra column (expired).

store_id | medicine_id | expired
    1         2           1
    1         3           0

Also I need to count total medicines which expired like this

Count pivot values

Both models have withPivot('expired') model relation.

Controller

public function show($id)
{
    $medicine = Medicine::findOrFail($id);
    $stores = $medicine->findorfail($id)->stores()->get();
    return view ('medicines', compact('medicine', 'stores'));
}

View

I can list medicines with this

@foreach ($stores as $store)
{{ $store->value('name') }}
{!! $store->pivot->expired == 1 ?
'Expired' : 'Not-Expired' !!}
<a href="{{ route('store', $store->id) }}">View</a>
@endforeach

I tried to count Cities like this:

@foreach($stores as $store)
{{ ($store->pivot->expired==1) }}
@endforeach

It gives 1 1. How can I count total cities with have expired Cetamols?

laravel pivot-table orm loops php
1 Answers

The following should work:

Sum on the relationship collection:

$stores->sum('pivot.expired');

Sum on the Query Builder:

Medicine::findOrFail($id)->stores()->sum('expired');

Also your controller code does not make much sense at the moment and could be simplified to this:

public function show($id)
{
    $medicine = Medicine::findOrFail($id);
    $stores = $medicine->stores;

    return view('medicines', compact('medicine', 'stores'));
}

I removed the second call to findOrFail() and removed the get() since it is unnecessary.

user_91930550
Related