I'm having trouble querying a join table. I use, for example, the findAll() Model method. It gives me this error

code:

const { UserItems, sequelize } = require('./dbObjects');
const { Op } = require('sequelize');

async function run() {
    const userItem = await UserItems.findAll();
    console.log(user);
}

run();

error:

    at async Function.findAll (E:\1dsktp\bot\node_modules\sequelize\dist\lib\model.js:1119:21)
    at async run (E:\1dsktp\bot\dbTesting.js:14:15) {
  name: 'SequelizeDatabaseError',
  parent: [Error: SQLITE_ERROR: no such column: id] {
    errno: 1,
    code: 'SQLITE_ERROR',
    sql: 'SELECT `id`, `amount` FROM `userItems` AS `userItems`;'
  },
  original: [Error: SQLITE_ERROR: no such column: id] {
    errno: 1,
    code: 'SQLITE_ERROR',
    sql: 'SELECT `id`, `amount` FROM `userItems` AS `userItems`;'
  },
  sql: 'SELECT `id`, `amount` FROM `userItems` AS `userItems`;',
  parameters: {}
}

For some reason, It's trying to look for a column named 'id', which of course doesn't exist.

My db is pretty simple, it consists of 3 tables, one being a join table:

• User
• Item
• UserItems (join table and the one I'm having trouble with)

UserItems has two foreign keys, which are userId from User and itemId from Item. These were added associating both tables with the belongsToMany() method.

User.belongsToMany(Item, { through: UserItems, foreignKey: 'userId' });
Item.belongsToMany(User, { through: UserItems, foreignKey: 'itemId' });

Here is a picture of what the UserItems table looks like (I'm using SQLiteStudio for this)

Here is the UserItems model

module.exports = (sequelize, DataTypes) => {
    const model = sequelize.define('userItems', {
        amount: {
            type: DataTypes.INTEGER,
            defaultValue: 0,
            allowNull: false,
        },
    }, {
        timestamps: false,
    });
    return model;
};

CodePudding user response：

Obviously, you need to add a primary key column for UserItems (both in a table and in a model) because Sequelize does not support composite primary and foreign keys and by default, it suggests you have id as a primary key if you don't define any primary key explicitly.