CODEWITHSUNDEEP
sequelize.js
Oscar Reyes
Oscar Reyes

Reputation: 4342

sequelize: association is referencing to wrong foreignKey column name

I'm struggling with this problem for hours, i cannot seem to find a fix to this confusing error. I am trying to make a query with joined table and show result with nested object.

So the tables are like this: blogs table is associated to accounts table through foreign key accounts_id on blogs table, this seems to be an easy join query, but i cannot make sequelize use the right foreign key column name as it thinks the foreign key is called as account_id

The models are defined this way:

accounts: 

module.exports = function(sequelize, DataTypes) {
    return sequelize.define('accounts', {
        id: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        username: {
            type: DataTypes.STRING,
            allowNull: false
        },
        ............
    }, {
        tableName: 'accounts',
        timestamps: false,
        freezeTableName: true
    });

blogs:

module.exports = function(sequelize, DataTypes){
    var model = sequelize.define("blogs", {
        id: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        title: {
            type: DataTypes.STRING,
            allowNull: false,
        },
        content: {
            type: DataTypes.STRING,
            allowNull: false
        },
        accounts_id: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        ............
    }, {
        tableName: "blogs",
        timestamps: false,
        underscored: true
    });

    model.belongsTo(sequelize.models.accounts);
    sequelize.models.accounts.hasMany(model, {targetKey: "accounts_id"});

    return model;
};

and the query is like this:

var criteria = {
    include: [$db.models.accounts]
};

return model.findAll(criteria);

but it throws Error: ER_BAD_FIELD_ERROR: Unknown column 'blogs.account_id' in 'field list'

Upvotes: 3

Views: 4281

Answers (2)

Ciro Santilli OurBigBook.com
Ciro Santilli OurBigBook.com

Reputation: 382502

You have to set both hasMany({ sourceKey and belongsTo({ targetKey

As of sequelize 6.14.0 to get custom join columns you should do something like:

const Country = sequelize.define('Country', {
  country_name: { type: DataTypes.STRING, unique: true },
});
const City = sequelize.define('City', {
  parent_country: { type: DataTypes.STRING },
  city_name: { type: DataTypes.STRING },
});
Country.hasMany(City, { foreignKey: 'parent_country', sourceKey: 'country_name' } )
City.belongsTo(Country, { foreignKey: 'parent_country', targetKey: 'country_name' } )

Full runnable example at: Sequelize targetKey not working

Upvotes: 1

Keval
Keval

Reputation: 3326

has many doesnt supports target key option https://github.com/sequelize/sequelize/issues/4258

Can you try, point foregin key in both

    model.belongsTo(sequelize.models.accounts, {foreignKey: "accounts_id"});
    sequelize.models.accounts.hasMany(model, {foreignKey: "accounts_id"});

Upvotes: 6

Related Questions