Filter on related records produces no results

[heymartinadams]

Bug description

Unable to perform a filter on related records. I’m probably overlooking something really silly.

How to reproduce

1. Create the following schema:

   
   model Event {
   eventId                    String        @unique
   venue                      Venue?        @relation("EventsToOVenue", fields: [venueId], references: [venueId])
   venueId                    String?
   }

model Venue { venueId String @unique events Event[] @relation("EventsToOVenue") venueRegion VenueRegion @relation("VenuesToRVenueRegion", fields: [venueRegionId], references: [venueRegionId]) venueRegionId String }

model VenueRegion { venueRegionId String @unique venues Venue[] @relation("VenuesToRVenueRegion") }

2. Add a random Event record, with a random Venue record and a random VenueRegion record, and connect all.

3. Any of the following queries produce an empty array.

```js
let events = await prisma.event.findMany({
    where: {
        venue: {
            venueId: 'some_random_venueId'
        }
    }
})

let events = await prisma.event.findMany({
    where: {
        venue: {
            venueId: { equals: 'some_random_venueId' }
        }
    }
})

let events = await prisma.event.findMany({
    where: {
        venue: {
            venueRegion: {
                venueRegionId: 'some_random_venueRegionId'
            }
        }
    }
})

let events = await prisma.event.findMany({
    where: {
        venue: {
            venueRegion: {
                venueRegionId: { equals: 'some_random_venueRegionId' }
            }
        }
    }
})

Expected behavior

The venueId or venueRegionId filter criteria are entered correctly and records exist for these, so events should not be an empty array.

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: 12.6.0
• Prisma version:

  2.4.0-dev.8

[heymartinadams]

Hosted using Vercel, I’m seeing the following console on this particular function call: [GET] /api/events?region=san-francisco-bay-area with region being transformed to:

let events = await prisma.event.findMany({
    where: {
        venue: {
            venueRegion: {
                venueRegionId: { equals: 'san-francisco-bay-area' }
            }
        }
    }
})
console.log(events)

console

2020-07-23T20:29:42.594Z engine { cwd: '/var/task/node_modules/.prisma/client' }
2020-07-23T20:29:42.597Z engine {
  dotPrismaPath: '/var/task/node_modules/.prisma/client/query-engine-rhel-openssl-1.0.x'
}
2020-07-23T20:29:42.597Z    24def52f-2881-4333-9263-05509ee6bd61    ERROR   2020-07-23T20:29:42.597Z plusX Execution permissions of /var/task/node_modules/.prisma/client/query-engine-rhel-openssl-1.0.x are fine
2020-07-23T20:29:42.598Z engine {
  flags: [ '--enable-experimental=aggregations', '--enable-raw-queries' ]
}
2020-07-23T20:29:42.598Z engine port: 45691
2020-07-23T20:29:42.662Z engine stdout {
  timestamp: 'Jul 23 20:29:42.661',
  level: 'INFO',
  target: 'quaint::pooled',
  fields: { message: 'Starting a postgresql pool with 5 connections.' }
}
2020-07-23T20:29:42.683Z engine stdout {
  timestamp: 'Jul 23 20:29:42.683',
  level: 'INFO',
  target: 'query_engine::server',
  fields: { message: 'Started http server' }
}
2020-07-23T20:29:42.694Z    24def52f-2881-4333-9263-05509ee6bd61    ERROR   2020-07-23T20:29:42.694Z prisma-client Client Version 2.4.0-dev.8
2020-07-23T20:29:42.695Z    24def52f-2881-4333-9263-05509ee6bd61    ERROR   2020-07-23T20:29:42.694Z prisma-client Engine Version 0f3750ce5b6509b766811fbe350e83f232daa57f

// Output:
[]

[heymartinadams]

ps: Completely deleted the (development) database, and reseeded it with sample data — to no effect.

[divyenduz]

@heymartinadams I am unable to reproduce this,

I used the following typescript queries

//@ts-check
const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  await prisma.event.deleteMany({})
  await prisma.venue.deleteMany({})
  await prisma.venueRegion.deleteMany({})

  let event = await prisma.event.create({
    data: {
      eventId: '1',
    },
  })
  console.log({ event })

  const venueRegion = await prisma.venueRegion.create({
    data: {
      venueRegionId: '1',
    },
  })
  console.log({ venueRegion })

  const venue = await prisma.venue.create({
    data: {
      venueId: '1',
      venueRegion: {
        connect: {
          venueRegionId: '1',
        },
      },
    },
  })
  console.log({ venue })

  event = await prisma.event.update({
    where: {
      eventId: '1',
    },
    data: {
      venue: {
        connect: {
          venueId: '1',
        },
      },
    },
  })

  let events = await prisma.event.findMany({
    where: {
      venue: {
        venueId: '1',
      },
    },
  })
  console.log({ events })
}

main()

And the response was expected

divyendusingh [prisma-client-js-800]$ node index.js                                                                                         130 ↵
{ event: { eventId: '1', venueId: null } }
{ venueRegion: { venueRegionId: '1' } }
{ venue: { venueId: '1', venueRegionId: '1' } }
{ events: [ { eventId: '1', venueId: '1' } ] }

Am I doing something differently?

I used Postgres and Prisma CLI version 2.4.0-dev.13

[heymartinadams]

Was able to reproduce it! So relieved, since I’ve been encountering this issue for months. So I really hope you can fix it (if the issue is with Prisma) or correct my code (if the issue is with me), @divyenduz.

In the result below, result should not be an empty array.

Schema

model TestOne {
  id        String    @default(uuid()) @id
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  // --- PUBLIC ID ---
  testOneId String    @unique
  // --- RELATIONS ---
  testTwos  TestTwo[] @relation("TestOneToTestTwos")
}

model TestTwo {
  id        String   @default(uuid()) @id
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  // --- PUBLIC ID ---
  testTwoId String   @unique
  // --- RELATIONS ---
  testOne   TestOne  @relation("TestOneToTestTwos", fields: [testOneId], references: [testOneId])
  testOneId String
}

Seed

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
    await prisma.testTwo.deleteMany({})
    await prisma.testOne.deleteMany({})

    let testOne = await prisma.testOne.create({
        data: {
            testOneId: 'testOneRandom'
        }
    })
    console.log({ testOne })

    const testTwo = await prisma.testTwo.create({
        data: {
            testTwoId: 'testTwoRandom',
            testOne: {
                connect: {
                    testOneId: 'testOneRandom'
                }
            }
        }
    })
    console.log({ testTwo })

    testOne = await prisma.testOne.update({
        where: {
            testOneId: 'testOneRandom'
        },
        data: {
            testTwos: {
                connect: {
                    testTwoId: 'testTwoRandom'
                }
            }
        },
        include: {
            testTwos: true
        }
    })
    console.log({ testOne })
    console.log({ connection: testOne.testTwos })

    let result = await prisma.testOne.findMany({
        where: {
            testTwos: {
                some: {
                    testTwoId: {
                        equals: 'testTwoRandom'
                    }
                }
            }
        }
    })
    console.log({ result })
}

main()

Result

{
  testOne: {
    id: '3492f533-95e5-416c-9268-5de63d3a4867',
    createdAt: 2020-08-19T20:06:41.438Z,
    updatedAt: 2020-08-19T20:06:41.438Z,
    testOneId: 'testOneRandom'
  }
}
{
  testTwo: {
    id: '19e3b949-8cb1-4648-905a-d7ac13d0c59a',
    createdAt: 2020-08-19T20:06:41.877Z,
    updatedAt: 2020-08-19T20:06:41.877Z,
    testTwoId: 'testTwoRandom',
    testOneId: 'testOneRandom'
  }
}
{
  testOne: {
    id: '3492f533-95e5-416c-9268-5de63d3a4867',
    createdAt: 2020-08-19T20:06:41.438Z,
    updatedAt: 2020-08-19T20:06:41.438Z,
    testOneId: 'testOneRandom',
    testTwos: [ [Object] ]
  }
}
{
  connection: [
    {
      id: '19e3b949-8cb1-4648-905a-d7ac13d0c59a',
      createdAt: 2020-08-19T20:06:41.877Z,
      updatedAt: 2020-08-19T20:06:41.877Z,
      testTwoId: 'testTwoRandom',
      testOneId: 'testOneRandom'
    }
  ]
}
{ result: [] }

Version

@prisma/cli          : 2.6.0-dev.8
Current platform     : darwin
Query Engine         : query-engine 62c83f00cd815bc777fd0f326c7284d5b1556f8f (at node_modules/@prisma/cli/query-engine-darwin)
Migration Engine     : migration-engine-cli 62c83f00cd815bc777fd0f326c7284d5b1556f8f (at node_modules/@prisma/cli/migration-engine-darwin)
Introspection Engine : introspection-core 62c83f00cd815bc777fd0f326c7284d5b1556f8f (at node_modules/@prisma/cli/introspection-engine-darwin)
Format Binary        : prisma-fmt 62c83f00cd815bc777fd0f326c7284d5b1556f8f (at node_modules/@prisma/cli/prisma-fmt-darwin)
Studio               : 0.261.0

Schema directives

AWS RDS PostgreSQL database

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

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-1.0.x"]
}

[heymartinadams]

Eager to have someone have a look at this. Can’t really move forward while queries like this aren’t working.

[pantharshit00]

@heymartinadams

I am unable to reproduce this with 2.6.0-dev.41. I got correct result.

{
  testOne: {
    id: '879701e3-8856-4b32-a9cf-ac993c9f7689',
    createdAt: 2020-08-27T11:50:48.213Z,
    updatedAt: 2020-08-27T11:50:48.214Z,
    testOneId: 'testOneRandom'
  }
}
{
  testTwo: {
    id: 'b816dd6d-d6e3-4231-b8ad-59f0d790d81c',
    createdAt: 2020-08-27T11:50:48.218Z,
    updatedAt: 2020-08-27T11:50:48.219Z,
    testTwoId: 'testTwoRandom',
    testOneId: 'testOneRandom'
  }
}
{
  testOne: {
    id: '879701e3-8856-4b32-a9cf-ac993c9f7689',
    createdAt: 2020-08-27T11:50:48.213Z,
    updatedAt: 2020-08-27T11:50:48.214Z,
    testOneId: 'testOneRandom',
    testTwos: [ [Object] ]
  }
}
{
  connection: [
    {
      id: 'b816dd6d-d6e3-4231-b8ad-59f0d790d81c',
      createdAt: 2020-08-27T11:50:48.218Z,
      updatedAt: 2020-08-27T11:50:48.219Z,
      testTwoId: 'testTwoRandom',
      testOneId: 'testOneRandom'
    }
  ]
}
{
  result: [
    {
      id: '879701e3-8856-4b32-a9cf-ac993c9f7689',
      createdAt: 2020-08-27T11:50:48.213Z,
      updatedAt: 2020-08-27T11:50:48.214Z,
      testOneId: 'testOneRandom'
    }
  ]
}

Can you please try again with this version?

[heymartinadams]

Very strange, @pantharshit00. It now works both for 2.6.0-dev.8 and 2.6.0-dev.41.

What might give us an additional clue is that I’m now trying out both versions on a new computer. I think I remember having cleared Prisma’s cache on the old computer, though, to no avail.

Anyhow, I guess we can close this.

I’m super glad it’s working again! 🎉