Footprint createAssociation does not work correctly.

[cesar2064]

I have the following models: User.js:

module.exports = class User extends Model {
  static config (app,Sequelize) {
    return {
       options: {
         hooks:{
          beforeCreate: (values,options) =>{
            return app.services.Utils.createBcryptHash(values.password).then(
              (hash)=>{
                values.password = hash
              },
              (err)=>{
                app.log.error(err)
              }
            )
          }
         },
         instanceMethods:{
          toJSON: function () {
            let $user = this.dataValues;
            delete $user.password;
            return $user;
          }
         },
         classMethods: {
           //If you need associations, put them here
           associate: (models) => {
             //More information about associations here : http://docs.sequelizejs.com/en/latest/docs/associations/
             models.User.belongsToMany(models.Role, {
               as: 'roles',
               onDelete: 'CASCADE',
               through:{
                model: models.UserRole,
                unique:false
               },
               foreignKey: "user_id"
             })
           }
         }
       }
     }
  }

  static schema (app,Sequelize) {
    return {
      name: {
        type: Sequelize.STRING,
        allowNull: true,
        unique : true
      },
      email:{
        type: Sequelize.STRING,
        allowNull: false,
        unique : true,
        validate:{
          isEmail : true
        }
      },
      external:{
        type: Sequelize.BOOLEAN
      },
      password: {
        type : Sequelize.STRING,
        allowNull: false
      },
      displayName: {
        type : Sequelize.STRING,
        allowNull: true
      }
    }
  }
}

Role.js

module.exports = class Role extends Model {

  static config () {
    return {
       options: {
         classMethods: {
           //If you need associations, put them here
           associate: (models) => {
             //More information about associations here : http://docs.sequelizejs.com/en/latest/docs/associations/
             models.Role.belongsToMany(models.User, {
               as: 'users',
               onDelete: 'CASCADE',
               through:{
                model: models.UserRole,
                unique:false
               },
               foreignKey: "role_id"
             })
           }
         }
       }
     }
  }

  static schema (app,Sequelize) {
    return {
      name: {
        type: Sequelize.STRING,
        allowNull: false,
        unique:true
      }
    }
  }
}

UserRole.js

module.exports = class UserRole extends Model {

  static config () {
  }

  static schema (app,Sequelize) {
    return {
      role_id: {
        type: Sequelize.INTEGER,
        references: {
          model: 'role',
          key: 'id'
        },
        allowNull: false
      },
      user_id:{
        type: Sequelize.INTEGER,
        references:{
          model: 'user',
          key: 'id'
        },
        allowNull: false
      }
    }
  }
}

when I access /POST /api/v1/user/1/roles, body: {"name":"role"}

The application creates the role with name:role, but does not create the association (UserRole). This is the orm output:

Executing (b68a81ff-1f56-4c0e-bdb7-9c3be8215ec8): SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Executing (b68a81ff-1f56-4c0e-bdb7-9c3be8215ec8): INSERT INTO "role" ("id","name","createdAt","updatedAt") VALUES (DEFAULT,'role','2016-06-14 19:32:25.273 +00:00','2016-06-14 19:32:25.273 +00:00') RETURNING *;
Executing (b68a81ff-1f56-4c0e-bdb7-9c3be8215ec8): UPDATE "user" SET "updatedAt"='2016-06-14 19:32:25.289 +00:00' WHERE "id" = '1'
Executing (b68a81ff-1f56-4c0e-bdb7-9c3be8215ec8): COMMIT;

[jaumard]

@PlasmaPower do you mind taking a look ? As you're the one working on associations. If not I'll look at it when I can.

[cesar2064]

Also I have found the following issues: GET /api/v1/user/1/roles?id=1 found a role that does not have an association with the user with id = 1.

PUT /api/v1/user/1/roles?id=1 body:{"name":"role"}, updates a role that does not have an association with the user with id=1.

DELETE api/v1/user/1/roles?id=1, deletes a role that does not have an association with the user with id=1.

[PlasmaPower]

Sure, this is probably a problem with my code - I'll take a look, hopefully today if not tomorrow.

[PlasmaPower]

createAssociation itself wasn't implemented for belongsTo because there's no good argument for the middle table's properties. You can do that yourself though by doing three create calls. The other bugs are probably rooted in the find logic for belongsTo, I'll look into it.

[PlasmaPower]

Oh, I think I get it. If you're manually specifying the ID then it overrides the in clause needed to find it with a belongsToMany association. I'm not sure if this should be expected behavior. See https://github.com/trailsjs/trailpack-sequelize/blob/master/api/services/FootprintService.js#L263

[PlasmaPower]

Oh, I should probably be joining them in a $and clause. I'll make a PR for that now.

[PlasmaPower]

Anyone have any ideas on how to pass in values for the middle table for createAssociation if I implement that?

[cesar2064]

