CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
luis
luis

Reputation: 202

Sequelize: extra column in query

i have the following table define

product table

const Product = sequelize.define('product', {
  name: { type: Sequelize.TEXT },
  amount: { type: Sequelize.DECIMAL },
  category: { type: Sequelize.BIGINT, field: 'categoryId' },
  brand: { type: Sequelize.BIGINT, field: 'brandId' },
  vendor: { type: Sequelize.BIGINT, field: 'vendorId' },
  status: { type: Sequelize.BIGINT, field: 'statusId' },
  image: { type: Sequelize.TEXT },
  stock: { type: Sequelize.INTEGER },
  description: { type: Sequelize.TEXT },
  model: { type: Sequelize.TEXT },
  code: { type: Sequelize.TEXT },
});

status table

const Statuses = sequelize.define('statuses', {
  name: {
    type: Sequelize.TEXT
  },
});

associations

Product.belongsTo(Statuses, { foreignKey: 'statusId', as: 'productStatus'});

when i run thhe following:

product = await Product.findOne({
  where: {
    id: req.query.id
  }, 
  include: ['productStatus']
})

I get the following query:

SELECT 
"product"."id", 
"product"."name", 
"product"."amount", 
"product"."categoryId" AS "category", 
"product"."brandId" AS "brand", 
"product"."vendorId" AS "vendor", 
"product"."statusId" AS "status", 
"product"."image", 
"product"."stock", 
"product"."description", 
"product"."model", 
"product"."code",
"product"."createdAt", 
"product"."updatedAt", 
"product"."statusId", 
"product"."categoryId", 
"product"."vendorId", 
"product"."brandId", 
"productStatus"."id" AS "productStatus.id", 
"productStatus"."name" AS "productStatus.name", 
"productStatus"."createdAt" AS "productStatus.createdAt", 
"productStatus"."updatedAt" AS "productStatus.updatedAt", 
"productStatus"."status" AS "productStatus.status" 
FROM "products" AS "product" 
LEFT OUTER JOIN "statuses" AS "productStatus" ON "product"."statusId" = "productStatus"."id" 
WHERE "product"."id" = '1'

not sure why

"productStatus"."status" AS "productStatus.status"

is there and it causes an unknown column error since i don't have that column in statuses table

Upvotes: 0

Views: 862

Answers (1)

Anatoly
Anatoly

Reputation: 22783

User.hasOne(Status, { foreignKey: 'status', as: 'UserStatus'}) means Status has the status column which is not true. That's why Sequelize knows that Status model has status column and adds it to SQL-query as well.

I suppose User has status column instead of Status. If so, all you need is to change association like this:

User.belongsTo(Status, { foreignKey: 'status', as: 'UserStatus'})

Upvotes: 1

Related Questions