3 Way Pivot Table or Complex Relationship

wow, you've had no help on this. but I've also looked at it, I'm a little stumped.

question, can a customer at one time be a biller, and another time a destination?

also, what kind of queries are you looking to do?

get shipments for a customer?

get shipments by type for a customer?

assuming that there can be only one of each relation, the shipment table should have a customer_id, a source_id and a destination_id

then create relationships for each in the shipment model, specifying the customer class and the relevant foreign key

personally I would have a separate locations table and Location model so that cutomer shipment is fom one location to another location, neither of which are the customer's own premises

@robrogers3 - It's okay, it gives me time to work on the other aspects. As far as your other questions, they are all yes. With the first question however, that's a yes as well as an addition that a customer can be all three at once (as in origin, destination and biller). I'd appreciate any help you can provide though, or any thoughts! Anything helps :)

@Snapey - would your solution work okay if a customer was all three of those relationships (as in origin, destination and biller)? And would you prefer this over a form of a pivot table? Thanks again!

understand that you ONLY need a pivot when there are multiple results on BOTH sides of the relationship.

If you assume belongsTo for each of the shipment-to-customer links then a pivot is not necessary

Shipment model

public function customer()
{
    $this->belongsTo(Customer::class, 'customer_id');
}

public function shipTo()
{
    $this->belongsTo(Customer::class, 'shipto_id');
}

public function shipFrom()
{
    $this->belongsTo(Customer::class, 'shipfrom_id');
}

and create three columns on the shipments table for the above (or whatever you want to call the columns)

Then you can load like;

shipment = Shipment::with('customer', 'shipTo', 'shipFrom')->find(1)

and Eloquent will load the same table as the relation for each use

@mprythero

one thing you said is kinda weird:

""" as an addition that a customer can be all three at once (as in origin, destination and biller). """

how is this possible? there is customer who bills himself? (if it's say interdepartmental, then I'd say we are missing something, like department)

also how can a customer be a biller?

Or we missing something? Perhaps a Customer is say 'Your' customer? and they basically could be:

• a biller, or
• a destination, or
• a origin

Where you are tracking 'your Customer' on whether they are one, or 2 or all of these types?

That would change thing a bit, right?

@Snapey

a customer can be a biller, you'd need:

public function biller(Customer::class, 'biller_id')
{
    $this->belongsTo(Customer::class, 'biller_id');
}

as a Customer is not just a customer, they could be any one of the 3 or a combination. According to @mprythero

That is if there is no generic customer, does that matter?

@robrogers3 I'm illustrating the principle not the specific relationships. btw you have an error in your example. No need to pass anything into the relationship.

@Snapey thanks got it.

I think the problem is that @MPRYTHERO will need to find all of the customers shipments.

i.e. the reverse of your query.

would we have to define different getters (relations) for each say shipTo (destination), shipFrom (origin) etc. and then how could he union the results

the reverse relationships can be made with hasMany

Well it's time we all defer to @Snapey he's right.

But you need to define the inverse like so:

    public function shipments()
    {
        return $this->shippingTo()->union($this->shippingFrom()->toBase())->union($this->biller()->toBase());
    }

    public function shippingTo()
    {
        return $this->hasMany(Shipment::class, 'shipto_id');
    }

    public function biller()
    {
        return $this->hasMany(Shipment::class, 'biller_id');
    }

    public function shippingFrom()
    {
        return $this->hasMany(Shipment::class, 'shipfrom_id');
    }

@MPRYTHERO notice the first one. it allows you to fetch all shipments as a union.

I think I got this right.