CODEWITHSUNDEEP
phplaraveldatabaseeloquentrelation
user618383
user618383

Reputation: 11

Laravel with custom relation

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

orders- table with order data

orders_products - table with data on products in orders

remainings- table with product stock data by date and shop

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.

Upvotes: 1

Views: 68

Answers (1)

Martin Bean
Martin Bean

Reputation: 39389

I think your data could be modelled a bit better.

For example, you shouldn’t be directly linking orders and products, as what happens if you delete a product at a later date? Or change its name? Or its price? You’re going to mess up existing order records if you’re just defining a pivot between orders and products and then referencing attributes on products. A customer is going to be a little pissed if they order Product A for $10, you then drop the price to $7.50 at a later date, they check their orders and see that they’ve been charged $10 but their order says the total is only $7.50.

Your issue with trying to get “remainings” is that its dependent on the shop ID contained in the order, so no, you’re not going to be able to query this with built-in Eloquent relations because you need to fetch the Order model (to get its shop_id value) before you can then query your remainings table for stock left for that shop and product combination. So you’re going to need to do a join to get this data. However, I’d pick a better name than “remainings”, such as inventories or something.

Schema::create('inventories', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained();
    $table->foreignId('shop_id')->constrained();
    $table->unsignedInteger('in_stock');
});

I also hope you’re incrementing and decrementing stock within a transaction to avoid stock going “missing” or materialising in case of exceptions.

Upvotes: 1

Related Questions