Filtering on Smart Field through a relationship

Help me!
1

I have the following set up.
Two tables.
Accounts and Users
User table has a JSONB field called tags that I expose in a simplified smart field. I can see an internal value of the JSONB field and filter on it using the following code

fields:[{
isSpecialField: {
    field: 'isSpecial',
    type: 'Boolean',
    isFilterable: true,
    get: (user) => {
      return !!(user.tags?.special);
    },
    filter: async ({ condition, where }) => {
      const positive = condition.operator === 'equal' && !!condition.value || condition.operator === 'not_equal' && !condition.value
      return { tags: { special: positive } }
      }
    }
  }
}]

This works great if I’m looking at the user table, I see a column for isSpecial and I can filter it for both positive and negative values.

My issue is when I want to run that same filter but through an association.
Each Account belongsTo a User - which is exposed fine.
But when I try to filter the Account Table by [account].[user].[isSpecial] = true
I get an error from Forest and I get the following error in the console.
[forest] 🌳🌳🌳 Unexpected error: column User.isSpecial does not exist

and it doesn’t go through my filter func at all.

Is there a way to support filters on smart field via relationships?

1 Like
2

Hello @yonbergman and welcome to the ForestAdmin community.

Filtering on SmartField via a relationship is possible on some cases.

From what I understand from you post, the code below should work:

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

collection('users', {
  actions: [],
  fields:[{
        field: 'isSpecial',
        type: 'Boolean',
        isFilterable: true,
        get: (user) => {
          return !!(user.tags?.special);
        },
        filter: async ({ condition }) => {
          const positive = condition.operator === 'equal' && !!condition.value || condition.operator === 'not_equal' && !condition.value

          const conditionFieldParts = condition.field.split(':');
          let alias = '';
          if (conditionFieldParts.length == 2) alias = `"${conditionFieldParts[0]}".`;

          return sequelize.literal(`${alias}"tags"->>'special' = '${positive}'`);
          },
      },
  ],
  segments: [],
});

Please note the conditionFieldParts part which handle the case where the filter is applied from the Users collection or from the Accounts one.

This should work as Sequelize filter condition (condition.field) is actually the table alias.

I also removed the isSpecialField: { level in your code to match the expected definition of a SmartField.

Hope this helps.

3

Thanks @anon79585656.
It still doesn’t work.

That code works great if I try to filter the User table, but when I go to Account and try to filter by association it fails for the same reason

Executing (default): SELECT count("account"."id") AS "count" FROM "public"."account" AS "account" LEFT OUTER JOIN "public"."user" AS "user" ON "account"."userId" = "user"."id" WHERE "user"."isSpecial" = true;
[forest] 🌳🌳🌳  Unexpected error: column user.isSpecial does not exist

Perhaps there’s something that I need to change in the default router behavior?

4

@yonbergman

The code seem to work both from accounts and from users on my end. We may still have a slight difference in our implementations.

Here are the requests performed on my end from:

  • accounts:
SELECT count("accounts"."id") AS "count" FROM "public"."accounts" AS "accounts" LEFT OUTER JOIN "public"."users" AS "user" ON "accounts"."user_id" = "user"."id" WHERE "user"."tags"->>'special' = 'false';
  • users:
SELECT count(*) AS "count" FROM "public"."users" AS "users" WHERE "tags"->>'special' = 'false';

As the request in your message ends by WHERE "user"."isSpecial" (and not WHERE "tags"->>'special') it recon the custom filter implementation is not properly called.

Could you try to set a console.log or a debugger breakpoint in it (at const positive = condition.operator) to ensure the call is made?
You can also log condition.

I see isFilterable: true, in your first message and in my snippet. Did you also check the matching option in the SmartField setting in you admin panel?
You can see it in the second screenshot of this documentation section:

5

That’s weird :slight_smile:

Just add console.logs in the custom getter and filter methods. while querying the user table they’re called as expected.

FILTER {
   condition: { field: 'isSpecial', operator: 'equal', value: false },
   where: { [Symbol(eq)]: false }
}

But when querying the Account table It doesn’t get called.

Can you share your model and router code too?
Perhaps something in the association is defined incorrectly?

6

@yonbergman,

Users 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 Users = sequelize.define('users', {
    name: {
      type: DataTypes.STRING,
    },
    tags: {
      type: DataTypes.JSONB,
      defaultValue: {},
    },
  }, {
    tableName: 'users',
    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.
  Users.associate = (models) => {
    Users.hasMany(models.accounts, {
      foreignKey: {
        name: 'userIdKey',
        field: 'user_id',
      },
      as: 'accounts',
    });
  };

  return Users;
};

Users table schema:

-- DDL generated by Postico 1.5.17
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name text,
    tags jsonb DEFAULT '{}'::jsonb
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX users_pkey ON users(id int4_ops);

Accounts 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 Accounts = sequelize.define('accounts', {
    name: {
      type: DataTypes.STRING,
    },
  }, {
    tableName: 'accounts',
    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.
  Accounts.associate = (models) => {
    Accounts.belongsTo(models.users, {
      foreignKey: {
        name: 'userIdKey',
        field: 'user_id',
      },
      as: 'user',
    });
  };

  return Accounts;
};

Accounts table schema:

-- DDL generated by Postico 1.5.17
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name text,
    user_id integer REFERENCES users(id)
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX accounts_pkey ON accounts(id int4_ops);

I have no custom code in route files.

7

OK
Thanks @anon79585656

After more deep diving the issue was just using an old version of forest-express-sequalize. upgrading it to the latest version solved the issue.
Thanks!