Sequelize hasMany error foreignkey not found

Help me!
1

Hello,

I have a relation between Price and Schedule, (a schedule has one price, a price can be used in some schedules)

DB:
Table price

Capture d’écran 2022-05-27 à 17.09.55
Capture d’écran 2022-05-27 à 17.09.551496×141 27.7 KB

Table schedule
Capture d’écran 2022-05-27 à 17.11.18
Capture d’écran 2022-05-27 à 17.11.181496×141 28.5 KB

My association config:

  Price.associate = (models) => {
    Price.hasMany(models.schedule, {
      foreignKey: {
        name: 'priceIdKey',
        field: 'price_id',
      },
      as: 'price',
    });
  };

  Schedule.associate = (models) => {
    Schedule.belongsTo(models.price, {
      foreignKey: {
        name: 'priceIdKey',
        field: 'price_id',
      },
      as: 'price',
    });
  }

The list view loaded well, but when i try to open a detailed view i got an error from sequelize with the SQL request : Unexpected error: column price.price_id does not exist

My full request : SELECT "price"."id", "price"."created_at" AS "createdAt", "price"."updated_at" AS "updatedAt", "price"."name", "price"."amount", "price"."care_giver_amount" AS "careGiverAmount", "price"."fee_amount" AS "feeAmount", "price"."system_tag" AS "systemTag", "price"."type", "price"."price_id" AS "priceIdKey" FROM "public"."price" AS "price" WHERE "price"."id" = 'b178589a-a3a6-417f-98de-7dcbf74bf7fc';

2

Ok i just tried by deleting the both association, and i still got the error…

4

Hello @Justin_Martin,

Thanks for your feedback. Can you please share with us the information about your configuration, that is asked when you create a post on the community?

Please provide in this mandatory section, the relevant information about your configuration:

  • Project name: …
  • Team name: …
  • Environment name: …
  • Agent type & version: …

Can you also share with us the code for your models schedule and price? The error shows that a column price_id is declared on the model named price, whereas this column should not have been declared here but on the schedule model.

5

My error is in dev environnement:

Agent type

  • forest-express-sequelize@8.5.7
  • sequelize@5.15.1

I have the same error by removing association between price and schedule

Price:

const { PRICE_TYPE } = require('../enums/price.enum');
// This model was generated by Forest CLI. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const Price = sequelize.define(
    'price',
    {
      id: {
        type: DataTypes.UUID,
        primaryKey: true,
        defaultValue: Sequelize.literal('uuid_generate_v4()'),
        allowNull: false,
      },
      createdAt: {
        type: DataTypes.DATE,
        defaultValue: Sequelize.literal('now()'),
      },
      updatedAt: {
        type: DataTypes.DATE,
        defaultValue: Sequelize.literal('now()'),
      },
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      amount: {
        type: DataTypes.DECIMAL(20, 2),
        allowNull: false,
      },
      careGiverAmount: {
        type: DataTypes.DECIMAL(20, 2),
        allowNull: false,
      },
      feeAmount: {
        type: DataTypes.DECIMAL(20, 2),
        allowNull: false,
      },
      systemTag: {
        type: DataTypes.STRING,
        allowNull: false,
        default: '',
      },
      type: {
        type: DataTypes.ENUM(Object.values(PRICE_TYPE)),
        default: PRICE_TYPE.FAMILY_V1,
      },
    },
    {
      tableName: 'price',
      underscored: true,
      schema: process.env.DATABASE_SCHEMA,
      timestamps: true,
    },
  );

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Price.associate = (models) => {
    Price.belongsTo(models.announce, {
      foreignKey: {
        name: 'announceIdKey',
        field: 'announce_id',
      },
      as: 'announce',
    });
  };

  return Price;
};
6

Indeed if i create column “price_id” in “price” it works :thinking:

7

Thanks for sharing the code of the price model. I cannot see anything wrong with this declaration. The problem should have been caused by another declaration in your model.

Can you share the schedule model too?

Is there another model in your code that is associated to price?

8

I removed the relation between schedule and price.

models/announce.js :

