将原始sql转换为查询生成器laravel

c86crjj0  于 2021-06-25  发布在  Mysql
关注(0)|答案(0)|浏览(278)

我有一个查询和子查询,但它仍然是原始sql形式。我想在查询生成器laravel中进行更改。但我遇到了一些问题,特别是在子查询的别名(as)中,以便在进一步的条件下重用。
这是我的原始sql。
下面是我的查询生成器:

$sub_query = DB::table('orders')
                ->join('order_details', 'orders.order_id', '=', 'order_details.order_id')
                ->leftjoin('shipment_details', 'shipment_details.order_detail_id', '=', 'order_details.order_detail_id')
                ->leftjoin('shipment_headers', 'shipment_headers.shipment_header_id', '=', 'shipment_details.shipment_header_id')
                ->where('orders.order_mask_id', '=', 'TZ4CFI')
                ->select('order_details.order_detail_id', 'shipment_details.qty')
                ->groupBy('order_details.order_detail_id');

    $result_2 = DB::table(DB::raw("({$sub_query->toSql()}) AS sub") )
        ->mergeBindings($sub_query);

    $result_2 = $result_2->from('orders')
        ->join('customers', 'orders.customer_id', '=', 'customers.id')
        ->join('order_details', 'orders.order_id', '=', 'order_details.order_id')
        ->join('products', 'order_details.product_id', '=', 'products.product_id')
        ->join('merchants', 'products.merchant_id', '=', 'merchants.merchant_id')
        ->join('product_subcategories', 'products.product_subcategory_id', '=', 'product_subcategories.product_subcategory_id')
        ->join('product_categories', 'product_subcategories.product_category_id', '=', 'product_categories.product_category_id')
        ->join('product_super_categories', 'product_categories.product_super_category_id', '=', 'product_super_categories.product_super_category_id')
        ->where('orders.order_mask_id', '=', 'TZ4CFI')
        ->where('sub.order_detail_id', '=', 'order_details.order_detail_id')
        ->whereRaw('(order_details.qty - sub.qty) != 0');

    $result_2 =   $result_2->select('orders.order_mask_id', 'orders.created_at', 'customers.email', 'customers.name', 'product_super_categories.product_super_category_name', 'product_categories.product_category_name', 'product_subcategories.product_subcategory_name', 'products.product_name', 
                    DB::raw('
                        order_details.qty - qty AS qty
                    '),
                    DB::raw('
                        IF(products.is_perishable, "Perishable", "Non Perishable") AS is_perishable
                    '), 
                    'merchants.merchant_name', 'orders.order_payment_type', 'orders.order_payment_status', 'orders.order_status', 
                    DB::raw('
                        "-" AS shipment_status
                    '), 
                    DB::raw('
                        "-" AS seller_name
                    '),
                    DB::raw('
                        "-" AS shipping_carrier_name
                    '),
                    DB::raw('
                        "-" AS shipping_service_name
                    '),
                    DB::raw('
                        "-" AS tracking_number
                    '),
                    DB::raw('
                        "-" AS scheduled_ship_date
                    '),
                    DB::raw('
                        "-" AS actual_ship_date
                    '),
                    DB::raw('
                        "-" AS delivery_date 
                    '))->get();
     dd($result_2);

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题