Node.js与Sequelize新手求助：如何通过嵌套Include与Sequelize.col从关联表获取数据并返回单一扁平化对象

Let's break down what's going wrong and fix it step by step — I've been in your shoes with Sequelize associations, so I get the confusion!

Why You're Getting That Error #

The error Unknown column 'item.itemCategory.itemCategoryName' happens because you're trying to reference itemCategory.itemCategoryName from within the item model's attributes block. Sequelize's context here is the item table, but when building the full query, the nested association path needs to be referenced from the top-level model (itemRequirement).

Additionally, your current setup is returning nested structures because you're keeping some fields in the nested item object instead of pulling everything up to the top level.

Fixed Code to Get Flattened Data #

Here's the adjusted version of your endpoint that will resolve the error and return the flat structure you want:

router.get("/", async (req, res) => {
  try {
    const itemRequirements = await itemRequirement.findAll({
      include: [
        {
          model: item,
          include: [
            {
              model: itemCategory,
              attributes: [], // Don't return raw fields from itemCategory
            },
          ],
          attributes: [], // Don't return raw fields from item (we'll pull what we need to the top)
        },
        { 
          model: requirementStatus, 
          attributes: [] // Don't return raw fields from requirementStatus
        },
      ],
      attributes: [
        "id",
        "quantity",
        "requiredBy",
        [Sequelize.col("item.itemName"), "itemName"],
        // Correct path to the nested category name
        [Sequelize.col("item.itemCategory.itemCategoryName"), "itemCategoryName"],
        [Sequelize.col("requirementStatus.requirementStatusName"), "requirementStatusName"],
      ],
      raw: true, // Critical: Returns flat JS objects instead of nested model instances
    });

    console.log(itemRequirements);
    res.json(itemRequirements);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: error.message });
  }
});

Key Changes Explained #

1. Fixed Column Path: We reference item.itemCategory.itemCategoryName from the top-level attributes block (not inside the item model's attributes), which matches the full association chain from itemRequirement → item → itemCategory.
2. Empty Attributes for Associations: By setting attributes: [] on all nested models, we prevent Sequelize from returning unnecessary nested objects.
3. raw: true Option: This tells Sequelize to skip creating nested model instances and instead return a flat, plain JavaScript object exactly like the structure you want.

Quick Check: Ensure Your Associations Are Correct #

Double-check that your models have the right belongsTo associations defined (this is easy to miss!):

// Item model
item.belongsTo(itemCategory, { foreignKey: 'itemCategoryID' });

// ItemRequirement model
itemRequirement.belongsTo(item, { foreignKey: 'itemID' });
itemRequirement.belongsTo(requirementStatus, { foreignKey: 'requirementStatusID' });

If you used an as alias when defining any association (e.g., belongsTo(itemCategory, { as: 'category', foreignKey: 'itemCategoryID' })), make sure to update the column path to match (like item.category.itemCategoryName).

With this setup, your response will match the flat structure you're aiming for!

内容的提问来源于stack exchange，提问作者Niresh