时间戳

首页 > 解决方案 > 如何以有效的方式合并来自多个表的结果 Laravel

mysql - 如何以有效的方式合并来自多个表的结果 Laravel

问题描述

我必须合并 Laravel 中多个表的结果。目前我正在使用以下方式：

public function getMachines(Request $request) {
        $vendor_id = Auth::id();
        $ml_8_machine=ML8Machine::where('vendor_id','=',$vendor_id)->get();
        $ml_16_machine=ML16Machine::where('vendor_id','=',$vendor_id)->get();
        $ml_32_machine=ML32Machine::where('vendor_id','=',$vendor_id)->get();
        $ml_64_machine=ML64Machine::where('vendor_id','=',$vendor_id)->get();
        $ml_96_machine=ML96Machine::where('vendor_id','=',$vendor_id)->get();
        $ml_128_machine=ML128Machine::where('vendor_id','=',$vendor_id)->get();
        $machines = collect($ml_8_machine)
                        ->merge($ml_16_machine)
                        ->merge($ml_32_machine)
                        ->merge($ml_64_machine)
                        ->merge($ml_96_machine)
                        ->merge($ml_128_machine);
        return view('vendor.machines', compact('machines'));      
    }

我正在寻找一种有效的方法来在一个集合中进行查询和合并结果。

标签： mysqlsqllaravellaravel-5eloquent

解决方案

如果你的列是相同的，你可以unionAll用来合并记录。这将降低 IO 成本：

public function getMachines(Request $request) {
        $vendor_id = Auth::id();
        $ml_8_machine=ML8Machine::where('vendor_id','=',$vendor_id)->select('column1', 'column2'...);
        $ml_16_machine=ML16Machine::where('vendor_id','=',$vendor_id)->select('column1', 'column2'...);
        $ml_32_machine=ML32Machine::where('vendor_id','=',$vendor_id)->select('column1', 'column2'...);
        $ml_64_machine=ML64Machine::where('vendor_id','=',$vendor_id)->select('column1', 'column2'...);
        $ml_96_machine=ML96Machine::where('vendor_id','=',$vendor_id)->select('column1', 'column2'...);
        $ml_128_machine=ML128Machine::where('vendor_id','=',$vendor_id)->select('column1', 'column2'...);
        $machines = $ml_8_machine->unionAll($ml_16_machine)
                        ->unionAll($ml_32_machine)
                        ->unionAll($ml_64_machine)
                        ->unionAll($ml_96_machine)
                        ->unionAll($ml_128_machine)
                        ->get();
        return view('vendor.machines', compact('machines'));      
    }

请记住，您需要保持每列与其他列的顺序相同。

您也可以添加paginate()此查询：

$machines = $ml_8_machine->unionAll($ml_16_machine)
                        ->unionAll($ml_32_machine)
                        ->unionAll($ml_64_machine)
                        ->unionAll($ml_96_machine)
                        ->unionAll($ml_128_machine)
                        ->paginate(10);

推荐阅读