Sequelize save Data in belongsToMany created Table and Read from it(Solution at the End)

Question

I am using for my Backend Node.js,Express.js and Sequelize for the connection to the Database. I have a n:m Relationship between Tasks and Keys.

Tasks and Keys is created by me and TaskKey through Sequelize with:

Backend

    // Tasks n:m Keys
    db.DictKey.belongsToMany(db.Task, { through: 'TaskKeys' , foreignKey:'key_id'});
    db.Task.belongsToMany(db.DictKey, { through: 'TaskKeys' , foreignKey: 'task_id'});

Now if I create a new Task on the

frontend

$scope.create = () => {
    this.$http.post('/api/tasks', {
        user_id: $scope.selectUser,
        lang_id: $scope.selectLang,
        name: $scope.newTask
    });
}

I want to send with that request an Array of all Keys the User selected.

In the Backend it should add an entry to TaskKeys for the new Task for every DictKey sent. For example

Table Task:

ID | some values
1  | some values | this is the new task created

Send Keys in Array[2,5,6]

TaskKey / in the same moment create in this Table the Dependant Keys

TaskID | KeyID
1      | 2
1      | 5
1      | 6

How can I achieve that ?

After that I would like to show a Task. Taking the Example before. Get task where id = 1 ng-repeat all datas and as well get all Keys thanks to the Table TaskKey.

I could not find a Example which explains that and the only solution I have at the moment is at the front end with $http.post(taskkey) with a foreach for every key in the Table TaskKey. But later in the Live System there would be over 1000 keys, so this isn't an acceptable solution. Is there a good solution for that for backend?

Edit1:

