Customize relational tables

Feature(s) impacted

relationships and collections

Observed behavior

smart field doesn’t fetch the record

Expected behavior

get the record in smart field in a relational table

Context

we have two tables that have many-to-many relationship
let’s call them tasks, and rewards
both rewards, and tasks has id, and title fields
to connect tasks and rewards we have a pivot table called rewards_tasks which has a reward_id, and task_id

in the rewards_tasks collection I’m trying to add a smart field to show the title of rewards, and the title the of tasks instead of their id, but when I try to add a smart field, it doesn’t show the value, it doesn’t even try to fetch the record to get the title from

this code works on other collections that are not relational, but it’s not working on this one
is there any other way to get the title from the related entities?

thank you

  • Project name: …Merit Circle
  • Team name: Merit Circle
  • Environment name: all

Hello @Reza_Erami,

Thanks for your message :raised_hands:

There should be no issue with what you’re trying to achieve.
I just tried on my side and I didn’t face any issue.

Are you able to see the Task Title field in the UI?
When it is displayed, can you see the logs in your server?
The record you are displaying (a reward_task) should be able to access to a reward (or at least a reward_id, so that you can fetch the reward in that case) and so access to the reward title.

Let me know where you’re at in your test.
Cheers!

nope, there is no log available, seems “get” method doesn’t work in join tables
for example, this code works in the rewards (giveaways) table
image

and as you see the same code doesn’t work on a join table

Hello @Reza_Erami,

We have an idea of what could be the issue.
Do you have an id field on this join table (rewards_tasks)? Or does it use a composite key?

Let me know!

1 Like

Hey @Reza_Erami :wave:

I just did a quick test on my end and I’m currently unable to reproduce the issue.

Could you confirm that your join table indeed consider task_id & reward_id as a composite primary key ?

(Like in the following Sequelize example:)

// 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 UserProject = sequelize.define('userProject', {
    user_id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
    },
    project_id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
    },
  }, {
    tableName: 'user_project',
    underscored: true,
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });
  UserProject.removeAttribute('id');
  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  UserProject.associate = (models) => {
    UserProject.belongsTo(models.project, {
      foreignKey: {
        name: 'projectIdKey',
        field: 'project_id',
      },
      as: 'project',
    });
    UserProject.belongsTo(models.user, {
      foreignKey: {
        name: 'userIdKey',
        field: 'user_id',
      },
      as: 'user',
    });
  };

  return UserProject;
};

Thanks in advance :pray:

@jeffladiray
yes they are both primary keys
here is the full model

// 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 GiveawayTasks = sequelize.define('giveawayTasks', {
    giveawayId: {
      type: DataTypes.UUID,
      primaryKey: true,
      allowNull: false,
    },
    taskId: {
      type: DataTypes.UUID,
      primaryKey: true,
      allowNull: false,
    },
  }, {
    tableName: 'giveaway_tasks',
    timestamps: false,
    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.
  GiveawayTasks.associate = (models) => {
    GiveawayTasks.belongsTo(models.giveaway, {
      foreignKey: {
        name: 'giveawayIdKey',
        field: 'giveawayId',
      },
      as: 'giveaway',
    });
    GiveawayTasks.belongsTo(models.task, {
      foreignKey: {
        name: 'taskIdKey',
        field: 'taskId',
      },
      as: 'task',
    });
  };

  return GiveawayTasks;
};

@adriguy there is an id in database, but it’s not in the forest admin model

Oh, then that’s the way to go.

Updating your Sequelize model to make it the primary key should then fix the issue I guess.
We do support composite primary key, but they are know to be the source of potential issues.

Eventually, could you try to add the id column as the primary key, and remove it from the giveawayId & taskId in Sequelize?

Thanks in advance, and let me know if that help

@jeffladiray
nope, didn’t work

so this is my model now, very basic

// 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 GiveawayTasks = sequelize.define(
    'giveawayTasks',
    {
      id: {
        type: DataTypes.UUID,
        primaryKey: true,
        defaultValue: Sequelize.literal('uuid_generate_v4()'),
        allowNull: false,
      },
    },
    {
      tableName: 'giveaway_tasks',
      schema: process.env.DATABASE_SCHEMA,
    },
  );

  return GiveawayTasks;
};

and here is setting

const { collection } = require('forest-express-sequelize');
const models = require('../models');

// This file allows you to add to your Forest UI:
// - Smart actions: https://docs.forestadmin.com/documentation/reference-guide/actions/create-and-manage-smart-actions
// - Smart fields: https://docs.forestadmin.com/documentation/reference-guide/fields/create-and-manage-smart-fields
// - Smart relationships: https://docs.forestadmin.com/documentation/reference-guide/relationships/create-a-smart-relationship
// - Smart segments: https://docs.forestadmin.com/documentation/reference-guide/segments/smart-segments
collection('giveawayTasks', {
  actions: [],
  fields: [
    {field: "foo", get: () => "foo"}
  ],
  segments: [],
});

Mhh, weird, I can’t see any new .forestadmin-schema.json sent on your project. Did you restart your development server?

On the other end, maybe you could set the Reference field (Available in your collection settings) to replace Task and Reward always to their title (See our documentation) would suit your need ?

@jeffladiray yeah i was working with my personal account
I switch to the company account and did the same changes but nothing happened

the issue was on te both sides of the relationship
so I had to first add id to pivot table
then use correct methods for relationships on two ends of the pivot table
by regenerating the models it worked.

1 Like