ConsoleBot/Scripts/Postgres/ToDoListDb_Select.sql - main - d.alekseev/SmartMenuBot

ConsoleBot/Scripts/Postgres/ToDoListDb_Select.sql
109 строк4 KB
d.alekseev
feat: SQL-скрипты PostgreSQL для модели ToDoList
15 фев 2026, 16:22
15 фев 2026, 16:224152bc2
-- SELECT scripts for IToDoRepository methods (PostgreSQL)
-- Note: double quotes are used to preserve identifier case.
-- ToDoItemState: 0 = Active, 1 = Completed

-- IToDoRepository.GetAsync(Guid id)
-- Params:
--   id (uuid)
SELECT t."Id"             AS "ToDoItemId",
       t."Name"           AS "ToDoItemName",
       t."CreatedAt"      AS "ToDoItemCreatedAt",
       t."Deadline"       AS "ToDoItemDeadline",
       t."State"          AS "ToDoItemState",
       t."StateChangedAt" AS "ToDoItemStateChangedAt",
       u."UserId"         AS "UserId",
       u."TelegramUserId",
       u."TelegramUserName",
       u."RegisteredAt",
       l."Id"             AS "ToDoListId",
       l."Name"           AS "ToDoListName",
       l."CreatedAt"      AS "ToDoListCreatedAt"
FROM "ToDoItem" t
         JOIN "ToDoUser" u ON u."UserId" = t."UserId"
         LEFT JOIN "ToDoList" l ON l."Id" = t."ListId"
WHERE t."Id" = 'cccccccc-cccc-cccc-cccc-cccccccccccc'::uuid;

-- IToDoRepository.GetActiveByUserIdAsync(Guid userId)
-- Params:
--   userId (uuid)
SELECT t."Id"             AS "ToDoItemId",
       t."Name"           AS "ToDoItemName",
       t."CreatedAt"      AS "ToDoItemCreatedAt",
       t."Deadline"       AS "ToDoItemDeadline",
       t."State"          AS "ToDoItemState",
       t."StateChangedAt" AS "ToDoItemStateChangedAt",
       u."UserId"         AS "UserId",
       u."TelegramUserId",
       u."TelegramUserName",
       u."RegisteredAt",
       l."Id"             AS "ToDoListId",
       l."Name"           AS "ToDoListName",
       l."CreatedAt"      AS "ToDoListCreatedAt"
FROM "ToDoItem" t
         JOIN "ToDoUser" u ON u."UserId" = t."UserId"
         LEFT JOIN "ToDoList" l ON l."Id" = t."ListId"
WHERE t."UserId" = '11111111-1111-1111-1111-111111111111'::uuid
  AND t."State" = 0
ORDER BY t."CreatedAt";

-- IToDoRepository.GetAllByUserIdAsync(Guid userId)
-- Params:
--   userId (uuid)
SELECT t."Id"             AS "ToDoItemId",
       t."Name"           AS "ToDoItemName",
       t."CreatedAt"      AS "ToDoItemCreatedAt",
       t."Deadline"       AS "ToDoItemDeadline",
       t."State"          AS "ToDoItemState",
       t."StateChangedAt" AS "ToDoItemStateChangedAt",
       u."UserId"         AS "UserId",
       u."TelegramUserId",
       u."TelegramUserName",
       u."RegisteredAt",
       l."Id"             AS "ToDoListId",
       l."Name"           AS "ToDoListName",
       l."CreatedAt"      AS "ToDoListCreatedAt"
FROM "ToDoItem" t
         JOIN "ToDoUser" u ON u."UserId" = t."UserId"
         LEFT JOIN "ToDoList" l ON l."Id" = t."ListId"
WHERE t."UserId" = '11111111-1111-1111-1111-111111111111'::uuid
ORDER BY t."CreatedAt";

-- IToDoRepository.CountActiveAsync(Guid userId)
-- Params:
--   userId (uuid)
SELECT COUNT(*)
FROM "ToDoItem" t
WHERE t."UserId" = '11111111-1111-1111-1111-111111111111'::uuid
  AND t."State" = 0;

-- IToDoRepository.ExistsByNameAsync(Guid userId, string name)
-- Params:
--   userId (uuid)
--   name (text)
SELECT EXISTS(SELECT 1
              FROM "ToDoItem" t
              WHERE t."UserId" = '11111111-1111-1111-1111-111111111111'::uuid
                AND t."Name" = 'Сформировать стратегию');

-- IToDoRepository.FindAsync(Guid userId, Func<ToDoItem, bool> predicate)
-- В SQL можно выбрать набор по пользователю (дальше predicate применяется в коде).
-- Params:
--   userId (uuid)
SELECT t."Id"             AS "ToDoItemId",
       t."Name"           AS "ToDoItemName",
       t."CreatedAt"      AS "ToDoItemCreatedAt",
       t."Deadline"       AS "ToDoItemDeadline",
       t."State"          AS "ToDoItemState",
       t."StateChangedAt" AS "ToDoItemStateChangedAt",
       u."UserId"         AS "UserId",
       u."TelegramUserId",
       u."TelegramUserName",
       u."RegisteredAt",
       l."Id"             AS "ToDoListId",
       l."Name"           AS "ToDoListName",
       l."CreatedAt"      AS "ToDoListCreatedAt"
FROM "ToDoItem" t
         JOIN "ToDoUser" u ON u."UserId" = t."UserId"
         LEFT JOIN "ToDoList" l ON l."Id" = t."ListId"
WHERE t."UserId" = '11111111-1111-1111-1111-111111111111'::uuid
ORDER BY t."CreatedAt";