Laravel with custom relation

General
1

I have a question regarding Eloquent relations in a Laravel 10 project. Let’s say there are three tables:

orders- table with order data

  • id - order ID
  • shop_id - ID of the shop to which the order belongs

orders_products - table with data on products in orders

  • order_id - order ID
  • product_id - product ID

remainings- table with product stock data by date and shop

  • date - date
  • shop_id - shop ID
  • product_id - product ID
  • remaining - remaining stock

The question is: what relation should be defined in the OrdersProduct class to retrieve the remaining stock for product_id, shop_id (via the orders table), on the most recent date? I tried using hasOneThrough:

public function remainings(): HasOneThrough
{
    return $this->hasOneThrough(Remaining::class, Order::class, 'id', 'shop_id', 'order_id', 'shop_id')->whereProductId($this->product_id)->latest('date');
}

However, this results in a separate subquery to the database for each call to this method. I’d like to load all the data through the relation in the “correct” way. Or is Laravel not capable of this yet?

Solve the problem with scope:

public function scopeWithLatestRemaining2(Builder $query): Builder
{
    return $query->join('orders', 'orders.id', 'orders_products.order_id')->addSelect([
        'latest_remaining' => Remaining::select('remaining')
            ->whereColumn('remainings.product_id', 'orders_products.product_id')
            ->whereColumn('remainings.shop_id', 'orders.shop_id')
            ->orderByDesc('date')
            ->limit(1)
    ]);
}

But I would prefer to do it through a relation.