John Davidson

Message:

I have a customer model with many transactions, and I need to get customers only where the customer's latest transaction is < 5 years from now. So the result was a customer who had any transaction that matched the condition. Even the transaction that matched the condition is not the latest one.

public function lastTransaction()

{

    return $this->hasOne(Transaction::class, 'user_id', 'id')->latest();

}



$customers = Customer::whereHas('lastTransaction', function ($q) {

    $q->whereDate('created_at', '<', Carbon::now()->subYears(5));

})->get();

---

My Answer:
You can achieve this by using Laravel Eloquent's `whereHas` method along with a subquery to filter the parent records based on the latest child record's `created_at` date.

Here's an example code snippet to demonstrate this:

php
$parents = Parent::whereHas('children', function ($query) {
$query->where('created_at', function ($subquery) {
$subquery->selectRaw('MAX(created_at)')
->from('children');
});
})->get();


In this code snippet:
- We are querying the `Parent` model using the `whereHas` method to filter the parent records based on the existence of related children records.
- Within the `whereHas` callback function, we are using a subquery to select the maximum `created_at` date from the `children` table.
- This subquery ensures that only the parent records with the latest child record's `created_at` date are returned.

You can adjust the code according to your specific database schema and relationships between the parent and child models.