Hi Hibernate Community,

I have the following usecase:
Three classes C1,C2,C3 are related like this: C3 has_a C2 has_a C1
I now like to establish a kind of transitive relation from C3 via C2 to C1. Imagine an access control implementation based on C1 as some kind of permission. A user may access c3 based on its permission for c1 and depending on c3's c2.
Derived properties seemed a feasible option for that.
(In the following Grails/GORM DSL is used for brevity, it should be straight forward to understand)
The idea was to use a formula in the C3 class (which has a Long c1_id and a C2 c2 property) to derive the id of c1 in c3 via its c2 instance:
c1_id formula: "(SELECT c1_id FROM C2 c2 WHERE c2.id = c2_id)"
I used the ids as I have no idea how to directly access the instance as a whole, it doesn't even seem to be possible at all.
When accessing c3 in my application, the following exception is thrown:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'c30_.c1_id' in 'field list'
What now puzzles me:
* c1_id is not a field as it should be calculated
* the SQL statement works well when directly executed on the DB.
* other derived properties (not based on ids) work very well

So basically my question is:
Can formulas be used to derive complex types (C1 in this case) or could this somehow be emulated (using the ids instead as I tried above)?

Thanks
fatzopilot

To make it a bit more illustrative:
I have the following set-up



So everything is somehow related to Account, LogEntry has a Report property and Report has an Account property. So in case of the LogEntry, there is a transitive relation via Report.
For convenience, I would like to have direct access to Account from LogEntry and to use it in a hibernate filter condition for access control.
Usually, access to Account from LogEntry could be done via customized getters in LogEntry, e.g. getAccount() and a transient Account property. However, Hibernate filters (using the Account property) cannot be used in this setting, because they are defined using SQL which needs to address the account column which is not there in case of transient property.
So my hope is that it can be remedied by derived properties as I described in the former post.
Is this a hopeless approach in the end?

Thanks
fatzopilot