How to get an array in laravel query-CodePudding

Heres my query

$bbaplaukts = DB::table('materials')
            ->where('statuss', '=', 'Izlietots')
            ->orwhere('statuss', '=', 'Pasutits')
            ->leftJoin('bbaplaukts', 'materials.scanner_code', '=', 'bbaplaukts.scanner_code')
            ->leftJoin('alternatives', 'materials.id', '=', 'alternatives.material_id')
            ->select(
              'materials.scanner_code as scanner_code',
              'materials.reference as reference',
              'materials.description as description',
              'materials.id as id',
              'bbaplaukts.place as place',
              'alternatives.alternatives as alternatives',
              'alternatives.links as links',
              DB::raw('count(IF(statuss = "Izlietots",bbaplaukts.statuss,null)) as izlietots'),
              DB::raw('count(IF(statuss = "Pasutits",bbaplaukts.statuss,null)) as pasutits'))
            ->groupBy('materials.scanner_code', 'materials.reference', 'materials.description',
             'materials.id', 'bbaplaukts.place', 'alternatives.alternatives', 'alternatives.links')
            ->orderBy('bbaplaukts.place', 'asc')
            ->paginate(15);

And heres the result it returns

#items: array:4 [▼
      0 => {#1303 ▼
         "scanner_code": "DM DS D2 D0"
         "reference": "Metāla šķēres 20cm"
         "description": "Metāla šķēres 20cm"
         "id": 2
         "place": "Bbaskapis"
         "alternatives": "Test123"
         "links": "Test123"
         "izlietots": 2
         "pasutits": 0
      }
      1 => {#1322 ▼
         "scanner_code": "DM DS D2 D0"
         "reference": "Metāla šķēres 20cm"
         "description": "Metāla šķēres 20cm"
         "id": 2
         "place": "Bbaskapis"
         "alternatives": "Test12345"
         "links": "https://www.google.com/search?client=firefox-b-d&q=talsu riepas"
         "izlietots": 2
         "pasutits": 0
      }

As you can see they are almost identical, only "alternatives" and "links" are different. Is there a way in which i could get the query to return a single entry, but the "alternatives" and "links" are arrays of all the different entry's?

Sorry for the bad tittle, cant really think of a way how to phrase this.

CodePudding user response：

Laravel provides a most efficient feature to achieve this through Model Relationships. However if you prefer to use query builder, you could try to delete 'alternatives' join from your main query and make it separately:

$bbaplaukts = DB::table('materials')
            ->where('statuss', '=', 'Izlietots')
            ->orwhere('statuss', '=', 'Pasutits')
            ->leftJoin('bbaplaukts', 'materials.scanner_code', '=', 'bbaplaukts.scanner_code')
            ->select(
              'materials.scanner_code as scanner_code',
              'materials.reference as reference',
              'materials.description as description',
              'materials.id as id',
              'bbaplaukts.place as place',
              DB::raw('count(IF(statuss = "Izlietots",bbaplaukts.statuss,null)) as izlietots'),
              DB::raw('count(IF(statuss = "Pasutits",bbaplaukts.statuss,null)) as pasutits'))
            ->groupBy('materials.scanner_code', 'materials.reference', 'materials.description',
             'materials.id', 'bbaplaukts.place')
            ->orderBy('bbaplaukts.place', 'asc')
            ->paginate(15);
            
foreach ($bbaplaukts as $key=>$bbaplaukt){
            $alternatives = DB::table('alternatives')->select('alternatives')->where('material_id', $bbaplaukt->id)->get();
            $links = DB::table('alternatives')->select('links')->where('material_id', $bbaplaukt->id)->get();
            $bbaplaukts[$key]->alternatives = $alternatives;
            $bbaplaukts[$key]->links = $links;
        }
return  response($bbaplaukts, 200);