Eloquent Deep Dive

I encountered another unique problem that I've been struggling to find a great solution for. This ties back to the other day when I talked about working with databases you don't maintain or have control over.

Here is the situation. I have one to one relationship I am trying to define; an Order has Shipping. In both tables there is an INVOICE column that is defined as an nvarchar(15). In the Order table, the INVOICE values are front padded with however many extra spaces are needed in order to fill the 15 characters, ' 265039'. While in the Shipping table, the INVOICE values are as you’d expect '265039'

Because of this the standard hasOne and belongsTo relationships will not work. They will create queries that resemble the following.

-- Will return no results since Shipping INVOICE does not have the padding
SELECT
  *
FROM
  [SHIPPING]
WHERE
  [SHIPPING].[INVOICE] = '          12345';

-- Will also return no results since Order INVOICE values have paddded
SELECT
  *
FROM
  [ORDERS]
WHERE
  [ORDERS].[INVOICE] = '12345'

This led me to taking a deep dive into how Eloquent builds up the queries for these relationships.

My somewhat viable solution

One thing I have been able to do with mild success is on the Shipping and Order models is add an invoice attribute that will either pad or trim the Invoice value when it is accessed

// Shipping.php
public function invoice(): Attribute  
{  
    return Attribute::make(  
        get: fn(string $value) => Str::padLeft($value, 15)  
    );
}

// Order.php
public function invoice(): Attribute  
{  
    return Attribute::make(  
        get: fn(string $value) => Str::ltrim(Str::rtrim($value))  
    );
}

I haven't fully tested or committed to this just yet, just what I am currently exploring. So far this does allow me to call the relationship off of each model.

Order::find($orderId)->shipping;
Shipping::find($shipingId)->order

However this does not currently work with eager loading.

Order::find($orderId)->with("shipping");
Shipping::find($shipingId)->with("order")