Fields from Smart relationship are not available for filtering

What problem does this solve for you?

I’m trying to add Smart relationship to collection …
image

… but for some reason properties from related models are not available in filters on the main collection

Hello @Vasilii,

Thanks for your feedback.

Unfortunately, Forest Admin does not support filtering on Smart Fields or Smart Relationships. Filters are directly used to create SQL queries, in order to run performant researches on your tables.

As described in this thread: Smart field filtering - #4 by lamatt_v you can define smart segments if you want to create a pre-defined set of searches.

In your case, why did you create a smart relationship, and not directly a hasOne association in your model? It seems to have a direct relation between a lead an a job.

@GuillaumeGautreau, thanks for your response!

I’ve created a smart relationship with job just to test it out.
In fact, we want to add a table, that will have a ‘1-to-1’ relationship with Job, but won’t have any relationships with Leads.

As I understand, there’s no such option is sequelize to setup a ‘1-to-1 through’ relationship. It’s only ‘many-to-many’ relationship, that supports junction table. ‘Many-to-many’ cannot be used for Forest Admin filtering purposes either.

So, our solution was to imitate ‘1-to-1 through’ with the help of Smart relationship.

Unfortunately, solution provided by @lamatt_v does not meet the set requirements.
Filtering criteria must be adjusted on the fly.

Hi @Vasilii,

You’re right, neither sequelize nor ForestAdmin exposes a way to handle 1-1 through relationship as it is just basically two separated 1-1 relationships.

However, if your need is to make this relationship appear in the first table, you can use smart relationships to “hide” the through table.

But as noted by Guillaume, you won’t be able to filter (on the fly) those relationship fields.

Hi Vasilii,

Just wondering => the relationship between Leads and Job seems to be in the same database.
Am I right?
In this case, why do you need a smart relationship instead of a BelongsTo in your model definition?

Hi, @SebastienP

Thanks for your attention and willingness to help.
Please, see my comment above with the explanation and reasoning.

Sorry Vasilii I did not notice your elaboration.
I could suggest a workaround but it depends on the database you are using
Is it a postgres or mysql or sql server database?
Could you provide a simple DDL of your 3 tables to elaborate an accurate sample?

Hi, @SebastienP!

We are using PostgreSQL.

Simplified diagram would look something like that:

The end goal here is to be able to filter Leads collection by the fields from the JobAlias (in this example it’s Alias text column).

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

@SebastienP, looks like a proper solution for our challenges. I will try it out, thanks!

2 Likes

@SebastienP, your solution is a perfect fit, many thanks! :raised_hands:t2:

3 Likes