Prisma•2w ago

Retrieve accurate data

Hi! Hope you're well! I have recipe categories (lasagne, chocolate cake...) that have recipes (vegetarian lasagne, gluten-free chocolate cake...). These recipes have ingredients (butter, eggs...) On the customer's side, I have a selector that allows the user to select the ingredients they want and don't want to have in their search. Example: the user wants recipes that have eggs AND apples, but NO cheese. A recipes with eggs, apples and butter must be displayed. Here's my query:

return await prisma.recipesCategory.findMany({
  where: {
    recipes: {
      some: {
        ingredients: {
          some: {
            ingredientId: { 
              in: ingredientsIdsWanted 
            },
      },
      every: {
        ingredientId: {
          notIn: ingredientsIdsNotWanted,
        },
      },
    },
      },
    },
  },
});

return await prisma.recipesCategory.findMany({
  where: {
    recipes: {
      some: {
        ingredients: {
          some: {
            ingredientId: { 
              in: ingredientsIdsWanted 
            },
      },
      every: {
        ingredientId: {
          notIn: ingredientsIdsNotWanted,
        },
      },
    },
      },
    },
  },
});

the problem I have with this query is that it returns the recipe even if it contains only one ingredient in the list, which is normal since there's the some However, if I put an every and the recipe contains additional ingredients, it won't be displayed even if the conditions are met. I've been trying to find a solution for I don't know how long, and I just can't seem to get it, I feel like I'm going crazy or maybe it's simple and I'm looking for too complicated ahah thanks in advance!

7 Replies

RaphaelEtim•2w ago

Hi @IceCrew Can you please share your schema? Meanwhile will this solve your usecase

return await prisma.recipesCategory.findMany({
  where: {
    recipes: {
      some: {
        AND: [
          {
            ingredients: {
              every: {
                ingredientId: {
                  in: ingredientsIdsWanted,
                },
              },
            },
          },
          {
            ingredients: {
              none: {
                ingredientId: {
                  in: ingredientsIdsNotWanted,
                },
              },
            },
          },
        ],
      },
    },
  },
  include: {
    recipes: {
      include: {
        ingredients: true,
      },
    },
  },
});

return await prisma.recipesCategory.findMany({
  where: {
    recipes: {
      some: {
        AND: [
          {
            ingredients: {
              every: {
                ingredientId: {
                  in: ingredientsIdsWanted,
                },
              },
            },
          },
          {
            ingredients: {
              none: {
                ingredientId: {
                  in: ingredientsIdsNotWanted,
                },
              },
            },
          },
        ],
      },
    },
  },
  include: {
    recipes: {
      include: {
        ingredients: true,
      },
    },
  },
});

IceCrewOP•2w ago

This is my schema : https://github.com/lviardcretat/recepto/blob/main/prisma%2Fschema.prisma

GitHub

recepto/prisma/schema.prisma at main · lviardcretat/recepto

A solution for storing a large number of recipes and ingredients for daily use (recipe book, weekly recipe management, etc.). - lviardcretat/recepto

IceCrewOP•2w ago

it doesn't work, that's the problem I was explaining above if I want to search for a recipe with just one of its ingredients, you won't find anything because with the every you have to put in ALL its ingredients

IceCrewOP•2w ago

This is my issue ahah

IceCrewOP•2w ago

I think I found what I need

IceCrewOP•2w ago

but I'm on sqlite and that's why I can't use it...

IceCrewOP•2w ago

Solved :

return await prisma.recipesCategory.findMany({
        where: {
            recipes: {
                some: {
                    AND: [
                        ...ingredientsIdsWanted.map((id) => ({
                            ingredients: {
                                some: {
                                    ingredientId: id,
                                },
                            },
                        })),
                        {
                            ingredients: {
                                none: {
                                    ingredientId: {
                                        in: ingredientsIdsNotWanted,
                                    },
                                },
                            },
                        },
                    ],
                },
            },
        },
    });

return await prisma.recipesCategory.findMany({
        where: {
            recipes: {
                some: {
                    AND: [
                        ...ingredientsIdsWanted.map((id) => ({
                            ingredients: {
                                some: {
                                    ingredientId: id,
                                },
                            },
                        })),
                        {
                            ingredients: {
                                none: {
                                    ingredientId: {
                                        in: ingredientsIdsNotWanted,
                                    },
                                },
                            },
                        },
                    ],
                },
            },
        },
    });

Gaming

Programming

Retrieve accurate data