How to get multiple column data from two tables in one view

Question

I am trying to get data from two tables(brands and raw_materials), connected by a foreign key (brand_id). A brand can have multiple raw materials. I want it all displaying under its respective brand on one page. Instead, I get the results displayed separately.

Using Laravel 5.7 and Eloquent.

My Tables
[brands]
id    name
----------
1   Apple 
2   Colgate
3   LintX

[raw_materials]
id    name        brand_id      quantity
-----------------------------------------
1    50g pouch        1        200
2    10g laminate     3        320
3    75mm oil         1        500
4    Mint Rubber      3        400
5    Fire pouch       2        550
6    400 String       1        500
7    90g pouch        2        200

PageController

$brandData = DB::table('brands')
            ->join('raw_materials','raw_materials.brand_id','brands.id')
            ->select('brands.id as brand_id','brands.name as brand_name','raw_materials.name as raw_material','raw_materials.quantity')
            ->orderBy('brands.id')
            ->get();<br>
return view('rawmaterials.index', [
            'brandData'=>$brandData,
        ]);

View

@foreach ($brandData as $brand)
<p>Brand Name: {{$brand->brand_name}}</p>
<p>{{$brand->raw_material}} | Quantity: {{$brand->quantity}}</p>
<br>
@endforeach

Expected Results:

Brand Name: Apple
50g pouch | Quantity: 200
75mm oil | Quantity: 500
400 String | Quantity: 500

Brand Name: Colgate
Fire pouch | Quantity: 550
75mm oil | Quantity: 500
400 String | Quantity: 500

next brand ..


Actual Result:

Brand Name: Apple
50g pouch | Quantity: 200

Brand Name: Apple
75mm oil | Quantity: 500

Brand Name: Apple
400 String | Quantity: 500

Brand Name: Colgate
Fire Pouch | Quantity: 550

And so on..

Answer 1

This will work

$brandData = \DB::table('brands')->orderBy("brands.id")->get();

foreach ($brandData as $brand) {
    $brand->raw_materials = \DB::table("raw_materials")->where("brand_id", $brand->id)->get();
}

return view('rawmaterials.index', [
            'brandData'=>$brandData
        ]);

Then in your view you'll use nested like this ...

@foreach ($brandData as $brand)
<p>Brand Name: {{$brand->name}}</p>
@foreach($brand->raw_materials as $raw)
<p>{{$raw->name}} | Quantity: {{$raw->quantity}}</p>
@endforeach
<br>
@endforeach

That should get the job done.

On the other hand if you would like to use Eloquent rather you have to set the relationships in both Brand model and RawMaterial model as follows, assuming Brand and RawMaterial models are directly in the app folder

class Brand extends Model
{
    public function raw_materials()
    {
        return $this->hasMany('App\RawMaterial');
    }
}

class RawMaterial extends Model
{
    public function brand()
    {
        return $this->belongsTo('App\Brand');
    }
}

Then in your controller you simply do this ...

$brandData = \App\Brand::with("raw_materials")->orderBy("id")->get();

No need to change the blade syntax in your .blade file as the same foreach loop applies

Answer 2

You can create a relationship first:

function raw_materials() {
     return $this->hasMany(RawMaterial.class);
}

In your controller, use the with() function like so:

$brandData = Brand::with('raw_materials')->all();
return view('rawmaterials.index', [
    'brandData'=>$brandData
]);

Then in your view, you can get the raw_materials table data:

@foreach($branData as $brand)
   <h3>{{ $brand->name }}</h3>
   @foreach($brand->raw_materials as $raw_material)
       <div>{{ $raw_material->name }}</div>
       <div>{{ $raw_material->quantity }}</div>
   @endforeach
@endforeach