Laravel is there a way to have relation belongsTo based on two columns?

Question

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?

Jihad Hashm · Accepted Answer · 2022-06-04T15:06:03.847

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
    $baseUnit->id = $this->baseUnit->id; // Set the id to the original unit id
    $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.

score 0 · Answer 2 · edited Jun 03 '22 at 14:33

0

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.

edited Jun 03 '22 at 14:33

Adrian Mole

49,934
160
51
83

answered Jun 03 '22 at 13:03

FIlipponik

11
1

I didn't think of that. will try and see how it goes. Thank You – Jihad Hashm Jun 03 '22 at 13:53

Donkarnash · Answer 3 · 2022-06-03T13:22:31.580

0

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

edited Jun 03 '22 at 13:22

answered Jun 03 '22 at 13:10

Donkarnash

12,433
5
26
37

@JihadHashm No worries. Looking at the table structures **many-to-many** relationship pops out as the most convenient way to go forward. However there are many different approaches one may take - it's ultimately a matter of choice. Good luck – Donkarnash Jun 03 '22 at 13:38
Thank you. I'm sorry deleted my previous comment by mistake new to stack overflow :). I will still look if no luck I will try to change the database structure like you said then. – Jihad Hashm Jun 03 '22 at 13:44
@JihadHashm You don't need to change your existing tables `units` or `products` just add a new migration for the `product_unit` pivot table - and define **belongsToMany** relationships - that's it. – Donkarnash Jun 03 '22 at 13:47
Yes I will do that if I don't find other solution. Thanks. – Jihad Hashm Jun 03 '22 at 13:51

Laravel is there a way to have relation belongsTo based on two columns?

3 Answers3