如何在Supabase中获取所有用户并显示是否已关注？

首先先修正你提供的follows表创建语句的语法错误（PostgreSQL表定义需用圆括号而非大括号）：

CREATE TABLE follows (
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  following_user_id UUID,
  followed_user_id UUID,
  CONSTRAINT pk_follow PRIMARY KEY (following_user_id, followed_user_id),
  FOREIGN KEY (following_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (followed_user_id) REFERENCES users(id) ON DELETE CASCADE
);

针对你需要在获取用户时附带当前登录用户关注状态的需求，以下是几种在Supabase中的实现方案：

方案1：封装为RPC存储过程（推荐） #

将查询逻辑封装为数据库端的存储过程，既安全又便于维护，还能结合Supabase的行级安全（RLS）控制权限。

针对「获取关注我的用户+我是否关注他们」的场景 #

创建存储过程：

CREATE OR REPLACE FUNCTION get_followers_with_follow_status(user_id UUID)
RETURNS TABLE(userid UUID, username TEXT, profile_img TEXT, isfollowing BOOLEAN)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT 
      f.followed_user_id AS userid,
      u.username,
      u.profile_img,
      EXISTS (
          SELECT 1
          FROM follows
          WHERE followed_user_id = user_id AND following_user_id = f.followed_user_id
      ) AS isfollowing
  FROM follows f
  JOIN users u ON f.followed_user_id = u.id
  WHERE f.following_user_id = user_id;
END;
$$;

在Supabase JS客户端调用：

// 获取当前登录用户ID
const { data: { user } } = await supabase.auth.getUser();
const currentUserId = user.id;

// 调用RPC函数
const { data, error } = await supabase.rpc('get_followers_with_follow_status', { user_id: currentUserId });

针对「获取所有用户+我是否关注他们」的场景 #

如果需要获取全量用户并附带关注状态，可创建另一个存储过程：

CREATE OR REPLACE FUNCTION get_all_users_with_follow_status(user_id UUID)
RETURNS TABLE(userid UUID, username TEXT, profile_img TEXT, isfollowing BOOLEAN)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
      u.id AS userid,
      u.username,
      u.profile_img,
      EXISTS (
          SELECT 1
          FROM follows
          WHERE followed_user_id = user_id AND following_user_id = u.id
      ) AS isfollowing
  FROM users u;
END;
$$;

客户端调用方式同上，只需替换RPC函数名即可。

方案2：客户端直接执行参数化查询 #

如果不想创建存储过程，也可以直接在Supabase客户端中编写参数化查询，避免SQL注入风险：

const { data: { user } } = await supabase.auth.getUser();
const currentUserId = user.id;

const { data, error } = await supabase
  .from('users')
  .select(`
    id,
    username,
    profile_img,
    (
      SELECT EXISTS (
        SELECT 1 FROM follows 
        WHERE followed_user_id = :user_id AND following_user_id = users.id
      )
    ) AS isfollowing
  `)
  .params({ user_id: currentUserId });

补充：配置行级安全（RLS） #

为保证数据安全，建议给follows表开启RLS，限制用户只能访问自己的关注关系：

ALTER TABLE follows ENABLE ROW LEVEL SECURITY;

-- 用户只能查看自己的关注/被关注记录
CREATE POLICY "View own follow relationships" ON follows
FOR SELECT USING (auth.uid() = followed_user_id OR auth.uid() = following_user_id);

-- 用户只能创建自己的关注记录
CREATE POLICY "Create own follow records" ON follows
FOR INSERT WITH CHECK (auth.uid() = followed_user_id);

-- 用户只能删除自己的关注记录
CREATE POLICY "Delete own follow records" ON follows
FOR DELETE USING (auth.uid() = followed_user_id);

内容的提问来源于stack exchange，提问作者Gaeun Yoo