The Exists works when it is the next level but I haven't had luck when it is further down the relationship tree. Since you are just looking for a flat report (so not expanding nodes) and you stated you could do it in SQL you could create a SQL view and and pair that to a new federated item type. Once you have that the TGV is just adding the new item type.
I made some tests back then. I just noticed that I never provided some feedback.
The original idea was using a self-repeating multilevel query like this:
But using "exists" in this context doesn´t work properly. Deeper levels are cut when upper levels don´t contain a document.
What DOES work is something ingenious like this:
The highlighted green stuff are the "exists" filters. [emoticon:0e469978eb8947d18b9c41123db3725a]
This one gives this result:
Pro:
- It´s a proper tree, even with the intermediate levels!
Con:
- I have up to 10 levels...my sample just used 4.
- Server crashed at a certain complexity of the query. So much for five year record without a developer-induced server crash.
So far I already used an SQL based solution for these kind of renderings.
That is my current flat view.
Con: It doesn´t list the intermediate levels. Users don´t see, how the data is connected.
In my use case, I would prefer to not have a flat-view, but a tree like rendering. I already used some external JS libs for tree rendering in the past, but it´s always a lot of work and the UI/UX often doesn´t blend-in.
Any idea of how to make the SQL result more "tree-ish"?
