Unable to edit join table records

Help me!
1

Expected behavior

I can edit join table records.

Actual behavior

The SQL being fired is using 1|1 as my record id.

I receive an error:

Screen Shot 2021-07-21 at 11.17.24 AM
Screen Shot 2021-07-21 at 11.17.24 AM2436×1286 184 KB

Failure Logs

[forest] 🌳🌳🌳  Unexpected error: invalid input syntax for integer: "1|1"
{
  "name": "SequelizeDatabaseError",
  "parent": {
    "length": 98,
    "name": "error",
    "severity": "ERROR",
    "code": "22P02",
    "position": "188",
    "file": "numutils.c",
    "line": "106",
    "routine": "pg_atoi",
    "sql": "SELECT \"isFirstQuestion\", \"createdAt\", \"updatedAt\", \"questionId\", \"questionnaireId\" FROM \"questionnaire_questions\" AS \"QuestionnaireQuestion\" WHERE \"QuestionnaireQuestion\".\"questionId\" = '1|1';"
  },
  "original": {
    "length": 98,
    "name": "error",
    "severity": "ERROR",
    "code": "22P02",
    "position": "188",
    "file": "numutils.c",
    "line": "106",
    "routine": "pg_atoi",
    "sql": "SELECT \"isFirstQuestion\", \"createdAt\", \"updatedAt\", \"questionId\", \"questionnaireId\" FROM \"questionnaire_questions\" AS \"QuestionnaireQuestion\" WHERE \"QuestionnaireQuestion\".\"questionId\" = '1|1';"
  },
  "sql": "SELECT \"isFirstQuestion\", \"createdAt\", \"updatedAt\", \"questionId\", \"questionnaireId\" FROM \"questionnaire_questions\" AS \"QuestionnaireQuestion\" WHERE \"QuestionnaireQuestion\".\"questionId\" = '1|1';",
  "stack": "SequelizeDatabaseError: invalid input syntax for integer: \"1|1\"\n    at Query.formatError (/Users/camillefeghali/dev/foxxbee/forest-api/node_modules/sequelize/lib/dialects/postgres/query.js:386:16)\n    at Query.run (/Users/camillefeghali/dev/foxxbee/forest-api/node_modules/sequelize/lib/dialects/postgres/query.js:87:18)\n    at processTicksAndRejections (internal/process/task_queues.js:93:5)"
}

Context

Please provide any relevant information about your setup.

  • Package Version:
  • Express Version: ^4.17.1
  • Sequelize Version: ^6.6.2
  • Database Dialect: postgres
  • Database Version: 11.0
  • Project Name: forest-api
2

Hello @cooki

Which forest-express-sequelize package version are you using?

3

Hey @anon39940173 I am using "forest-express-sequelize": "^7.12.0"

4

ok, I’ll try to replicate updating a collection with a composite primary key.
I’ll come back to you shortly

5

Thank you, looking forward to your reply.

6

Hey @anon39940173, any update on this issue ?

8

Just finished setting up a test project
Composite pks are working on my end.

Can you show me your model file? (for the questionnairequestion model)
Also, did that happen when upgrading your agent, or it never worked?

9

Sure,

This is the QuestionnaireQuestion model:

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class QuestionnaireQuestion extends Model {
    static associate({ Questionnaire, Question }) {
      this.belongsTo(Questionnaire, {
        foreignKey: {
          name: "questionnaireId",
          field: "id",
        },
        as: "questionnaire",
        onDelete: "CASCADE",
      });

      this.belongsTo(Question, {
        foreignKey: {
          name: "questionId",
          field: "id",
        },
        as: "question",
        onDelete: "CASCADE",
      });
    }
    toJSON() {
      return { ...this.get(), id: undefined };
    }
  }
  QuestionnaireQuestion.init(
    {},
    {
      sequelize,
      tableName: "questionnaire_questions",
      modelName: "QuestionnaireQuestion",
      freezeTableName: true,
    }
  );
  return QuestionnaireQuestion;
};

This is the Questionnaire model:

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class Questionnaire extends Model {
    static associate({ Question, QuestionnaireQuestion }) {
      this.belongsToMany(Question, {
        through: { model: QuestionnaireQuestion, unique: true },
        foreignKey: "questionnaireId",
        otherKey: "questionId",
        as: "questions",
      });
    }
    toJSON() {
      return {
        ...this.get(),
        id: undefined,
        userId: undefined,
        QuestionnaireQuestion: undefined,
      };
    }
  }

  Questionnaire.init(
    {
      title: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      active: {
        type: DataTypes.BOOLEAN,
      },
    },
    {
      sequelize,
      hooks: {
      },
      tableName: "questionnaires",
      modelName: "Questionnaire",
      freezeTableName: true,
    }
  );
  return Questionnaire;
};

And this is the Question model:

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class Question extends Model {
    static associate({ Questionnaire, QuestionnaireQuestion }) {
      this.belongsToMany(Questionnaire, {
        through: { model: QuestionnaireQuestion, unique: true },
        foreignKey: "questionId",
        otherKey: "questionnaireId",
        as: "questionnaires",
      });
    }
    toJSON() {
      return {
        ...this.get(),
        questionTypeId: undefined,
        createdAt: undefined,
        updatedAt: undefined,
      };
    }
  }
  Question.init(
    {
      description: {
        type: DataTypes.STRING,
        allowNull: false,
        validate: {
          notNull: { msg: "Question must have a description" },
          notEmpty: { msg: "Description must not be empty" },
        },
      },
    },
    {
      sequelize,
      tableName: "questions",
      modelName: "Question",
      freezeTableName: true,
    }
  );
  return Question;
};
10

I am reproducing the issue, will come back to you shortly

11

I’m filling a bug report on our issue tracker

As a workaround, customers usually associate and dissociate relationships from the related data page.

Is this working for you?

image
image1157×342 50.3 KB

12

Hey @anon39940173, adding the fields to my models’ init function fixed it:

Questionnaire.init(
    {
      id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
      },
      title: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      active: {
        type: DataTypes.BOOLEAN,
      },
    },
    {
      sequelize,
      hooks: {
      },
      tableName: "questionnaires",
      modelName: "Questionnaire",
      freezeTableName: true,
    }
  );
13

Happy to hear that you found a solution.

Still, editing a value that is used in a composite pk is broken, so I’m leaving the bug report

1 Like