Supabase JS查询过滤问题：逾期用户筛选逻辑异常求助

我正在编写Supabase JS查询，数据库包含users表与contracts表，每个user拥有多份contract，contract分为活跃或逾期状态。需求是查询时仅获取每个user的最新contract，并依据该最新contract的状态筛选user。

目前代码在其他状态筛选时正常，但当筛选状态为「vencido（逾期）」时，若user存在活跃contract，仍会被返回，不符合预期（预期仅返回最新contract为逾期的user）。

以下是我的代码：

export async function obtenerUsuariosPaginados(
  params: PaginationParams
): Promise<PaginatedResponse<Usuario & { contracts?: Contrato[] }>> {
  const { page, pageSize, search, estado, tipo } = params;
  const start = (page - 1) * pageSize;

  // Base query
  let query = supabase()
    .from("users")
    .select("*, contracts(*)", { count: "exact" })
    .order("apellido", { ascending: true })
    .not("contracts", "is", null) // Only include users with contracts
    .order("fecha_final", {
      ascending: false,
      referencedTable: "contracts",
    }) // Get latest contract first
    .limit(1, { referencedTable: "contracts" }); // Fetch only the latest contract per user

  // Apply search filter
  if (search) {
    const searchLower = search?.toLowerCase();
    query = query.or(
      `apellido.ilike.%${searchLower}%,nombre.ilike.%${searchLower}%,legajo.ilike.%${searchLower}%,cuil.ilike.%${searchLower}%`
    );
  }

  // Apply contract status filter
  const now = new Date().toISOString();
  if (estado) {
    if (estado === "activo") {
      query = query
        .lte("contracts.fecha_inicio", now)
        .gte("contracts.fecha_final", now);
    } else if (estado === "renovar") {
      const twoMonthsBeforeEnd = addMonths(new Date(), 2).toISOString();
      query = query
        .lte("contracts.fecha_inicio", now)
        .lte("contracts.fecha_final", twoMonthsBeforeEnd)
        .gte("contracts.fecha_final", now);
    } else if (estado === "vencido") {
      query = query
        .lt("contracts.fecha_final", now) // Contract is expired
        .not("contracts.fecha_final", "gte", now); // Ensure no newer active contract exists
    }
  }

  // Apply contract type filter
  if (tipo) {
    query = query.eq("contracts.tipo", tipo?.toLowerCase());
  }

  // Apply pagination
  const { data, error, count } = await query.range(start, start + pageSize - 1);

  if (error) throw error;

  const totalPages = Math.ceil((count ?? 0) / pageSize);

  return {
    data: data.map((item) => ({
      ...item,
    })) as (Usuario & { contracts?: Contrato[] })[],
    total: count ?? 0,
    page,
    pageSize,
    totalPages,
  };
}

问题分析 #

核心问题是筛选条件的执行顺序错误。当前代码先对所有contract应用筛选规则（比如lt("contracts.fecha_final", now)），再取每个用户剩余contract中的最新一条。这会导致：如果用户有逾期的旧合同，即使存在活跃的新合同，旧合同会被筛选保留并被当作"最新"返回，最终用户被错误纳入结果。

正确逻辑应该是：先找到每个用户的最新contract，再判断该最新contract是否符合逾期条件。

解决方案 #

通过子查询先获取每个用户的最新合同日期，再关联contracts表定位到该最新合同，最后进行状态筛选。修改后的代码如下：

export async function obtenerUsuariosPaginados(
  params: PaginationParams
): Promise<PaginatedResponse<Usuario & { contracts?: Contrato[] }>> {
  const { page, pageSize, search, estado, tipo } = params;
  const start = (page - 1) * pageSize;
  const now = new Date().toISOString();

  // 子查询：获取每个用户的最新合同的最大fecha_final
  const latestContractSubquery = supabase()
    .from("contracts")
    .select("user_id, MAX(fecha_final) as latest_fecha")
    .groupBy("user_id");

  // 主查询：关联用户表和最新合同子查询，再关联contracts表获取最新合同详情
  let query = supabase()
    .from("users")
    .select("*, contracts(*)", { count: "exact" })
    // 关联子查询，确保只取每个用户的最新合同
    .innerJoin("contracts", "users.id", "contracts.user_id")
    .innerJoin(
      latestContractSubquery.as("latest_contracts"),
      function (join) {
        join
          .on("contracts.user_id", "=", "latest_contracts.user_id")
          .on("contracts.fecha_final", "=", "latest_contracts.latest_fecha");
      }
    )
    .order("apellido", { ascending: true });

  // Apply search filter
  if (search) {
    const searchLower = search?.toLowerCase();
    query = query.or(
      `apellido.ilike.%${searchLower}%,nombre.ilike.%${searchLower}%,legajo.ilike.%${searchLower}%,cuil.ilike.%${searchLower}%`
    );
  }

  // Apply contract status filter
  if (estado) {
    if (estado === "activo") {
      query = query
        .lte("contracts.fecha_inicio", now)
        .gte("contracts.fecha_final", now);
    } else if (estado === "renovar") {
      const twoMonthsBeforeEnd = addMonths(new Date(), 2).toISOString();
      query = query
        .lte("contracts.fecha_inicio", now)
        .lte("contracts.fecha_final", twoMonthsBeforeEnd)
        .gte("contracts.fecha_final", now);
    } else if (estado === "vencido") {
      // 直接判断最新合同的fecha_final是否小于当前时间
      query = query.lt("contracts.fecha_final", now);
    }
  }

  // Apply contract type filter
  if (tipo) {
    query = query.eq("contracts.tipo", tipo?.toLowerCase());
  }

  // Apply pagination
  const { data, error, count } = await query.range(start, start + pageSize - 1);

  if (error) throw error;

  const totalPages = Math.ceil((count ?? 0) / pageSize);

  return {
    data: data.map((item) => ({
      ...item,
      // 移除子查询带来的冗余字段，保持返回结构一致
      latest_contracts: undefined
    })) as (Usuario & { contracts?: Contrato[] })[],
    total: count ?? 0,
    page,
    pageSize,
    totalPages,
  };
}

关键修改点 #

• 新增子查询latestContractSubquery，通过MAX(fecha_final)和groupBy("user_id")获取每个用户的最新合同日期
• 主查询使用innerJoin关联子查询和contracts表，确保只获取每个用户的最新合同记录
• 筛选vencido状态时，直接判断该最新合同的fecha_final < now即可，无需额外的not条件，因为已经确保这是用户的最新合同

内容的提问来源于stack exchange，提问作者Javier Langer