# Filtering on Smart Field through a relationship

**URL:** <https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820>\
**Category:** Help me!\
**Created:** [December 2, 2021, 11:46am UTC](https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820 "2021-12-02T11:46:30Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![yonbergman](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/yonbergman/32/3668_2.png) [@yonbergman](https://community.forestadmin.com/u/yonbergman)\
**Post date:** [December 2, 2021, 11:46am UTC](https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820/1 "2021-12-02T11:46:30Z")

</div>

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

```javascript
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?

---

<div class="post-metadata">

**Author:** ![anon79585656](https://avatars.discourse-cdn.com/v4/letter/a/7ab992/32.png) [@anon79585656](https://community.forestadmin.com/u/anon79585656)\
**Post date:** [December 2, 2021, 6:11pm UTC](https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820/2 "2021-12-02T18:11:21Z")

</div>

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:

```javascript
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.

---

<div class="post-metadata">

**Author:** ![yonbergman](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/yonbergman/32/3668_2.png) [@yonbergman](https://community.forestadmin.com/u/yonbergman)\
**Post date:** [December 2, 2021, 9:24pm UTC](https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820/3 "2021-12-02T21:24:47Z")

</div>

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

```javascript
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?

---

<div class="post-metadata">

**Author:** ![anon79585656](https://avatars.discourse-cdn.com/v4/letter/a/7ab992/32.png) [@anon79585656](https://community.forestadmin.com/u/anon79585656)\
**Post date:** [December 3, 2021, 9:05am UTC](https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820/4 "2021-12-03T09:05:19Z")

</div>

@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`:

```javascript
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`:

```javascript
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:

> **[Create and manage Smart Fields](https://docs.forestadmin.com/documentation/reference-guide/fields/create-and-manage-smart-fields#filtering)**

---

<div class="post-metadata">

**Author:** ![yonbergman](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/yonbergman/32/3668_2.png) [@yonbergman](https://community.forestadmin.com/u/yonbergman)\
**Post date:** [December 3, 2021, 11:04am UTC](https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820/5 "2021-12-03T11:04:45Z")

</div>

That’s weird 🙂

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

```javascript
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?

---

<div class="post-metadata">

**Author:** ![anon79585656](https://avatars.discourse-cdn.com/v4/letter/a/7ab992/32.png) [@anon79585656](https://community.forestadmin.com/u/anon79585656)\
**Post date:** [December 3, 2021, 1:49pm UTC](https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820/6 "2021-12-03T13:49:00Z")

</div>

@yonbergman,

Users model:

```javascript
// 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:

```javascript
-- 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:

```javascript
// 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:

```javascript
-- 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.

---

<div class="post-metadata">

**Author:** ![yonbergman](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/yonbergman/32/3668_2.png) [@yonbergman](https://community.forestadmin.com/u/yonbergman)\
**Post date:** [December 5, 2021, 2:55pm UTC](https://community.forestadmin.com/t/filtering-on-smart-field-through-a-relationship/3820/7 "2021-12-05T14:55:25Z")

</div>

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!