Filtering relations with 'include' and 'where'

Question

I'm building a simple todo app where a user can have multiple lists and share them. Here is my Prisma Schema:

model users {
  id                  String       @id @db.Char(30)
  name                String       @db.VarChar(255)
  email               String       @unique @db.VarChar(255)
  created_at          DateTime     @default(now()) @db.Timestamptz(6)
  updated_at          DateTime     @default(now()) @db.Timestamptz(6)
  lists_authored      lists[]
  lists_owned         user_lists[]
}

model lists {
  id                  String              @id @db.Char(36)
  name                String              @db.VarChar(255)
  status              enum_lists_status?  @default(NOT_STARTED)
  last_status_update  DateTime?           @db.Timestamptz(6)
  is_default          Boolean?            @default(false)
  is_parent           Boolean?            @default(false)
  created_at          DateTime            @default(now()) @db.Timestamptz(6)
  updated_at          DateTime            @default(now()) @db.Timestamptz(6)
  author              String?             @db.Char(30)
  users               users?              @relation(fields: [author], references: [id])
  children            list_lists[]        @relation("parents")
  parents             list_lists[]        @relation("children")
  owners              user_lists[]
}

model user_lists {
  user_id       String     @db.Char(30)
  list_id       String     @db.Char(36)
  lists         lists      @relation(fields: [list_id], references: [id], onDelete: Cascade)
  users         users      @relation(fields: [user_id], references: [id], onDelete: Cascade)

  @@id([user_id, list_id])
}

model list_lists {
  child_id      String     @db.Char(36)
  parent_id     String     @db.Char(36)
  child         lists      @relation("children", fields: [child_id], references: [id], onDelete: Cascade)
  parent        lists      @relation("parents", fields: [parent_id], references: [id], onDelete: Cascade)

  @@id([child_id, parent_id])
}

The relationships can be described as:

There is a many-to-many relationship (m-n relation) between Lists and themselves (join table list_lists). This means, that Lists act as both the parent list, and the list-item (child list). There is also a m-n relationship between Users and Lists (join table user_lists), as a list can have many owners (to enable sharing). Users also have a one-many (lists_authored) relationship with Lists, as they can author many Lists.

The Problem:

I'm having trouble querying a users shared lists. Plainly -- this should be any list that a user owns, that is NOT authored by them:

const sharedLists = prisma.user_lists.findMany({
  where: {
    user_id: { 
      equals: THE_USERS_ID 
    },
  },
  include: {
    lists: {
      where: {
        author: {
          not: THE_USERS_ID
        },
      },
    },
  },
});

This doesn't work. What am i missing? relevant documentation

The error:

Invalid `prisma.user_lists.findMany()` invocation:

{
  where: {
    user_id: {
      equals: 'auth0|615200b5899b9200705b76eb'
    }
  },
  include: {
    lists: {
      where: {
      ~~~~~
        author: {
          not: 'auth0|615200b5899b9200705b76eb'
        }
      }
    }
  }
}

Unknown arg `where` in include.lists.where for type lists. Did you mean `select`? Available args:
type lists {

}

Answer 1

In simple words you can apply where to arrays (denoted by []). But you're trying to apply where to user_lists.lists which is not an array (it doesn't have [])

Naming models in plural looks a little confusing to me, because it becomes harder to understand which field can be used with where and which can't.

So I would advise to stick to the naming convention suggested in official Prisma docs (uppercase singular names in schema and using @@map to set actual table names in database)