Home > Software design >  How to query with nested distinct records in Laravel/Eloquent?
How to query with nested distinct records in Laravel/Eloquent?

Time:09-24

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?

CodePudding user response:

As I understand, hasManyThrough works in a scenario that a product has many stocks and a stock has many colors. This is not the case.

You can simply eager load the relationship and present the data the way you want, using collection and higher order messages:

public function index()
{
    $products = Product::with('stocks.color')->get();

    return ProductResource::collection($products);
}

class ProductResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'available_sizes' => $this->stocks->map->size->unique()->toArray(),
            'available_colors' => $this->stocks
                ->map->color
                ->map->name
                ->unique()
                ->toArray()
        ]
    }
}

CodePudding user response:

here you will use models ProductModel

public function stocks()
{
return $this->belongsto(StockModel::class,'product_id');
}
StockModel
public function colors()
{
return $this->hasmany(ColorModel::class,'color_id');
}

Controller

$product=Product::with('stocks.colors')->find($id);
dd($product);

and check if the data are available.

or according to the documentation hasmanythrough ProductModel:

public function colors(){
return $this->hasManyThrough(
            Stock::class,
            Color::class,
            'product_id', // Foreign key on the product table...
            'color_id', // Foreign key on the colors table...
            'id', // Local key on the products table...
            'id' // Local key on colors table...
        );
}

Controller:

$product=Product::with('colors')->find($id);
  • Related