Add the ability to add a where clause for "one-to" side of "one-to-one" and "one-to-many"

[firatoezcan]

Problem

I'm trying to build a tool ontop of Prisma that handles access control defined via a ruleset.
What I currently have is the ability to block certain tables from being queried as well as only returning a subset of tables that are accessible by the defined rules.

However while implementing I found that there is no where argument available for relations that are on the "one" side from a "one-to-many" relationship (or both sides with a "one-to-one" relation)

An example query that I would want to send is here:

prisma.invoiceLine.findMany({
  where: {
    OR: [
      {
        Invoice: {
          is: {
            CustomerId: {
              equals: 1,
            },
          },
        },
      },
    ],
  },
  select: {
    InvoiceId: true,
    Track: {
      where: {
        InvoiceLine: {
          some: {
            Invoice: {
              is: {
                InvoiceId: {
                  in: [
                    327,
                    98,
                  ],
                },
              },
            },
          },
        },
      },
      select: {
        Name: true,
        Album: {
          select: {
            Title: true,
          },
        },
        Composer: true,
      },
    },
    UnitPrice: true,
    Quantity: true,
  },
});

This obviously tells me (correctly) that where does not exist on Track but without having a where clause there, my only other option is to fetch every record that is required for checking the rule and check this in userspace instead of having this be dealt with in the database layer.

This becomes a problem with a large amount of data as the rules can use relations and thus making me load a lot of unnecessary data into memory to then filter out again.

Suggested solution

Add where to the "one" side of relations so it can be null depending on if the where matches or not

Alternatives

There is the alternative that I would add the where clause higher up on the invoiceLine, but I want to fetch the data for the invoiceLine but have Track be null in this case. If this was my own application I could make assumptions in the way I query but since this is aimed to be a general purpose tool I can't push that assumption to the users.

