Untitled

    this.db
      .with("ranked_activities", (qb) =>
        qb
          .selectFrom("activities as a")
          .leftJoin("users as u", "a.user_id", "u.id")
          .select([
            "a.id",
            "a.user_id",
            "a.description",
            "a.max_participants",
            "a.datetime",
            "a.address",
            "a.category_id",
            "a.created_at",
            "a.updated_at",
            sql<number>`COALESCE(ST_X(a.location::geometry), 0)`.as("longitude"),
            sql<number>`COALESCE(ST_Y(a.location::geometry), 0)`.as("latitude"),
            sql<Users>`COALESCE(json_build_object(
            'id', u.id,
            'created_at', u.created_at,
            'gender', u.gender,
            'birthdate', u.birthdate,
            'longitude', COALESCE(ST_X(u.location::geometry), 0),
            'latitude', COALESCE(ST_Y(u.location::geometry), 0),
            'address', u.address,
            'full_name', u.full_name
          ), '{}')`.as("user"),
            sql`ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY a.created_at DESC)`.as(
              "rn",
            ),
          ]),
      )
      .with("activity_data", (qb) =>
        qb
          .selectFrom("ranked_activities")
          .select([
            "id",
            "user_id",
            "description",
            "max_participants",
            "datetime",
            "address",
            "category_id",
            "created_at",
            "updated_at",
            "longitude",
            "latitude",
            "user",
            "rn",
            sql<boolean>`CASE WHEN datetime < CURRENT_TIMESTAMP AT TIME ZONE 'UTC' THEN true ELSE false END`.as(
              "is_past",
            ),
          ])
          .select((eb) => [
            eb
              .selectFrom("avatars")
              .select(sql`COALESCE(json_agg(avatars), '[]'::json)`.as("avatars"))
              .whereRef("avatars.user_id", "=", "ranked_activities.user_id")
              .as("avatars"),
            eb
              .selectFrom("activities_users")
              .select(sql<number>`CAST(COUNT(*) AS INTEGER)`.as("joined_count"))
              .whereRef("activities_users.activity_id", "=", "ranked_activities.id")
              .as("joined_count"),
            eb
              .selectFrom("activities_users as au")
              .innerJoin("users as ju", "au.user_id", "ju.id")
              .select(
                sql`COALESCE(json_agg(json_build_object(
                'id', ju.id,
                'full_name', ju.full_name,
                'gender', ju.gender,
                'birthdate', ju.birthdate
              )), '[]'::json)`.as("joined_users"),
              )
              .whereRef("au.activity_id", "=", "ranked_activities.id")
              .as("joined_users"),
          ]),
      )
      .selectFrom("activity_data")
      .select([
        "id",
        "user_id",
        "description",
        "max_participants",
        "datetime",
        "address",
        "category_id",
        "created_at",
        "updated_at",
        "longitude",
        "latitude",
        "user",
        "rn",
        "is_past",
        "avatars",
        "joined_count",
        "joined_users",
        sql<boolean>`CASE WHEN joined_count >= max_participants THEN true ELSE false END`.as(
          "is_full",
        ),
        sql<boolean>`EXISTS (
        SELECT 1 FROM activities_users 
        WHERE activity_id = activity_data.id AND user_id = ${userMakingRequest}
      )`.as("is_user_joined"),
      ])
      .where("rn", "=", 1)
      .where("datetime", ">", sql`CURRENT_TIMESTAMP` as any)
      .limit(10)
      .offset(0)
      .orderBy("created_at", "desc")
      .execute()