CODEWITHSUNDEEP
laraveleloquent-relationship
Abhay
Abhay

Reputation: 27

Getting the value of another table and summing it up

I have a table user and table payment which are connected to eachother using one to many relationship, In my user's table, i have a column name client_type which values are {residential, commercial, medical and industrial), if any of this clientType make payment, his user_id is stored in payment along with the amount paid..Now, i want to sum all the amount paid by any of this clientType from my payment_table .

These are my codes below Users Model

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use HasFactory, Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'first_name',
        'last_name',
        'address',
        'phone',
        'email',
        'lga',
        'ogwema_ref',
        'password',
        'role',
        'client_type'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function payments()
    {
        return $this->hasMany(Payment::class);
    }
}

// Payment Model
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Payment extends Model
{
    use HasFactory;
    
    protected $fillable = [
        'user_id',
        'amount',
        'bank_charges',
        'ref',
        'paystack_ref',
        'status',
    ];

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}
This is the code am trying to solve it
Route::get('/chart', function () {
    $residential = DB::select('select id from users where client_type = ?', ['residential']);
    return $residential->payments;
    
    
});

my output ErrorException Trying to get property 'payments' of non-object

How can i solve this, thanks in advance,

Upvotes: 0

Views: 92

Answers (2)

Freeman
Freeman

Reputation: 71

This select command $residential = DB::select('select id from users where client_type = ?', ['residential']); returns array of results (not object) so that this line $residential->payments throws ErrorException.

The logic of select command should be like this:

$totalMoney = DB::select('select sum(amount) from users INNER JOIN payments ON users.id=payments.user_id where users.client_type = ?', ['residential']);

Upvotes: 1

Joseph
Joseph

Reputation: 6269

you get this error because you write a native query so it hasn't accessed to eloquent model so to fix that you could write it by eloquent query

$residential = User::where('client_type', 'residential')->with('payments')->get();

and if you want to get the total payment for each user you could just add withSum method

$residential = User::where('client_type', 'residential')->withSum('payments', 'amount')->get();

Upvotes: 0

Related Questions