How can I create such a request?

Question

Python

I have a task: to select inactive users who have a work (works) that has been closed (works_closed), but so that a new work is not opened from the moment he closed the last one, since then the user is already considered active. An active job is considered one that has roles and start_date starts before the current date.
I have this code in Laravel, but it selects everyone who has a closed job, not taking into account that a new one may be open. If you don't know how to do it in Laravel I'll be grateful even for pure SQL :)

User::whereHas('works', function ($query) {
        $query->whereHas('works_closed', function ($query) {
            $query->where('closed_at', '<=', now());
        });
    })
    ->get();

user has works, which has roles and has no entry in works_closed - so the user is active
user has works, which has roles and has an entry in works_closed - so it is inactive
Users table

- id
- name

works table

- id
- user_id
- name

table works_closed

- id
- work_id
- closed_at

roles table

- id
- name
- start_date
- end_date

Answer 1

User::query()
            ->whereHas('works', function ($query) {
                $query->whereHas('works_closed', function ($query) {
                    $query->where('closed_at', '<=', now());
                });
            })
            ->whereDoesntHave('works', function ($query) {
               
                    $query->where('roles', '!=', null);
                    $query->where('start_date', '>',  now());
            
            })
            ->get();