I'm trying to make a query, and it keeps bringing something that I don't want. I have a db with users, each user has contracts. Those contracts could be active or past due. When I try to search for the users I want to bring the latest contract they have, to see if that user’s latest contract is active o past due. This is my code:

Everything works except when I try to search for users that their latest contract is past due, because if a user has 2 or more contracts and 1 of them is still active, when I apply my filter for "vencido" it brings that that user with a contract that is past due.

Is there something I'm missing?

Let me know if you need more explanation.

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,
  };
}

If estado is "vencido", if a user has 2 o more contracts and the latest is still active, I don't want that user in the end result.