Fields from Smart relationship are not available for filtering

Hi @Vasilii
I will describe a workaround that is used for scoping data like A → B → C where C does not have the column to enable the scoping.

Your use case is similar. To create a ‘short cut’ between Lead and JobAlias, you miss the AliasId column.
So let have it!
Postgres allows to create editable SQL View. So if we create a view on JobAlias, you will conserve the CRUD capabilities and allows sequelize to link the 2 tables.
cf. Documentation here ( Updatable Views Section) => PostgreSQL: Documentation: 9.5: CREATE VIEW
Let’s go with the implementation.
First create the View on JobAlias =>

CREATE VIEW job_alias_view AS
SELECT ja.*, (select j.id from job j where j.alias_id  = ja.id)  as job_id
FROM job_alias ja ;

Adjust the model definition for job_alias => change the tableName to the view name + add the jobId column

  const JobAlias = sequelize.define('jobAlias', {
    data: {
      type: DataTypes.STRING,
    },
    jobId: {
      type: DataTypes.INTEGER,
    },
  }, {
    tableName: 'job_alias_view',
    timestamps: false,
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

Add the Lead.belongsTo JobAlias =>

    Lead.belongsTo(models.jobAlias, {
      foreignKey: {
        name: 'jobIdKey',
        field: 'job_id',
      },
      as: 'jobAlias',
    });

Here you are!
Now you can access the JobAlias from Lead and Filtering is available too!

Let me know if this works for you

3 Likes