# Filtering and sorting on smart relationship

**URL:** <https://community.forestadmin.com/t/filtering-and-sorting-on-smart-relationship/4533>\
**Category:** Help me!\
**Created:** [April 25, 2022, 9:56am UTC](https://community.forestadmin.com/t/filtering-and-sorting-on-smart-relationship/4533 "2022-04-25T09:56:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![agarbund](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/agarbund/32/502_2.png) [@agarbund](https://community.forestadmin.com/u/agarbund)\
**Post date:** [April 25, 2022, 9:56am UTC](https://community.forestadmin.com/t/filtering-and-sorting-on-smart-relationship/4533/1 "2022-04-25T09:56:15Z")

</div>

## Feature(s) impacted

> Smart relationships

## Observed behavior

> Can’t filter or sort on smart relationship

## Expected behavior

> Would be nice to be able to 🙂

## 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](https://docs.forestadmin.com/documentation/reference-guide/models/relationships/create-a-smart-relationship) 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: …

---

<div class="post-metadata">

**Author:** ![anon39848301](https://avatars.discourse-cdn.com/v4/letter/a/b9e5f3/32.png) [@anon39848301](https://community.forestadmin.com/u/anon39848301)\
**Post date:** [April 25, 2022, 10:10am UTC](https://community.forestadmin.com/t/filtering-and-sorting-on-smart-relationship/4533/2 "2022-04-25T10:10:35Z")

</div>

Hey @agarbund,

We do have a fews pieces of documentation:

- [Sort by smart field](https://docs.forestadmin.com/documentation/reference-guide/smart-fields/smart-field-examples/sort-by-smart-field)
- [Filter using filter() function](https://docs.forestadmin.com/documentation/reference-guide/smart-fields)
- [Filtering on zendesk](https://docs.forestadmin.com/documentation/reference-guide/integrations/zendesk/authentication-filtering-and-sorting#filtering-and-sorting-using-the-api)

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](https://docs.forestadmin.com/documentation/reference-guide/smart-fields#route-overriding))

Let me know if that helps

---

<div class="post-metadata">

**Author:** ![agarbund](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/agarbund/32/502_2.png) [@agarbund](https://community.forestadmin.com/u/agarbund)\
**Post date:** [April 25, 2022, 10:37am UTC](https://community.forestadmin.com/t/filtering-and-sorting-on-smart-relationship/4533/3 "2022-04-25T10:37:05Z")

</div>

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

This is how our custom route currently looks like:

```javascript
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);
    }
);

```

---

<div class="post-metadata">

**Author:** ![Justin\_Martin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/justin_martin/32/1028_2.png) [@Justin\_Martin](https://community.forestadmin.com/u/Justin_Martin)\
**Post date:** [April 25, 2022, 12:09pm UTC](https://community.forestadmin.com/t/filtering-and-sorting-on-smart-relationship/4533/4 "2022-04-25T12:09:36Z")

</div>

> [@agarbund](#):
>
> RecordsGetter

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

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

```