According sequelize docs (http://docs.sequelizejs.com/en/latest/docs/associations/#belongs-to-many-associations), I can create the second record and associates it by using this function in belongs to many associations:

user.addProject(project, { status: 'started' })

In my case it would be: Role.create({name:"role4"}).then(function(role){ User.find({id:1}).then(function(user){user.addRole(role).then(console.log)}) })

[PlasmaPower]

@cesar2064 An interesting implementation. What would happen if the middle table had a property named transaction for example?

[cesar2064]

The second parameter of the dynamic function : user.addProject(project, { status: 'started' }), has the attributes of middle table or association.

[PlasmaPower]

@cesar2064 But it also functions as the options correct?

[PlasmaPower]

@cesar2064 I think I'd prefer an implementation like user.addProject(project, { options, middleTable })

[cesar2064]

Yes, but why is necessary to indicate the middleTable?

[PlasmaPower]

In their implementation you can't have a key named transaction, hooks, individualHooks, ignoreDuplicates, validate, fields, or logging because those are all options: https://github.com/sequelize/sequelize/blob/3e5b8772ef75169685fc96024366bca9958fee63/lib/associations/belongs-to-many.js#L638

[PlasmaPower]

It would create a very confusing issue if you had a key named one of those.

[cesar2064]

What a problem.

[PlasmaPower]

I'm serious though, if your UserRole had a boolean validate you wouldn't be able to create it with that value and you'd get no error message if it could be null - if it didn't allow nulls it would just say you didn't specify it.

[cesar2064]

Yes, is the easiest way but it may be cause new weird issues.

[PlasmaPower]

@jaumard Which way would you prefer?

[jaumard]

The find should apply to the association so the child not the parent.

What are the different way and for doing what? I'm out for few days and have only my phone it's hard to follow the conversation ^^

[PlasmaPower]

@jaumard Makes sense. We're wondering how to design an API for createAssociation with a belongsTo object - it needs to create both the object and a middle/through object linking it to the parent. However, the middle object may need values. How should those be supplied? @cesar2064 is proposing putting them in with the options, as sequelize does it, but then you couldn't create a middle object with keys like validate, transaction, or ignoreDuplicates as those are all options. I'm proposing having the second object being {options, throughData}.

[jaumard]

OK I understand thanks! I think {options, throughData} look more clean and safe as I understand. But keep in mind that the first usage of FootprintService is by footprintController witch don't allow to send data for middle table so at least it should associate the tree table. If we can use footprintService alone and specify middle table data that should be great :)

[cesar2064]

I agree with @PlasmaPower solution. The problem now is how to send the data for middle table through the footprints as @jaumard said. Sorry for my bad English.

[PlasmaPower]

It would be a bit hacky, but we could have a prefix for each of the tables. Something like child_field=value&through_field=otherValue. The end user shouldn't be using the JS API directly, correct? (only through an HTTP request). In that case, it makes sense.

We could also just assume that the table doesn't have anything special in it when accessed through an HTTP request, but I'm not sure that's a good idea. I guess the end user could always just do 3 creates if necessary.

[jaumard]

Yeah we want to prevent hacky things like this ^^ for me, with HTTP, user can't add extra fields for middle table, they only can create an associations.

Users can use the JS API if they want to override the default behavior of the footprints:) like this they can do anything they want to enable this if they need it (witch is not a current case in my opinion^^)

[cesar2064]

Yes, I agree that the main function of the footprint "/POST /api/v1/user/1/roles" is to create the association and that's enough for most cases, but I also think the "relation data" are an important part of that. I don't know is only my opinion, please let me know what will you decide, because I want to know how can I continue with my implementation. Thanks for your help.

[PlasmaPower]

I've begun working on a basic implementation as suggested by @jaumard, the PR should be up soon.

[PlasmaPower]

I've created a PR to solve the main issue here (#22)

[cesar2064]

Thanks @PlasmaPower, your implementations work fine :)

[cesar2064]

When will you integrate the changes with npm?

[PlasmaPower]

@cesar2064 I'd expect @jaumard will create a new NPM version after the PRs get merged.

[cesar2064]

ok. Thanks @PlasmaPower

[cesar2064]

Hi, has the trailpack-sequelize 1.0.4 those changes?, because it still has the issues.

[PlasmaPower]

@cesar2064 It should. Can you check if it works if you replace the module with a clone from git?

[jaumard]

@PlasmaPower is there any tests on this ? I modify some things find/update/destroy on v1.0.4 but all tests was green so I didn't look associations methods

[PlasmaPower]

@jaumard Yeah all the things I fixed should be tested...

[jaumard]

@PlasmaPower cool :) so @cesar2064 try to install from the master or delete/install again your local version and let us know about this please

[PlasmaPower]

@cesar2064 If it's broken somewhere, could you do a git bisect? That'd be very helpful. I'm also looking into it.

[PlasmaPower]

Yeah the createAssociation belongsToMany test seems to be in place and working on master.

[cesar2064]

Yes in master it seems to work fine. thanks. I directly downloaded the lib from git.

[jaumard]

Cool @cesar2064 try again with the v1.0.4 because it's currently same as the master, there no pending dev on the master for the moment :) it have to work

[cesar2064]

ok, probably cache problems?

[jaumard]

Yeah or npm doesn't install it correctly the first time... it append to me sometime on some module I have to delete and install again... If it keep not working with the 1.0.4 let us know !

[cesar2064]

It works now with the npm version, thanks.

[jaumard]

Cool glad it work ! I was afraid I broke something with my last update ^^