I’m build a app with laravel 8. This app has table order and table tracking. The order and tracking has a one to many relation, the models are like below:

class Order extends Model
{
    protected $with = ['tracking'];

    public function tracking() 
    {
        return $this->hasMany(Tracking::class);
    }
}

class Tracking extends Model
{
    public function order() 
    {
        return $this->belongsTo(Order::class);
    }
}

Now I want query the orders filtering them by status in the last tracking inserted. For exemple, with the data below:

Order 
ID         VALUE
1          100.00
2          200.00
3          300.00

Tracking
ID     ORDER_ID  STATUS     CREATED_AT
1      1         Accept     2022-03-01 00:00:00
2      1         Paid       2022-03-02 00:00:00
3      2         Accept     2022-03-01 00:00:00
4      2         Paid       2022-03-02 00:00:00
5      2         Cancel     2022-03-03 00:00:00
6      3         Accept     2022-03-01 00:00:00
7      3         Paid       2022-03-02 00:00:00

If the param in where clause is status = Paid, I want return order with id 1 and 3, with all tracking related.

I try to use whereHas like this:

$query = Order::query();

$query->whereHas('tracks', function ($query) use ($request) {
    return $query->where('status', '=', 'Paid');
});

but this make a query in all tracking, and I need query only if the last status is like Paid.

And I need data like this:

Order ID   VALUE     TRACKING
1          100.00    [[ID: 1, STATUS: Accept], [ID: 2, STATUS: Paid]]
3          300.00    [[ID: 6, STATUS: Accept], [ID: 7, STATUS: Paid]]

Notice, that order with id 2 has Paid in 2022-03-02 but in 2022-03-03 00:00:00 its canceled, so this wont appear.

Any help is welcome.

Thanks.

CodePudding user response：

I cannot reproduce the situation right now. So, suggesting the following solution without any performance testing. I hope it will solve your issue.

DB::table("tracking")
->join("order", function($join){$join;})
->select("tracking.order_id", "order.val", "max (tracking.created_at) as status_updated")
->where("order.id", "=", tracking.order_id)
->groupBy("tracking")
->get();

CodePudding user response：

Edit: My previous answer was totally wrong, so here's another attempt:

An easier way to do this probably would be to define an extended relationship in the Order model:

// App\Models\Order
public function active_tracking() {
    return $this->tracking()->where('status', 'Paid');
}

Then you can just fetch your orders with with active Tracking records:

Order::with('active_tracking')->get();

CodePudding user response：

Simply you can do like so,

$query = Order::whereHas('tracks', function ($q) use ($request) {
    return $q->where('status', '=', 'Paid');
})
    ->orderBy('id', 'desc')
    ->get();

this query will return the latest tracks as you want, you may want also to use the take method to take a specific number of tracks like so,

$query->take(5);

you can also skip some recorders via using the skip method then get the successor recorders like so,

$query->skip(2)->take(5);

Notice: the skip and take are equivalent to offset and limit

CodePudding user response：

The wanted result is a bit complex to achieve, so we need to pay attention to how we'll achieve it.

To do so, let's decompose the fetching process into parts :

1. we want to select the following columns :
   • order_id and value from orders table.
   • tracking_id, status and created_at from the related tracking records. Those related records will be fetched using an INNER JOIN clause on tracking.order_id = orders.id.
2. Say we need to filter by status = 'paid' so in the query we'll likely to have something like where tracking.status = 'paid'.
3. The wanted result can be described as follows: GET ME orders WHERE LATEST TRACKING STATUS IS 'paid' which means if the latest tracking.status for a specific order is not 'paid' then we don't want that order, nor its trackings, at all.
4. To achieve the above statement, we need to know the latest tracking record related to order and if that related record has a status equals to paid then fetch all the related records for that order.

Here's an SQL query that fetches the wanted results when we need tracking.status = 'paid' :

-- selects the needed columns
SELECT `orders`.`id` AS `order_id`, `value`, `t`.`id` AS `tracking_id`, `t`.`status`
FROM `orders`
-- fetch the related "tracking" records for each order in the set. The table "tracking" is aliased as "t" for simplicity
INNER JOIN `tracking` `t` ON `t`.`order_id` = `orders`.`id`
-- we need to get only the related records ONLY when the latest "tracking" saved for the current "order" is the status we want (in our case "paid")
WHERE (
    -- this subquery gets the status of the latest added "tracking" of each "order"
    -- check below sub queries
    SELECT `status`
    FROM `tracking`
    WHERE `order_id` = `orders`.`id` AND `id` = (
        -- this subquery gets the "id" of the latest added "tracking"
        -- check below sub query
        SELECT `id`
        FROM `tracking`
        WHERE `order_id` = `orders`.`id` AND `created_at` = (
            -- this subquery filters the records and returns the "created_at" values of the latest added "tracking"
            SELECT `created_at`
            FROM `tracking`
            WHERE `order_id` = `orders`.`id`
            -- instead of using "max" function we can simply get the first record in the reverse order (DESC)
            ORDER BY `created_at` DESC LIMIT 1
        )
    )
) = 'paid'

Let me try to translate the above query into Laravel's query builder:

use Illuminate\Support\Facades\DB

/** inform the query builder that the "orders" table is main table in the query */
DB::table('orders')
    /** instruct to select those columns */
    ->select('orders.id as order_id', 'orders.value', 't.status', 't.created_at') 
    /** have an INNER JOIN clause to get the related "tracking" records */
    ->join('tracking as t', 't.order_id', '=', 'orders.id')
    ->where('t.status', function ($q) {
        /** creates a subquery to get the status of the last inserted related "tracking" */
        $q->select('status')
            ->from('tracking')
            ->whereColumn('order_id', 'orders.id')
            ->where('created_at', function ($q) {
                /** a subquery to get the latest inserted tracking record (only the last will be returned) */
                $q->select('created_at')
                    ->from('tracking')
                    ->whereColumn('order_id', 'orders.id')
                    ->orderByDesc('created_at')
                    ->limit(1);
            });
    })->get(); /** execute the query */

The above query was not tested so it's better that you take some time to test it and tweak it the way you want.

Based on the data sample provided in the question, the above SQL query should return :

order_id	value	tracking_id	status
1	100.00	1	Accept
1	100.00	2	Paid
3	300.00	6	Accept
3	300.00	7	Paid

Hope i have pushed you further.