getting the values from 3 tables in sequelize using nested includes and sequelize.col as single obje-CodePudding

I am new to nodejs as well as sequelize and any kind of ORMs I wish to get all the values from 3 tables linked together through belongsTo associations

3 tables :

item -  [id, itemName, itemCategoryID] 
itemCategory -  [id, itemCategoryName]
itemRequirement - [id, itemID, quantity, requirementDate, requirementStatusID]
requirementStatus -  [id, requirementStatusName]

this is my get api req for getting the item requirements

      router.get("/", async (req, res) => {
      const itemRequirements = await itemRequirement
       .findAll({
         include: [
            {
              model: item,
              include: [
           {
              model: itemCategory,
               attributes: [],
           },
          ],
            attributes: [
               //gets error in this line
             [Sequelize.col("itemCategory.itemCategoryName"),"itemCategoryName",],
              //alternatively this line works fine
              ['itemCategoryID']
         ],
       },
          { model: requirementStatus, attributes: [] },
       ],
       attributes: [
        "id",
        "quantity",
        "requiredBy",
         [Sequelize.col("item.itemName"), "itemName"],
         [
           Sequelize.col("requirementStatus.requirementStatusName"),
          "requirementStatusName",
        ],
      ],
    })
     .then((itemRequirements) => {
       console.log(itemRequirements);
      res.json(itemRequirements);
    });
});

I get error when trying to do a sequelize.col but I am able to get the ID alone if I don't use the sequelize.col in the above code at the mentioned line

 code: 'ER_BAD_FIELD_ERROR',
errno: 1054,
sqlState: '42S22',
sqlMessage: "Unknown column 'item.itemCategory.itemCategoryName' in 'field list'",

currently i am getting this if i directly get the id

[
{
    "id": 1,
    "quantity": 10,
    "requiredBy": "2022-02-28T18:30:00.000Z",
    "itemName": "vanilla essence",
    "requirementStatusName": "pending",
    "item": {
        "itemCategoryID": 1
    }
  }
 ]

i wish to get this

 [
  {
    "id": 1,
    "quantity": 10,
    "requiredBy": "2022-02-28T18:30:00.000Z",
    "itemName": "vanilla essence",
    "requirementStatusName": "pending",
    "itemCategoryName":"someCategoryName"
    }
 ]

CodePudding user response：

You should use DB column name in Sequelize.col instead of its field counterpart in a model:

// let's say the model field is itemCategoryName and the column name in a table is item_category_name
Sequelize.col("itemCategory.item_category_name")

CodePudding user response：

To query more than 2 tables using joins in sequelize we will have to use reference the table and column name correctly.

Instead of adding [Sequelize.col("itemCategory.itemCategoryName"),"itemCategoryName",] as an attribute to the referencing table and to get the response as a single json object without nesting we need to add this [Sequelize.col("item.itemCategory.itemCategoryName"),"itemCategoryName"] as the attribute to the table from which you are querying now

below is the edited code which returns json as expected

router.get("/", async (req, res) => {
const itemRequirements = await itemRequirement
.findAll({
  include: [
    {
      model: item,
      include: [
        {model:itemCategory,attributes:[]},
        {model:quantityType,attributes:[]}
      ],
      attributes:[]
    },
    { model: requirementStatus, attributes: [] },
  ],
  attributes: [
    "id",
    "quantity",
    "requiredBy",
    [Sequelize.col("item.itemName"), "itemName"],
    [
      Sequelize.col("requirementStatus.requirementStatusName"),
      "requirementStatusName",
    ],
    //actual way of referencing the different tables to get an object without 
    //nesting
    [Sequelize.col("item.itemCategory.itemCategoryName"),"itemCategoryName"],
    [Sequelize.col("item.quantityType.quantityName"),"quantityTypeName"]
  ],
})
.then((itemRequirements) => {
  console.log(JSON.stringify(itemRequirements,null,2));
  res.json(itemRequirements);
});
});

 module.exports = router;

output

[
  {
    "id": 4,
    "quantity": 10,
    "requiredBy": "2022-02-03T00:00:00.000Z",
    "itemName": "choco",
    "requirementStatusName": "pending",
    "itemCategoryName": "Essence",
    "quantityTypeName": "ml"
  }
]