MongoDB: Filtering by relation returns no results

[2color]

Bug description

Given a schema with two models: User and Post and a relation between the two, when querying for posts based on the author's email returns no results.

How to reproduce

1. Create the prisma schema using the schema below
2. Create a user and 3 related posts with the following query:

const user = await prisma.user.create({
    data: {
      email: 'david@prisma.io',
      firstName: 'David',
      lastName: 'Deutsch',
      posts: {
        create: [
          { title: 'Hello world' },
          { title: 'Introduction to Prisma with Mongo' },
          { title: 'MongoDB and schemas' },
        ],
      },
    },
    include: {
      posts: true,
    },
  })

3. Query for posts associated with the user:

const posts = await prisma.post.findMany({
    include: {
      author: true,
    },
    where: {
      author: {
        email: 'david@prisma.io',
      },
    },
  })

No results are returned

When trying to filter using the authorId the correct results are returned:

const posts = await prisma.post.findMany({
    include: {
      author: true,
    },
    where: {
      authorId: '609be3590072a87400dd7c29',
    },
  })

Expected behavior

Calling findMany with filtering based on the author.email should return the posts.

Prisma information

Prisma schema

model User {
  id        String   @id @default(dbgenerated()) @map("_id") @db.ObjectId
  createdAt DateTime @default(now())
  firstName String
  lastName  String?
  email     String   @unique
  posts     Post[]
}

model Post {
  id        String   @id @default(dbgenerated()) @map("_id") @db.ObjectId
  createdAt DateTime @default(now())
  title     String
  body      String?
  views     Int      @default(0)
  author    User?    @relation(fields: [authorId], references: [id])
  authorId  String?  @db.ObjectId
}

Environment & setup

• OS: MacOS
• Database: MongoDB
• Node.js version: v14.16.1
• Prisma version:

prisma               : 2.22.1
@prisma/client       : 2.22.1
Current platform     : darwin
Query Engine         : query-engine 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 60cc71d884972ab4e897f0277c4b84383dddaf6c
Studio               : 0.379.0
Preview Features     : mongoDb

[pantharshit00]

I can confirm this bug with version 2.23.0-dev.28. Thanks for the detailed report! Really appreciate that.

[dpetrick]

Fixed in 2.28.

[luiso1979]

Hello everybody,
I'm using prisma 3.15.2 and this bug is still there. When I try to filter a collection using a property in a relation it returns 0 elements.

This is my schema:

  model Campaign {
    id                   String            @id @default(auto()) @map("_id") @db.ObjectId
    name                 String
    status               CampaignStatus
    signatures           Signature[]
  }

  model Signature {
    id                   String            @id @default(auto()) @map("_id") @db.ObjectId
    campaign             Campaign          @relation(fields: [campaignId], references: [id])
    campaignId           String
  }

  enum CampaignStatus {
    NEW
    ACTIVE
    PAUSED
    FINISHED
    EXPIRED
  }

The query I'm trying is:

    const c = await prisma.signature.findMany({
        include: {
            campaign: true
        },
        where: {
            campaign: {
                status: "ACTIVE",
            }
        },
    });

And the result is that c has length 0

I checked the DB and the data is there and correctly linked.

Thanks in advance,
Luis

[janpio]

Please open a new issue @luiso1979, too much changed from this old version for this to make sense to reopen again. You can basically copy/paster your text into a bug issue with its template.