Sequelize error: column reference "id" is ambiguous. Can't find answer in documentation

Question

I use Sequelize to link the database to my application. But I recently encountered the above bug. It says everywhere that it is a database error. Since both tables have the same primary key. In SQL this is solved with aliases. But in documentation to Sequelize I haven't found solution to this problem. What should I do ?

    const { DataTypes } = require("sequelize");
const db = require("../config/database");

const Office = db.define(
    "Office",
    {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true,
        },
        address: DataTypes.STRING(45),
    },
    {
        // Other model options go here
        tableName: "office",
        timestamps: false,
    }
);

const Employees = db.define(
    "Employees",
    {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true,
        },
        name: DataTypes.STRING(45),
        job: DataTypes.STRING(45),
        reg_date: DataTypes.DATEONLY,
        salary: DataTypes.DECIMAL(10, 2),
        weekend: DataTypes.INTEGER,
        office_id: { type: DataTypes.INTEGER, allowNull: false },
    },
    {
        // Other model options go here
        tableName: "employees",
        timestamps: false,
    }
);

Employees.belongsTo(Office, { foreignKey: "office_id" }); // Foreign key

const Developer = db.define(
    "Developer",
    {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true,
        },
        role: DataTypes.STRING(45),
        level: DataTypes.STRING(45),
        project_count: DataTypes.INTEGER,
    },
    {
        // Other model options go here
        tableName: "developer",
        timestamps: false,
    }
);

// Developer.belongsTo(Employees);
Employees.hasOne(Developer, { foreignKey: "id", targetKey: "id" }); // Foreign key

const Clients = db.define(
    "Clients",
    {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true,
        },
        name: DataTypes.STRING(45),
        total_sum: DataTypes.DECIMAL(20, 2),
    },
    {
        // Other model options go here
        tableName: "clients",
        timestamps: false,
    }
);

const Projects = db.define(
    "Projects",
    {
        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true,
        },
        price: DataTypes.DECIMAL(15, 2),
        started: DataTypes.DATEONLY,
        ended: DataTypes.DATEONLY,
        teamlead_id: { type: DataTypes.INTEGER, allowNull: false },
        designer_id: { type: DataTypes.INTEGER, allowNull: false },
        programmer_id: { type: DataTypes.INTEGER, allowNull: false },
        dbarch_id: { type: DataTypes.INTEGER, allowNull: false },
        client_id: { type: DataTypes.INTEGER, allowNull: false },
    },
    {
        // Other model options go here
        tableName: "projects",
        timestamps: false,
    }
);

Developer.hasOne(Projects, { foreignKey: "teamlead_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "designer_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "programmer_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "dbarch_id" }); // Foreign key
Clients.hasOne(Projects, { foreignKey: "client_id" }); // Foreign key

module.exports = { Clients, Developer, Employees, Office, Projects };

And express request:

app.get("/office_dev_workers_spec_count", (req, res) => {
    Employees.findAll({
        include: {
            model: Developer,
            where: {
                "$Developer.role$": req.query.dev,
                "$Developer.level$": req.query.lvl,
                "$Employees.office_id$": req.query.office,
            },
            attributes: [
                [sequelize.fn("COUNT", sequelize.col("id")), "n_devEmployees"],
            ],
        },
    }).then((result) => {
        res.send(result);
    });
});

Answer 1

in my case [SOLVED] .... so we can ignore the ambigous column in some condition here is example

i have user table ( here is a users )

id
full_name

i have driver table ( here is a person )

id
user_id
full_name

when we load the driver table like these

...
await driver.findAndCountAll({
      include: [
        {
          model: user, // here is problem its would like to ambiguous column
          paranoid: false,
        },
      ],
      where: arrayOfSearch // here is problem that trigger ambigious column // if you didnt implement to search its work.
 ...
...

then why we got ambigous ? here is why ( by the war advanceSearchCondition is arrayOfSearch ya !!! )

// /api/driver?keyword=full_name%3DA%26phone%3D2
if(keyword){
      let keywordSplit = keyword.split('&');
      keywordSplit.map(el => {
        const data = el.split("=");
        if (data[0] == 'created_at') {
          advanceSearchCondition.push({
            created_at: {
              [Op.substring]: `${data[1]}`
            }
          });
        } else if (data[0] == 'updated_at') {
          advanceSearchCondition.push({
            updated_at: {
              [Op.substring]: `${data[1]}`
            }
          });
        } else if (data[0] == 'full_name') { // if you didnt code like these you will get ambigous column for full_name
          advanceSearchCondition.push({
            full_name: {
              [Op.like]: Sequelize.literal(`\'%${data[1]}%\'`)
            }
          });
        } else if (data[0] == 'phone') {
          advanceSearchCondition.push({
            phone: {
              [Op.like]: Sequelize.literal(`\'%${data[1]}%\'`)
            }
          });
        } else { // because you make code like these 
          winLogger.error(data[0]);
          winLogger.error(data[1]);
          advanceSearchCondition.push({
            [`$${data[0]}$`]: {
              [Op.substring]: `${data[1]}`
            }
          });
        }
      })
    }

explanation... in else condition i search by $driver.full_name$ so i dont need to pass in include / associate where properties... we can put the search in outside of include but it would ambigous when other associate have same column and you implement search with $table.column$ to put outside the include.

Answer 2

Both of your tables contain the field id so in the COUNT function SQL does not know which id to use. Try to specifiy ID in sequelize.col("id"). For example sequelize.col("Employees.id").