I have a Categories table, Attributes and an intermediate table, AttributeCategory, which has id, attribute_id, category_id, date and value, the problem is that when sequelize maps the relationship if the attribute_id and category_id match it does not map it correctly, example:
Category table: id = 1, description = "some"
Attribute table: id = 1, description = "one", id = 2, description = "two"
Category Attribute Table,
id = 1, category_id = 1, attribute_id = 1, value = 10, date = 2020-05-10
id = 2, category_id = 1, attribute_id = 1, value = 15, date = 2020-05-15
id = 3, category_id = 1, attribute_id = 2, value = 10, date = 2020-05-10
The query to db does it correctly, but when it maps the object it only returns me:
id = 2, category_id = 1, attribute_id = 1, value = 15, date = 2020-05-15
id = 3, category_id = 1, attribute_id = 2, value = 10, date = 2020-05-10
as the same attribute_id and category_id are repeated, it only returns the last one.
My models are:
// Atrributes
'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class InmAtributo extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// Atributo tiene varias categorias
InmAtributo.belongsToMany(models.InmCategoria, {
as: 'categorias',
through: models.InmAtributoCategoria,
foreignKey: 'atributo_id',
otherKey: 'categoria_id'
});
}
};
InmAtributo.init({
descripcion: DataTypes.STRING,
abreviado: DataTypes.STRING
}, {
sequelize,
modelName: 'InmAtributo',
tableName: 'inm_atributos',
timestamps: false,
});
return InmAtributo;
};
//Categorys
'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class InmCategoria extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// Categoria tiene varios atributos
InmCategoria.belongsToMany(models.InmAtributo, {
as: 'atributos',
through: models.InmAtributoCategoria,
foreignKey: 'categoria_id',
otherKey: 'atributo_id'
});
}
};
InmCategoria.init({
descripcion: DataTypes.STRING,
abreviado: DataTypes.STRING
}, {
sequelize,
modelName: 'InmCategoria',
tableName: 'inm_categorias',
timestamps: false,
});
return InmCategoria;
};
//AtributoCategoria
'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class InmAtributoCategoria extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
}
};
InmAtributoCategoria.init({
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false
},
valor: DataTypes.FLOAT,
fecha_activo: DataTypes.DATE,
}, {
sequelize,
modelName: 'InmAtributoCategoria',
tableName: 'inm_atributo_categoria',
timestamps: true,
});
return InmAtributoCategoria;
};
My migrations are:
//Attributes
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('inm_atributos', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
descripcion: {
allowNull: false,
type: Sequelize.STRING(100)
},
abreviado: {
allowNull: false,
type: Sequelize.STRING(5)
}
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('inm_atributos');
}
};
//Categorys
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('inm_categorias', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
descripcion: {
allowNull: false,
type: Sequelize.STRING(100)
},
abreviado: {
allowNull: false,
type: Sequelize.STRING(5)
}
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('inm_categorias');
}
};
//AtributoCategoria
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('inm_atributo_categoria', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
atributo_id: {
allowNull: false,
references: {
model: "inm_atributos",
key: "id"
},
type: Sequelize.BIGINT
},
categoria_id: {
allowNull: false,
references: {
model: "inm_categorias",
key: "id"
},
type: Sequelize.BIGINT
},
fecha_activo: {
allowNull: false,
type: Sequelize.DATE
},
valor: {
allowNull: false,
type: Sequelize.FLOAT
},
created_at: {
type: Sequelize.DATE
},
updated_at: {
type: Sequelize.DATE
}
});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('inm_atributo_categoria');
}
};
My function in controller:
async show(req, res) {
try {
const inmCategoria = await InmCategoria.findByPk(req.params.id, {
include: {
association: 'atributos',
through: {
attributes: ['categoria_id', 'atributo_id', 'valor', 'fecha_activo',
'updated_at', 'created_at']
}
}
});
return res.json({ 'status': 'Exito', 'body': inmCategoria })
} catch (error) {
return res.json({ 'status': 'Error', 'body': error.message })
}
},
Result
Query generated automatically
SELECT "InmCategoria"."id", "InmCategoria"."descripcion", "InmCategoria"."abreviado",
"atributos"."id" AS "atributos.id", "atributos"."descripcion" AS "atributos.descripcion",
"atributos"."abreviado" AS "atributos.abreviado", "atributos->InmAtributoCategoria"."id" AS
"atributos.InmAtributoCategoria.id", "atributos->InmAtributoCategoria"."categoria_id" AS
"atributos.InmAtributoCategoria.categoria_id", "atributos->InmAtributoCategoria"."atributo_id"
AS "atributos.InmAtributoCategoria.atributo_id", "atributos->InmAtributoCategoria"."valor" AS
"atributos.InmAtributoCategoria.valor", "atributos->InmAtributoCategoria"."fecha_activo" AS
"atributos.InmAtributoCategoria.fecha_activo", "atributos->InmAtributoCategoria"."updated_at"
AS "atributos.InmAtributoCategoria.updated_at", "atributos->InmAtributoCategoria"."created_at"
AS "atributos.InmAtributoCategoria.created_at" FROM "inm_categorias" AS "InmCategoria" LEFT
OUTER JOIN ( "inm_atributo_categoria" AS "atributos->InmAtributoCategoria" INNER JOIN
"inm_atributos" AS "atributos" ON "atributos"."id" = "atributos-
>InmAtributoCategoria"."atributo_id") ON "InmCategoria"."id" = "atributos-
>InmAtributoCategoria"."categoria_id" WHERE "InmCategoria"."id" = '10';
JSON output
{
"body": {
"id": "10",
"descripcion": "ZONE A01",
"abreviado": "A01",
"atributos": [
{
"id": "10",
"descripcion": "Additional wasteland",
"abreviado": "ABAL",
"InmAtributoCategoria": {
"categoria_id": "10",
"atributo_id": "10",
"valor": 50,
"fecha_activo": "2010-01-01",
"updated_at": "2021-10-06T19:11:27.000Z",
"created_at": "2021-10-06T19:11:27.000Z"
}
},
{
"id": "21",
"descripcion": "Environmental rate",
"abreviado": "TAMIN",
"InmAtributoCategoria": {
"categoria_id": "10",
"atributo_id": "21",
"valor": 120,
"fecha_activo": "2014-01-01",
"updated_at": "2021-10-06T19:11:27.000Z",
"created_at": "2021-10-06T19:11:27.000Z"
}
}
]
}
}
Query result in navicat
id descripcion abreviado atributos.id atributos.descripcion atributos.abreviado atributos.InmAtributoCategoria.id atributos.InmAtributoCategoria.categoria_id atributos.InmAtributoCategoria.atributo_id atributos.InmAtributoCategoria.valor atributos.InmAtributoCategoria.fecha_activo atributos.InmAtributoCategoria.updated_at atributos.InmAtributoCategoria.created_at
|id|descripcion|abreviado|atributos.id|atributos.descripcion|atributos.abreviado|atributos.InmAtributoCategoria.id|atributos.InmAtributoCategoria.categoria_id|atributos.InmAtributoCategoria.atributo_id|atributos.InmAtributoCategoria.valor|atributos.InmAtributoCategoria.fecha_activo|atributos.InmAtributoCategoria.updated_at|atributos.InmAtributoCategoria.created_at|
|10|ZONA A01|A01|10|ADICIONAL BALDIO|ABAL|167|10|10|60|2021-01-01|2021-10-06 16:11:27|2021-10-06 16:11:27|
|10|ZONA A01|A01|21|TASA AMBIENTAL MINIMO|TAMIN|175|10|21|265|2019-01-01|2021-10-06 16:11:27|2021-10-06 16:11:27|
|10|ZONA A01|A01|21|TASA AMBIENTAL MINIMO|TAMIN|179|10|21|800|2021-01-01|2021-10-06 16:11:27|2021-10-06 16:11:27|
Thanks! Cheers!
CodePudding user response：
If you use category_id and attribute_id as foreign keys for the N:M join table then they cannot be the same in two different records because it violates N:M type of relationship between tables.
If you want to store several records with the same pair of values category_id and attribute_id then you need hasMany from InmCategoria to InmAtributoCategoria and belongsTo from InmAtributoCategoria to InmAtributo like this:
class InmCategoria extends Model {
...
static associate(models) {
InmCategoria.hasMany(models.InmAtributoCategoria, {
as: 'atributoCategoria',
foreignKey: 'categoria_id'
});
}
class InmAtributoCategoria extends Model {
...
static associate(models) {
InmAtributoCategoria.belongsTo(models.InmAtributo, {
as: 'atributo',
foreignKey: 'atributo_id'
});
}
And the query will look like this:
const inmCategoria = await InmCategoria.findByPk(req.params.id, {
include: {
association: 'atributoCategoria',
attributes: ['categoria_id', 'atributo_id', 'valor', 'fecha_activo',
'updated_at', 'created_at'],
include: {
association: 'atributo'
}
}
});