Построение красивой выборки.

[Willy53]

Здравствуйте. Eсть следующие таблицы:

• Задачи
• Работники
• Связки задач и работников (какие работники могут выполнять какую задачу)

Работников на задачу может быть как 1, так и больше. Одни и те же работники могут быть записаны на выполнение разных задач в разных комбинациях.

Например. У нас есть работники Иван, Пётр, Степан, Максим.
Есть задачи "Уборка территории", "Покраска стен", "Смазывание дверей", "Разгрузка макулатуры", "Подстригание газона"

Уборкой территории могут заниматься Иван и Пётр.
Покраской стен - Пётр и Степан.
Смазыванием дверей -Степан и Максим.
Разгрузкой макулатуры - Максим и Иван.
Подстриганием газона - Пётр.

Всё это записано вот в такие таблицы:
tasks

id
description
status

workers

id
name

tasks_workers_links

id
task_id
worker_id

Первая задача (Уборка территории), в которой задействованы Иван и Пётр, имеет статус working. Остальные задачи имеют статус wait.

Мне нужно отобрать одну задачу, которая готова идти в работу (status=wait), и ни один из её работников не задействован в задачах status=working.

Проблемы создаёт тот факт что один и то же рабочий может быть записан на разные задачи. В данном примере нужно получить только одну задачу:

Смазыванием дверей - Степан и Максим.

потому что Иван и Пётр, задействованные в первой таске (которая уже working) не могут учавствовать в других задачах из-за своей занятости.

Я хотел получить некий красивый JOIN на выходе, но придумать его к сожалению не смог.
Решил некрасиво следующими запросами.

Сперва берём задачи которые уже в работе

SELECT id FROM tasks WHERE status='working'

Затем берём id работников которые в этих задачах задействованы

SELECT worker_id FROM tasks_worker_links WHERE task_id IN(предыдущий запрос)

На основании этого, берём все id задач где есть работники из предыдущего запроса:

SELECT task_id FROM tasks_workers_links WHERE worker_id IN(предыдущий запрос)

Их мы должны игнорировать при выборке.

И наконец выбираем задачи которые могут пойти в работу:

SELECT * FROM tasks WHERE status='wait' AND id NOT IN(предыдущий запрос) LIMIT 1

Таким образом, я соединяю аж 4 запроса.

SELECT * FROM tasks WHERE status='wait' AND id NOT IN(
    SELECT task_id FROM tasks_workers_links WHERE worker_id IN(
        SELECT worker_id FROM tasks_worker_links WHERE task_id IN(
            SELECT id FROM tasks WHERE status='working'
        )
    )
) LIMIT 1

При тысячах записей это создаст серьёзные проблемы с производительностью - вложенные запросы извлекают слишком много данных.

В общем, буду благодарен за любые идеи как это всё превратить в красивый и быстрый единый SQL.

[Valick]

SELECT t.description
FROM tasks AS t
WHERE t.status = 'wait'
  AND NOT EXISTS (SELECT 1
                  FROM tasks_workers_links AS l
                  WHERE l.tasks_workers_links = t.id
                    AND EXISTS (SELECT 1
                                FROM tasks_workers_links AS l2
                                         JOIN tasks AS t2 ON l2.task_id = t2.id
                                WHERE l2.worker_id = l.worker_id
                                  AND t2.status = 'working'))
LIMIT 1;

Попробуйте

[evg_m]

1.

select tw.*, --задачи выполняемые 
ww.*, -- задействование в них люди 
tp.*, -- другие работы (планируемые) в которых они(люди) заняты
from tasks tw
inner join tasks_workers_links ww on ww.taskid =tw.id
leftinner join tasks tp on tp.id =ww.taskid and tp.status ='wait'
where tw.status ='working'

2.

select * 
from tasks 
left join ( -- задачи для исключения
            select distinct tp.id 
            .... --смотри первый запрос  
           ) ti on ti.id =tasks.id

where tasks.status ='wait' --планируемые задачи 
  and ti.taskid is null -- и не включенные в список запретов(с занятыми людьми)

[Willy53]

Спасибо!