Many-to-many relationship not through linking table

[jmwilkinson]

Problem

Give tables a and b, where a{id, linking_id} and b{id, linking_id}, and linking_id is non-unique in both tables, it is impossible to define this relationship using prisma, and thus impossible to query it normally.

In a SQL query, this is obviously trivial:

SELECT * FROM a JOIN b ON a.linking_id = b.linking_id

Suggested solution

Allow a relationship to be defined in the schema specifying this sort of many-to-many relationships, possibly:

model a {
  id: BigInt
  linking_id: BigInt
  b b[] @relation(fields: [linking_id], references: [linking_id])
}

Additional context

Although there are workaround via raw sql, those are not composable with code that generates filtering queries and the like using prisma's standard syntax.