Home > Back-end >  How to add if..else conditional statements to the db query builder of Laravel
How to add if..else conditional statements to the db query builder of Laravel

Time:01-21

I have a export method in Laravel 5.8 for exporting some results from oders table:

public static function getAllData()
    {
       $records = DB::table('orders as t1')
            ->leftjoin('payments as t2', 't1.ord_id', '=', 't2.pay_ord_id')
            ->leftjoin('members as t3', 't2.pay_creator_id', '=', 't3.mbr_usr_id')
            ->where('t2.pay_confirm', 1)
            ->where('t1.ord_status','completed')
            ->orWhere('t1.ord_status','processing')
            ->select('t1.ord_id','t1.ord_date','t1.ord_total','t3.mbr_mobile','t3.mbr_name','t3.mbr_family')
            ->get(); 
       return $records;
    }

So it works fine and clean but I do need to add one more column for checking if the order payment type is by wallet or paid directly.

So every payment has a pay_type_id and if it is set to 189, then it should print Paid With Wallet otherwise print Paid Online as the appended column.

But the problem is, I don't know how to check this if..else condition in this method...

So if you know, please let me know.

Thanks.

CodePudding user response:

Try

->select(DB::raw('IF(pay_type_id=189,"Paid With Wallet","Paid Online as") as order_payment_type'))

CodePudding user response:

you could use case to do if else condition

for example like this

    public static function getAllData()
    {
        $records = DB::table('orders as t1')
        ->leftjoin('payments as t2', 't1.ord_id', '=', 't2.pay_ord_id')
        ->leftjoin('members as t3', 't2.pay_creator_id', '=', 't3.mbr_usr_id')
        ->where('t2.pay_confirm', 1)
        ->where('t1.ord_status', 'completed')
        ->orWhere('t1.ord_status', 'processing')
        ->select(DB::raw("case when pay_type_id=189 then 'Paid With Wallet' else 'Paid Online' end as your_column_name"),'t1.ord_id', 't1.ord_date', 't1.ord_total', 't3.mbr_mobile', 't3.mbr_name', 't3.mbr_family')
        ->get();
        return $records;
    }
  • Related