John Davidson

Message:

I have two database tables "inventory" and "inventory_sale_records". Products can have many sale records. I need to get all products from the database only by the latest created_at sale record, I don't need any other sell record that has been made before the last sale.

Here are my models:

Inventory model

public function sellRecord(){

        return $this->hasMany('App\Models\Inventory\SellRecord', 'product_id');

    }

SellRecords model:

public function inventory()

    {

        return $this->belongsTo('App\Models\Inventory\Inventory', 'id');

    }

How can I get all products from the inventory table sorted only by the latest sale record date in inventory_sell_records from the database?

I am also adding screenshots of both table's structure.

This is "inventory" table

This is "inventory_sell_records" table:

Thank you!

---

My Answer:
You can achieve this by using Laravel's Eloquent ORM and querying the database with a subquery to get the latest sale record for each product. Here's an example of how you can do this:

php
use App\Models\Inventory;
use App\Models\SellRecord;

$products = Inventory::select('inventory.*')
->join('inventory_sell_records', function ($join) {
$join->on('inventory.id', '=', 'inventory_sell_records.product_id')
->whereRaw('inventory_sell_records.created_at = (select max(created_at) from inventory_sell_records where product_id = inventory.id)');
})
->get();



In this code snippet, we are selecting all columns from the `inventory` table and joining it with the `inventory_sell_records` table using a subquery to get the latest sale record for each product. The `whereRaw` clause is used to filter the sale records based on the latest `created_at` date.

You can then use the `$products` variable to access the products with their latest sale records.