Null is given when trying to find data from pivot table

Question

I have a Many To Many relationship between User & Wallet Models:

Wallet.php:

public function users()
    {
        return $this->belongsToMany(User::class,'user_wallet','wallet_id','user_id')->withPivot('balance');
    }

User.php:

public function wallets()
    {
        return $this->belongsToMany(Wallet::class,'user_wallet','user_id','wallet_id')->withPivot('balance');
    }

And the pivot table user_wallet goes like this:

Then at the Controller, I need to access the balance field:

public function chargeWallet(Request $request, $wallet, $user)
{ 
            // $wallet is wallet_id (2) & $user is user_id (373)
            $bal = Wallet::with("users")
                ->whereHas('users', function ($query) use ($user) {
                    $query->where('id',$user);
                })->where('id', $wallet)->first();
            dd($bal->balance);
}

But now I get null as the result of dd($bal->balance) !!

So what is wrong here? How can I properly get the balance ?

Answer 1

Since it is a Many to Many relationships, you have Users attached to many Wallets, and Wallets attach to many Users. For each relation between a single Wallet and a single User: you have a pivot value (pivot relation). From your query, you'll retrieve Wallet with Users, each user's having the pivot relation value attached. So to retrieve the pivot table data (for each relation) you have to use a loop (added a with callback to make sure that only Users with matching user_id are eager loaded with the Wallets):

$bal = Wallet::with(["users"=>function ($query) use ($user) {
                $query->where('user_id',$user);
            }])
            ->whereHas('users', function ($query) use ($user) {
                $query->where('user_id',$user);
            })->find($wallet);

To avoid repeating same callback (where user_id =) you can assign it to variable:

$callback = function ($query) use ($user) {
     $query->where('user_id',$user);
};

then use it in your query:

 $bal = Wallet::with(['users' => $callback])
                ->whereHas('users', $callback)->find($wallet);

and then use a foreach loop:

foreach ($bal->users as $value){
   dd($value->pivot->balance);
}

or

if you only want to return the pivot value for the first User of the first Wallet of your query, then:

$user = $bal->users->first();
dd($user->pivot->balance);