incorrect field casing

[Joshua Chan]

I have tables for users, groups, and roles, and I have a table to join all three. I had to enforce the field name on the join table, which solved most of my errors. However, I still have one.

Despite having explicitly set the column name in my join model, when I execute

User.find({ where: {google: profile.sub},
                    include: [ { model: UserXGroupXRole, include: [ Group, Role ] } ] })

The following sql is generated

SELECT "User".*, "UserXGroupXRoles"."id" AS "UserXGroupXRoles.id", "UserXGroupXRoles"."userId" AS "UserXGroupXRoles.userId", "UserXGroupXRoles"."groupId" AS "UserXGroupXRoles.groupId", "UserXGroupXRoles"."roleId" AS "UserXGroupXRoles.roleId", "UserXGroupXRoles"."createdAt" AS "UserXGroupXRoles.createdAt", "UserXGroupXRoles"."updatedAt" AS "UserXGroupXRoles.updatedAt", "UserXGroupXRoles"."RoleId" AS "UserXGroupXRoles.RoleId", "UserXGroupXRoles"."UserId" AS "UserXGroupXRoles.UserId", "UserXGroupXRoles.Group"."id" AS "UserXGroupXRoles.Group.id", "UserXGroupXRoles.Group"."name" AS "UserXGroupXRoles.Group.name", "UserXGroupXRoles.Group"."createdAt" AS "UserXGroupXRoles.Group.createdAt", "UserXGroupXRoles.Group"."updatedAt" AS "UserXGroupXRoles.Group.updatedAt", "UserXGroupXRoles.Role"."id" AS "UserXGroupXRoles.Role.id", "UserXGroupXRoles.Role"."name" AS "UserXGroupXRoles.Role.name", "UserXGroupXRoles.Role"."createdAt" AS "UserXGroupXRoles.Role.createdAt", "UserXGroupXRoles.Role"."updatedAt" AS "UserXGroupXRoles.Role.updatedAt"

FROM (SELECT "User"."id", "User"."name", "User"."dateOfBirth", "User"."email", "User"."phone", "User"."google", "User"."createdAt", "User"."updatedAt" FROM "user" AS "User" WHERE "User"."google" = '.....' LIMIT 1) AS "User"
 LEFT OUTER JOIN "userXgroupXrole" AS "UserXGroupXRoles" ON "User"."id" = "UserXGroupXRoles"."UserId"
 LEFT OUTER JOIN "group" AS "UserXGroupXRoles.Group" ON "UserXGroupXRoles"."id" = "UserXGroupXRoles.Group"."id"
 LEFT OUTER JOIN "role" AS "UserXGroupXRoles.Role" ON "UserXGroupXRoles"."id" = "UserXGroupXRoles.Role"."id";

The problem is "UserXGroupXRoles"."UserId", which for some reason is using the wrong case for userId.

How can I make this camel case to mach the field name?

By the way, if there is some default convention for me to abide by, I'll gladly use it.

-Josh

[Mick Hansen]

Can you show your relationship definitions? You might need to define foreignKey and otherKey on the BTM relations.

--

Mick Hansen

@mhansendev
mhansen.io

[Joshua Chan]

Here's my UserXGroupXRole model

'use strict';
module.exports = function(sequelize, DataTypes) {
  var UserXGroupXRole = sequelize.define('UserXGroupXRole', {
    userId: {
      type: DataTypes.INTEGER,
      field: "userId",
      references: "user",
      referencesKey: "id",
      allowNull: false
    },
    groupId: {
      type: DataTypes.INTEGER,
      field: "groupId",
      references: "group",
      referencesKey: "id",
      allowNull: false
    },
    roleId: {
      type: DataTypes.INTEGER,
      field: "roleId",
      references: "role",
      referencesKey: "id",
      allowNull: false
    }
  },{
    classMethods: {
      associate: function(models) {
        // associations can be defined here
        UserXGroupXRole.hasOne(models.User, { foreignKey: 'id' })
        UserXGroupXRole.hasOne(models.Group, { foreignKey: 'id' })
        UserXGroupXRole.hasOne(models.Role, { foreignKey: 'id' })
      }
    },
    freezeTableName: true,
    tableName:'userXgroupXrole'
  })


  return UserXGroupXRole;
};

And here's my User model

'use strict';
module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define('User', {
    name: {
      allowNull: false,
      type: DataTypes.STRING
    },
    dateOfBirth: DataTypes.DATEONLY,
    email: {
      allowNull: false,
      type: DataTypes.STRING,
      validate:{
        isEmail: true
      }
    },
    phone: DataTypes.CHAR(10),
    google: DataTypes.STRING
  }, {
    classMethods: {
      associate: function(models) {
        // associations can be defined here
        User.hasMany(models.UserXGroupXRole);
      }
    },
    freezeTableName: true,
    tableName:'user'
  });


  return User;
};

[Mick Hansen]

I don't think you meant to use hasOne here, you probably want to use belongsTo instead, and then have the foreignKey actually point at the foreign key attribute ('userId', 'groupId', etc respecticely)

[Joshua Chan]

I just changed my UserXGroupXRole model to

        UserXGroupXRole.belongsTo(models.User, { foreignKey: 'userId' })

        UserXGroupXRole.belongsTo(models.Group, { foreignKey: 'groupId' })

        UserXGroupXRole.belongsTo(models.Role, { foreignKey: 'roleId' })

      }

    },

    freezeTableName: true,

    tableName:'userXgroupXrole'

  })

  return UserXGroupXRole;

};

But I get the same error 

column UserXGroupXRoles.UserId does not exist

It generated the same sql as before too

[Mick Hansen]

Try defining foreignKey on hasMany aswell, there might be an execution order issue.