Properties' name being truncated, sequelize bug?

[Ernesto Chavez]

Hello, 

I have a requirement where a person can register multiple times to the app, in doing so he/she will answer a number of questions for the current registration and is given the option to register children as part of that registration where the children have their own set of questions.

I have the following table structure set up:

Tables

Question.hasMany(Answer).hasMany(PersonAnswer)

Answer.belongsTo(Question).hasMany(PersonAnswer)

Person.hasMany(Registration)

Registration.belongsTo(Person).hasMany(PersonAnswer).hasMany(Children)

PersonAnswer.belongsTo(Registration)

Children.belongsTo(Registration).hasMany(PersonAnswer)

Then I have the following query: 

 return db.Registration.find({

          where: {

            id: id

          },

          include: [

            db.Person,

            {

              model: db.PersonAnswer,

              include: [db.Question, db.Answer]

            },

            {

              model: db.Children,

              include: [{

                model: db.PersonAnswer,

                include: [db.Question, db.Answer]

              }]

            }

          ]

        });

Now from that query I can access the Person's answers easily simply by Person.PersonAnswer[0].Answer.answerText, however when doing the same thing for the children's properties I found out they were being truncated, so if I try Children.PersonAnswer[0].Answer.answerText if comes as undefined. I logged the objects being returned and they were:

Good for Person association: 

- Question: { id: 1, questionText: 'This is some text' }

- Answer: { id: 1, answerText: 'This is some answer' }

Bad for Children association:

- Question: { id: 1, questionT: 'This is some text' }

- Answer: { id: 1, answe: 'This is some text' }

I noticed the length that they were being truncated at is not consistent either for Question and Answer query results either. I tried using instance methods that would return the results of the properties however it also failed:

- Question: { instanceMethods: detail: function() { return this.questionText; } }

- Answer: { instanceMethods: detail: function() { return this.answerText; } }

Any help would be much appreciated,

Thanks

[Ernesto Chavez]

Also I'm using Sequelize v1.7.2, node v0.10.24, expressjs v3.5.1

[Mick Hansen]

Never seen anything like that. Are the field names correct in the database?
What does the findAll SQL look like? What does .dataValues look like for the results?

On Mon, Apr 28, 2014 at 5:59 PM, Ernesto Chavez <ernesto...@ceroic.com> wrote:

Also I'm using Sequelize v1.7.2, node v0.10.24, expressjs v3.5.1

--

Mick Hansen

@mhansendev

[Ernesto Chavez]

