Supabase select data with relation

Question

I have two tables: users (id, name) and tasks (id, user_id, status, ...).

I would like to get list of all users with information (count) how many unfinished tasks they have (based on tasks.status value).

I expect result like that:

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

How can I do it? Is it possible?

Thank you for your answers!

score 0 · Answer 1 · answered Oct 10 '22 at 06:12

Give the following tables

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
);

You can create a view:

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;

And you can then query that view via supabase-js:

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

Which will give you the desired result.

Supabase select data with relation

1 Answers1