I have the following tables and want to get all units (base unit, and sub units) of the product

Units

id	name	multiplier	base_unit_id
1	Piece	1	null
2	Dozen - 12	12	1

Products

id	name	cost	price	unit_id
1	product1	10	14	1
1	product2	10	14	1

Relationship inside Product Model

public function units()
{
    return $this->belongsTo(Unit::class, 'unit_id', 'id'); // Or where base_unit_id = product unit id
    // I have tried this
    // $this->belongsTo(Unit::class, 'unit_id', 'id')->orWhere('base_unit_id', $this->unit_id)
    // Does not work
}

I like to get the products like so

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

Expected output is

[
    {
        "id": 1,
        "name": "Product 1",
        "unit_id": 1,
        "cost": 10,
        "price": 14,
        "units": [
            {
                "id": 1,
                "name": "Piece",
                "multiplier": 1,
                "base_unit_id": null
            },
            {
                "id": 1,
                "name": "Dozen - 12",
                "multiplier": 12,
                "base_unit_id": 1
            }
        ]
    },
    {
        "id": 1,
        "name": "Product 2",
        "unit_id": 1,
        "cost": 10,
        "price": 14,
        "units": [
            {
                "id": 1,
                "name": "Piece",
                "multiplier": 1,
                "base_unit_id": null
            },
            {
                "id": 1,
                "name": "Dozen - 12",
                "multiplier": 12,
                "base_unit_id": 1
            }
        ]
    }
]

I want the units in one list how can I do this?

CodePudding user response：

You will need to implement many-to-many relation between Product and Unit models via a pivot table

Basically it's like: Product record can have many Unit records and Unit record can have many Product records - Many-to-Many

public function up()
{
    Schema::create('product_unit', function(Blueprint $table) {
        $table->foreignId('product_id')->constrained('products')->onDelete('cascade');
        $table->foreignId('unit_id')->constrained('units')->onDelete('cascade');
        $table->timestamps();

        $table->primary(['product_id', 'uint_id']);
    });
}

public function down()

{
    Schema::dropIfExists('product_unit');
}

Next you need to define relationships

//Product model
public function units()
{
    return $this->belongsToMany(Unit::class)->withTimestamps();
}

//Unit model
public function products()
{
    return $this->belongsToMany(Product::class)->withTimestamps();
}

Laravel Docs - Eloquent Relationships - Many-to-Many

Laravel Docs - Eloquent Relationships - Updating Many-to-Many relationships

CodePudding user response：

There's no easy way to do this.

My solution is using getAttribute:

public function getUnitsAttribute()
{
    Unit::where('id', $this->unit_id)->orWhere('base_unit_id')->get();
}

But anyway, you can't use relation methods (like with() or load()) and you can't use it like a query builder.

Also, you can try this: https://stackoverflow.com/a/29775608/19262677.

CodePudding user response：

This is the solution that I came up with after looking for a while.

Product Model

public function baseUnit()
{
    return $this->belongsTo(Unit::class, 'unit_id', 'id');
}

Unit Model

public function subUnits()
{
    return $this->hasMany(Unit::class, 'base_unit_id', 'id');
}

And you can call it like

$product = Product::with(['baseUnit.subUnits'])->get();

Which gets you this output

[
    {
        "id": 1,
        "name": "Product 1",
        "unit_id": 1,
        "cost": 10,
        "price": 14,
        "base_unit": {
            "id": 1,
            "name": "Piece",
            "multiplier": 1,
            "base_unit_id": null,
            "sub_units": [
                {
                    "id": 1,
                    "name": "Dozen - 12",
                    "multiplier": 12,
                    "base_unit_id": 1
                }
            ]
        }
    }
]

And you can make a helper function inside the Product Model to format the units like this.

public function units()
{
    $baseUnit = $this->baseUnit->replicate(); // Clone it so we don't modify the original
    $result = collect();
    $baseUnit->subUnits->each(function ($unit) use (&$result) {
        $result->push($unit);
    });
    $baseUnit->offsetUnset('subUnits');
    $result->push($baseUnit);
    unset($baseUnit);
    return $result;
}

Output

[ 
    {
        "id": 1,
        "name": "Dozen - 12",
        "multiplier": 12,
        "base_unit_id": 1
    },
    {
        "id": 1,
        "name": "Piece",
        "multiplier": 1,
        "base_unit_id": null
    }
]

Thanks to everyone who took time to answer me.