我有一个查询和子查询,但它仍然是原始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);
暂无答案!
目前还没有任何答案,快来回答吧!