Home > Blockchain >  How can I get columns using sequelize in node.js
How can I get columns using sequelize in node.js

Time:02-16

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: [],
        },
      ],
    });
  • Related