CODEWITHSUNDEEP
node.jsdatabasepostgresqlforeign-keyssequelize.js
Gabriel
Gabriel

Reputation: 13

Sequelize Associations - Association Table

I'm working with Sequelize, PostgreSQL and Node JS.

I've designed my database like this:

  1. Table A => ID is autoincremented
  2. Table B => ID is autoincremented
  3. Table C => ID is autoincremented
  4. Table Association => ID is autoincremented

In the Association table I add foreign keys for Table A, Table B and Table C ID's.

So the Association table looks something like this:

| id | tableA_id | tableB_id | tableC_id |

Table A Associations:

Table A => Has **Many** Table **B**.

Table A => Has **Many** Table **C**.

Table B Associations:

Table B => Has **One** Table **A**.

Table B => Has **Many** Table **C**.

Table C Associations:

Table C => Has **One** Table **A**.

Table C => Has **One** Table **B**.

This being said, I have this associations in sequelize:

 TableAssociationModel.hasMany(TableAModel, {
    as: 'TableAModel',
    foreignKey: 'tableA_id',
    targetKey: 'tableA_id',
  });
  TableAssociationModel.hasMany(TableBModel, {
    as: 'TableBModel',
    foreignKey: 'tableB_id',
    targetKey: 'tableB_id',
  });

  TableAssociationModel.hasMany(TableCModel, {
    as: 'TableCModel',
    foreignKey: 'tableC_id',
    targetKey: 'tableC_id',
  });

Database example:

| id | tableA_id | tableB_id | tableC_id |
|  1 |     2     |      1    |      1    |
|  2 |     2     |      1    |      2    |
|  3 |     2     |      2    |      3    |

If I searched for Table C ID = 1, I would get my associations, but if I searched for ID = 2, nether Table A or Table B data where passed.

Also, I search in my Tables (A, B and C), and want to return my associations in Table Association.

Any help please?

Thanks in advance and I hope I explained myself good enough, any doubt please let me know, really want to understand how this works.

PS: I'm a junior, don't be to harsh xD

Upvotes: 0

Views: 931

Answers (1)

Gabriel
Gabriel

Reputation: 13

So I figured it out:

TableAModel.hasMany(TableAssociationModel, {
    as: 'relationsData',
    foreignKey: 'tableA_id',
    sourceKey: 'tableA_id',
  });
  
TableAssociationModel.belongsTo(TableAModel, {
    as: 'tableAData',
    foreignKey: 'tableA_id',
    sourceKey: 'tableA_id',
  });

 TableBModel.hasMany(TableAssociationModel, {
    as: 'relationsData',
    foreignKey: 'tableB_id',
    sourceKey: 'tableB_id',
  });

  TableAssociationModel.belongsTo(TableBModel, {
    as: 'tableBData',
    foreignKey: 'tableB_id',
    sourceKey: 'tableB_id',
  });

  TableCModel.hasMany(TableAssociationModel, {
    as: 'relationsData',
    foreignKey: 'tableC_id',
    sourceKey: 'tableC_id',
  });

  TableAssociationModel.belongsTo(TableCModel, {
    as: 'tableCData',
    foreignKey: 'tableC_id',
    targetKey: 'tableC_id',
  });

My associations where only 1 way and were wrongly done.

Figured out that even though I want to search in the TableAssociationModel, my main models were Table A, B and C, so I needed to treat them as the more important and tell Sequelize that they are the main.

Btw, if you want to see the query generated in Sequelize you can use:

DEBUG=sequelize* node ***your/file.js***

your/file.js <= the file where you use for example:

TableAModel.findAll({

})

Upvotes: 1

Related Questions