const { union } = require('lodash');
const {
  CONTRACT_TYPE,
  ANNOUNCE_STATUS,
  ANNOUNCE_TYPE,
  FAMILY_SERVICES,
  INSTITUTION_SERVICES,
  ADDITIONAL_NEEDS,
} = require('../enums/announce.enum');
const { GENDER, LANGUAGE_CODE } = require('../enums/person.enum');
// This model was generated by Forest CLI. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const Announce = sequelize.define(
    'announce',
    {
      id: {
        type: DataTypes.UUID,
        primaryKey: true,
        defaultValue: Sequelize.literal('uuid_generate_v4()'),
        allowNull: false,
      },
      createdAt: {
        type: DataTypes.DATE,
        defaultValue: Sequelize.literal('now()'),
      },
      updatedAt: {
        type: DataTypes.DATE,
        defaultValue: Sequelize.literal('now()'),
      },
      startDate: {
        type: DataTypes.DATE,
      },
      endDate: {
        type: DataTypes.DATE,
      },
      contractType: {
        type: DataTypes.ENUM(Object.values(CONTRACT_TYPE)),
      },
      hourSalary: {
        type: DataTypes.INTEGER,
      },
      status: {
        type: DataTypes.ENUM(Object.values(ANNOUNCE_STATUS)),
        defaultValue: ANNOUNCE_STATUS.DRAFT,
        allowNull: false,
      },
      announceType: {
        type: DataTypes.ENUM(Object.values(ANNOUNCE_TYPE)),
        defaultValue: ANNOUNCE_TYPE.FAMILY,
        allowNull: false,
      },
      additionalInformation: {
        type: DataTypes.STRING,
      },
      services: {
        type: DataTypes.ARRAY(
          DataTypes.ENUM(Object.values(union(FAMILY_SERVICES, INSTITUTION_SERVICES))),
        ),
      },
      hasSeniorityBonus: {
        type: DataTypes.BOOLEAN,
      },
      hasBonus: {
        type: DataTypes.BOOLEAN,
      },
      numberOfProfiles: {
        type: DataTypes.INTEGER,
        defaultValue: 1,
      },
      additionalNeeds: {
        type: DataTypes.ARRAY(DataTypes.ENUM(Object.values(ADDITIONAL_NEEDS))),
        defaultValue: Sequelize.literal("'{}'::announce_additional_needs_enum[]"),
      },
      careGiverGenderWanted: {
        type: DataTypes.ARRAY(DataTypes.ENUM(Object.values(GENDER))),
        defaultValue: Sequelize.literal(
          `'{${GENDER.MAN}, ${GENDER.WOMAN}}'::announce_care_giver_gender_wanted_enum[]`,
        ),
      },
      careGiverLanguageWanted: {
        type: DataTypes.ENUM(Object.values(GENDER)),
        defaultValue: LANGUAGE_CODE.FRENCH,
      },
      reference: {
        type: DataTypes.INTEGER,
        defaultValue: Sequelize.literal("nextval('announce_reference_seq'::regclass)"),
        allowNull: false,
      },
      dispute: {
        type: DataTypes.BOOLEAN,
        defaultValue: false,
        allowNull: false,
      },
      nextCallDate: {
        type: DataTypes.DATEONLY,
      },
      publishedAt: {
        type: DataTypes.DATEONLY,
      },
    },
    {
      tableName: 'announce',
      underscored: true,
      schema: process.env.DATABASE_SCHEMA,
      timestamps: true,
    },
  );

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Announce.associate = (models) => {
    Announce.belongsTo(models.beneficiary, {
      foreignKey: {
        name: 'beneficiaryIdKey',
        field: 'beneficiary_id',
      },
      as: 'beneficiary',
    });
    Announce.belongsTo(models.backofficeAdmin, {
      foreignKey: {
        name: 'backofficeAdminKey',
        field: 'backoffice_admin',
      },
      as: 'backofficeAdmin',
    });
    Announce.belongsTo(models.institution, {
      foreignKey: {
        name: 'institutionIdKey',
        field: 'institution_id',
      },
      as: 'institution',
    });
    Announce.belongsTo(models.certificate, {
      foreignKey: {
        name: 'certificateOfProfilesIdKey',
        field: 'certificate_of_profiles_id',
      },
      targetKey: 'id',
      as: 'certificateOfProfiles',
    });
    Announce.belongsToMany(models['care-giver'], {
      through: 'proposedAnnounce',
      foreignKey: 'announce_id',
      otherKey: 'care_giver_id',
      as: 'careGiverThroughProposedAnnounces',
    });
    Announce.hasMany(models.technicalInfo, {
      foreignKey: {
        name: 'announceIdKey',
        field: 'announce_id',
      },
      as: 'technicalInfos',
    });
    Announce.hasMany(models.schedule, {
      foreignKey: {
        name: 'announceIdKey',
        field: 'announce_id',
      },
      as: 'schedules',
    });
    Announce.hasMany(models.application, {
      foreignKey: {
        name: 'announceIdKey',
        field: 'announce_id',
      },
      as: 'applications',
    });
    Announce.hasMany(models.mission, {
      foreignKey: {
        name: 'announceIdKey',
        field: 'announce_id',
      },
      as: 'missions',
    });
    Announce.hasMany(models.price, {
      foreignKey: {
        name: 'priceIdKey',
        field: 'price_id',
      },
      as: 'prices',
    });
    Announce.hasOne(models.lead, {
      foreignKey: {
        name: 'announceIdKey',
        field: 'announce_id',
      },
      as: 'lead',
    });
  };

  return Announce;
};
9

Oh i try to remove my relation between announce and price and the error gone. What is wrong with my relation

10

Ok changed my Announce relation with price with this and it works:

    Announce.hasMany(models.price, {
      foreignKey: {
        name: 'announceIdKey',
        field: 'announce_id',
      },
      as: 'prices',
    });
11

Thanks for sharing your solution.

Indeed the declaration of the association was not correct. When you use a hasMany association, it means that the linked table (here price) will have a foreign key to your target table (here announce). So the previous version of your code actually declared a column price_id on your price table, which was not correct.