Sequelize - Association Many to Many - 3 Foreign Keys

user_5838764 0
Viewed 734

I'm looking for some help on properly defining an association table with 3 foreign keys in a Sequelize model:

Situation: I'm building an email client, the relative models to this question are:

  • User Model (user records)
  • Thread Model (thread records for each new email-thread)
  • Folder Model (folders for default folders e.g. inbox, sent, etc and custom folders)
  • ThreadFolder Model (association linking a specific a) User Model, to a specific b) Thread Model, to a specific c) Folder Model)

Problem: My problem is the association model/table (ThreadFolder), I'm not able to create an association for all 3 tables in the ThreadFolder association Model.

First Attempt I'm able to create an association with Sequelize that allows the ThreadFolder model to create foreign keys for 2 of the three models above, but not all 3. Here is the association for that:

Thread.belongsToMany(Folder, { through: ThreadFolder, foreignKey: 'thread_id', otherKey: 'folder_id' })
Folder.belongsToMany(Thread, { through: ThreadFolder, foreignKey: 'folder_id', otherKey: 'thread_id' })

SQL Input Attempts:

  1. user_id: 1 | thread_id: 1 | folder_id: 1 | Successful Insert
  2. user_id: 1 | thread_id: 1 | folder_id: 2 | Successful Insert
  3. user_id: 1 | thread_id: 2 | folder_id: 1 | Failed Insert -- Error below...
  4. user_id: 2 | thread_id: 1 | folder_id: 1 | Successful Insert
  5. user_id: 2 | thread_id: 1 | folder_id: 2 | Successful Insert

First Attempt's Error: 

Executing:
INSERT INTO `iwantmail-core`.`thread_folders` (`user_id`, `deleted`, `archived`, `created_at`, `updated_at`, `thread_id`, `folder_id`) VALUES ('1', '0', '0', '2020-03-05 23:34:16', '2020-03-05 23:34:16', '30', '1');

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1062: 1062: Duplicate entry '30-1' for key 'PRIMARY'
SQL Statement:
INSERT INTO `iwantmail-core`.`thread_folders` (`user_id`, `deleted`, `archived`, `created_at`, `updated_at`, `thread_id`, `folder_id`) VALUES ('1', '0', '0', '2020-03-05 23:34:16', '2020-03-05 23:34:16', '30', '1')

Second Attempt I can specify the association as shown below, to allow me to add records with different user_id and folder_id, however, if I use a different thread_id, I get an error shown below.

Folder.hasMany(ThreadFolder, { foreignKey: 'folder_id' })
ThreadFolder.belongsTo(Folder, { foreignKey: 'folder_id' })
Thread.belongsToMany(Folder, { through: ThreadFolder })

Thread.hasMany(ThreadFolder, { foreignKey: 'thread_id' })
ThreadFolder.belongsTo(Thread, { foreignKey: 'thread_id' })

User.hasMany(ThreadFolder, { foreignKey: 'user_id' })
ThreadFolder.belongsTo(User, { foreignKey: 'user_id' })
Folder.belongsToMany(User, { through: ThreadFolder })

SQL Input Attempts:

  1. user_id: 1 | thread_id: 1 | folder_id: 1 | Successful Insert
  2. user_id: 1 | thread_id: 1 | folder_id: 2 | Successful Insert
  3. user_id: 1 | thread_id: 2 | folder_id: 1 | Failed Insert -- Error below...
  4. user_id: 2 | thread_id: 1 | folder_id: 1 | Successful Insert
  5. user_id: 2 | thread_id: 1 | folder_id: 2 | Successful Insert

Second Attempt's Error

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1062: 1062: Duplicate entry '1-1' for key 'PRIMARY'
SQL Statement:
INSERT INTO `mail-core`.`thread_folders` (`user_id`, `thread_id`, `folder_id`) VALUES ('1', '2', '1')

Note that I'm basically trying to indicate that User#1 of Thread#1 are in Folder#1 and as soon as I try to indicate that User#1 of Thread#2 are in Folder#1, the above error occurs.

Help:

  • Could someone please point me towards the right direction / show how the association should be written to take into account the 3rd association?
  • Is there a different way to write this association all together so all 3 foreign keys are taken into account in the association table?

Thanks for any help/assistance in advance!

(Relative technologies used: MySQL, MySQL Workbench, Node 12.x, TypeScript, Serverless Framework)

EDIT: Made edits to the post, 2nd attempt was presented as a partial solution, after further testing, both 1st and second attempts fail when a 2nd user is added to the same thread and folder in the association table ThreadFolder.

sequelize.js many-to-many associations mysql
2 Answers

After looking at your create statements I think you have defined associations thread_folders properly. In my opinion, your second attempt at defining association is correct.

You are getting an error while inserting records because your primary key is the combined key of two attributes namely thread_id and folder_id. Let us say that in your thread_folders table there is already record for thread_id 1 and folder_id 1 then you can not insert another record with thread_id 1 and folder_id 1.

If you remove the combined primary key of thread_id and folder_id then you will be able to insert the records that you want to insert in the thread_folders table.

I hope it helps!

user_40688170

Working solution ended up being a variance of the second attempt:

User.hasMany(ThreadFolder, { foreignKey: 'user_id' })
ThreadFolder.belongsTo(User, { foreignKey: 'user_id' })

Folder.hasMany(ThreadFolder, { foreignKey: 'folder_id' })
ThreadFolder.belongsTo(Folder, { foreignKey: 'folder_id' })

Thread.hasMany(ThreadFolder, { foreignKey: 'thread_id' })
ThreadFolder.belongsTo(Thread, { foreignKey: 'thread_id' })

Most of my model calls (based on how they were written before) will end up changing to start with the association table first e.g.

 ThreadFolder.findAll({
        where: {
            user_id: 1,
            folder_id: 1,
        },
        include: [
            {
                model: Thread,
                include: [
                    'recipient',
                    'sender'
                ]
            }
        ]
    })

Hope this helps others that have attempted to do a multi-foreign key association table beyond 2 foreign keys, presumable this approach should work with any amount of foreign keys in the association table.

user_58387640
Related