Sum of column in where clause in laravel eloquent

Question

I have a FamilyMember model and a SubscriptionCharge model, SubscriptionCharge store charges of multiple months of each family member. I have to select members whos sum of charges greater than a specific amount.

$members=FamilyMember::whereHas('subscription_charge', function ($qq) {
   $qq->where(); // how to check sum of column 'amount' in where
})->get();

How to check the sum of column 'amount' in a where condition

Are you using MySQL?

Jonas Staudenmeir
– Jonas Staudenmeir

2019-04-19 19:21:46 +00:00
Commented Apr 19, 2019 at 19:21 — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Apr 19, 2019 at 19:21

Rutvij Kothari · Accepted Answer · 2019-04-20 21:19:59Z

1

Based on your comment, Updated Answer

$memeber = FamilyMember::query()
    ->join(DB::raw('("SELECT subscription_charge.family_member_id, SUM(subscription_charge.amount) as total_charges FROM subscription_charge GROUP BY subscription_charge.family_member_id HAVING SUM(subscription_charge.amount) > 
AMOUNT_YOU_WANT") as sub_table'), function($join) {
         $join->on('sub_table.family_member_id', '=', 'family_member.id');
    })->get();

Note: Not tested

edited Apr 20, 2019 at 21:19

answered Apr 19, 2019 at 19:23

Rutvij Kothari

1,28511 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Reghunath Over a year ago

showing sql error SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select count(*) as aggregate from family_members where exists (select * from subscription_charges where family_members.id = subscription_charges.family_member_id and sum(amount) > 10))

MrMoxy · Accepted Answer · 2019-04-19 20:46:03Z

0

I think you might be able to do something like this. (I'm at the airport so unable to test it.)

$members=FamilyMember->join(DB::raw('(SELECT family_member_id, SUM(amount) AS sum_of_charges 
            FROM subscription_charge
            WHERE family_member_id = x
            GROUP BY family_member_id
//                  (or HAVING family_member_id = x ?)
            ) charges'),
        function($join)
        {
           $join->on('FamilyMember.id', '=', 'charges.family_member_id');
        })
       ->select(                
            'FamilyMember.id',
            'charges.sum_of_charges'
        )
        ->where('sum_of_charges', '>', 'Y')
    })->get();

answered Apr 19, 2019 at 20:46

MrMoxy

4406 silver badges17 bronze badges

Collectives™ on Stack Overflow

Sum of column in where clause in laravel eloquent

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related