60 lines
1.9 KiB
SQL
60 lines
1.9 KiB
SQL
-- name: ListTasksByTablo :many
|
|
SELECT id, tablo_id, title, description, status, position, created_at, updated_at, etape_id
|
|
FROM tasks
|
|
WHERE tablo_id = $1
|
|
ORDER BY status, position, created_at;
|
|
|
|
-- name: InsertTask :one
|
|
INSERT INTO tasks (tablo_id, title, description, status, position, etape_id)
|
|
VALUES ($1, $2, $3, $4, $5, $6)
|
|
RETURNING id, tablo_id, title, description, status, position, created_at, updated_at, etape_id;
|
|
|
|
-- name: GetTaskByID :one
|
|
SELECT id, tablo_id, title, description, status, position, created_at, updated_at, etape_id
|
|
FROM tasks
|
|
WHERE id = $1 AND tablo_id = $2;
|
|
|
|
-- name: UpdateTask :one
|
|
UPDATE tasks
|
|
SET title = $2, description = $3, status = $4, position = $5, etape_id = $6, updated_at = now()
|
|
WHERE id = $1
|
|
RETURNING id, tablo_id, title, description, status, position, created_at, updated_at, etape_id;
|
|
|
|
-- name: DeleteTask :exec
|
|
DELETE FROM tasks WHERE id = $1 AND tablo_id = $2;
|
|
|
|
-- name: MaxPositionByTabloAndStatus :one
|
|
SELECT COALESCE(MAX(position), 0)::integer AS max_position
|
|
FROM tasks
|
|
WHERE tablo_id = $1 AND status = $2;
|
|
|
|
-- name: ListTasksByTabloAndEtape :many
|
|
SELECT id, tablo_id, title, description, status, position, created_at, updated_at, etape_id
|
|
FROM tasks
|
|
WHERE tablo_id = $1 AND etape_id = $2
|
|
ORDER BY status, position, created_at;
|
|
|
|
-- name: ListUnassignedTasksByTablo :many
|
|
SELECT id, tablo_id, title, description, status, position, created_at, updated_at, etape_id
|
|
FROM tasks
|
|
WHERE tablo_id = $1 AND etape_id IS NULL
|
|
ORDER BY status, position, created_at;
|
|
|
|
-- name: ListOpenTasksByUser :many
|
|
SELECT
|
|
tasks.id,
|
|
tasks.tablo_id,
|
|
tasks.title,
|
|
tasks.description,
|
|
tasks.status,
|
|
tasks.position,
|
|
tasks.created_at,
|
|
tasks.updated_at,
|
|
tasks.etape_id,
|
|
tablos.title AS tablo_title
|
|
FROM tasks
|
|
JOIN tablos ON tablos.id = tasks.tablo_id
|
|
WHERE tablos.user_id = $1
|
|
AND tasks.status <> 'done'
|
|
ORDER BY tasks.created_at DESC
|
|
LIMIT 30;
|