Ordering by belongsToMany count

Help me!
1

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:

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:
"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
2

Hello @mathieuh

As specified on our documentation on smart fields, 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.

I hope this helps.

Best regards,

Dogan

4

Hi @dogan.ay,

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,

5

Hello @mathieuh,

To sort the user company count, you can override the GET route as described in the Forest Admin documentation here.
Please find the sample code below to achieve this. Make sure to customize the code to match your needs.

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.

6

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)

I hope this helps.

Best regards,

Dogan