How to filter by entity and it's children?

Support
#1

I have an entity for hierarchical dictionary (e.g. nested diseases classification). Customers want to filter records by diagnosis and children diagnoses. I wonder if it can be done without adding children in list for IN query (because there are about 12132 entries).

One way I see - is to add path (see materialized path pattern) property to each diagnosis and use a query: SELECT r FROM demo$Record r WHERE r.diagnosis.path LIKE ‘1.10.22.%’ OR r.diagnosis.id = 22, where 1.10.22 is a path to the selected node, so the query will select all records with children diagnoses and with the root one.

Of course I can make custom filter property, but there are at least 5 to 7 such dictionaries, so maybe some common solution can be made?

#2

We use similar approach for Access Group entity: there is an additional entity GroupHierarchy that stores all parents up to the root for each Group. It allows us to gather constraints effectively for the whole branch: select c from sec$GroupHierarchy h join h.parent.constraints c where h.group.id = ?1. The content of GroupHierarchy is kept in sync with Group by the entity listener, see GroupEntityListener.

Such additional entity can be used in Filter component with the help of custom conditions with JOIN clause.

Hope this helps.