In managing customer surveys, I have encountered a problem with the many-to-many polymorphic association setup. The issue arises when using the 'add' mixin on the model instance of 'Survey'. If the joining table already contains an item with the 'surveyed' field matching the ID of the new surveyable entity, it gets overwritten.
'survey' table:
| id | name |
|---|---|
| 1 | 'Customer Survey' |
'scheduled_sessions' table:
| id | appointment_data |
|---|---|
| 10 | { "someData" : [] } |
'service_provider' table:
| id | name |
|---|---|
| 10 | Joe Doe |
'survey_surveyable' table:
| survey | surveyable | surveyed |
|---|---|---|
| 1 | serviceProvider | 10 |
When adding a scheduled session with the same ID as a service provider, the data in the join table gets overwritten by default:
const surveyInstance = await DB.Survey.findByPk(1);
const scheduledSessionInstance = await DB.ScheduledSession.findByPk(10);
surveyInstance.addScheduledSession(
scheduledSessionInstance,
{ through: { surveyable: "scheduledSession" } }
);
return surveyInstance.save();
The generated SQL queries by Sequelize show how the overwrite occurs:
SELECT "id", "name"
FROM "surveys" AS "Survey"
WHERE "Survey"."id" = 1;
SELECT "id", "appointment_data" AS "appointmentData"
FROM "scheduled_sessions" AS "ScheduledSession"
WHERE "ScheduledSession"."id" = 10;
SELECT "survey", "surveyable", "surveyed"
FROM "survey_surveyable" AS "SurveySurveyable"
WHERE
"SurveySurveyable"."survey" = 1 AND
"SurveySurveyable"."surveyed" IN (10);
UPDATE "survey_surveyable"
SET "surveyable"=$1
WHERE
"survey" = $2 AND
"surveyed" = $3