Laravel where has on last record

Question

Here is the current query I'm trying to execute

Statement::whereHas('history',  function ( $query) use ($end) {
                $query->where('created_at', '<', Carbon::parse($end))->latest()->limit(1)->where('read_flag', '=', 0);
            });
        })->get()

I'm trying to get some statements where the latest history value has a read_flag = false.

• If the latest value has a read_flag = false the model should be returned.
• If the a statements latest history value has a read_flag = true a model shouldn't be returned even if there are older histories with a read_flag = false.

At the moment the query I'm doing still returns older values with a read_flag = false when I only want to query the latest record only. Any ways of achieving this?

The $end value I'm using is 2020-02-09 23:59:59

When using the query I have to records setup.

This record is the newer record with read_flag = true

"id":77,
"statement_id":4,
"statement_version_id":4,
"read_count":1,
"unread_count":0,
"read_flag":1,
"created_at":"2020-02-09 12:16:23",
"updated_at":"2020-02-10 12:16:23"
}

This record is the older record with read_flag = false

"id":65,
"statement_id":4,
"statement_version_id":4,
"read_count":0,
"unread_count":2,
"read_flag":0,
"created_at":"2020-02-07 13:25:06",
"updated_at":"2020-02-07 13:25:06"
}

So when running the query I'd expect no statements to be returned because the latest record has read_flag = true

However this is the output I get

{
"id":4,
"site_id":9786,
"team_id":9421,
"name":"ds",
"company_statement_id":1,
"current_statement_version_id":4,
"system_company_statement_id":null,
"created_at":"2020-02-03 10:17:13",
"updated_at":"2020-02-03 10:17:13",
}

Answer 1

AFAIK there is no built-in eloquent function that gives you this kind of behaviour.

However you can filter your collection after retrieval and reject the elements that doesn't match your criteria, like this (code is not tested):

$statements = Statement::whereHas('history', function ($query) use ($end) {
    $query->where([
        ['created_at', '<', Carbon::parse($end)],
        ['red_flag', 0]
    ]);
})
->get()
->reject(function($element) use($end) {
    $latestHistory = $element->history()->latest()->first();
    return (Caron::parse($latestHistory->created_at)->gte(Carbon::parse($end)) || $latestHistory->red_flag !== 0);
});

EDIT: Fixed some code typos.