The field names are correctly set in the tables ( furthermore the same PersonAnswers table is used for the Person's answers which are returned correctly).

The data values for the children look like this: 

Question: 

{ dataValues:

   { id: 13,

     questi: 'Question text',

     order: 13,

     isActi: true,

     isMino: true,

     create: Mon Apr 21 2014 23:31:44 GMT+0000 (UTC),

     update: Mon Apr 21 2014 23:31:44 GMT+0000 (UTC) },

  _previousDataValues:

   { id: 13,

     questi: 'Question text',

     order: 13,

     isActi: true,

     isMino: true,

     create: Mon Apr 21 2014 23:31:44 GMT+0000 (UTC),

     update: Mon Apr 21 2014 23:31:44 GMT+0000 (UTC) }

}

Answer:

{ dataValues:

   { id: 25,

     answerTe: 'Yes',

     isNegati: false,

     order: 1,

     createdA: Mon Apr 21 2014 23:31:44 GMT+0000 (UTC),

     updatedA: Mon Apr 21 2014 23:31:44 GMT+0000 (UTC),

     Question: 13 },

  _previousDataValues:

   { id: 25,

     answerTe: 'Yes',

     isNegati: false,

     order: 1,

     createdA: Mon Apr 21 2014 23:31:44 GMT+0000 (UTC),

     updatedA: Mon Apr 21 2014 23:31:44 GMT+0000 (UTC),

     Question: 13 }

}

[Ernesto Chavez]

And the sql query for it looks like this:

 SELECT "Registration".*, "Person"."id" AS "Person.id", "Person"."firstName" AS "Person.firstName", "Person"."lastName" AS "Person.lastName", "Person"."email" AS "Person.email", "Person"."createdAt" AS "Person.createdAt", "Person"."updatedAt" AS "Person.updatedAt", "PersonAnswers"."id" AS "PersonAnswers.id", "PersonAnswers"."createdAt" AS "PersonAnswers.createdAt", "PersonAnswers"."updatedAt" AS "PersonAnswers.updatedAt", "PersonAnswers"."AnswerId" AS "PersonAnswers.AnswerId", "PersonAnswers"."RegistrationId" AS "PersonAnswers.RegistrationId", "PersonAnswers"."ChildrenId" AS "PersonAnswers.ChildrenId", "PersonAnswers"."QuestionId" AS "PersonAnswers.QuestionId", "PersonAnswers.Question"."id" AS "PersonAnswers.Question.id", "PersonAnswers.Question"."questionText" AS "PersonAnswers.Question.questionText", "PersonAnswers.Question"."order" AS "PersonAnswers.Question.order", "PersonAnswers.Question"."isActive" AS "PersonAnswers.Question.isActive", "PersonAnswers.Question"."isMinor" AS "PersonAnswers.Question.isMinor", "PersonAnswers.Question"."createdAt" AS "PersonAnswers.Question.createdAt", "PersonAnswers.Question"."updatedAt" AS "PersonAnswers.Question.updatedAt", "PersonAnswers.Answer"."id" AS "PersonAnswers.Answer.id", "PersonAnswers.Answer"."answerText" AS "PersonAnswers.Answer.answerText", "PersonAnswers.Answer"."isNegative" AS "PersonAnswers.Answer.isNegative", "PersonAnswers.Answer"."order" AS "PersonAnswers.Answer.order", "PersonAnswers.Answer"."createdAt" AS "PersonAnswers.Answer.createdAt", "PersonAnswers.Answer"."updatedAt" AS "PersonAnswers.Answer.updatedAt", "PersonAnswers.Answer"."QuestionId" AS "PersonAnswers.Answer.QuestionId", "Children"."id" AS "Children.id", "Children"."firstName" AS "Children.firstName", "Children"."lastName" AS "Children.lastName", "Children"."createdAt" AS "Children.createdAt", "Children"."updatedAt" AS "Children.updatedAt", "Children"."RegistrationId" AS "Children.RegistrationId", "Children.PersonAnswers"."id" AS "Children.PersonAnswers.id", "Children.PersonAnswers"."createdAt" AS "Children.PersonAnswers.createdAt", "Children.PersonAnswers"."updatedAt" AS "Children.PersonAnswers.updatedAt", "Children.PersonAnswers"."AnswerId" AS "Children.PersonAnswers.AnswerId", "Children.PersonAnswers"."RegistrationId" AS "Children.PersonAnswers.RegistrationId", "Children.PersonAnswers"."ChildrenId" AS "Children.PersonAnswers.ChildrenId", "Children.PersonAnswers"."QuestionId" AS "Children.PersonAnswers.QuestionId", "Children.PersonAnswers.Question"."id" AS "Children.PersonAnswers.Question.id", "Children.PersonAnswers.Question"."questionText" AS "Children.PersonAnswers.Question.questionText", "Children.PersonAnswers.Question"."order" AS "Children.PersonAnswers.Question.order", "Children.PersonAnswers.Question"."isActive" AS "Children.PersonAnswers.Question.isActive", "Children.PersonAnswers.Question"."isMinor" AS "Children.PersonAnswers.Question.isMinor", "Children.PersonAnswers.Question"."createdAt" AS "Children.PersonAnswers.Question.createdAt", "Children.PersonAnswers.Question"."updatedAt" AS "Children.PersonAnswers.Question.updatedAt", "Children.PersonAnswers.Answer"."id" AS "Children.PersonAnswers.Answer.id", "Children.PersonAnswers.Answer"."answerText" AS "Children.PersonAnswers.Answer.answerText", "Children.PersonAnswers.Answer"."isNegative" AS "Children.PersonAnswers.Answer.isNegative", "Children.PersonAnswers.Answer"."order" AS "Children.PersonAnswers.Answer.order", "Children.PersonAnswers.Answer"."createdAt" AS "Children.PersonAnswers.Answer.createdAt", "Children.PersonAnswers.Answer"."updatedAt" AS "Children.PersonAnswers.Answer.updatedAt", "Children.PersonAnswers.Answer"."QuestionId" AS "Children.PersonAnswers.Answer.QuestionId" FROM (SELECT "Registration".* FROM "Registration" WHERE "Registration"."id"=64 LIMIT 1) AS "Registration" LEFT OUTER JOIN "Persons" AS "Person" ON "Person"."id" = "Registration"."PersonId" LEFT OUTER JOIN "PersonAnswers" AS "PersonAnswers" ON "Registration"."id" = "PersonAnswers"."RegistrationId" LEFT OUTER JOIN "Questions" AS "PersonAnswers.Question" ON "PersonAnswers.Question"."id" = "PersonAnswers"."QuestionId" LEFT OUTER JOIN "Answers" AS "PersonAnswers.Answer" ON "PersonAnswers.Answer"."id" = "PersonAnswers"."AnswerId" LEFT OUTER JOIN "Children" AS "Children" ON "Registration"."id" = "Children"."RegistrationId" LEFT OUTER JOIN "PersonAnswers" AS "Children.PersonAnswers" ON "Children"."id" = "Children.PersonAnswers"."ChildrenId" LEFT OUTER JOIN "Questions" AS "Children.PersonAnswers.Question" ON "Children.PersonAnswers.Question"."id" = "Children.PersonAnswers"."QuestionId" LEFT OUTER JOIN "Answers" AS "Children.PersonAnswers.Answer" ON "Children.PersonAnswers.Answer"."id" = "Children.PersonAnswers"."AnswerId"; 

[Jeri Lamy]

This is quite old, however I ran into the same thing so I thought I would post my findings.

To simplify my answer I will just say that I have a highly relational database structure where for a single object I sometimes need to pull several children and grandchildren as includes.  When Sequelize generates the query for this find, it concatenates the path similar to this:

Order.OrderGroupItem.OrderGroupItemDelivery.estimatedItemDeliveryDate

i.e.

"SELECT Order.OrderGroupItem.OrderGroupItemDelivery.estimatedItemDeliveryDate AS Order.OrderGroupItem.OrderGroupItemDelivery.estimatedItemDeliveryDate FROM..." blah blah blah.

69 total characters.  PostGres (and other databases) have a limitation on the length of column names...for PostGres 9.4, it is 64 characters, or 63 effective characters followed by a trailing blank.  So this SELECT is returned from PostGres with the name truncated to 63 characters:

"Order.OrderGroupItem.OrderGroupItemDelivery.estimatedItemDelive"

Sequelize actually handled it fairly well, and returned the Model as such with the truncated properties.  Previous versions of PostGres had even shorter limits.  However, this causes an obvious issue when you go to update with values from that returned Model as they will not match up with what you have defined in Sequelize.

Unfortunately, there doesn't appear to be an easy fix for this unless you'd like to maintain your own version of PostGres, because you have to change the NAMEDATALEN property in their config files, and recompile.

http://doxygen.postgresql.org/pg__config__manual_8h_source.html

For our application that isn't really an option, so it appears that we will have to change the way that we do these finds to remove some grandchild includes.  We will be looking into doing separate finds for those objects and assigning them to the main Model.

[Mick Hansen]

Jeri: We're working on `include.seperate` (implemented for HasMany so far) which would mitigate some of this. It effectively runs the selected includes in a seperate query, does good things for performance when there's large cartesian products aswell.

--

Mick Hansen

@mhansendev
mhansen.io

[Jeri Lamy]

That would be great!  Thanks for letting me know to look out for it.

Jeri Lamy

[Cristian Rinaldi]

Any advance for this issue?