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

Question

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

Maksim · Answer 1 · 2021-08-04T19:26:26.067

1

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();

edited Aug 04 '21 at 19:26

answered Aug 04 '21 at 18:54

Maksim

2,653
2
13
28

I would like the data returned to also contain if($user->isOverLimited) in the query as am passing the data to another app which only requires to receive only users which havent reached the limit. – Geoff Aug 04 '21 at 19:13
HI yes after checking on this there is an error of Illegal operator and value combination. Seems pointing to ->having('payments_count', '<', $this->pay_limit) – Geoff Aug 11 '21 at 12:32

score 0 · Answer 2 · answered Aug 24 '21 at 12:52

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')");
}

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

2 Answers2