Sequelize query - Return all items, if one item matches query

Admin 1
Viewed 195

I have 2 models, Recipe and Ingredient.

Basically I have a search function, where ideally when you search for an ingredient, it will return the recipes that uses said ingredient.

I manage to get the query working with the below. The only problem is, it only returns the ingredients that matches the query. I'd like for it to return all ingredients in the Recipe, if one of the ingredients matches the query. How would I adapt the below to do that?

For example if I search for tomato, I will get recipes with tomatoes win them, but the returned ingredients only includes tomatoes, but not the rest of the ingredients.

const recipes = await Recipe.findAll({
      include: {
        model: Ingredient,
        where: {
          name: {
            [Op.iLike]: `%${ingredients}%`,
          },
        },
      },
});

Thanks in advance!

sequelize.js
2 Answers

You can define two associations from Recipe to Ingredient with different aliases: one alias for filtering and another one for fetching all Ingredients like this:

Recipe.hasMany(Ingredient, { foreignKey: 'recipeId', as: 'Ingredients' })
Recipe.hasMany(Ingredient, { foreignKey: 'recipeId', as: 'FilteringIngredients' })
...
const recipes = await Recipe.findAll({
      include: [{
        model: Ingredient,
        as: 'FilteringIngredients',
        where: {
          name: {
            [Op.iLike]: `%${ingredients}%`,
          },
        },
      }, {
        model: Ingredient,
        as: 'Ingredients',
        separate: true
      }],
});
user_13766180

I had to use the same trick as the one provided by @Anatoly and it works perfectly!

The thing is, it also returns a field FilteringIngredients with the matched element inside. To those who want to remove this field from the returned object, it is possible to add a attributes: [] as follows :

await Recipe.findAll({
  include: [{
    model: Ingredient,
    as: "FilteringIngredients",
    where: { /* my where clause */ },
    attributes: [],
  },
  {
    model: Ingredient,
    as: "Ingredients",
    separate: true,
  }]
})
user_121943860
Related