Hey guys, I'm currently going insane

const name = _input_.name ?? undefined;

   

const categories = _input_.categories ?? undefined;

   

const games = await prisma.game.findMany({

     

where: {

       

AND: {

         

name: { contains: name, mode: 'insensitive' },

         

categories: {

           

some: {

           

}

         

}

       

}

     

},

I have this query and I'm trying to to check if any category matches one of the categories array (input) how do I map this?

Are you trying to find any

game

matching the provided name and where it has a category in a list of categories provided? If so, give something like this a try:

const name = input.name ?? undefined;
const categories = input.categories ?? undefined;

const games = await prisma.game.findMany({
  where: {
    AND: [
        { name: { contains: name, mode: "insensitive" }},
        { categories: {
          some: {
            name: { in: categories.map( category => category.name) },
          },
        }}
    ]
  },
});

kindof. Firstly I wanted to figure out how this works but now I would like to find any game containing ALL categories provided. The game can have more but it has to have the ones provided (all of them). So some or every won't suffice. Anything I can do?

const name = input.name ?? undefined;
const categories = input.categories ?? undefined;

const games = await prisma.game.findMany({
  where: {
    AND: [
        { name: { contains: name, mode: "insensitive" }},
        { categories: {
          every: {
            name: { in: categories.map( category => category.name) },
          },
        }}
    ]
  },
});

So this should work by just switching to that

every

key. This is saying "give me all the games where every category in the provided list is associated with the game and where the name matches"

I dont know if I'm doing something wrong but I ran this now

const games = await prisma.game.findMany({
      where: {
        AND: [
          { name: { contains: name, mode: 'insensitive' } },
          { categories: {
            every: {
              description: { in: categories.map( (category) => category) },
            },
          }},
        ]
      },
      take: limit + 1,
      cursor: cursor ? { id: cursor } : undefined,
      include: {
        categories: true,
        genres: true
      },
      orderBy: {
        id: 'asc'
      }
    });

And I only get games where the input and the games categories exactly align so if my input is Single-player but the game itself has more categories it's not getting returned

Actually yes, sorry, I misunderstood.

const name = input.name ?? undefined;
    const categories = input.categories ?? undefined;

    const games = await prisma.game.findMany({
      where: {
        AND: [
          { name: { contains: name, mode: 'insensitive' } },
          {
            categories: {
              name: {
                some: {
                  OR: categories.map(category => ({ name: category.name })),
                },
              },
            },
          },
        ],
      },
    });

Could you try this?

this doesnt work because it doesnt let me put description (you used name) before some. after opening categories: I can only use Every, Some or none

const games = await prisma.game.findMany({
  where: {
    AND: [
      { name: { contains: name, mode: 'insensitive' } },
      {
        categories: {
          some: {
            OR: categories.map(category => ({ description: category })),
          },
        },
      },
    ],
  },
});

Ah okay, sorry working without your schema generated so I think I messed up the syntax

this returns the game if it has one of the specified categories but not all of the specified

Switch the OR to AND?

then none get returned

Did this solution work for you?

where: {
  AND: [
    ...categories!.map((value) => ({ categories: { some: { description: { contains: value } } } })),
  ]
},

Saw this in the main chat

Yes it does but I can't believe this is how you are. supposed to solve this. in my opinion it isnt very readable and it confused me that mongodb has a hasEvery which would solve this without this confusing syntax

I agree, the hasMany syntax is a lot easier. It could be cleaned up a bit like

where: {
  AND: categories.map(description => ({ categories: { some: { description: { contains:description } } } }))
}

there is another 'problem' with this. using the .map() makes it so you can't use mode: 'insensitive' anymore since you'll get a type-error. I already created an issue for this.

Sweet, was just about to post a snippet of it working 👍

the generator idea is great but this makes the mode inavailable again and you get the error type 'string' is not assignable to type 'QueryMode | undefined'

Yeah, you'd have to add the proper input/return types to the generator function using prisma's

Prisma

namespace to pull out your required types

I got it with defining an interface as return type

Sweet!

is it possible to share this generate function somehow between columns with a similar structure like lets say categories and genres or do i have to copy paste the function?

You could definitely get crafty with it. A more "generalized" version of the one above would be something like:

const generateRelationSomeFilter = (list, relationName, column) => 
  list.map(value => ({ [relationName]: { some: { [column]: { contains: value } } } }))

Of course you could play around with it and make the search shape dynamic too

got this

const generateRelationFilter = (list: string[], relationName: string, column: string) => list.map((value) => ({ [relationName]: { some: { [column]: { contains: value, mode: 'insensitive' } } } }));

running like this:

{ AND: generateRelationFilter(categories, 'categories', 'description') },

and it works like a charm

Awesome! Yeah a

hasMany

function in relation filters would be 👌 But for now that looks pretty good 🙂

yep thank you and @Maciek K for this solution. I created a feature request for a hasEvery for sql databases and I hope it get's implemented. Until then I'll work with this