How to configure sequelize to return embedded array in a one-to-many association?

Question

here is the deal:

I have two models - User and House
User has many houses
House belongs to a User

I've entered some dummy data and created a minimal express app to serve an api
I want to return a list of users and include each users houses.

the api endpoint looks like this:

router.get('/users', function(req, res,next) {
  models.User.findAll({
      include: [ models.House ],
      raw: true // returns result-set as a clean json...
  })
  .then(function(users) {
      d('num users found: ' + users.length);
      res.json(users);
  })
  .catch(function(err){
      d('DB ERROR: '+err.message);
      next(err);
  });

});

The default behavior of Sequelize is to return multiple instances of the same User data, each time with a different house data like so:

[
  {
    id: 1,
    first_name: "aa",
    last_name: "aaaaaa",
    email: "aa@aa.aa",
    Houses.id: 1,
    Houses.description: "house 1 desc",
    Houses.user_id: 1
  },
  {
    id: 1,
    first_name: "aa",
    last_name: "aaaaaa",
    email: "aa@aa.aa",
    Houses.id: 3,
    Houses.description: "house 3 desc",
    Houses.user_id: 1
  },
  {
    id: 2,
    first_name: "bb",
    last_name: "bbbbbb",
    email: "bb@bb.bb",
    Houses.id: 2,
    Houses.description: "house 2",
    Houses.user_id: 2
  }
]

Can I get an array of houses embedded in the owning user?
Is there a way to configure Sequelize to return the data like so:

[
  {
    id: 1,
    first_name: "aa",
    last_name: "aaaaaa",
    email: "aa@aa.aa",
    Houses:[
       {
          id: 1,
          description: "house 1 desc",
          user_id: 1
       },
       {
          id: 3,
          description: "house 3 desc",
          user_id: 1
       },
    ]
  },

  {
    id: 2,
    first_name: "bb",
    last_name: "bbbbbb",
    email: "bb@bb.bb",
    Houses:[
       {
          id: 2,
          description: "house 2",
          user_id: 2
       }
    ]
  }
]

If the answer is no, can you recommend of a way to parse the data into such a structure?

The User Model:

module.exports = function(sequelize, DataTypes) {

  var User = sequelize.define('User', {

        first_name: DataTypes.STRING,
        last_name: DataTypes.STRING,
        email: DataTypes.STRING

    }, 
    {
      classMethods: {
        associate: function(models) {
          User.hasMany(models.House)
        }
      }
  });

  return User;
};

The house model:

module.exports = function(sequelize, DataTypes) {
  var House = sequelize.define("House", {
      description: DataTypes.TEXT
  }, 
  {
    classMethods: {
      associate: function(models) {
        House.belongsTo(models.User);
      }
    }
  });

  return House;
};

Thank you @AlessandroLoziobizBisi !! I've just added the Models, let me know if I can provide anything else. — Ajar, Feb 27 '16 at 17:23

Cordon Davies · Answer 1 · 2017-01-26T22:26:00.870

8

I had a similar problem and just found a solution that seems to work in my case:

router.get('/users', function(req, res, next) {
  models.User.findAll({
      include: [ {model: models.House, as: 'houses'} ],
      raw: false // returns result-set as sequelize object...
  })
  .then(function(users) {
      users = users.get(); //turns sequelize object into json
      d('num users found: ' + users.length);
      res.json(users);
  })
  .catch(function(err){
      d('DB ERROR: '+err.message);
      next(err);
  });

});

Set raw to false (or let it default to that), then, anytime you need to get the raw json before the res.json, use users.get() ("users" now being a sequelize instance) and you can get the json of that sequelize object that is now an object with an array of its associations!

edited Jan 26 '17 at 22:26

answered Jan 26 '17 at 20:35

Cordon Davies

211
2
11

users.get() is showing typeerror. It is not a function :( – Rohit Kumar May 26 '20 at 06:25
1

It seems that putting raw: false instead of raw:true solves the problem, but I haven't expected that behavior from raw option. As I understood, that option should just return you pure response from database instead of sequelize Object with all metadata information. – milosdju Jul 19 '20 at 21:49

SANTHOSH.SJ · Answer 2 · 2021-06-24T10:24:14.313

2

 models.User.findAll({
      include: [ models.House ],
      nest: true
  })

add nest attribute to your query
remove raw true

for more related to issue
github-sequelize-issue 3885
stackoverflow-questions-61532849

edited Jun 24 '21 at 10:24

answered Jun 24 '21 at 09:02

SANTHOSH.SJ

343
1
4
7

Thanks, man, Worked for me and saved a lot of time! – Raj Jan 27 '22 at 13:19

score 0 · Answer 3 · answered Feb 27 '16 at 22:40

0

try adding 'as' option to the User->House 1:n relationship

module.exports = function(sequelize, DataTypes) {

      var User = sequelize.define('User', {

            first_name: DataTypes.STRING,
            last_name: DataTypes.STRING,
            email: DataTypes.STRING

        }, 
        {
          classMethods: {
            associate: function(models) {
              User.hasMany(models.House, {as: 'houses'})
            }
          }
      });

      return User;
    };

and update the query accordingly

router.get('/users', function(req, res,next) {
  models.User.findAll({
      include: [ {model: models.House, as: 'houses'} ],
      raw: true // returns result-set as a clean json...
  })
  .then(function(users) {
      d('num users found: ' + users.length);
      res.json(users);
  })
  .catch(function(err){
      d('DB ERROR: '+err.message);
      next(err);
  });

});

answered Feb 27 '16 at 22:40

Alessandro Loziobiz Bisi

346
1
8

Thanks again @Alessandro Loziobiz Bisi Unfortunately, after adding , {as: 'houses'} like you suggested in both the User model and the api query I got the following error message - Error: House is not associated to User! Which is strange because it is associated :( Any ideas? – Ajar Feb 28 '16 at 12:33
It's quite strange! Are you sure you have used the same value? Because this is the error thrown when you specify an alias in the model and you omit or change it in the query... – Alessandro Loziobiz Bisi Feb 28 '16 at 13:44
Indeed strange... I went over it again and again and there was one thing I missed but still I get - " House (houses) is not associated to User! " Pay attention to the fact I also define an association on the House model - House.belongsTo(models.User); - I don't know if it has something to do with it... should I modify it as well in some fashion? – Ajar Feb 28 '16 at 14:42
update - now it seems to work with no errors... strange... the models are still wired the same way and the api query is the same... maybe an internal cache of some sort...? in any case - the final behavior doesn't solve the original problem - instead of returning House.id , House.description etc... it returns the alias name - houses.id, houses.description and so on... – Ajar Feb 28 '16 at 16:57

How to configure sequelize to return embedded array in a one-to-many association?

3 Answers3