one to many apply where Condition in last record

I've Tables like , 1.Loads -> id , name , created_at 2.Deliveries -> id , load_id , date Each load can have multiple deliveries, Now i need to get the results of all loads delivery ends between from , to. This where between should apply on last deliver of a load.

public function scopeDateFilter($query, $from, $to, $feild)
    {
            $from = $from->startOfDay();
            $to = $to->endOfDay();
            $query->whereBetween($feild, [$from, $to]);
    }

The above one is applying for all deliveries , instead of this i need to apply this where condition only on last delivery.

Snapey

4 years ago

Level 122

If you are on L8 or L9 you can use the HasOneOfMany relationship to get the last delivery on each load and then filter from there?

https://laravel.com/docs/8.x/eloquent-relationships#has-one-of-many

theone

4 years ago

Level 3

@Snapey unfortunately i'm in 6.8, But i written sql for this like ,

SELECT * FROM (SELECT * FROM deliveries GROUP BY load_id ORDER BY id DESC) as dat WHERE date BETWEEN '2022-02-01' AND '2022-03-01'

But not sure how to combine into my function

Please or to participate in this conversation.