Trouble with custom relationship table

Benjamin_Marquis · July 28, 2020, 6:47pm

Hello, I am having some trouble with some custom relationship table.

In my models, I have a Software model, and a Feature model, the software can have many features.
I also have a featureMappingStatus model, so that the ralationship between a software and a feature has this mappingStatus as well, like so:

So I created a software-feature model that belong to the 3 other model to be able to see all the info in admin forest.

my models are defined like this

FEATURE:

// This model was generated by Lumber. 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 Feature = sequelize.define('feature', {
    nameEn: { type: DataTypes.STRING, },

    // uuid      : { type: DataTypes.UUID, },
    // nameFr    : { type: DataTypes.STRING, },
    created_at: {
      type        : 'TIMESTAMP',
      defaultValue: 'now()',
    },
    updated_at: { type: DataTypes.DATE, },
  }, {
    hooks: {
      async afterCreate(feature) {

        const { software } = sequelize.models;

        await feature.setSoftware(await software.findAll());

      },
    },
    tableName  : 'feature',
    underscored: true,
    schema     : process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Feature.associate = models => {

    Feature.hasMany(models.softwareFeature, {
      foreignKey: {
        field: 'feature_id',
      },
      as: 'softwareFeature'
    });

    Feature.belongsToMany(models.software, {
      through   : 'software_feature',
      foreignKey: 'feature_id',
      otherKey  : 'software_id',
    });

    Feature.belongsToMany(models.recommandation, {
      through   : 'recommandation_feature',
      foreignKey: 'feature_id',
      otherKey  : 'recommandation_id',
    });

    Feature.belongsToMany(models.label, {
      through   : 'label_feature',
      foreignKey: 'feature_id',
      otherKey  : 'label_id',
    });

    Feature.belongsTo(models.featureLevel, {
      as: 'featureLevel'
    });

  };

  return Feature;

};

SOFTWARE:

// This model was generated by Lumber. 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 Software = sequelize.define('software', {

    // uuid       : { type: DataTypes.UUID, },
    name       : { type: DataTypes.STRING, },
    logo       : { type: DataTypes.STRING, },
    domain     : { type: DataTypes.STRING, },
    supportPage: { type: DataTypes.STRING, },
    created_at : {
      type        : 'TIMESTAMP',
      defaultValue: 'now()',
    },
    updated_at: { type: DataTypes.DATE, },
  }, {
    hooks: {
      async afterCreate(software) {

        const { feature } = sequelize.models;

        await software.setFeatures(await feature.findAll());

      },
    },
    tableName  : 'software',
    underscored: true,
    schema     : process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Software.associate = models => {

    Software.belongsTo(models.softwareType);

    // Software.hasMany(models.recommandationSoftware,);

    // Software.hasMany(models.internalLink);

    Software.belongsToMany(models.feature, {
      through   : 'software_feature',
      foreignKey: 'software_id',
      otherKey  : 'feature_id',
    });

    Software.hasMany(models.label);

    Software.hasMany(models.softwareFeature, {
      foreignKey: {
        field: 'software_id',
      },
      as: 'softwareFeature'
    });

  };

  return Software;

};

MAPPING STATUS

// This model was generated by Lumber. 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 FeatureMappingStatus = sequelize.define('featureMappingStatus', {

    // uuid      : { type: DataTypes.UUID, },
    name      : { type: DataTypes.STRING, },
    created_at: {
      type        : 'TIMESTAMP',
      defaultValue: 'now()',
    },
    updated_at: { type: DataTypes.DATE, },
  }, {
    tableName  : 'feature_mapping_status',
    underscored: true,
    schema     : process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  FeatureMappingStatus.associate = models => {

    FeatureMappingStatus.hasMany(models.softwareFeature, {
      foreignKey: {
        name : 'featureMappingStatusIdKey',
        field: 'feature_mapping_status_id',
      },
    });

  };

  return FeatureMappingStatus;

};

SOFTWARE FEATURE

