how to return values from joined tables in sequelize at the same level as master table

Question

I am trying to find a way how to put all joined tables at the same level as my master table... so far it only results in the nested values in my final object ..

Here is what I have

Orders.findAll({
   include: [
      {model: Products, attributes: ['product_name']}
   ],
   attributes: ['id_order', 'dtime_order', 'amount']
})

what I am getting is:

[
 {
   id_order: 1, 
   dtime_order: '2021-05-24T22:00:00.000Z',
   amount: 20,
   products: {
      product_name: 'Picture'
   }
 }
]

but what I wanna get is:

[
 {
   id_order: 1, 
   dtime_order: '2021-05-24T22:00:00.000Z',
   amount: 20,
   product_name: 'Picture'
 }
]

I tried this How to return result from include model in same level of main model in Sequelize? but unfortunately when I did:

Orders.findAll({
   include: [
      {model: Products, attributes: []}
   ],
   attributes: ['id_order', 'dtime_order', 'amount', ['products.product_name', 'product_name']]
})

doesn't work for me saying

column "products.product_name" does not exist

There might be a hacky way to modify the object before sending it back in the response .. but I would rather do it within Sequelize ..

any idea is very welcome... thank you guys!

EDIT: adding the generated SQL

Executing (default): SELECT "orders"."id_order", "orders"."dtime_order", "orders"."amount", "products.product_name", FROM "orders" AS "orders" LEFT OUTER JOIN "products" AS "products" ON "orders"."id_order" = "products"."ir_order";
error:   Get dashboard data error: column "products.product_name" does not exist

SOLUTION:

I had to use an alias in my association

Orders.hasOne(Products, {as: 'products', ....})

And then use that EXACTLY SAME alias in my include and referencing

include: [{model: Products, attributes: [], as: 'products'}]

And

attributes: [ ... , [Sequelize.col('products.product_name', 'product_name')]

without the raw: true works like a charm :) Thank you @Emma !!!

Answer 1

Please use Sequelize.col to wrap the nested column so that Sequelize can properly alias the column.

Orders.findAll({
  include: [
    {model: Products, attributes: []}
  ],
  attributes: ['id_order', 'dtime_order', 'amount', [Sequelize.col('products.product_name'), 'product_name']],
  raw: true
})