Is it possible to filter on a smart relationship

Help me!
1

Feature(s) impacted

Filtering on a smart relationship

Observed behavior

In our DB, we have clients, having many bank accounts, having many transactions. When browsing a list of transactions, we’d like to see owning client directly, without needing to browse through related bank account. We’ve created a smart relationship for that:

{
    field: "client",
    type: "String",
    reference: "client.id",
    get: async (transaction) => {
        return models.client.findOne({
            include: [
                {
                    model: models.bankAccount,
                    where: {
                        id: transaction.bankAccount.id,
                    },
                },
            ],
        });
    },
},

That works well and we’re able to see clients’ names on the transactions list.

Now, however, we’d also like to filter by a specific client. We’ve updated the smart relationship in the following way:

{
    field: "client",
    type: "String",
    reference: "client.id",
    get: async (transaction) => {
        return models.client.findOne({
            include: [
                {
                    model: models.bankAccount,
                    where: {
                        id: transaction.bankAccount.id,
                    },
                },
            ],
        });
    },
    filter: ({ condition, where }) => {
        const field = condition.field.replace(':', '.');

        return {
            include: [
                {
                    model: models.bankAccount,
                    include: models.client
                },
            ],
            where: {
                [`$bankAccount.${field}$`]: where,
            }
        };
    },
    isFilterable: true
},

This is throwing an error when we try to filter by a client. As I understand, we can only return where clauses, and not joins.

Expected behavior

Be able to specify joins when filtering by a smart relationship/smart field.

Failure Logs

[forest] 🌳🌳🌳  Unexpected error: Invalid value { model: bankAccount, include: client }
{
  "stack": "Error: Invalid value { model: bankAccount, include: client }\n    at Object.escape (/app/node_modules/sequelize/lib/sql-string.js:65:11)\n    at PostgresQueryGenerator.escape (/app/node_modules/sequelize/lib/dialects/abstract/query-generator.js:986:22)\n    at /app/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2387:69\n    at Array.map (<anonymous>)\n    at PostgresQueryGenerator._whereParseSingleValueObject (/app/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2387:52)\n    at PostgresQueryGenerator.whereItemQuery (/app/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2169:19)\n    at /app/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2074:25\n    at Array.forEach (<anonymous>)\n    at PostgresQueryGenerator.whereItemsQuery (/app/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2072:35)\n    at PostgresQueryGenerator.getWhereConditions (/app/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2484:19)\n    at PostgresQueryGenerator.selectQuery (/app/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1307:28)\n    at QueryInterface.rawSelect (/app/node_modules/sequelize/lib/query-interface.js:1154:37)\n    at Function.aggregate (/app/node_modules/sequelize/lib/model.js:1990:32)\n    at /app/node_modules/sequelize/lib/model.js:2042:19\n    at tryCatcher (/app/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)\n    at Promise._settlePromiseFromHandler (/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:547:31)\n    at Promise._settlePromise (/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:604:18)\n    at Promise._settlePromise0 (/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:649:10)\n    at Promise._settlePromises (/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:729:18)\n    at _drainQueueStep (/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:93:12)\n    at _drainQueue (/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:86:9)\n    at Async._drainQueues (/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:102:5)\n    at Immediate.Async.drainQueues [as _onImmediate] (/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:15:14)\n    at processImmediate (internal/timers.js:461:21)"
}

Context

  • Project name: Kick
  • Team name: …
  • Environment name: …
  • Agent type & version: forest-express-sequelize@8.5.1
  • Recent changes made on your end if any: …
1 Like
3

If someone would face this issue I’ve ended up doing it the following way:

async ({ condition, where }) => {
    const field = condition.field.split(":").pop();
    const clients = await models.client.findAll({
        where: { [field]: where },
        attributes: ["id"],
    });
    const clientIds = clients.map((client) => client.id);
    const noMatch = { amount: null };

    if (clientIds.length) {
        const accounts = await models.bankAccount.findAll({
            where: { clientId: { [Op.in]: clientIds } },
            attributes: ["id"],
        });

        if (accounts.length) {
            return {
                accountId: { [Op.in]: accounts.map((a) => a.id) },
            };
        } else {
            return noMatch;
        }
    } else {
        return noMatch;
    }
}

Instead of joining and filtering in a single query, I split it into multiple queries, so joining is unnecessary. Def less performant solution and would be great to be able to include joins directly in the return value of filter function.

1 Like
4

Love this answer, but still waiting for some include joins directly as you said