// This model was generated by Lumber. 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 Software = sequelize.define('software', {

    // uuid       : { type: DataTypes.UUID, },
    name       : { type: DataTypes.STRING, },
    logo       : { type: DataTypes.STRING, },
    domain     : { type: DataTypes.STRING, },
    supportPage: { type: DataTypes.STRING, },
    created_at : {
      type        : 'TIMESTAMP',
      defaultValue: 'now()',
    },
    updated_at: { type: DataTypes.DATE, },
  }, {
    hooks: {
      async afterCreate(software) {

        const { feature } = sequelize.models;

        await software.setFeatures(await feature.findAll());

      },
    },
    tableName  : 'software',
    underscored: true,
    schema     : process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Software.associate = models => {

    Software.belongsTo(models.softwareType);

    // Software.hasMany(models.recommandationSoftware,);

    // Software.hasMany(models.internalLink);

    Software.belongsToMany(models.feature, {
      through   : 'software_feature',
      foreignKey: 'software_id',
      otherKey  : 'feature_id',
    });

    Software.hasMany(models.label);

    Software.hasMany(models.softwareFeature, {
      foreignKey: {
        field: 'software_id',
      },
      as: 'softwareFeature'
    });

  };

  return Software;

};

Expected behavior

Be able to update a Software Feature row

Actual behavior

The Software Feature model i defined that belong to the 3 models has an id like this -> 169|62
so i can’t update it

Failure Logs

Please include any relevant log snippets, if necessary.

Context

Please provide any relevant information about your setup.

“forest-express-sequelize”: “^6.0.0”,
“express”: “~4.16.3”,
“sequelize”: “~5.15.1”,
Database Dialect: postgres
“pg”: “~6.1.0”,
Project Name: toolt-mvp

vince · July 29, 2020, 6:49am

Hi @Benjamin_Marquis,

Could you please share a video reproducing your issue (You could use loom for instance) ?
With the video I should clearly understand your issue because as of right now I’m not sure what it is . Did you mean that you want to update the id of the record ?

Benjamin_Marquis · July 29, 2020, 10:56pm

Hi @vince sorry for the delay here are some screenshot for more information, let me know if it is enougth.

-> this is a row of featureSoftware model (from join table) so far so good notice a proper id

-> but when i click on it the id become , so imposible to change the feature mapping feature
Screenshot from 2020-07-29 18-52-04

_> i get following error

invalid input syntax for type integer: "53|21

Benjamin_Marquis · July 29, 2020, 11:41pm

Well simply adding did this the trick haha

SoftwareFeature.belongsTo(models.featureMappingStatus, {
      foreignKey: {
                name : 'featureMappingStatusIdKey',
                field: 'feature_mapping_status_id',
              },
      as: 'featureMappingStatus'
    });

Thanks @vince for all your help !

Benjamin_Marquis · July 30, 2020, 12:07am

Actually i still get the same issue in on one of my other environement with same base code…

Maybe you could reset the layout ?

Benjamin_Marquis · July 30, 2020, 9:31pm

Hello @vince, any news or information you would like me to add ?

Sliman_Medini · July 31, 2020, 8:59am

Hi @Benjamin_Marquis,

Is your project name Tooly-MVP? What environment still contains the issue?

Before resetting manually your layout, please consider this should be done automatically since the layout is refreshed as soon as your API restarts and contains a diff between current and previous models.

Can you share the use-case (if it’s clear for you) that drives to need a manual reset? (We’ll try to automate it)

Benjamin_Marquis · July 31, 2020, 4:21pm

hi @Sliman_Medini thanks for ytour help, this my idea of what could fix my problem but I migth be wrong, I’ll add a video that show what is wrong (I my development environment the id of a related model is fine but in my other environments the id when i click on a specific related model is messed up)

You can download video here : https://we.tl/t-vtWM4Hv1fJ

All the environement have exact same code base and freshly build (I deploy the app with kubernetes, so fresh docker contasiner build)

jeffladiray · August 3, 2020, 9:02am

Hi @Benjamin_Marquis,

Ids that are generated with a | are composite primary keys, and we currently have a few issue with composite key support.
Since all your tables have an id (Which I suppose is a primary key), I can’t see any reason for your lumber project not to use it.

Do you have any composite keys contraints set on your database ?

(I can still reset your layout if you want, but IMO it wouldn’t fix the issue. Just let me know if you still want to try this)

Benjamin_Marquis · August 3, 2020, 11:46am

Hie @jeffladiray, thanks for helping. I do not have any composite primary keyz in any of my table
Let’s try to reset the dev environement and see how it goe maybe ?