I also cannot make the Track be a optional field (if this is why I don't have a where clause) because I want the database to validate that data is correctly inserted.

A last resort solution would be to detect when I hit a "boundary" like this and get all IDs and make a subsequent Track.findMany but I'd like to not do that if possible and make a single roundtrip to the database instead for latency reasons

Additional context

The Prisma schema as well as the migrations for some test data are available here: https://github.com/firatoezcan/typegraphql-prisma-cms/tree/nexus/apps/api/prisma

Schema

generator client {
  provider = "prisma-client-js"
  // previewFeatures = ["interactiveTransactions"]
}

generator nexusPrisma {
  provider = "nexus-prisma"
}

// generator typegraphql {
//   provider = "typegraphql-prisma"
//   output   = "../node_modules/.prisma/type-graphql"
// }

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Album {
  AlbumId  Int     @id(map: "PK_Album") @unique
  Title    String  @db.VarChar(160)
  ArtistId Int
  Artist   Artist  @relation(fields: [ArtistId], references: [ArtistId], onDelete: NoAction, onUpdate: NoAction, map: "FK_AlbumArtistId")
  Track    Track[]

  @@index([ArtistId], map: "IFK_AlbumArtistId")
}

model Artist {
  ArtistId Int     @id(map: "PK_Artist") @unique
  Name     String? @db.VarChar(120)
  Album    Album[]
}

model Customer {
  CustomerId   Int       @id(map: "PK_Customer") @unique
  FirstName    String    @db.VarChar(40)
  LastName     String    @db.VarChar(20)
  Company      String?   @db.VarChar(80)
  Address      String?   @db.VarChar(70)
  City         String?   @db.VarChar(40)
  State        String?   @db.VarChar(40)
  Country      String?   @db.VarChar(40)
  PostalCode   String?   @db.VarChar(10)
  Phone        String?   @db.VarChar(24)
  Fax          String?   @db.VarChar(24)
  Email        String    @db.VarChar(60)
  SupportRepId Int?
  Employee     Employee? @relation(fields: [SupportRepId], references: [EmployeeId], onDelete: NoAction, onUpdate: NoAction, map: "FK_CustomerSupportRepId")
  Invoice      Invoice[]

  @@index([SupportRepId], map: "IFK_CustomerSupportRepId")
}

/// Employees Table
model Employee {
  EmployeeId     Int        @id(map: "PK_Employee") @unique
  LastName       String     @db.VarChar(20)
  FirstName      String     @db.VarChar(20)
  Title          String?    @db.VarChar(30)
  ReportsTo      Int?
  BirthDate      DateTime?  @db.Timestamp(6)
  HireDate       DateTime?  @db.Timestamp(6)
  Address        String?    @db.VarChar(70)
  City           String?    @db.VarChar(40)
  State          String?    @db.VarChar(40)
  Country        String?    @db.VarChar(40)
  PostalCode     String?    @db.VarChar(10)
  Phone          String?    @db.VarChar(24)
  Fax            String?    @db.VarChar(24)
  Email          String?    @db.VarChar(60)
  Employee       Employee?  @relation("EmployeeToEmployee", fields: [ReportsTo], references: [EmployeeId], onDelete: NoAction, onUpdate: NoAction, map: "FK_EmployeeReportsTo")
  Customer       Customer[]
  other_Employee Employee[] @relation("EmployeeToEmployee")

  @@index([ReportsTo], map: "IFK_EmployeeReportsTo")
}

model Genre {
  GenreId Int     @id(map: "PK_Genre") @unique
  Name    String? @db.VarChar(120)
  Track   Track[]
}

model Invoice {
  InvoiceId         Int           @id(map: "PK_Invoice") @unique
  CustomerId        Int
  InvoiceDate       DateTime      @db.Timestamp(6)
  BillingAddress    String?       @db.VarChar(70)
  BillingCity       String?       @db.VarChar(40)
  BillingState      String?       @db.VarChar(40)
  BillingCountry    String?       @db.VarChar(40)
  BillingPostalCode String?       @db.VarChar(10)
  Total             Decimal       @db.Decimal(10, 2)
  Customer          Customer      @relation(fields: [CustomerId], references: [CustomerId], onDelete: NoAction, onUpdate: NoAction, map: "FK_InvoiceCustomerId")
  InvoiceLine       InvoiceLine[]

  @@index([CustomerId], map: "IFK_InvoiceCustomerId")
}

model InvoiceLine {
  InvoiceLineId Int     @id(map: "PK_InvoiceLine") @unique
  InvoiceId     Int
  TrackId       Int
  UnitPrice     Decimal @db.Decimal(10, 2)
  Quantity      Int
  Invoice       Invoice @relation(fields: [InvoiceId], references: [InvoiceId], onDelete: NoAction, onUpdate: NoAction, map: "FK_InvoiceLineInvoiceId")
  Track         Track   @relation(fields: [TrackId], references: [TrackId], onDelete: NoAction, onUpdate: NoAction, map: "FK_InvoiceLineTrackId")

  @@index([InvoiceId], map: "IFK_InvoiceLineInvoiceId")
  @@index([TrackId], map: "IFK_InvoiceLineTrackId")
}

model MediaType {
  MediaTypeId Int     @id(map: "PK_MediaType") @unique
  Name        String? @db.VarChar(120)
  Track       Track[]
}

model Playlist {
  PlaylistId    Int             @id(map: "PK_Playlist") @unique
  Name          String?         @db.VarChar(120)
  PlaylistTrack PlaylistTrack[]
}

model PlaylistTrack {
  PlayListTrackId String?  @unique @default(uuid())
  PlaylistId      Int
  TrackId         Int
  Playlist        Playlist @relation(fields: [PlaylistId], references: [PlaylistId], onDelete: NoAction, onUpdate: NoAction, map: "FK_PlaylistTrackPlaylistId")
  Track           Track    @relation(fields: [TrackId], references: [TrackId], onDelete: NoAction, onUpdate: NoAction, map: "FK_PlaylistTrackTrackId")

  @@id([PlaylistId, TrackId], map: "PK_PlaylistTrack")
  @@index([TrackId], map: "IFK_PlaylistTrackTrackId")
}

model Track {
  TrackId       Int             @id(map: "PK_Track") @unique
  Name          String          @db.VarChar(200)
  AlbumId       Int?
  MediaTypeId   Int
  GenreId       Int?
  Composer      String?         @db.VarChar(220)
  Milliseconds  Int
  Bytes         Int?
  UnitPrice     Decimal         @db.Decimal(10, 2)
  Album         Album?          @relation(fields: [AlbumId], references: [AlbumId], onDelete: NoAction, onUpdate: NoAction, map: "FK_TrackAlbumId")
  Genre         Genre?          @relation(fields: [GenreId], references: [GenreId], onDelete: NoAction, onUpdate: NoAction, map: "FK_TrackGenreId")
  MediaType     MediaType       @relation(fields: [MediaTypeId], references: [MediaTypeId], onDelete: NoAction, onUpdate: NoAction, map: "FK_TrackMediaTypeId")
  InvoiceLine   InvoiceLine[]
  PlaylistTrack PlaylistTrack[]

  @@index([AlbumId], map: "IFK_TrackAlbumId")
  @@index([GenreId], map: "IFK_TrackGenreId")
  @@index([MediaTypeId], map: "IFK_TrackMediaTypeId")
}

[JoeRoddy]

Were you every able to find an alternative way to accomplish it @firatoezcan ? Experiencing a similar issue. Imagine I have user profiles that the user can mark as private and I want to fetch all the users and only include the profile data based on the toggle:

model User {
  id        Int      @id @default(autoincrement())
  name     String
  profile   Profile? @relation(fields: [profileId], references: [id])
  profileId Int?     @unique
}

model Profile {
  isPrivate Boolean @default(true)
  email     String
  location  String

  id   Int   @id @default(autoincrement())
  user User?
}

const data = await db.user.findMany({ include: { profile: { where: { isPrivate: false } } } });
^This would be the intuitive way to do this, but I don't think this is possible currently. I 100% agree with Firat, pretty silly to have to filter out this data manually after querying, and becomes orders of magnitude more complex if theres multiple levels of include statements.

[firatoezcan]

@JoeRoddy nope, I tried doing multiple client calls but the performance was not good enough for me so I switched to using Hasura

[tonypeng]

+1, also in this situation

the only workaround unfortunately seems to be as mentioned; fetching and then performing another query to filter and then ANDing the two results