Question 16446 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to subtract two columns from each other when using case expression – Laravel

I want to export some results in Excel from the Model method, so I added this,

public static function getAllData()
    {
    $records = DB::table('orders as t1')
        ->leftjoin('payments as t2', 't1.ord_id', '=', 't2.pay_ord_id')
        ->select(DB::raw("case when pay_type_id=190 then t1.ord_total - t2.pay_amount as Paid_Amount_Online"),
        ...
        ->get();
    
    return $records;
    }

So as you can see I want to check if the payment type id is equals to 190 that means user has paid it with wallet and the remaining balance is paid online.

So in order to get the Paid_Amount_Online value, I need to subtract the whole order total amount from the payment amount:

case when pay_type_id=190 then t1.ord_total - t2.pay_amount as Paid_Amount_Online

But this obviously wrong and returns syntax error.

So I wonder how can I subtract the amount values from eachother when using sql case expression.

Tags:

4

Answers


  1. 0

    check :

    public static function getAllData()
{
$records = DB::table('orders as t1')
    ->leftjoin('payments as t2', 't1.ord_id', '=', 't2.pay_ord_id')
    ->select(DB::raw("case when pay_type_id=190 then t1.ord_total - t2.pay_amount end as Paid_Amount_Online"),
    ...
    ->get();

return $records;
}

    before as use end

    Login or Signup to reply.
  2. 0

    Your

    case when pay_type_id=190 then t1.ord_total - t2.pay_amount as Paid_Amount_Online

    is raw SQL injected into the query. The syntax is like this:

    CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END

    That is:

    • you start the expression with case
    • there can be one or more conditions
      • a condition starts with a when, followed by an expression, continuing with a then and ending with the actual value
      • you may have an else that stands for a fallback logic when all conditions are false
    • you end the expression with end

    In your code you have a case, a condition with a when and a then, but you are missing its end.

    Login or Signup to reply.
  3. 0

    here is your solution, if you got any error after this, please post error also, in my answer you can remove else or you can give your comment –

     public static function getAllData()
    {
    $records = DB::table('orders as t1')
        ->leftjoin('payments as t2', 't1.ord_id', '=', 't2.pay_ord_id')
        ->select(DB::raw('(CASE WHEN t1.pay_type_id=190 Then t1.ord_total - t2.pay_amount ELSE "No Action Found" End) AS Paid_Amount_Online'),
        ...
        ->get();
    
    return $records;
    }
    Login or Signup to reply.
  4. 0

    You just forgot END at the end of your case expression:

    public static function getAllData()
        {
        $records = DB::table('orders as t1')
            ->leftjoin('payments as t2', 't1.ord_id', '=', 't2.pay_ord_id')
            ->select(DB::raw("case when pay_type_id=190 then t1.ord_total-t2.pay_amount as Paid_Amount_Online END as something"),
            ...
            ->get();
        
        return $records;
        }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search