Hierarchical filters only apply to one level

[autermann]

DbQuery.addHierarchicalFilterRestriction used to add restrictions for filtering on offerings and procedures will only filter a single hierarchy level (in the child direction).

So given this data (not really representative):

 id       | value 
----------+-------
 01       |  'a'
 -- 02    |  'a'
 -- -- 03 |  'b'
 04       |  'a'
 -- 05    |  'b'
 -- -- 06 |  'a'
 07       |  'b'
 -- 08    |  'a'
 -- -- 09 |  'a'
 10       |  'a'
 -- 11    |  'b'
 12       |  'b'
 -- 13    |  'a'
 14       |  'a'
 -- 15    |  'a'
 -- -- 16 |  'a'
 17       |  'a'
 -- 18    |  'a'
 19       |  'a'

Or in SQL:

DROP TABLE IF EXISTS entity;

CREATE TABLE entity (
  id INTEGER PRIMARY KEY,
  value CHAR(1),
  parent INTEGER REFERENCES entity (id)
);

INSERT INTO entity (id, value, parent) 
VALUES ( 1, 'a', NULL), ( 2, 'a', 1), ( 3, 'b', 2),
       ( 4, 'a', NULL), ( 5, 'b', 4), ( 6, 'a', 5),
       ( 7, 'b', NULL), ( 8, 'a', 7), ( 9, 'a', 8),
       (10, 'a', NULL), (11, 'b', 10), 
       (12, 'b', NULL), (13, 'a', 12),
       (14, 'a', NULL), (15, 'a', 14), (16, 'a', 15),
       (17, 'a', NULL), (18, 'a', 17),
       (19, 'a', NULL);

Filtering the value by b, it would currently execute something like this:

SELECT e1.*
FROM entity AS e1
  LEFT OUTER JOIN entity AS e2 ON (e1.parent = e2.id)
WHERE e2.value = 'b' OR e1.value = 'b'
ORDER BY e1.id;

Which only currently only return 3, 5, 7, 8 and 11, 12, 13:

 id | value | parent 
----+-------+--------
  3 | b     |      2
  5 | b     |      4
  6 | a     |      5
  7 | b     |       
  8 | a     |      7
 11 | b     |     10
 12 | b     |       
 13 | a     |     12
(8 rows)

It should do a full recursive search:

WITH RECURSIVE 
children AS (
  SELECT * FROM entity WHERE value = 'b' 
  UNION ALL 
  SELECT e.* FROM entity AS e JOIN children AS c ON e.id = c.parent
), 
parents AS (
  SELECT * FROM entity WHERE value = 'b'
  UNION ALL 
  SELECT e.* FROM entity AS e JOIN parents AS p ON e.parent = p.id
)
SELECT * FROM children 
UNION 
SELECT * FROM parents
ORDER BY id

... and return everything from 1 to 13:

 id | value | parent 
----+-------+--------
  1 | a     |       
  2 | a     |      1
  3 | b     |      2
  4 | a     |       
  5 | b     |      4
  6 | a     |      5
  7 | b     |       
  8 | a     |      7
  9 | a     |      8
 10 | a     |       
 11 | b     |     10
 12 | b     |       
 13 | a     |     12
(13 rows)

Currently this is not possible with Hibernate, so we may need another solution...

As a side note, the parents are always filtered on the pkid field while the children may be filtered on the domainId field...

[PasunuriSrinidhi]

I would like to work on this issue, can you please assign this to me