The foreign key connection field is queried twice

Question

I have two tables, one about articles and the other about comments

//this is the table model of articles
module.exports = (app) => {
    const { STRING, INTEGER, DATE } = app.Sequelize;

    const Model = app.model.define(
        "all_topics",
        {
            id: { type: INTEGER, primaryKey: true, autoIncrement: true },
            title: STRING(30),
            content: STRING,
            user_id: INTEGER,
            created_at: DATE,
            updated_at: DATE,
            tag: STRING(30),
        },
        {
            underscored: true,
        }
    );

    Model.associate = function () {
        Model.belongsTo(app.model.Users, { foreignKey: "user_id" });
        Model.hasMany(app.model.Comments, {
            foreignKey: "topic_id",
            sourceKey: "id",
        });
    };

    return Model;
};

//this is the table model of comments
module.exports = (app) => {
    const { STRING, INTEGER, DATE } = app.Sequelize;

    const Model = app.model.define(
        "comments",
        {
            id: { type: INTEGER, primaryKey: true, autoIncrement: true },
            topic_id: INTEGER,
            user_id: INTEGER,
            parent_id: INTEGER,
            content: STRING,
            created_at: DATE,
        },
        {
            timestamps: false,
            freezeTableName: true,
            underscored: true,
        }
    );

    Model.associate = function () {
        Model.belongsTo(app.model.Users, {
            foreignKey: { user_id: "id", parent_id: "id" },
        });
        Model.belongsTo(app.model.AllTopics, {
            foreignKey: "topic_id",
            sourceKey: "id",
        });
    };

    return Model;
};

//Query the article that specifies the id and the comments it contains
const Service = require("egg").Service;

class TopicLoadService extends Service {
    async topicLoad(id) {
        const { ctx } = this;
        const topic = await ctx.model.AllTopics.findOne({
            where: {
                id,
            },
            include: [{ model: ctx.model.Comments }],
        });
        return { topic };
    }
}

module.exports = TopicLoadService;

Foreign keys have been set up in the MySQL database But, an error was returned: Unknown column 'comments.userId' in 'field list'

The query statement it executes is

SELECT `all_topics`.`id`, 
       `all_topics`.`title`, 
       `all_topics`.`content`, 
       `all_topics`.`user_id`, 
       `all_topics`.`created_at`, 
       `all_topics`.`updated_at`, 
       `all_topics`.`tag`, 
       `comments`.`id` AS `comments.id`, 
       `comments`.`topic_id` AS `comments.topic_id`, 
       `comments`.`user_id` AS `comments.user_id`,       //Queried
       `comments`.`parent_id` AS `comments.parent_id`, 
       `comments`.`content` AS `comments.content`,     
       `comments`.`created_at` AS `comments.created_at`, 
       `comments`.`user_id` AS `comments.userId`       //Why is this another query?
FROM `all_topics` AS `all_topics` 
LEFT OUTER JOIN `comments` AS `comments` 
ON `all_topics`.`id` = `comments`.`topic_id` 
WHERE `all_topics`.`id` = '20000000';

I am a novice, some knowledge is not mastered, can you answer for me? Thank you very much

user_332988 · Answer

A couple of points to note:

The foreignKey property usually takes a string representing the column name of the foreign key. There's another property that's either called sourceKey or targetKey, depending on whether it's a has or belongs relationship lacking a junction table, that represents the referenced key.
sequelize usually has singular names for the names of the models, and constructs plural names for the names of tables in the database. It's a good convention to follow, since the model instances will end up with helper methods like Foo.addBar and Foo.addBars for creating new child rows in the database. See the sequelize docs for more information.
There were some missing properties in the model definitions. They might not be strictly necessary to get things working, but this way your models will more closely map to their corresponding structures in the database.

Try this:

// articles
module.exports = (app) => {
    const { STRING, INTEGER, DATE } = app.Sequelize;

    const Model = app.model.define(
        "all_topic", // Following sequelize convention, it might be better to have the model name be singular,
                     //  i.e. "all_topic", instead of "all_topics"....
        {
            id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
            title: { type: STRING(30), allowNull: false },
            content: { type: STRING, defaultValue: null },
            user_id: { type: INTEGER, references: { model: app.model.Users, key: 'id' }, allowNull: false },
            created_at: { type: DATE, defaultValue: null, allowNull: true },
            updated_at: { type: DATE, defaultValue: null, allowNull: true },
            tag: { type: STRING(30), allowNull: false },
        },
        {
            tableName: "all_topics",
            underscored: true
        }
    );

    Model.associate = function () {
        Model.belongsTo(app.model.Users, {
            foreignKey: "user_id",
            targetKey: "id",
            onDelete: "RESTRICT",
            onUpdate: "RESTRICT"
        });
        Model.hasMany(app.model.Comments, {
            foreignKey: "topic_id",
            sourceKey: "id",
            onDelete: "RESTRICT",
            onUpdate: "RESTRICT"
        });
    };

    return Model;
};

And for the comments model:

// comments
module.exports = (app) => {
    const { STRING, INTEGER, DATE } = app.Sequelize;

    const Model = app.model.define(
        "comment", // Following sequelize convention, it might be better to have the model name be singular,
                   //  i.e. "comment", instead of "comments"....
        {

            id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
            topic_id: { type: INTEGER, allowNull: false },
            user_id: { type: INTEGER, allowNull: false, references: { model: app.model.Users, key: 'id' } },
            parent_id: { type: INTEGER, allowNull: false, references: { model: app.model.Users, key: 'id' } },
            topic_id: { type: INTEGER, allowNull: false, references: { model: app.model.AllTopics, key: 'id' } },
            content: { type: STRING, allowNull: false },
            created_at: { type: DATE, allowNull: false },
        },
        {
            timestamps: false
        }
    );

    Model.associate = function () {
        Model.belongsTo(app.model.Users, {
            foreignKey: "user_id",
            targetKey: "id",
            as: "user",
            onDelete: "RESTRICT",
            onUpdate: "RESTRICT"
        });
        Model.belongsTo(app.model.Users, {
            foreignKey: "parent_id",
            targetKey: "id",
            as: "parent",
            onDelete: "RESTRICT",
            onUpdate: "RESTRICT"
        });
        Model.belongsTo(app.model.AllTopics, {
            foreignKey: "topic_id",
            sourceKey: "id",
            onDelete: "RESTRICT",
            onUpdate: "RESTRICT"
        });
    };

    return Model;
};

Just a quick note about sequelize timestamps. It is possible to enable timestamps and provide custom names for the columns. According to the docs:

It is also possible to enable only one of createdAt/updatedAt, and to provide a custom name for these columns:

class Foo extends Model {}
Foo.init({ /* attributes */ }, {  
  sequelize,

  // don't forget to enable timestamps!
  timestamps: true,

  // I don't want createdAt
  createdAt: false,

  // I want updatedAt to actually be called updateTimestamp  
  updatedAt: 'updateTimestamp' });

The foreign key connection field is queried twice

1 Answers