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?