Laravel left join check if a conditon is greater than a count

user_5540863 0
Viewed 110

I would like to check a limit in number of user payment where a limit is set on user table. So i have the following database structure

table user
 id,name,...,pay_limit

and payment table

table payment
 id, user_id, payment_ref

So i have created the following code

$query = User::query();
//other stuff
$query->leftJoin('payment','payment.user_id','=','user.id')

//stuck

Am stuck on how to check if the totals of payments on a user is not greater than the user pay_limit

How can i check the above in a query

eloquent laravel
2 Answers

Simple with relations. Suppose payment model is Payment and payment amount in payment_amount column

class User extends Model{

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

    public function getIsOverLimitedAttribute(): bool
    {
        //if you check amount
        return $this->payments()->sum('payment_amount') > $this->pay_limit;
        //or if you check count of payments
        return $this->payments()->count() > $this->pay_limit;
    }
    public function scopeNoOverLimited($query){
        return $query->withCount('payments')->having('payments_count', '<', $this->pay_limit);
    }
}

And use

if($user->isOverLimited){
    //do stuff
}

Or get not over limited users:

User::noOverLimited()->get();
user_112519460

In terms of performance (since you want to be able to return all such users), the best approach is to store a summary column in your users table... total_payment

You can update all current users maybe via a migration file as such:

$table->integer('total_payment')->unsigned()->nullable();
DB::update("update users set total_payment = ('select count(id) from payments where user_id = id')");

Then to get all such users, you can do:

User::whereRaw('total_payment > pay_limit')->get();

Then add a PaymentObserver to increase the total_payment on new successful payments.

If you don't have access to modify the table, you can still use a scope like, but this can be performance intensive if being run all the time such as on user login without caching:

public function scopeAboveLimit($query)
{
    $id = $this->id;//user id
    return $query->whereRaw("pay_limit < ('select count(id) from payments where user_id = $id')");
}
user_6127170
Related