Why does Sequelize add extra columns to SELECT query?

Question

When i want to get some records with joined data from the referenced tables, Sequelize adds the reference columns twice: the normal one and a copy of them, written just a little bit different.

This is my model:

module.exports = function(sequelize, DataTypes) {
return sequelize.define('result', {
id: {
  type: DataTypes.INTEGER(10),
  allowNull: false,
  primaryKey: true,
  autoIncrement: true
},
test_id: {
  type: DataTypes.INTEGER(10),
  allowNull: false,
  references: {
    model: 'test',
    key: 'id'
  }
},
item_id: {
  type: DataTypes.INTEGER(10),
  allowNull: false,
  references: {
    model: 'item',
    key: 'id'
    }
 },
}, // and many other fields
{
tableName: 'result',
timestamps: false, // disable the automatic adding of createdAt and    updatedAt columns
underscored:true
});
}

In my repository I have a method, which gets the result with joined data. And I defined the following associations:

const Result = connection.import('../../models/storage/result');
const Item = connection.import('../../models/storage/item');
const Test = connection.import('../../models/storage/test');

Result.belongsTo(Test, {foreignKey: 'test_id'});
Test.hasOne(Result);

Result.belongsTo(Item, {foreignKey: 'item_id'});
Item.hasOne(Result);

// Defining includes for JOIN querys
var include = [{
model: Item,
attributes: ['id', 'header_en']
}, {
model: Test,
attributes: ['label']
}];

var getResult = function(id) {

    return new Promise((resolve, reject) => { // pass result
        Result.findOne({
            where: { id : id },
            include: include,
        //     attributes: ['id',
        // 'test_id',
        // 'item_id',
        // 'result',
        // 'validation'
        // ]
        }).then(result => {
            resolve(result);
            });
    }); 
}

The function produces the following query:

SELECT `result`.`id`, `result`.`test_id`, `result`.`item_id`,  `result`.`result`, `result`.`validation`, `result`.`testId`, `result`.`itemId`, `item`.`id` AS `item.id`, `item`.`title` AS `item.title`, `test`.`id` AS `test.id`, `test`.`label` AS `test.label` FROM `result` AS `result` LEFT OUTER JOIN `item` AS `item` ON `result`.`item_id` = `item`.`id` LEFT OUTER JOIN `test` AS `test` ON `result`.`test_id` = `test`.`id` WHERE `result`.`id` = '1';

Notice the extra itemId, testId it wants to select from the result table. I don't know where this happens. This produces:

Unhandled rejection SequelizeDatabaseError: Unknown column 'result.testId' in 'field list'

It only works when i specify which attributes to select.

EDIT: my tables in the database already have references to other tables with item_id and test_id. Is it then unnecessary to add the associations again in the application code like I do?

A result always has one item and test it belongs to.

How can i solve this?

Thanks in advance,

Mike

Answer 1

SOLUTION:

Result.belongsTo(Test, {foreignKey: 'test_id'});
// Test.hasMany(Result);

Result.belongsTo(Item, {foreignKey: 'item_id'});
// Item.hasOne(Result);

Commenting out the hasOne, hasMany lines did solve the problem. I think I messed it up by defining the association twice. :|