Eloquent compare only to most recent entry in whereRelation

Level 63

Why don't you just add a where clause on the date of the shipment lines ?

Level 1

@vincent15000 It is the date on the shipment_line_exfty_dates that I only want to get the most recent. A filter on the shipment_line would only filter when shipment lines are made. Basically I want all the shipment_line records which has the most recent shipment_line_exfty_date within the week. The shipment_line_exfty_dates is essentially a history of all the shipment dates that a line has been given, but the most recent is the most up to date one.

1 like

Level 51

// you have to make sure this format
// for your column (its the only reason I didnt inline it)
[$from, $to] = [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()] ;

$shipments = ShipmentLine::whereHas('shipment_line_exfty_dates', fn ($q) =>
    $q->whereBetween('date', [$from, $to]) 
)->get();

1 like

Level 51

whereRelation is for when you want

to query for a relationship's existence with a single, simple where condition

In your case you need the closure. So use whereHas

Level 1

@webrobert Thanks, I may be missing something, but I don't see where that looks at only the latest shipment_line_exfty_dates for that record?

Level 51

@thedocrow0124

ShipmentLine -> shipment_line_exfty_dates ->

this query is on the relation 'shipment_line_exfty_dates'

$q->whereBetween('date', [$from, $to])

you can add to it if needed.

$q->whereBetween('date', [$from, $to])
  ->where('another', 'thing')

Level 1

@webrobert Thanks very much, that makes sense now. Not sure if this is because of a separate issue or not, but it doesn't seem to return anything, even though there are 2 dates within this week that should show?

select * from shipment_line_exfty_dates;

id, shipment_line_id, date, created_at, updated_at
'48', '50', '2023-01-30', '2022-10-13 10:55:44', '2022-10-13 10:55:44'
'49', '51', '2023-01-30', '2022-10-13 10:57:40', '2022-10-13 10:57:40'

The model looks correct as it should format it as a date?

    protected $dates = ['date'];

    protected $fillable = [
        'shipment_line_id',
        'date',
    ];
    public function shipment_lines()
    {
        return $this->belongsTo(ShipmentLine::class);
    }

Level 51

@thedocrow0124

[$from, $to] = [
Carbon::now()->startOfWeek()->toDateString(), 
Carbon::now()->endOfWeek()->toDateString()
] ;

Level 1

@webrobert Thanks. I understand how that should work, but it still returns nothing. I am guessing this is because it won't compare between when they are strings? Do I need to convert my column to timestamp instead of date?

Level 51

@thedocrow0124, good question, It should work as a 'date' column.

Do you have a cast for it on the model?

   protected $casts = [
        'date' => 'date',
    ];

Level 1

@webrobert Thanks, that works! Regarding only looking at the latest, using 'latest()' does not seem to look at the latest but matches to any of them?

$shipments = ShipmentLine::
        with('shipment_line_exfty_dates', 'shipments', 'customer_order_lines.colourways.style_versions.styles.designs')
        ->whereHas('shipment_line_exfty_dates', fn ($q) =>
            $q->latest()->whereBetween('date', [$from, $to])
        )->get();

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

Level 51

@thedocrow0124, my bad, you just want ONE?

ahh, I think you want latestOfMany()

You'll add another relationship for shipment_line_exfty_dates something like latestExftyDate()

depending on the relationship

https://laravel.com/docs/9.x/eloquent-relationships#one-of-many-polymorphic-relations