[FIXED] how do i take 3 different schema tables with 1 query in laravel

Issue

I want to take 3 different schema tables, each schema requires a join with a params table, so here I am trying to take one and merge, but I think that method is not effective and makes old data load, this my code

 $tabel1  = DB::table('schema1.vendors AS ven')
                        ->orderBy('created_at','desc')
                        ->leftJoin('schema1.admin_params AS ap','ap.admin_param_uuid','=','ven.product_type')
                        ->select('ven.*','ap.description','ap.param_value')
                        ->where('ven.deleted_by',null)
                        ->where('ap.deleted_by',null)
                        ->get(); 
$tabel2  = DB::table('schema2.vendors AS ven')
                        ->orderBy('created_at','desc')
                        ->leftJoin('schema2.admin_params AS ap','ap.admin_param_uuid','=','ven.product_type')
                        ->select('ven.*','ap.description','ap.param_value')
                        ->where('ven.deleted_by',null)
                        ->where('ap.deleted_by',null)
                        ->get();   
$merged = $tabel1->merge($tabel2)->sortByDesc('created_at'); 

I feel that my code is too heavy when loading, is there any suggestion to make my code simplified? because there will be 5 different schemas, but still the table being called is the vendor

Solution

You can use unionAll instead of collection merge, it will reduce the IO cost of database.

$tabel1  = DB::table('schema1.vendors AS ven')
                        ->leftJoin('schema1.admin_params AS ap','ap.admin_param_uuid','=','ven.product_type')
                        ->select('ven.column1', 'ven.column2', 'ven....', 'ap.description','ap.param_value')
                        ->whereNull('ven.deleted_by')
                        ->whereNull('ap.deleted_by');

$tabel2  = DB::table('schema2.vendors AS ven')
                        ->leftJoin('schema2.admin_params AS ap','ap.admin_param_uuid','=','ven.product_type')
                        ->select('ven.column1', 'ven.column2', 'ven....','ap.description','ap.param_value')
                        ->whereNull('ven.deleted_by')
                        ->whereNull('ap.deleted_by');

$table1->unionAll($table2)->orderBy('created_at')->get();

PS: the columns you that selected need to be in same order.

Answered By - TsaiKoga