Query on count of related entities

Feature(s) impacted

Querying using the Forest Admin Query Interface.
(btw, you might want to add query-interface as a tag here?)

Observed behavior

N/A

Expected behavior

I would like to query users that were created today and have no orders.
Users have a one-to-many relationship to orders.

Can I use the query interface for this use-case? Or is it limited to many-to-one relationships as this page seems to imply?
Query interface and Native Queries | Node.js Developer Guide (forestadmin.com)

Failure Logs

N/A

Context

  • Agent technology: nodejs
  • Agent (forest package) name & version: 1.40.1 with sequelize 6.37.3 and sequelize-typescript 2.1.6
  • Database type: MSSQL
  • Recent changes made on your end if any: Migrating from old agent.

Hello @kll ,

As orders are not referenced as a field in the collection users, you won’t be able to filter on this condition with the query interface.
Here is a potential workaround if the number of users with orders is not too big:

collection.addSegment('mySegment', async context => {
  const itemsCount = await context.dataSource.getCollection('order').aggregate({}, {
    operation: "Sum",
    value: 1,
    groups: [{ field: "userIdentifier" }], /* smart field based on user:id 
                    as you won't be able to use "user:id" here */
  });

  return { field: 'id', operator: 'NotIn', value: itemsCount.map((item) => item.group.userIdentifier) };
});

kind regards

Hi @Enki,

Thanks for the reply! I’m a bit afraid of the performance impact, though. I think I’ll stick to my ugly SQL for now.

Are there any plans to support this kind of use-case via the query interface at some point?

There is also another solution using a plugin:

It is certainly a better solution than what I suggested before.

I’m not sure about the impact on performance though.