We have a hierarchical data set of organisations in the database, where each organisation stores a reference to it parent organisation. I'm trying to find all organisations 2 levels under a root organisation, e.g. where organisation.parentOrg is the root or organisation.parentOrg.parentOrg is the root. The query also contains a number of other predicates and it is built dynamically using Criteria API. It could be looking for other number of levels, 2 is just an example.
The test query looks like this:
SELECT DISTINCT $1 FROM Organisation $1 JOIN $1.attributeList $2 LEFT JOIN $1.parentOrg $3 LEFT JOIN $3.parentOrg $4 WHERE ((($2.name='Attribute1') AND ($2.valueAsString='1')) AND (($3.organisationId='root') OR ($4.organisationId='root')))
As I understand, since I use LEFT Joins this query should return all organisation under the root organisation and all organisations under those first level orgs. It returns only organisations under the first level organisations, e.g. it does not return the organisations directly under root. The root organisation has null as it's parent. The log generated when I run this against the DB is attached.
Could you please advise if this query is supposed to work like that? I'm running 2.3.4_05 version.