# Ordering by belongsToMany count

**URL:** <https://community.forestadmin.com/t/ordering-by-belongstomany-count/5811>\
**Category:** Help me!\
**Created:** [January 10, 2023, 9:53am UTC](https://community.forestadmin.com/t/ordering-by-belongstomany-count/5811 "2023-01-10T09:53:30Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mathieuh](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/mathieuh/32/953_2.png) [@mathieuh](https://community.forestadmin.com/u/mathieuh)\
**Post date:** [January 10, 2023, 9:53am UTC](https://community.forestadmin.com/t/ordering-by-belongstomany-count/5811/1 "2023-01-10T09:53:30Z")

</div>

## Feature(s) impacted

Sorting by a **smart field** based on a **belongsToMany** relationship.  
I created a smart field **users count** on a **company** collection where a company can get many users.  
Smart field looks like this:

```javascript
fields: [{
    field: 'users count',
    get: (company) => companyUser.count({ where: { companyId: company.id }}),
    type: 'Number',
    isSortable: true,
    isFilterable: true,
  }],

```

## Observed behavior

When I try to sort on this smart field I get the error `Unknown column 'companies.users count' in 'order clause'`. So I could rewrite `request.query.sort` but `count` is undefined too. What should I do here?

## Context

- Project name: MyCater
- Team name: All
- Environment name: Development
- Agent type & version:

```javascript
"liana": "forest-express-sequelize",
    "liana_version": "8.4.7",
    "stack": {
      "database_type": "mysql",
      "engine": "nodejs",
      "engine_version": "16.17.0",
      "orm_version": "5.15.2"
    }

```

- Recent changes made on your end if any: none

---

<div class="post-metadata">

**Author:** ![anon7284711](https://avatars.discourse-cdn.com/v4/letter/a/ac8455/32.png) [@anon7284711](https://community.forestadmin.com/u/anon7284711)\
**Post date:** [January 10, 2023, 12:21pm UTC](https://community.forestadmin.com/t/ordering-by-belongstomany-count/5811/2 "2023-01-10T12:21:15Z")

</div>

Hello @mathieuh

As specified on [our documentation on smart fields](https://docs.forestadmin.com/documentation/reference-guide/smart-fields#sorting), they do not natively support sorting. But there is a way for you to implement as shown on the guides. In your case you would have to refer to the guide on how to [sort on a smart field that includes value from a belongsTo relationship](https://docs.forestadmin.com/documentation/reference-guide/smart-fields/smart-field-examples/sort-by-smart-field-that-includes-value-from-a-belongsto-relationship).

I hope this helps.

Best regards,

Dogan

---

<div class="post-metadata">

**Author:** ![mathieuh](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/mathieuh/32/953_2.png) [@mathieuh](https://community.forestadmin.com/u/mathieuh)\
**Post date:** [January 10, 2023, 1:39pm UTC](https://community.forestadmin.com/t/ordering-by-belongstomany-count/5811/4 "2023-01-10T13:39:08Z")

</div>

Hi @anon7284711,

I read that article already but it didn’t help.  
That article shows you can access the email through `customer.email`. How can I access the `count` value, already try `users.count` but it doesn’t work.

Cheers,

---

<div class="post-metadata">

**Author:** ![Alban\_Bertolini](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/alban_bertolini/32/1077_2.png) [@Alban\_Bertolini](https://community.forestadmin.com/u/Alban_Bertolini)\
**Post date:** [January 11, 2023, 9:57am UTC](https://community.forestadmin.com/t/ordering-by-belongstomany-count/5811/5 "2023-01-11T09:57:28Z")

</div>

Hello @mathieuh,

To sort the user company count, you can override the `GET` route as described in the Forest Admin documentation [here](https://docs.forestadmin.com/documentation/reference-guide/routes/override-a-route).  
Please find the sample code below to achieve this. Make sure to customize the code to match your needs.

```javascript
router.get('/company', permissionMiddlewareCreator.list(), (request, response, next) => {
  const { query, user } = request;

  // if the sort doest not include the user count, then we can just use the default request
  if (!request.query.sort.includes('user count')) {
    return next();
  }

  const recordsGetter = new RecordsGetter(company, user, query);
  recordsGetter
    .getAll({
      include: ['userCompany'],
      // compute a user count column from the sequelize API and does it in a single request.
      attributes: {
        include: [[Sequelize.fn('COUNT', Sequelize.col('userCompany.userId')), 'user count']],
      },
      order: [
        [{ model: db.Company }, 'user count', request.query.sort.includes('-') ? 'DESC' : 'ASC'],
      ],
    })
    .then(records => recordsGetter.serialize(records))
    .then(recordsSerialized => response.send(recordsSerialized))
    .catch(next);
});

```

This sample code will sort the result based on user count column, you may need to adjust the `include`, `attributes`, and `order` options to match your database schema and desired sorting behavior.  
Please let me know if you have any further question or issues.

---

<div class="post-metadata">

**Author:** ![anon7284711](https://avatars.discourse-cdn.com/v4/letter/a/ac8455/32.png) [@anon7284711](https://community.forestadmin.com/u/anon7284711)\
**Post date:** [January 12, 2023, 5:03pm UTC](https://community.forestadmin.com/t/ordering-by-belongstomany-count/5811/6 "2023-01-12T17:03:19Z")

</div>

Hello @mathieuh,

After some thinking, here is an another alternative for you: by using Views directly in your database you can then create a smart collection and use the native sorting on “users count” field. ([link to the how-to](https://docs.forestadmin.com/documentation/how-tos/databases/manage-sql-views))

I hope this helps.

Best regards,

Dogan