Let’s start with an example.

I need to retrieve a subscription, that has 3 failed transactions in a row.

To simplify a bit, I’m personally fine even with

I need to retrieve a subscription, that has last three transactions with status = “error”

I’ve tried multiple approaches

1. whereHas()

Subscription::whereHas('transactions', function (Builder $query) {
    $query->where('status')->latest()->limit(3); 
}, '>=', 3);

2. dynamic relationship lastThreeTransactions and then whereHas -> same result as 1

3. SubSelect to get “lastThreeTransacitons” facing the same issue, that I have with 1

Overall, feels like I’m missing some SQL trick like CASE, that will get only last three and will count based on status or some strategically placed group by.

Will see if I think the same way after waking up.

p.s.

Pls, no ugly joins and DB::table(). Try to embrace the simplicity of Laravel.