Have issue with joining two tables with Sequelize

Question

I have two tables in my database (Users and Tasks) and I'm trying to get all the tasks for one user.

That's the error that I'm getting:

TypeError: self._expandAttributes is not a function
    at Function._conformOptions (/Users/in43sh/web-dev/projects/doit4me/node_modules/sequelize/lib/model.js:198:12)
    at Function._conformInclude (/Users/in43sh/web-dev/projects/doit4me/node_modules/sequelize/lib/model.js:264:12)
    at options.include.options.include.map.include (/Users/in43sh/web-dev/projects/doit4me/node_modules/sequelize/lib/model.js:213:59)
    at Array.map (<anonymous>)
    at Function._conformOptions (/Users/in43sh/web-dev/projects/doit4me/node_modules/sequelize/lib/model.js:213:39)
    at Promise.try.then (/Users/in43sh/web-dev/projects/doit4me/node_modules/sequelize/lib/model.js:1560:12)
    at tryCatcher (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/promise.js:694:18)
    at _drainQueueStep (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/async.js:138:12)
    at _drainQueue (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/async.js:131:9)
    at Async._drainQueues (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/async.js:147:5)
    at Immediate.Async.drainQueues [as _onImmediate] (/Users/in43sh/web-dev/projects/doit4me/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)

My Users model:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    nickname: {
      allosNull: false,
      type: DataTypes.STRING
    },
    password: {
      allosNull: false,
      type: DataTypes.STRING
    },
    email: {
      allosNull: false,
      type: DataTypes.STRING
    },
    ip: {
      allosNull: false,
      type: DataTypes.STRING
    },
    zip: {
      allosNull: false,
      type: DataTypes.INTEGER
    },
    personal: {
      allosNull: false,
      type: DataTypes.STRING
    },
    rating: {
      allosNull: false,
      type: DataTypes.FLOAT
    },
    photo: {
      allosNull: false,
      type: DataTypes.STRING
    }
  }, {
    timestamps: true,
    paranoid: true
  });
  User.associate = function(models) {
    User.hasMany(models.Task, {
      foreignKey: 'id',
      onDelete: 'CASCADE'
    })
  };
  return User;
};

My Tasks model:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Task = sequelize.define('Task', {
    title: {
      allosNull: false,
      type: DataTypes.STRING
    },
    category: {
      allosNull: false,
      type: DataTypes.STRING
    },
    creator_id: {
      allosNull: false,
      type: DataTypes.INTEGER
    },
    contractor_id: {
      allosNull: true,
      type: DataTypes.INTEGER
    },
    price: {
      allosNull: false,
      type: DataTypes.INTEGER
    },
    description: {
      allosNull: false,
      type: DataTypes.STRING
    },
    zip: {
      allosNull: false,
      type: DataTypes.INTEGER
    },
    deadline: {
      allosNull: true,
      type: DataTypes.DATE
    },
    status: {
      allosNull: true,
      type: DataTypes.STRING
    },
    photos: {
      allosNull: true,
      type: DataTypes.STRING
    },
    requests: DataTypes.STRING
  }, {
    timestamps: true,
    paranoid: true
  });
  Task.associate = function (models) {
    Task.belongsTo(models.User, {
      as: 'User',
      foreignKey: 'creator_id',
      onDelete: 'CASCADE' })
  }
  return Task;
};

The controller to get the data:

getTasksByUserId: (req, res, next) => {
  const { user_id } = req.params;
  return Task
  .findAll({
    include: [{
      model: "User",
      as: 'User',
      where: ["creator_id = user_id"]
    }]
  })
  .then(tasks => res.status(200).json({ status: 'Retrieved tasks for the user', tasks}))
  .catch(error => console.log(error));
}

The raw query to get the data works fine:

select t.title from "Tasks" t join "Users" u on t.creator_id = u.id where u.id = 1;

I guess my problem either where I'm using .findAll() with where: [].

I've found here that the problem might be with associations but nothing helped me. I've also tried this and the solution from Ryan Shillington, but couldn't fix it.

My Node version: v10.12.0 My Sequelize version: 5.3.0

On your `include` you are using `as: 'users'` but you did't define that on the association — Ellebkey, Feb 20 '19 at 15:37
I think I actually should use `as: 'User'` and the same in the model. Still, didn't work. I also updated the question. — in43sh, Feb 21 '19 at 02:22
Still, on your association definition you have it as `User` and on your include you have it `users`. This must be the same — Ellebkey, Feb 21 '19 at 02:29
I changed it, but still the same problem. It must be something else — in43sh, Feb 21 '19 at 02:35

score 1 · Accepted Answer · answered Feb 23 '19 at 02:58

I figured it out. So, both models have to have the foreign key as 'creator_id' and the controller has to be a little different. Part of User's model:

User.associate = function(models) {
    User.hasMany(models.Task, {
      as: 'Task',
      foreignKey: 'creator_id',
      onDelete: 'CASCADE'
    })
};

Part of Task's model:

Task.associate = function (models) {
    Task.belongsTo(models.User, {
      as: 'User',
      foreignKey: 'creator_id',
      onDelete: 'CASCADE'
    })
}

My controller:

getTasksByUserId: (req, res, next) => {
  const { user_id } = req.params;
  return Task
  .findAll({
    where: {creator_id: user_id}
  })
  .then(tasks => res.status(200).json({ status: 'Retrieved tasks for the user', tasks}))
  .catch(error => console.log(error));
}

Have issue with joining two tables with Sequelize

1 Answers1