How to limit hasMany relation with 1 last row?

Question

In laravel 11 / mysql 8 app

Currency Model has method :

public function latestCurrencyHistory(): HasMany
{
    return $this->hasMany(CurrencyHistory::class)->latest();
}

and with request :

$this->currencies = Currency
    ::getByActive(CurrencyActiveEnum::ACTIVE)
    ->with('latestCurrencyHistory')
    ->orderBy('ordering', 'asc')
    ->get();

I see sql-tracement :

SELECT *
FROM `currencies`
WHERE `active` = '1'
ORDER BY `ordering` asc

SELECT *
    FROM (  SELECT *, row_number() over (partition by `currency_histories`.`currency_id`
    ORDER BY `created_at` desc)     AS `laravel_row`
    FROM `currency_histories`
    WHERE `currency_histories`.`currency_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31))     AS `laravel_table`
    WHERE `laravel_row` <= 1
    ORDER BY `laravel_row`

I try to read only the last row by adding ->limit :

public function latestCurrencyHistory(): HasMany
{
    return $this->hasMany(CurrencyHistory::class)->latest()->limit(1);
}

But generated sql is the same . Looks like ->limit does not work here.

If there is a way to get only 1 last row? Can I add 1 additive filter on CurrencyHistory.created_at >= last 24 hours ?

Answer 1

Instead of limiting in the relationship method, you can modify the query to fetch the latest currency history directly while avoiding the overhead of eager loading all the rows:

public function latestCurrencyHistory(): HasOne
{
    return $this->hasOne(CurrencyHistory::class)->latestOfMany();
}

The latestOfMany() method is a specialized way in Laravel to get the latest record of a HasMany relationship. It automatically orders the related records by a timestamp (e.g., created_at) and selects only the latest one.

Then

$this->currencies = Currency::getByActive(CurrencyActiveEnum::ACTIVE)
    ->with('latestCurrencyHistory')
    ->orderBy('ordering', 'asc')
    ->get();