Query on count of related entities

Help me!
,
1

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.
3

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

4

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?

5

There is also another solution using a plugin:

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

6

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

7

Hi! I had a similar need today and this topic is actually the first that came up during a search =p

Are there any new features on that front?

8

Hi @kll ,
Thank you for notifying us,
There is no new feature on this subject, unfortunately. It is not in the roadmap currently, but some other user have requested a similar solution. I can’t promise you a new feature soon, but the request was heard.

Kind regards

2 Likes
9

Hi @Enki! Thanks for the reply, hopefully this makes it through one day :slight_smile:

I believe you’re focusing more on the ops side of things, so it would also be cool to have some sort of personalization available on the front-end to handle one-to-many scenarios easily. Things like counts, averages, sums…

2 Likes