Prisma•4mo ago

findMany - query table for all values not in another

hey, just trying to sort out a minor problem with a program of mine i'm trying to query a table, Track, and only return the records from the table if TrackID is not in another table, TrackToPlaylist in postgres, syntax for this would be similar to the following:

SELECT * FROM Track
WHERE TrackID NOT IN (SELECT * FROM TrackToPlaylist)

SELECT * FROM Track
WHERE TrackID NOT IN (SELECT * FROM TrackToPlaylist)

however, i'm a little stumped on how to go about implementing this in prisma, i've tried querying the entirety of TrackToPlaylist then filtering to create a list of TrackID's & using it in a not query, but i occasionally run into the following error when there's a lot of tracks

Assertion violation on the database: `too many bind variables in prepared statement, expected maximum of 32767, ...

Assertion violation on the database: `too many bind variables in prepared statement, expected maximum of 32767, ...

1 Reply

catOP•4mo ago

for reference here's my schema

model Track {
  TrackID     Int    @id @unique
  Title       String
  Description String
  TrackURL    String
  CreatedAt   DateTime

  TrackAuthor Author @relation(fields: [AuthorID], references: [AuthorID])
  AuthorID    Int

  TrackToPlaylist TrackToPlaylist[]
}

...

model TrackToPlaylist {
  TrackID    Int
  PlaylistID Int

  Track    Track    @relation(fields: [TrackID], references: [TrackID])
  Playlist Playlist @relation(fields: [PlaylistID], references: [PlaylistID])

  @@id([TrackID, PlaylistID])
}

model Track {
  TrackID     Int    @id @unique
  Title       String
  Description String
  TrackURL    String
  CreatedAt   DateTime

  TrackAuthor Author @relation(fields: [AuthorID], references: [AuthorID])
  AuthorID    Int

  TrackToPlaylist TrackToPlaylist[]
}

...

model TrackToPlaylist {
  TrackID    Int
  PlaylistID Int

  Track    Track    @relation(fields: [TrackID], references: [TrackID])
  Playlist Playlist @relation(fields: [PlaylistID], references: [PlaylistID])

  @@id([TrackID, PlaylistID])
}

forgive me, found an answer to my own question :P the solution was using this in the where clause within my findMany call

TrackToPlaylist: {
    none: {}
}

TrackToPlaylist: {
    none: {}
}

Gaming

Programming

findMany - query table for all values not in another