Eloquent custom relationship hasMany (foreign field contains text concatenated by foreign key)

Question

I have this database structure. 2 tables: shipment_out, stock_move.

shipment_out has the typical primary key integer id field.

stock_move has a field named shipment which is string type. This field can have these values:

"stock_shipment_out,1512",
"stock_shipment_in,65400",
"sale.line,358",
(...)

The thing is the table stock_move is related to a multiple tables based on the same field, so it has this text before.

In this case I want to define the relationship: shipment_out hasMany stock_move. So I need to join by stock_move.shipment has this value: 'stock_shipment_out,{id}'.

So how can I define this relationship? Would be something like:

public function stockMoves()
{
    return $this->hasMany(StockMove::class, 'shipment', 'stock.shipment.out,id');
}

I can achieve this relationship with query builder:

    $shipments = ShipmentOut
        ::join('public.stock_move', DB::raw('CONCAT(\'stock.shipment.out,\',public.stock_shipment_out.id)'), '=', 'stock_move.shipment')
        ->where('stock_shipment_out.id', '=', $shipmentOut);

But I need on a relationship too...

Answer 1

To solve this problem I had to define a custom attribute, and then I can define the relationship with this field.

public function getStockMoveShipmentAttribute()
{
    return "stock.shipment.out,{$this->id}";
}

public function stockMoves()
{
    return $this->hasMany(StockMove::class, 'shipment', 'stock_move_shipment')
}

Now I can use this relationship, but it's only one-direction... If I want to define the same relationship as the inverse it doesn't work.

I opened another question explaining it: Laravel relationship based on custom attribute not working both directions