Filtering and sorting on smart relationship

Help me!
1

Feature(s) impacted

Smart relationships

Observed behavior

Can’t filter or sort on smart relationship

Expected behavior

Would be nice to be able to :slight_smile:

Context

In our DB we have clients, which can have many bank accounts, which can have many transactions. We’d like to browse all client’s transactions from client details view and created a smart relationship for that following this tutorial. It works well, but when viewing clients’ transactions it’s not possible to sort or filter them. How we could implement that?

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

Hey @agarbund,

We do have a fews pieces of documentation:

Depending on the type of relationship you are dealing with (hasMany), I would say the easiest way would be to override the corresponding route. Using the request, you should be able to implement the behavior you want (See route override)

Let me know if that helps

3

Hey @jeffladiray
Is there a way to include custom joins and wheres when using RecordsGetter ?

This is how our custom route currently looks like:

router.get(
    "/client/:client_id/relationships/Transactions",
    (request, response, next) => {
        const clientId = parseInt(request.params.client_id);
        const limit = parseInt(request.query.page.size, 10) || 20;
        const offset = (parseInt(request.query.page.number, 10) - 1) * limit;
        const include = {
            model: models.bankAccount,
            where: { clientId }
        };

        const serializer = new RecordSerializer(models.transaction);

        Promise.all([
            models.transaction.findAll({
                include,
                offset,
                limit,
                order: [["date", "DESC"]],
            }),
            models.transaction.count({ include }),
        ])
            .then(([records, count]) =>
                serializer.serialize(records, {
                    count: count,
                })
            )
            .then((serialized) => response.send(serialized))
            .catch(next);
    }
);
4

No it’s not possible with the default RecordGetter. In my case a create a new class RelatedRecordsGetter

const { isEmpty } = require('lodash');
const { scopeManager } = require('forest-express');
const { RecordSerializer } = require('forest-express-sequelize');
const QueryOptions = require('forest-express-sequelize/dist/services/query-options');

class RelatedRecordsGetter {
  constructor(model, user, params, relatedSequelizeInclude) {
    this.relatedSequelizeInclude = relatedSequelizeInclude;
    this._model = model;
    this._user = user;
    this._params = params;
  }

  async _buildQueryOptions(buildOptions = {}) {
    const { forCount, tableAlias } = buildOptions;
    const { filters, search, searchExtended, segment, segmentQuery, timezone } = this._params;

    const scopeFilters = await scopeManager.getScopeForUser(this._user, this._model.name, true);

    const queryOptions = new QueryOptions(this._model, { tableAlias });

    await queryOptions.search(search, searchExtended);
    await queryOptions.filterByConditionTree(filters, timezone);
    await queryOptions.filterByConditionTree(scopeFilters, timezone);
    await queryOptions.segment(segment);
    await queryOptions.segmentQuery(segmentQuery);

    if (!forCount) {
      const { sort, page } = this._params;
      await queryOptions.sort(sort);
      await queryOptions.paginate(page?.number, page?.size);
    }

    return [
      // add scopes to model
      queryOptions.sequelizeScopes.reduce((m, scope) => m.scope(scope), this._model),
      queryOptions.sequelizeOptions,
    ];
  }

  async getAll() {
    // Load options
    const [model, options] = await this._buildQueryOptions();

    //Load records
    const records = await model.findAll({
      ...options,
      include: this.relatedSequelizeInclude,
    });

    return records;
  }

  async count() {
    // Load options
    const [model, options] = await this._buildQueryOptions({ forCount: true });

    //Load records
    const records = await model.count({
      ...options,
      include: this.relatedSequelizeInclude,
      col: isEmpty(this._model.primaryKeys) ? '*' : undefined,
    });

    return records;
  }

  async serialize(records) {
    return await new RecordSerializer(this._model).serialize(records);
  }
}

module.exports = RelatedRecordsGetter;
1 Like