Prisma 2 - Unkown arg 'where' in select.fruit.where for type UserFruit. Did you mean 'select'? Available args

Question

Trying to query in prisma and filter results from a related object but get the error:

Unknown arg 'where' in select.fruit.where for type UserFruit. Did you mean 'select'? Available args fruit{}

async findShops(req) {
        const userId = parseInt(req.params.id);
        const shop = await prisma.shop.findMany({
            select: {
              id: true,
              name: true,
              logo: true,
              fruit:{
                select:{
                  id:true,
                  userId:true,
                  fruitNumber:true,
                  created: true,
                  updated: true,
                },
                where: {
                  userId: userId
                }
              }
            }
            
          })
        return shop;
    };

example payload expected:

[
  { id: 1001, name: 'test1', logo: 'log.png', fruit: null },
  { id: 1002, name: 'test2', logo: 'log2.jpg', fruit: null },
  { id: 1003, name: 'test3', logo: 'log3.jpg', fruit: null },
  {
    id: 1005,
    name: 'test4',
    logo: 'log4.png',
    fruit: {
      id: '62450ee5-e75d-4a67-8d79-120d11ddf508',
      userId: 111,
      fruitNumber: '123456',
      created: 2022-07-01T06:39:52.924Z,
      updated: 2022-07-01T06:39:52.936Z
    }
  },
  {
    id: 1004,
    name: 'test5',
    logo: 'log5.jpg',
    fruit: {
      id: '20e9af37-2e6f-4070-8475-c5a914f311dc',
      userId: 111,
      fruitNumber: '123878',
      created: 2022-07-01T07:21:27.898Z,
      updated: 2022-07-01T07:21:27.901Z
    }
  }
]

I can easily achieve the expected output by not having the "where" but I need it because the fruit object can contain more than 1 object so I need to filter by userId e.g.

fruit: {
          id: '62450ee5-e75d-4a67-8d79-120d11ddf508',
          userId: 111,
          fruitNumber: '123456',
          created: 2022-07-01T06:39:52.924Z,
          updated: 2022-07-01T06:39:52.936Z
        },
        {
          id: '62450ee5-e75d-4a67-8d79-120d11ddf508',
          userId: 999,
          fruitNumber: '123456',
          created: 2022-07-01T06:39:52.924Z,
          updated: 2022-07-01T06:39:52.936Z
        }

For the fruit object I need nulls and anything that matches the userId and based on design it should only ever be 1 record for each shop for the specific user.

At somepoint my code seemed to work but after I did a prisma generate it stopped working. Is there another way I can achieve the same result or is there someway to fix this?

Note:version info below.

model UserFruit {
  id         String   @id @default(uuid())
  fruitNumber String   @map("fruit_number")
  shopId  Int      @unique @map("shop_id")
  userId     Int      @map("user_id")
  created    DateTime @default(now())
  updated    DateTime @updatedAt
  fruit    Fruit  @relation(fields: [fruitId], references: [id])

  @@unique([userId, fruitId], name: "userFruit")
  @@map("user_Fruit")
}

model Shop {
  id      Int       @id @default(autoincrement())
  name    String    @unique
  logo    String
  created DateTime  @default(now())
  updated DateTime  @updatedAt
  fruit    UserFruit?

  @@map("Shop")
}

model User {
  id        Int       @id @default(autoincrement())
  created   DateTime  @default(now())
  updated   DateTime  @updatedAt
  uid       String    @unique
  email     String    @unique
  phone     String    @unique
  firstName String    @map("first_name")
  lastName  String    @map("last_name")
  dob       DateTime?
  gender    String?
  roleId    Int       @default(1) @map("role_id")
  role      Role      @relation(fields: [roleId], references: [id])

  @@map("user")
}

You don't seem to be including any fruit relation inside your query. Is it supposed to be `fruit` instead of `card`? Could you kindly share the relevant portion of your Prisma schema as well? — Tasin Ishmam, Jul 01 '22 at 10:57
Thanks for picking that up, it was a typo in the question. In the code I actually had ```fruit``` so the issue still remains. I've attached the relevant schema. — TEZZ, Jul 01 '22 at 12:58
Your schema does not seem to be functional (there's a missing fruit model and the `UserFruit` relation on `Shop` is not defined on the `UserFruit` side), so I can't reproduce the problem. In general though, what you're trying to do should work fine. Also, you mentioned that there can be multiple instances of `fruit`, but this is not possible as each `Shop` can only have one `fruit` record related to it. — Tasin Ishmam, Jul 02 '22 at 12:33
Figuring out the correct schema and query would be ideal, but since I can't replicate it at the moment, a more simple/trivial solutio would be to `include` fruit without the `where` condition, and then use javascript `filter` to simply remove entities that do not match the condition. Basically take the where condition to your application code. — Tasin Ishmam, Jul 02 '22 at 12:35
```UserFruit``` is the fruit model, the key/reference is ```shop_id``` and ```user_id``` in ```UserFruit``` that ties to the other. If I was to filter it via javascript i don't need the ```include``` because I am already getting the full array. — TEZZ, Jul 07 '22 at 06:10
Can you please post your `Fruit` model? And the `findShops` calling code. — shmuels, Jul 14 '22 at 18:13
@shmuels as mentioned in previous comment ```UserFruit``` is the ```Fruit``` model and ```findShops``` calling code is in the first code block in the description. — TEZZ, Jul 16 '22 at 02:12

Shea Hunter Belsky · Accepted Answer · 2022-07-18T13:00:04.900

Why not do a nested where at the top level to only search for shops whose fruit has the userId you need, rather than doing a nested select? It should make your query simpler and also solve your problem.

const userId = parseInt(req.params.id);
const shop = await prisma.shop.findMany({
  select: {
    id: true,
    name: true,
    logo: true,
    fruit: {
      select: {
        id: true,
        userId: true,
        fruitNumber: true,
        created: true,
        updated: true,
      },
      // Removed the nested "where" from here
    },
  },
  where: {
    // One of the following conditions must be true
    OR: [
      // Return shops who have a connected fruit AND
      // the fruit's "userId" attribute equals the variable "userID"
      {
        fruit: {
          is: {
            userId: userId,
            // Can also simplify this to the below line if you want
            // userId
          },
        },
      },
      // Return shops who do not have a connected fruit
      // this will be true if "fruitId" is null
      // could also write this as {fruit: {is: {}}}
      {
        fruitId: {
          equals: null,
        },
      },
    ],
  },
});

This query should output an array of shops where the connected fruit model's userId property equals your userId variable.

The problem is I also want the shops where ```fruit``` object is ```null``` in addition to where if it's not null its equal to ```userId``` can I do an OR statement as well? — TEZZ, Jul 18 '22 at 12:39
@TEZZ I've edited my original answer, you can do `OR` to check that _either_ the connected fruit matches `userId` *OR* there is no connected fruit iat all — Shea Hunter Belsky, Jul 18 '22 at 13:00
Thanks, I realised that I needed to convert the ```fruit``` object to a List/Array as the data type and then I used your code with a bit of a tweek. ```is``` I used ```some```, ```equals``` I used ```every``` otherwise it pops up with an error. — TEZZ, Jul 19 '22 at 00:39

Prisma 2 - Unkown arg 'where' in select.fruit.where for type UserFruit. Did you mean 'select'? Available args

1 Answers1