290 laravel中的子查询

可以怎么写

$sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
->mergeBindings($sub->getQuery()) 
->count();

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Query\Builder as QBuilder;
//上面是引用类，为区别于Eloquent\Builder，重命名为QBuilder
//下面的是构建查询的核心代码
        //要点：用于构造子查询的临时表，无get方法
        $temp_a=DB::table('erp_orders_detail as items')
            ->leftJoin('erp_orders as orders','items.order_id','=','orders.id')
            ->leftJoin('erp_product as product','product.id','=','items.product_id')
            ->leftJoin('erp_product_category as cate','cate.product_id','=','items.product_id')
            ->where('orders.status','=',4)
            ->selectRaw('orders.order_code,items.quantity as item_quantity,items.product_id,items.id as item_id,product.sku,product.cn_name,sum(case cate.category_id when 26 then 1 else 0 end) self')
            ->groupBy('items.id');
        $temp_b=DB::table('erp_inventory as inventory')
            ->rightJoinSub($temp_a,'table_a',function(QBuilder $query){
                $query->on('inventory.product_id','=','table_a.product_id');
            })
            ->selectRaw('table_a.*,sum( inventory.quantity ) in_quantity,sum( inventory.quantity_lock ) in_quantity_lock ')
            ->groupBy('table_a.item_id');
        $result=DB::table('erp_inventory as inven')
            ->rightJoinSub($temp_b,'table_b',function(QBuilder $query){
                $query->on('inven.product_id','=','table_b.product_id');
            })
            ->selectRaw('table_b.*,sum( CASE inven.warehouse_id WHEN 1 THEN inven.quantity ELSE 0 END ) dg_quantity')
            ->groupBy('table_b.item_id')
            ->orderByDesc('self')
            ->get();

相信上面的已经可以解决大部分问题！
官方文档

参考链接：https://laravel.com/docs/5.8/queries#raw-expressions
https://blog.csdn.net/weixin_44107914/article/details/102745807
https://zhuanlan.zhihu.com/p/65673620
https://learnku.com/docs/laravel/8.x/queries/9401#b5bee6