Fastest way to get counts (need to redo schema?)

course_id, course_name, course_code, creator_id, instructor_count, student_count

course_id, course_name, course_code, creator_id, instructor_count, student_count

  type CourseGeneral = {
    id: number;
    name: string;
    code: string;
    role: 0 | 1 | 2;
    owner_name: string;
    instructor_count: number;
    student_count: number;
  };

  type CourseGeneral = {
    id: number;
    name: string;
    code: string;
    role: 0 | 1 | 2;
    owner_name: string;
    instructor_count: number;
    student_count: number;
  };

await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: { where: { title: 'Hello!' } },
      },
    },
  },
})

await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: { where: { title: 'Hello!' } },
      },
    },
  },
})

model Course {
  id   Int    @id @default(autoincrement())
  name String @db.VarChar(255)
  code String @unique @db.Char(7) // random 7 character alphanumeric uppercase string

  Enrolled Enrolled[]
  Meeting  Meeting[]
}

model Enrolled {
  user_id   String
  course_id Int
  role      Int    @db.SmallInt

  course Course @relation(fields: [course_id], references: [id], onDelete: Cascade)

  @@id([user_id, course_id])
}

model Course {
  id   Int    @id @default(autoincrement())
  name String @db.VarChar(255)
  code String @unique @db.Char(7) // random 7 character alphanumeric uppercase string

  Enrolled Enrolled[]
  Meeting  Meeting[]
}

model Enrolled {
  user_id   String
  course_id Int
  role      Int    @db.SmallInt

  course Course @relation(fields: [course_id], references: [id], onDelete: Cascade)

  @@id([user_id, course_id])
}

-- CreateTable
CREATE TABLE "Course" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "code" CHAR(7) NOT NULL,

    CONSTRAINT "Course_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Enrolled" (
    "user_id" TEXT NOT NULL,
    "course_id" INTEGER NOT NULL,
    "role" SMALLINT NOT NULL,

    CONSTRAINT "Enrolled_pkey" PRIMARY KEY ("user_id","course_id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Course_code_key" ON "Course"("code");

-- AddForeignKey
ALTER TABLE "Enrolled" ADD CONSTRAINT "Enrolled_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "Course"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- CreateTable
CREATE TABLE "Course" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "code" CHAR(7) NOT NULL,

    CONSTRAINT "Course_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Enrolled" (
    "user_id" TEXT NOT NULL,
    "course_id" INTEGER NOT NULL,
    "role" SMALLINT NOT NULL,

    CONSTRAINT "Enrolled_pkey" PRIMARY KEY ("user_id","course_id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Course_code_key" ON "Course"("code");

-- AddForeignKey
ALTER TABLE "Enrolled" ADD CONSTRAINT "Enrolled_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "Course"("id") ON DELETE CASCADE ON UPDATE CASCADE;

SELECT
    c.id,
    c.name,
    c.code,
    COUNT(CASE WHEN e.role = 0 THEN e.user_id END) AS enrolled_count,
    COUNT(CASE WHEN e.role = 1 OR e.role= 2 THEN e.user_id END) AS instructor_count
FROM Course c
INNER JOIN Enrolled e ON e.course_id = c.id
GROUP BY (c.id)

SELECT
    c.id,
    c.name,
    c.code,
    COUNT(CASE WHEN e.role = 0 THEN e.user_id END) AS enrolled_count,
    COUNT(CASE WHEN e.role = 1 OR e.role= 2 THEN e.user_id END) AS instructor_count
FROM Course c
INNER JOIN Enrolled e ON e.course_id = c.id
GROUP BY (c.id)