How can I get columns using sequelize in node.js

Question

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

Are table1 and table2 iinked as 1:1?

Anatoly
– Anatoly

2022-02-14 11:44:25 +00:00
Commented Feb 14, 2022 at 11:44 — Anatoly
– Anatoly, Commented Feb 14, 2022 at 11:44
Yes, that is it.

StormyTalent
– StormyTalent

2022-02-14 13:44:01 +00:00
Commented Feb 14, 2022 at 13:44 — StormyTalent
– StormyTalent, Commented Feb 14, 2022 at 13:44

Anatoly · Accepted Answer · 2022-02-14 15:34:32Z

2

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

answered Feb 14, 2022 at 15:34

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StormyTalent Over a year ago

I love StackOverflow. Because we have such an elegant and prompt professional. Thank you Anatoly...

Collectives™ on Stack Overflow

How can I get columns using sequelize in node.js

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related