Laravel get size amount if not exist 0

Question

I want to make a list with all the available products and the amount per size like

 name     | size1  | size2  | size 3 | size4   | total
 ________________________________________________________
 t-shirt1 |   0    |    1   |    3    |   9    | 13
 t-shirt2 |   3    |    1   |    1    |   9    | 14
 t-shirt3 |   0    |    0   |    0    |   0    | 0

My Product model has a relation with ProductSize like:

public function sizeAvailable(){
    return $this->hasMany('App\ProductSize');

}

My ProductSize has a relation with Product like:

public function product()
{
    return $this->belongsTo('App\Product');
}

My productSize has a relation with Size like:

public function size(){
    return $this->belongsTo('App\Size');
}

The ProductSize model contains a value for amount. So Im able to get all sizes for a product with the right amount, and im also able to get all products from a size. How could I create the list as in my example. Is it possible to use size as an index? so I could do (pseudo code) to fill the <td> tags:

for(all products)
{
    if(product[somesize] not null)
      {{ product[somesize]->amount }}
    else
       0
}

The problem I have is that I don't know how to set the amount in the <tr> of the table if there is for example 1 size (size3) and no others when I do $product->sizeAvailable() it will only contain 1 value (size3), so I need to place 0 under size1 but how can I check if size1 is not available for the product? Or is there a better way to fill the table size amounts?

What I currently have:

 <table id="myTable" class="tablesorter table table-striped table-responsive">
        <thead>
        <tr>
            <th>
                Product
            </th>
            @foreach($sizes as $size)
                <th>
                    {{ $size->name }}
                </th>
            @endforeach
        </tr>
        </thead>
        <tbody>

            @foreach($products as $product)
                <tr >
                    <td>
                        {{ucfirst($product->name)}}
                    </td>
                    @foreach($sizes as $size)
                        <td>
                             HERE I NEED TO DO A CHECK IF $product HAS $size RETURN 0 ELSE RETURN ProductSize->amount
                        </td>
                    @endforeach



                </tr>
            @endforeach

        </tbody>
    </table>

I could make a function in product model like:

 hasSize($size)
 {
    $result = select from ProductSize where size = $size  

    if ($result = null)
       return 0;

    return $result->amount();
 }

But with this solution I have to run a query products * sizes times, if I have 1000 products and 20 sizes, that would meen 20.000 querys for one table.

____________________________
Relevant Database data
____________________________


products
-id           (primary key)
-name

sizes
-id           (primary key)
-name

product_sizes
-pr           (primary key)
-prodcut_id
-size_id
-amount

Answer 1

You have manually built a Many to Many Relationship for which Laravel already provides several useful features:

1. Update Product model (add or replace existing relation):

public function sizes()
{
    // many to many relation with intermediate table column
    return $this->belongsToMany('App\Size', 'product_sizes')->withPivot('amount');
}

public function getTotalAttribute()
{
    // access pivot table directly and use sum aggregate
    return DB::table('product_sizes')->where('product_id', $this->attributes['id'])->sum('amount');
}

2. Retrieve models (in controller) like this:

$sizes = Size::all();
$products = Product::with('sizes')->get(); // eager load sizes

3. Blade template:

...
<td>
    {{ucfirst($product->name)}}
</td>
@foreach($sizes as $size)
<td>
    {{-- HERE I NEED TO DO A CHECK IF $product HAS $size RETURN 0 ELSE RETURN ProductSize->amount --}}
    {{-- check and retrieve by using Collection methods --}}
    {{ $product->sizes->contains($size) ? $product->sizes->find($size)->pivot->amount : 0 }}
</td>
@endforeach
<td>{{ $product->total }}</td>
....

---

After recreating your example with the changes above and a (very small) database seed I get:

Product     size1   size2   size3   total
T-shirt1    10      3       0       13
T-shirt2    5       0       0       5

---

• By default this solution only produces 3 queries due to eager loading. (Verify with DebugBar) Each total calculation adds one query (unfortunately the Laravel query builder has no aggregate methods for pivot tables).

• contains() and find() in the view traverse the prefetched collections - no additional queries here.

• You do not need the ProductSize model as it only houses a pivot table with an additional attribute (amount). See Many to Many relationships and retrieving intermediate table columns.

• For convenience: if the product_sizes table is renamed to product_size and therefore follows Laravel's table naming convention the second parameter in $this->belongsToMany('App\Size', 'product_sizes') is not needed and may be written like this: $this->belongsToMany('App\Size')

The [product_size] table is derived from the alphabetical order of the related model names.