
-- full join task + link
SELECT task.name, task.description, task.task_type, task.record_id, link.parent_id, link.child_id, link.json_data FROM task task
FULL JOIN task_link link ON link.parent_id = task.record_id
WHERE task.task_type = 'task' OR link.child_id IS NOT NULL
ORDER BY link.child_id, task.name

-- task list
SELECT name, record_id FROM task task
WHERE type = 'task list' AND state = 'active'
ORDER BY name

-- task_link to normal task
SELECT link.child_id, link.parent_id, link.json_data, task.record_id, task.name FROM task_link link
LEFT OUTER JOIN task task ON link.child_id = task.record_id
WHERE link.parent_id IS NOT NULL
AND task.task_state = 'active' AND task.task_type = 'task'
ORDER BY task.name

-- many task_links to selected task id's (= task list element id's)
SELECT task.name, task.description, task.task_type, task.record_id, link.child_id, link.parent_id, link.json_data FROM task task
LEFT OUTER JOIN task_link link ON link.child_id = task.record_id
WHERE task.task_state = 'active'
AND (task.task_type = 'task' OR task.task_type = 'task list')
AND link.parent_id IN ('78b1d6a0-a4e9-4339-c3a2-d0509942557e', '3fee724d-c5d8-41cd-c108-d0509942557e')
ORDER BY task.name

-- main task list lower level tasks
SELECT task.name, task.description, task.task_type, task.record_id, link.child_id, link.parent_id, link.json_data, link.level_order FROM task_link link
LEFT OUTER JOIN task task ON link.child_id = task.record_id
WHERE task.task_state = 'active'
AND (task.task_type = 'task' OR task.task_type = 'task list')
AND link.parent_id = '78b1d6a0-a4e9-4339-c3a2-d0509942557e' -- aga tak list id
ORDER BY task.name
