here's my question. I'm trying to get the nested relationships by Sequelize.

let results = await FarmVisitQuestionnaire.findOne({
        where: { Customer_ID, id:2 },
        include: [{
          model: FarmVisitSection,
          include: [{
            model: FarmVisitQuestion,
            include: [{
              model: FarmVisitQuestionOption,
            }]
          }]
        }],
      });

Everything works well excepts for the deepest relationship FarmVisitQuestionOption. It didn't return the complete data in FarmVisitQuestionOption section, there're two FarmVisitQuestionOption data but only one FarmVisitQuestionOption is returned ("i":1). And the key names in FarmVisitQuestionOption are limited to only the first character as below:

"questionnaires": {
        "id": 2,
        "Customer_ID": "3380",
        "Title": "Form title",
        "createdAt": "2022-07-05T19:42:50.292Z",
        "updatedAt": "2022-07-05T19:42:50.292Z",
        "FarmVisitSections": [
            {
                "id": 2,
                "Title": "Section title",
                "Description": "Section description",
                "Order": 0,
                "createdAt": "2022-07-05T19:42:50.295Z",
                "updatedAt": "2022-07-05T19:42:50.295Z",
                "FarmVisitQuestionnaireId": 2,
                "FarmVisitQuestions": [
                    {
                        "id": 1,
                        "Title": "Question title?",
                        "Description": "Question description / extra info",
                        "Order": 0,
                        "Required": "true",
                        "Type": "checkbox1",
                        "createdAt": "2022-07-05T19:42:50.299Z",
                        "updatedAt": "2022-07-05T19:42:50.299Z",
                        "FarmVisitSectionId": 2,
                        "FarmVisitQuestionOptions": [
                            {
                                "i": 1,
                                "L": "First Option",
                                "V": "1",
                                "c": "2022-07-05T19:42:50.303Z",
                                "u": "2022-07-05T19:42:50.303Z",
                                "F": 1
                            }
                        ]
                    }
                ]
            }
        ]
    }

My DB was set up correctly: FarmVisitQuestionOption table

However, the correct FarmVisitQuestionOption section should be:

"FarmVisitQuestionOptions": 
                      [
                            {
                                "id": 1,
                                "Label": "First Option",
                                "Value": "1",
                                "createdAt": "2022-07-05T19:42:50.303Z",
                                "updatedAt": "2022-07-05T19:42:50.303Z",
                                "FarmVisitQuestionId": 1
                            },
                            {
                                "id": 2,
                                "Label": "Second Option",
                                "Value": "2",
                                "createdAt": "2022-07-05T19:42:50.303Z",
                                "updatedAt": "2022-07-05T19:42:50.303Z",
                                "FarmVisitQuestionId": 1
                            }
                        ]

Can anyone help me with this issue? Any insights would be helpful. Thanks!

CodePudding user response：

If you use PostgreSQL you need to turn on the option minifyAliases in Sequelize instance due to Postgres alias character limit of 64.
See the official Sequelize documentation