...    working fine
$scope.create = () => {
            this.$http.post('/api/tasks', {
              task:{
              user_id: $scope.selectUser,
              lang_id: $scope.selectLang,
              name: $scope.newTask
            },
            keys:[1,2,3,4,5]
          });
    ...
 ... 
// Creates a new Task in the DB
export function create(req, res) {
  return Task.create(req.body.task)
  .then(function(task){
    return task.addTaskKeys(req.body.keys);//throws 500error
    //console.log(req.body.keys);//working fine getting the Keys
  })
    .then(respondWithResult(res, 201))
    .catch(handleError(res));
}
...

Reading the docs belongsToManyDoc doesnt help much because no Examples or Detailed Explained. Ive tried several things like:

• addTask/addTasks/addTaskKeys/addKey as in doc explained for adding an association
• createTask/createKey/createTaskKeys for creating an association

In my backend I dont have any Functions for TaskKeys only for Tasks and Keys. But as I understood I dont need any for TaskKeys because with

 // Tasks n:m Keys
    db.DictKey.belongsToMany(db.Task, { through: 'TaskKeys' , foreignKey:'key_id'});
    db.Task.belongsToMany(db.DictKey, { through: 'TaskKeys' , foreignKey: 'task_id'});  

creating Middle Table there is an association between Tasks and Keys. So normally add/create should just work and it should add instances in TaskKeys.

 export function create(req, res) {
      return Task.create(req.body.task)
      .then(function(task){
        return task.addTaskKeys(req.body.keys);//throws 500error

with task.addTaskKeys adding Associations with the just created Task=> task_id

with req.body.keys giving him the Keys =>key_id
If I take the Example from Doc change to minde DB:

... example
    Project.create({ id: 11 }).then(function (project) {
      user.addProjects([project, 12]);
    });
... mine
return Task.create(req.body.task)
  .then(function(task){
    return DictKey.addTasks([task,{key_id : 1}]);//still error
  })//DictKey.addTask(task,1); still error

Reading the BelongsToMany Association and Quoting it:

user.addProject(project, { status: 'started' }) By default the code will add projectId and userId to the UserProjects table

Trying out creating with Associations:

http://sequelize.readthedocs.io/en/latest/docs/associations/#creating-elements-of-a-hasmany-or-belongstomany-association Sorry not allowed to post more as 1 Link.

export function create(req, res) {
  return Task.create({
    name: req.body.task.name,
    user_id: req.body.task.user_id,
    lang_id: req.body.task.lang_id,
    key_id:[req.body.keys]
  },{
    include: [DictKey]
  })

No Error but only Creating Task not TaskKeys.... So what Iam doing wrong whole time?

Edit2: For Testing I inserted Data in the Table TaskKeys by Myself in MsSql. TaskKey / Self Created Datas with SqlQuery

TaskID | KeyID
1      | 1
1      | 2
1      | 3
2      | 4
2      | 5

getting it from my frontend

this.$http.get('/api/tasks/' +2 )
            .then(response => {
              this.Tasks = response.data;
              console.log(this.Tasks);
            });

backend

// Gets a single Task from the DB
export function show(req, res) {
  return Task.findAll({
    where: {
      _id: req.params.id
    },
    include: [{
      model: DictKey
    }]
  })
    .then(handleEntityNotFound(res))
    .then(respondWithResult(res))
    .catch(handleError(res));
}

ConsoleOutput:

[Object]
Object
_id:2 // the searched TaskId= 2 => right
dict_Keys:Array[2] // the Dependant 2 KeyIds => right

So the table which is created is working fine. The Question is now only why adding through Backend with addKey is not working by doing the same as in the Example. Trying the Example to check Associations:

// Creates a new Task in the DB
export function create(req, res) {
  Task.create({
  name: req.body.task.name,
  user_id: req.body.task.user_id,
  lang_id: req.body.task.lang_id
}).then(function(task) {
    return DictKey.create({ name:req.body.task.name,notice:req.body.task.name, user_id:req.body.task.user_id })
    .then(function(key) {
      return task.hasDictKey(key).then(function(result) {
        // result would be false
        return task.addDictKey(key).then(function() {
          return task.hasDictKey(key).then(function(result) {
            // result would be true
          })
        })
      })
    })
  })

Throws task.hasDictKey is not a function

db.Task.create({
  name: 'test',
  user_id: 266,
  lang_id: 9,
  key_id:[1,2]
},{
  include: [db.DictKey]
})

Throws dict_Keys is not associated to Task So it means they are not Connected together? BUt Reading data and Inserting Data in the Database is working?

Just Adding now the Example of Sequelize:

var Usert = db.sequelize.define('usert', {})
var Project = db.sequelize.define('project', {})
var UserProjects = db.sequelize.define('userProjects', {
    status: Sequelize.STRING
})

Usert.belongsToMany(Project, { through: UserProjects })
Project.belongsToMany(Usert, { through: UserProjects })
Usert.addProject(Project, { status: 'started' })//addProject is not a Function // for real ? now not even their own is not working? :/

Answer
By Reading the Docs I thought with

// Tasks n:m Keys
db.DictKey.belongsToMany(db.Task, { through: TaskKeys , foreignKey:'key_id',otherKey:'task_id'});
db.Task.belongsToMany(db.DictKey, { through: TaskKeys , foreignKey: 'task_id',otherKey: 'key_id'});

it would automatically generate add/set/get/create Task for DictKey and add/set/get/create DictKey for Task.

But

Naming strategy

By default sequelize will use the model name (the name passed to sequelize.define) to figure out the name of the model when used in associations.

Means it adds Functions by the name of the Table Model where you go define('dict_Keys'... so add/set/get/ Dict_Key to Task. That was the problem because I was using addDictkey instead of addDict_Key. I hope it helps someone else in the Future

Answer 1

foreignKey should be the same on both sides of the relation, to set the other key use the otherKey option.

Answer 2

Frontend: To get the data from the front end, just include the array as a property of the data you're posting. You might want to wrap the task specific properties in their own object as well:

this.$http.post('/api/tasks', {
    task: {
        user_id: $scope.selectUser,
        lang_id: $scope.selectLang,
        name: $scope.newTask
    },
    keys: [ 2, 5, 6 ]
});

Backend: Sequelize and other ORMs try to provide you with tools for using the relationships you've defined. Use them. You're already using belongsToMany, so you just need to do the next step of using your created Task object to create the m:n entries in TaskKeys:

...
return Task.create(req.body.task)
    .then(function(task) {
        return task.addProjects(req.body.keys);
    })
    .then(respondWithResult(res, 201))
    .catch(handleError(res));
...

As for getting a task with keys, just use the include option:

...
return Task.findById(req.params.id, {
    include: [ { model: db.DictKey } ]
})
...