jeffladiray · August 3, 2020, 11:57am

Seems like you have 2 environments that could match dev

You mean the “Development” environment or the “dev” environment ? (Or both?)

Benjamin_Marquis · August 3, 2020, 12:07pm

The Dev environement please !

jeffladiray · August 3, 2020, 12:11pm

Yes, I just did it, let me know if that helped

Benjamin_Marquis · August 3, 2020, 12:50pm

hummm it seems that the problem is still here /

jeffladiray · August 3, 2020, 1:02pm

Just to make sure, could you please check that the primary key with | is formed by associating 2 PK of your models (And eventually tell me which one of the three is ignored ?). That would make it easier for me to debug.

I still find this weird since your database schema seems to have ids, so I can’t find any reason for forest-express-sequelize not to use the direct ids.

Benjamin_Marquis · August 3, 2020, 1:10pm

That’s the thing those number**|**number reference nothing, and are the same for all record of this join model.
Here a little video

jeffladiray · August 3, 2020, 1:21pm

Just watched your video. I can clearly see the id column before you click on a record, so we should be able to do something

I checked your first message and it seems like the model SoftwareFeature was miscopied. Could you please share it here ?

Benjamin_Marquis · August 3, 2020, 1:30pm

Of course! Thanks for helping here it is

SofwareFeaturte:

/* eslint-disable import/no-cycle */
import { Sequelize, DataTypes } from 'sequelize';
import { Models } from 'types/model';
import { Feature } from './feature';
import { Software } from './software';
import { FeatureMappingStatus } from './feature-mapping-status';
import BaseModel from './base-model';

export class SoftwareFeature extends BaseModel {

  public feature!: Feature;
  public software!: Software;
  public featureMappingStatus!: FeatureMappingStatus;

  static associate = (models: Models) => {

    SoftwareFeature.belongsTo(models.feature, {
      foreignKey: {
        name : 'featureIdKey',
        field: 'feature_id',
      },
      as: 'feature'
    });

    SoftwareFeature.belongsTo(models.software, {
      foreignKey: {
        name : 'softwareIdKey',
        field: 'software_id',
      },
      as: 'software'
    });

    SoftwareFeature.belongsTo(models.featureMappingStatus, {
      foreignKey: {
        name : 'featureMappingStatusIdKey',
        field: 'feature_mapping_status_id',
      },
      as: 'featureMappingStatus'
    });

  };

}

export default (sequelize: Sequelize): typeof SoftwareFeature => {

  SoftwareFeature.init({
    created_at: {
      type        : 'TIMESTAMP',
      defaultValue: 'now()',
    },
    updated_at: { type: DataTypes.DATE, },
  }, {
    tableName  : 'software_feature',
    modelName  : 'softwareFeature',
    underscored: true,
    sequelize
  });

  return SoftwareFeature;

};

jeffladiray · August 3, 2020, 1:48pm

Are all your models reworked with typescript/new sequelize class ?

Also, I’m pretty sure this would not change anything, but could you try adding the id field of your collection, also with the primaryKey: true parameters ? Just to ensure that sequelize will use id instead of the strange composite key you are getting ? (Just like in this example)

Benjamin_Marquis · August 3, 2020, 1:57pm

Yes, all my models are typescript.

so i added this

SoftwareFeature.init({
    id: {
      type         : DataTypes.INTEGER,
      primaryKey   : true,
      autoIncrement: true
    },
    created_at: {
      type        : 'TIMESTAMP',
      defaultValue: 'now()',
    },
    updated_at: { type: DataTypes.DATE, },
  }, {
    tableName  : 'software_feature',
    modelName  : 'softwareFeature',
    underscored: true,
    sequelize
  });

But no luck it didnt work

Topic		Replies	Views
Creating many to many relation when using custom property Help me!	1	218	March 6, 2023
Adding relationship between two tables from two different databases Help me! howtos	6	27	August 5, 2024
Relationship not working on Production Mode Help me! howtos , setup	11	783	June 16, 2020
Can't create new entry with custom relationships Help me!	7	732	January 20, 2021
Can't create data Help me!	15	436	February 2, 2021

Trouble with custom relationship table

Expected behavior

Actual behavior

Failure Logs

Context

Related topics