Many-to-many tables with aggregation

Support
#1

Dear CUBA team.

Can I ask you the best solution for the following idea.
I have three entity:
img1

Each batch contains several items. The manager can create an order from items of different batches.
But each item can be used in several orders.

The entities Order and Items are created with associated fields with MANY-TO-MANY cardinality.

The goal is to create a table:
img2

which contains info about orders by batches.

I’m trying to realize this via two nested tables Orders and Stats. The first to select an order, the second to display the aggregated order info. I plan to use Valued Collection Datasource with JPQL custom query, but I can’t compose a correct request to Items based on selected order. I’ve started with a simple select of items:

select i.number, count(i.id)
from demo$Item i
where i.order.id = :ds$ordersDs.id

Error: The state field path ‘i.order.id’ cannot be resolved to a valid type.

What is the correct method to work with the many-to-many joined fields?
Or can you suggest another way?

#2

Many thank’s to Даниил Царёв, the task was solved by request:

select i.batch.number, count(i)
from demo$Item i
where i.id in
    (select its.id from demo$Order os join os.items its where os.id = :ds$ordersDs)
group by i.batch.number