Support for Distinct Query on Nested Field

[ApocalypseCalculator]

Problem

Suppose I have the sample schema from the documentation, and I add a GameCategory model (with a one-to-many relation to Game) such that the schema looks like this:

model User {
  id   Int     @id @default(autoincrement())
  name String?
  play Play[]
}

model Game {
  id   Int     @id @default(autoincrement())
  name String?
  play Play[]
  categoryId   Int?
  category     GameCategory? @relation(fields: [categoryId], references: [id])
}

model Play {
  id       Int   @id @default(autoincrement())
  score    Int?  @default(0)
  playerId Int?
  player   User? @relation(fields: [playerId], references: [id])
  gameId   Int?
  game     Game? @relation(fields: [gameId], references: [id])
}

model GameCategory {
  id   Int     @id @default(autoincrement())
  name String?
  game Game[]
}

In the sample query given with this schema, Prisma queries each player's highest score per game. Suppose I want to be able to query for each player's highest score per game category. That is, I want to be able to use distinct on categoryId, which I currently can't do since categoryId doesn't exist on the model Play. Is it possible to support using distinct on relation models?

Of course, I can filter this myself (which is not ideal with large amounts of data), but given that there is currently a database level DISTINCT in preview, I was wondering if it was possible to achieve this query at the database level as well.

[janpio]

What is the API you think Prisma Client should offer for this?
(Here is the current distinct API: https://www.prisma.io/docs/orm/prisma-client/queries/aggregation-grouping-summarizing#select-distinct)

[ApocalypseCalculator]

In my opinion, it could either be allowing subfields in the current API (i.e. distinct: ['game.categoryId']), or a similar API to select where we can have something like:

distinct: {
    game: {
        categoryId: true
    }
}

I am unsure of the technical challenges present here, but the raw SQL query (in Postgres) for this operation in particular would look something like

SELECT DISTINCT ON ("Game"."categoryId") * 
FROM "Play" 
INNER JOIN "Game" ON "Play"."gameId" = "Game".id 
INNER JOIN "User" ON "Play"."playerId" = "User".id
ORDER BY "Game"."categoryId" ASC, score DESC;