John Davidson

Message:

I have the following table structure:

Products

========

  id  | name 

------|-------

  1   | A

  2   | B



Stocks

========

  id  | product_id | color_id | size | qty

------|------------|----------|----- |-----

  1   | 1          | 1        | S    | 37

  2   | 1          | 1        | XL   | 89

  3   | 1          | 2        | S    | 6

  4   | 1          | 2        | L    | 8



Colors

========

  id  | name  |  hex

------|-------|-------

  1   | Red   | #ff0000

  2   | Green | #00ff00

What I want is to get the list of products with each of its available colors and sizes, so:

Product {

  id:               string

  name:             string

  available_sizes:  string[]

  available_colors: Color[]

}

I looked up the Laravel documentation and figured that I need to use hasManyThrough (probably) to make a relation between Product <==> Color. But since my colors table doesn't have a stock_id, I am not understanding how can I connect them.

How can I achieve this?

---

My Answer:
You can achieve this by defining relationships in your Product and Color models. Here's how you can do it:

1. Define relationships in your Product model:

php
class Product extends Model
{
public function stocks()
{
return $this->hasMany(Stock::class);
}

public function colors()
{
return $this->hasManyThrough(Color::class, Stock::class, 'product_id', 'id', 'id', 'color_id');
}
}


2. Define relationships in your Color model:

php
class Color extends Model
{
public function stocks()
{
return $this->hasMany(Stock::class);
}

public function products()
{
return $this->hasManyThrough(Product::class, Stock::class, 'color_id', 'id', 'id', 'product_id');
}
}


Now, you can query the products with each of its available colors and sizes like this:

php
$products = Product::with('colors')->get();

foreach ($products as $product) {
echo "Product: " . $product->name . "\n";
echo "Available Colors: \n";
foreach ($product->colors as $color) {
echo "- " . $color->name . "\n";
}
echo "Available Sizes: \n";
foreach ($product->stocks as $stock) {
echo "- " . $stock->size . "\n";
}
}


This will give you the list of products with each of its available colors and sizes.