using SUM in whereraw/where in laravel framework

Question

I have columns named as PlanCost Discount AmountPaid and a label which contains the Due

Here,I am trying to display only the rows where the PlanCost != AmountPaid

this is my whole query

 $getID = isset($inputArray['id']) ? $inputArray['id'] : 0;
    $query = TelePlanSelect::join('tele_plan', 'tele_plan.id', '=', 'tele_plan_select.teleplan_id'  )
        ->leftjoin('tele_payment_defs', 'tele_payment_defs.telereg_id', '=', 'tele_plan_select.telereg_id')
        ->leftjoin('tele_payment_items', function ($join){
            $join->on('tele_payment_items.telepaymentdefs_id', '=', 'tele_payment_defs.id')
                ->on('tele_payment_items.teleplan_id', '=' ,'tele_plan.id');
        } )

        ->selectRaw('tele_plan.id as `PlanID`,' .
            'tele_plan.plan_name as `PlanName`,' .
            'tele_plan.plan_cost as `PlanCost`,' .
            'tele_plan.plan_details as `PlanDetails`,'.
            'sum(tele_payment_items.amount) as  `AmountPaid` ,'.
            'COALESCE(sum(tele_payment_items.discount),0) as `Discount`,'.
            '(COALESCE(tele_plan.plan_cost,0) -  sum(COALESCE(tele_payment_items.discount,0))) - sum(coalesce(tele_payment_items.amount, 0)) as  `Due` '

        )
        ->where('tele_plan_select.telereg_id', $getID)
        ->groupBy('tele_plan.id')
      ->where(' tele_plan.plan_cost', '!=', 'sum(tele_payment_items.amount)');

I am not sure whether we can use sum in where/whereraw Could someone help me?

EDIT 1 : {"message":"SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select count(*) as aggregate from (select tele_plan.id asPlanID,tele_plan.plan_name asPlanName,tele_plan.plan_cost asPlanCost,tele_plan.plan_details asPlanDetails,sum(tele_payment_items.amount) asAmountPaid,COALESCE(sum(tele_payment_items.discount),0) asDiscount,(COALESCE(tele_plan.plan_cost,0) - sum(COALESCE(tele_payment_items.discount,0))) - sum(coalesce(tele_payment_items.amount, 0)) asDuefromtele_plan_selectinner jointele_planontele_plan.id=tele_plan_select.teleplan_idleft jointele_payment_defsontele_payment_defs.telereg_id=tele_plan_select.telereg_idleft jointele_payment_itemsontele_payment_items.telepaymentdefs_id=tele_payment_defs.idandtele_payment_items.teleplan_id=tele_plan.idwheretele_plan_select.telereg_id= 8 and tele_plan.plan_cost <> sum(tele_payment_items.amount) group bytele_plan.id) as a) @ C:\\xampp\\htdocs\\halframework\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php:625","status":"failed"}

Jerodev · Accepted Answer · 2016-12-08T09:14:54.467

0

You can use raw sql functions like sum, but you have to wrap them in laravel's \DB::raw() function so they won't be escaped.

For your Where clause, you should do the following

->where('tele_plan.plan_cost', '<>', \DB::raw('sum(tele_payment_items.amount)'));

You could also use laravel's WhereRaw function to write your own conditions:

->whereRaw('tele_plan.plan_cost <> sum(tele_payment_items.amount)')

Update: The error stated that there was a problem with the GroupBy. The reason for this is because a sum can only be done after a group by, but a where can't. So, this will have to be added in a HAVING clause.

Replace the where by a having, and this should work:

->havingRaw('tele_plan.plan_cost <> sum(tele_payment_items.amount)');

edited Dec 08 '16 at 09:14

answered Dec 08 '16 at 08:31

Jerodev

32,252
11
87
108

This doesn't work,what I have to know is can we use `sum` inside the where clause in laravel? – mohamed ashfaq Dec 08 '16 at 08:37
Yes, you can. Have you tried both examples in my answer? If so, could you update your question and add the output you get and the output you expect? – Jerodev Dec 08 '16 at 08:40
I have added the error message and I have written in my Question. I need the rows where the `plan_cost != tele_payment_items.amout` – mohamed ashfaq Dec 08 '16 at 08:54

using SUM in whereraw/where in laravel framework

1 Answers1