How to get sql result with only rows that has a connected relations row

user_12603109 0
Viewed 289

I am trying to query a database using the Prism Client and want to get back only the rows that has a foreign key in a joined table.

For example, get all users who have also created a post. I need a kind of "INNER JOIN".

I have tried something like:

return this.prisma.user.findMany({
    where: {
        Post: {
            some: {
                id: {
                    not: 0,
                }
            }
        }
    }
});

But the result is not the correct one. I'm not sure how to use "none, some or every"

prisma2 prisma
1 Answers

Suppose I have a schema like this:

model User {
  id        Int      @default(autoincrement()) @id
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @default(autoincrement()) @id
  title     String
  user      User?    @relation(fields: [userId], references: [id])
  userId    Int?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

I get the users without any posts via the following query:

  const usersWithoutPosts = await prisma.user.findMany({
    where: {
      posts: {
        none: {
          id: undefined,
        },
      },
    },
  })

Could you try this way and check?

user_88743510
Related