支持选择有关系的数据

Question

我有两个表：users(id，name(和tasks(id，user_id，status，…(。

我想获得所有用户的列表，并提供他们有多少未完成任务的信息(计数((基于tasks.status值(。

我期待这样的结果：

[
{ id: 1, name: 'John Doe', unfinishedTasks: 2 },
{ id: 2, name: 'Bob Brown', unfinishedTasks: 0 },
...
]

我该怎么做？有可能吗？

谢谢你的回答！

Answer 1

给出以下表格

create table users (
id bigint generated by default as identity primary key,
name text
);
CREATE TYPE status_type AS ENUM ('open', 'wip', 'completed');
create table tasks (
id bigint generated by default as identity primary key,
user_id bigint references users,
status status_type
);

您可以创建一个视图：

CREATE VIEW task_stats AS 
SELECT      users.id, users.name, count(tasks.id) AS totalTaskCount, 
sum(case when tasks.status = 'open' then 1 else 0 end) AS unfinishedTasks, 
sum(case when tasks.status = 'wip' then 1 else 0 end) AS inProgressTasks,   
sum(case when tasks.status = 'completed' then 1 else 0 end) AS finishedTasks  
FROM        users     
INNER JOIN  tasks on tasks.user_id = users.id
GROUP BY    users.id;

然后您可以通过supabase js:查询该视图

const { data } = await supabase.from('task_stats').select('*')

这会给你想要的结果。