I am using sequelize for MySQL in node.js and I want to get the same data as I can get from ( SELECT * from table1, table2 where table1.id = table2.id )
--table1.js--
const Table1= sequelize.define("table1", {id: {type: DataTypes.INTEGER,}, data: {type:DataTypes.STRING(255)});
--table2.js--
const Table2 = sequelize.define("table2", {id: {type: DataTypes.INTEGER,}, storage: {type:DataTypes.STRING(255)});
I tried to use such command Table1.belongsTo(Table2)
and get data by
const data = await Table1.findAll({
attributes: [
["data", "data_src"],
],
include: [
{
model: Table2,
on: {
storage: sequelize.where(
sequelize.col("Table1.id"),
"=",
sequelize.col("Table2.id")
),
},
attributes: [["storage", "storage_src"]],
},
],
});
But it failed. how can I get data formated as followings
[
{
data: 'some data1',
storage: 'some storage1',
},
{
data: 'some data2',
storage: 'some storage2',
},
]
Best regards
CodePudding user response:
First, you need to define primary keys in models (let's assume the same id
columns).
const Table1= sequelize.define("table1", {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true
},
data: {type:DataTypes.STRING(255)}
});
const Table2= sequelize.define("table2", {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
primaryKey: true
},
storage: {type:DataTypes.STRING(255)}
});
Second, you need to correct an association between models:
Table1.belongsTo(Table2, { foreignKey: 'id' })
And finally, you can include attributes from the included model into attributes of the main model in the query:
const data = await Table1.findAll({
attributes: [
"data",
[sequelize.col("Table2.storage"), "storage"]
],
include: [
{
model: Table2,
attributes: [],
},
],
});