Hi

RE: Problems using createAlias to specify a filter on deeply nested join.

I'm using createAlias to query on a fairly extensive table structure and I'd like to know if it's possible to use the createAlias method to specify where clauses for tables joined more than one table away. In object terms something like the following is required.

Hibernate objects (for example):

MainTableObject
fields:
mtoId (java.lang.Integer);
mtoSomeOtherObject (SomeOtherObject);

SomeOtherObject;
fields:
sooId (java.lang.Integer);
sooSomeOtherObject_2 (SomeOtherObject_2);

SomeOtherObject_2;
fields:
soo2ID (java.lang.Integer);
soo2SomeOtherObject_3 (SomeOtherObject_3):

SomeOtherObject_2;
fields:
soo3ID (java.lang.Integer);

where a createAlias would work for putting a filter on SomeOtherObject_3.soo3ID

something like -

note: MainTableObject would be represented as the primary class so would be 'this'.

createAlias("mtoSomeOtherObject",
"mtoSomeOtherObject");

createAlias("mtoSomeOtherObject.sooSomeOtherObject_2",
"mtoSomeOtherObject.sooSomeOtherObject_2");

createAlias("mtoSomeOtherObject.sooSomeOtherObject_2.soo2SomeOtherObject_3",
"mtoSomeOtherObject.sooSomeOtherObject_2.soo2SomeOtherObject_3");

and the expression might be:

Criteria.eq("mtoSomeOtherObject.sooSomeOtherObject_2.soo2SomeOtherObject_3.soo3Id", <an id value>);


The problem I'm having is that only the first level is mapped.

I.E. the following error occurs -

could not resolve property: sooSomeOtherObject_2.soo2SomeOtherObject_3 of: mtoSomeOtherObject



A couple of months ago I was sure I had this working but a lost class (which was a simple one) has given me these problems so I'm sure I'm just not specifying something correctly.

I have the following questions:

1) Is this actually possible?
2) Is there another way?
(NB: Subcriteria don't work because I may have several branches of nesting)
3) Can something wrong be seen with this idea?

NB: If I grab the query then all the above levels are mapped in the SQL where I can input a where clause to
limit soo2Id without modifying any other sections of the query.

NB2: The relationships are all specified in the hibernate.hbm.xml files and work correctly to retreive every
field from every level of nesting.

Regards
sasco

I have a solution.

For those who are interested the mechanism in this post has a few flaws but works in principal.

To make it work effectively you need to recursively add aliases as above but avoiding the dots in the alias name and reference the next createAlias association path with the alias name created prior. The hibernate Criteria will only accept one stage of an alias path at a time, it does this by pulling off the bean string before the FIRST dot (if there is one) so multi-level dotted paths will fail if they're attempted in one step.

i.e. the code in the original post would become something like:

createAlias("mtoSomeOtherObject", "sooAlias1");

createAlias("sooAlias1.sooSomeOtherObject_2", "sooAlias2");

createAlias("sooAlias2.sooSomeOtherObject_3", "sooAlias3");


and the expression becomes.

criteria.eq("sooAlias3.soo3ID", <some id value>);


You need also to ensure the alias name doesn't grow larger than 30 characters for most databases as these aliases are used directly in the sql and will cause an error if they're too large.


Regards
sasco