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

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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;
    }

Answer 4

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;
        }