supabase

team테이블의 정보를 받아올떄
team.id가 주어진다면
해당되는 team테이블의 row정보에다가
user.team_id 테이블이 배열인데 해당 배열에서 team.id가 일치하는 row중
해당 row들에서 type 칼럼의 데이터 string에 따라 count한 값을 처음 team table에 추가해줌

create or replace function get_team_with_user_type_count(team_id2 uuid)
returns table (
  team_id uuid,
  team_name text,
  team_image text,
  team_approval text[],
  player_count integer,
  coach_count integer
)
language sql
as $$ 
  SELECT 
    team.id as team_id,
    team.name as team_name,
    team.image as team_image,
    team.approval as team_approval,
    COUNT(CASE WHEN u.type = 'player' THEN 1 ELSE NULL END) as player_count,
    COUNT(CASE WHEN u.type = 'coach' THEN 1 ELSE NULL END) as coach_count
  FROM team
  JOIN "user" AS u ON team.id = ANY(u.team_id)
  WHERE team.id = team_id2
  GROUP BY team.id, team.name; -- team 테이블의 나머지 컬럼들 추가
$$;

 

이건 갯수가아니라 배열로

create or replace function get_team_with_user_type_array(_team_id uuid)
returns table (
  id uuid,
  name text,
  image text,
  approval text[],
  player_users text[],
  director_users text[],
  coach_users text[],
  parent_users text[]
)
language sql
as $$ 
  SELECT 
    team.id,
    team.name ,
    team.image ,
    team.approval ,
    array_remove(array_agg(CASE WHEN u.type = 'player' THEN u.name END), NULL) as player_users,
    array_remove(array_agg(CASE WHEN u.type = 'director' THEN u.name END), NULL) as director_users,
    array_remove(array_agg(CASE WHEN u.type = 'coach' THEN u.name END), NULL) as coach_users,
    array_remove(array_agg(CASE WHEN u.type = 'parent' THEN u.name END), NULL) as parent_users
  FROM team
  JOIN "user" AS u ON team.id = ANY(u.team_id)
  WHERE team.id = _team_id
  GROUP BY team.id, team.name
  ORDER BY team.created_at;
$$;