Create relationship from a pivot table

I have three models: order, product and shipment

order and product have a many to many relationship using pivot table order_product.

Shipments can be created for each product in an order. So an order may have multiple shipments.

Here is the simplified db structure for the above models:

https://drawsql.app/teams/self-14/diagrams/order

I can easily get relationship between order and products using belongsToMany relationship. But since shipment also uses the same order_product pivot table, how can I get relationships between shipments and order.product. I would like the following relationships

order->shipments() shipment->order() shipment->products()

For now, In order to get all products of a shipment, I'm using this:

    public function getProductsAttribute()
    {
        return $this->order()
            ->with(["products" => function($q) {
                $q->wherePivot("shipment_id", $this->id);
            }])
            ->get()
            ->pluck("products")->flatten();
    }

But I feel like this is not very optimized and it also doesn't generate a relationship.

kevinbui

3 years ago

Level 41

I think it could make sense to rename your order_product table to item, so we can define a model for the pivot table with a meaningful name. Then you can define a one-to-one relationship between item and shipment.

We end up with the following relationships:

class Order extends Model
{
    public function items()
    {
        return $this->hasMany(Item::class);
    }

    public function products()
    {
        return $this->belongsToMany(Product::class, 'item');
    }
}

class Product extends Model
{
    public function items()
    {
        return $this->hasMany(Item::class);
    }

    public function orders()
    {
        return $this->belongsToMany(Order::class, 'item');
    }
}

class Item extends Model
{
    public function order()
    {
        return $this->belongsTo(Order::class);
    }

    public function product()
    {
        return $this->belongsTo(Product::class);
    }

    public function shipment()
    {
        return $this->hasOne(Shipment::class);
    }
}

Please or to participate in this conversation.