Make eloquent query filtered by a result of this query

I try to get events from my database

$eventWithDatas = Event::with(['persons' => function($query) {
            $query->select('persons.id', 'name', 'title', 'status')
                    ->where('date_of_death', null);
        }, 'persons.health_items' => function($query) {
            $query->select('id', 'name', 'date_in', 'date_end', 'person_id');
        }])->get();

I would like to filter my relationship persons.health_items.

The ideal would be to have something like that :

$eventWithDatas = Event::with(['persons' => function($query) {
            $query->select('persons.id', 'name', 'title', 'status')
                    ->where('date_of_death', null);
        }, 'persons.health_items' => function($query) use ($eventWithDatas) {
            $query->select('id', 'name', 'date_in', 'date_end', 'person_id')
                    ->where('date_end', $eventWithDatas->end);
        }])->get();

$eventWithDatas should be the first part of my query (Event::get)

Example with result:

[
{
  / event collection (contain just 1 item for example)
  "title": "vaccines",
  "start": "2020-07-24 01:00:00",
  "end": "2020-07-24 01:00:00", // <----------- I WANT TO USE THIS DATA FOR FILTER persons.health_items.date_end (see bottom)
  "persons": [
     "id": 1,
     "name": "Jeremy",
     ...
     "health_items": [
        {
          "id": 14,
          "name": "my health item",
          "date_end": // <-------------------- DATA TO FILTER (see top) (2020-07-24 01:00:00)
        }
      ]
   ]
}
]

Do you know how I can do this?

Answer

The key to your constrained eager loading approach is to join to the events table, so we can compare the date_end and end fields. Assuming you have many-to-many persons to events, you can do this as follows:

Event::with(['persons.health_items' => function($query){
    $query->select('health_items.*')
          ->join('persons', 'persons.id', '=', 'health_items.person_id')
          ->join('event_person', 'event_person.person_id', '=', 'persons.id')
          ->join('events', 'events.id', '=', 'event_person.event_id')
          ->whereRaw('`health_items`.`date_end` = `events`.`end`');
}])->get();