Return collection based on model method result

Question

I have a User model with a Credits relation.

public function credits()
{
    return $this->hasMany('App\Credit');
}

I'd like to return all users where their credit balance is greater than 0. Right now, I have two methods; one to retrieve the total credits the user has amassed and another to retrieve the credits the user has spent.

public function creditsIncome()
{
    return $this->credits->where('type', 0)->sum('amount');
}

public function creditsExpense()
{
    return $this->credits->where('type', 1)->sum('amount');
}

To get the balance, I have a third method:

public function creditsBalance()
{
    return $this->creditsIncome() - $this->creditsExpense();
}

Is there any way to do something like User::where('creditsBalance', '>', 0);?

Answer 1

You can use a modified withCount():

User::withCount([
    'credits as income' => function($query) {
        $query->select(DB::raw('sum(amount)'))->where('type', 0);
    },
    'credits as expense' => function($query) {
        $query->select(DB::raw('sum(amount)'))->where('type', 1);
    }
])->having(DB::raw('income - expense'), '>', 0)->get();