I'm creating a recipe-database (monly known as a cookbook) where I need to have a many-to-many relationship between ingredients and recipes and I'm using sequelize.js in bination with postgresql.

When an ingredient is added to a recipe I need to declare the correct amount of that ingredient that goes into the recipe.

I've declared (reduced example)

var Ingredient = sequelize.define('Ingredient', {
    name: Sequelize.STRING
}, {
    freezeTable: true
});

var Recipe = sequelize.define('Recipe', {
    name: Sequelize.STRING
}, {
    freezeTable: true
});

var RecipeIngredient = sequelize.define('RecipeIngredient', {
    amount: Sequelize.DOUBLE
});

Ingredient.belongsToMany(Recipe, { through: RecipeIngredient });
Recipe.belongsToMany(Ingredient, {
    through: RecipeIngredient,
    as: 'ingredients'
});

My problem is with how data is returned when one my REST endpoints do

router.get('/recipes', function(req, res) {
    Recipe.findAll({
        include: [{
            model: Ingredient,
            as: 'ingredients'
         }]
    }).then(function(r) {
        return res.status(200).json(r[0].toJSON());
    })
});

The resulting JSON that gets sent to the client looks like this (timestamps omitted):

{
  "id": 1,
  "name": "Carrots",
  "ingredients": [
    {
      "id": 1,
      "name": "carrot",
      "RecipeIngredient": {
        "amount": 12,
        "RecipeId": 1,
        "IngredientId": 1
      }
    }
  ]
}

While all I wanted was

{
  "id": 1,
  "name": "Carrots",
  "ingredients": [
    {
      "id": 1,
      "name": "carrot",
      "amount": 12,
    }
  ]
}

That is, I want the amount field from the relation-table to be included in the result instead of the entire RecipeIngredient object.

The database generated by sequelize looks like this:

Ingredients
id  name
1   carrot

Recipes
id  name
1   Carrots

RecipeIngredients
amount  RecipeId  IngredientId
12      1         1

I've tried to provide an attributes array as a property to the include like this:

include: [{
    model: Ingredient,
    as: 'ingredients',
    attributes: []
 }]

But setting either ['amount'] or ['RecipeIngredient.amount'] as the attributes-value throws errors like

Unhandled rejection SequelizeDatabaseError: column ingredients.RecipeIngredient.amount does not exist

Obviously I can fix this in JS using .map but surely there must be a way to make sequelize do the work for me?

I'm creating a recipe-database (monly known as a cookbook) where I need to have a many-to-many relationship between ingredients and recipes and I'm using sequelize.js in bination with postgresql.

When an ingredient is added to a recipe I need to declare the correct amount of that ingredient that goes into the recipe.

I've declared (reduced example)

var Ingredient = sequelize.define('Ingredient', {
    name: Sequelize.STRING
}, {
    freezeTable: true
});

var Recipe = sequelize.define('Recipe', {
    name: Sequelize.STRING
}, {
    freezeTable: true
});

var RecipeIngredient = sequelize.define('RecipeIngredient', {
    amount: Sequelize.DOUBLE
});

Ingredient.belongsToMany(Recipe, { through: RecipeIngredient });
Recipe.belongsToMany(Ingredient, {
    through: RecipeIngredient,
    as: 'ingredients'
});

My problem is with how data is returned when one my REST endpoints do

router.get('/recipes', function(req, res) {
    Recipe.findAll({
        include: [{
            model: Ingredient,
            as: 'ingredients'
         }]
    }).then(function(r) {
        return res.status(200).json(r[0].toJSON());
    })
});

The resulting JSON that gets sent to the client looks like this (timestamps omitted):

{
  "id": 1,
  "name": "Carrots",
  "ingredients": [
    {
      "id": 1,
      "name": "carrot",
      "RecipeIngredient": {
        "amount": 12,
        "RecipeId": 1,
        "IngredientId": 1
      }
    }
  ]
}

While all I wanted was

{
  "id": 1,
  "name": "Carrots",
  "ingredients": [
    {
      "id": 1,
      "name": "carrot",
      "amount": 12,
    }
  ]
}

That is, I want the amount field from the relation-table to be included in the result instead of the entire RecipeIngredient object.

The database generated by sequelize looks like this:

Ingredients
id  name
1   carrot

Recipes
id  name
1   Carrots

RecipeIngredients
amount  RecipeId  IngredientId
12      1         1

I've tried to provide an attributes array as a property to the include like this:

include: [{
    model: Ingredient,
    as: 'ingredients',
    attributes: []
 }]

But setting either ['amount'] or ['RecipeIngredient.amount'] as the attributes-value throws errors like

Unhandled rejection SequelizeDatabaseError: column ingredients.RecipeIngredient.amount does not exist

Obviously I can fix this in JS using .map but surely there must be a way to make sequelize do the work for me?

• javascript
• node.js
• postgresql
• sequelize.js

Share Improve this question Follow asked Dec 28, 2015 at 16:25 ivarniivarni 17.9k1717 gold badges7878 silver badges9393 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 2

I am way late to this one, but i see it been viewed quite a bit so here is my answer on how to merge attributes

Some random examples in this one

router.get('/recipes', function(req, res) {
Recipe.findAll({
    include: [{
        model: Ingredient,
        as: 'ingredients',
        through: {
            attributes: ['amount']
        }
     }]
})
.then(docs =>{
    const response = {
        Deal: docs.map(doc =>{
            return{
                cakeRecipe:doc.recipe1,
                CokkieRecipe:doc.recipe2,
                Apples:doc.ingredients.recipe1ingredient
                spices:[
                    {
                     sugar:doc.ingredients.spice1,
                     salt:doc.ingredients.spice2
                    }
                ]

            }
        })
    }

})
res.status(200).json(response)

})

You can use sequelize.literal. Using Ingredient alias of Recipe, you can write as follows. I do not know if this is the right way. :)

[sequelize.literal('`TheAlias->RecipeIngredient`.amount'), 'amount'],

I tested with sqlite3. Received result with alias "ir" is

{ id: 1,
  name: 'Carrots',
  created_at: 2018-03-18T04:00:54.478Z,
  updated_at: 2018-03-18T04:00:54.478Z,
  ir: [ { amount: 10, RecipeIngredient: [Object] } ] }

See the full code here.

https://github./eseom/sequelize-cookbook

I've gone over the documentation but I couldn't find anything that seems like it would let me merge the attributes of the join-table into the result so it looks like I'm stuck with doing something like this:

router.get('/recipes', function(req, res) {
    Recipe.findAll({
        include: [{
            model: Ingredient,
            as: 'ingredients',
            through: {
                attributes: ['amount']
            }
         }]
    }).then(function(recipes) {
        return recipes[0].toJSON();
    }).then(function(recipe) {
        recipe.ingredients = recipe.ingredients.map(function(i) {
            i.amount = i.RecipeIngredient.amount;
            delete i.RecipeIngredient;
            return i;
        });
        return recipe;
    }).then(function(recipe) {
        return res.status(200).json(recipe);
    });
});

Passing through to include lets me filter out which attributes I want to include from the join-table but for the life of me I could not find a way to make sequelize merge it for me.

The above code will return the output I wanted but with the added overhead of looping over the list of ingredients which is not quite what I wanted but unless someone es up with a better solution I can't see another way of doing this.