Как я могу создать такой запрос?

Question

[alexfyodrv]

У меня задача: выбрать неактивных пользователей которые имеют работу(works) которая была закрыта(works_closed), но чтобы новая работа не была открыта с того момента как он закрыл последнею так как тогда пользователь уже считается активен. Активная работа считается той у которой есть роль(roles) и start_date начинается до текущей даты.

У меня такой код в Laravel, но он выбирает всех у кого есть закрыта работа, не учитывая что новая может быть открыта. Если вы не знаете как сделать это в ларавел буду благодарен даже за чистый SQL :)

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

юзер имеет works, которая имеет roles и не имеет записи в works_closed - значит юзер активен
юзер имеет works, которая имеет roles и имеет запись в works_closed - значит она неактивен

Таблица users

- id
- name

Таблица works

- id
- user_id
- name

Таблица works_closed

- id
- work_id
- closed_at

Таблица roles

- id
- name
- start_date
- end_date

Comments

[Николай]

Не проще ли в works добавить столбец closed?

[alexfyodrv]

нет, я сильно упростил таблицы. в works_closed есть много данных и естественно я не буду добавлять и дублировать их в works.

Answer 1

[NubasLol]

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();