Hi,

i need help with build criteria...
Hibernate error: duplicate association path

my criteria WAS:

Criteria crt = getSession().createCriteria(DMSDocument.class);
crt.createCriteria("form").add(Restrictions.eq("name","Contract"));
crt.createCriteria("values", "v").createCriteria("field", "f")
.add(Restrictions.conjunction()
.add(
Restrictions.conjunction()
.add(Restrictions.eq("v.valueStr", "1"))
.add(Restrictions.eq("f.name", "Quantity))
)
.add(
Restrictions.conjunction()
.add(Restrictions.eq("v.valueStr", "Active"))
.add(Restrictions.eq("f.name", "State"))
)
);

crt.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
result = crt.list();

select WAS:

select this_.id as id3_, this_.form_id as form2_20_3_, this_.access_rule_id as access3_20_3_, this_.state_id as state4_20_3_, v2_.id as id0_, v2_.value_str as value2_24_0_, v2_.field_id as field3_24_0_, v2_.document_id as document4_24_0_, f3_.id as id1_, f3_.name as name21_1_, f3_.comment as comment21_1_, f3_.screen_label as screen4_21_1_, f3_.field_type as field5_21_1_, f3_.form_id as form6_21_1_, f3_.field_source_id as field7_21_1_, f3_.value_type_id as value8_21_1_, dmsform1_.id as id2_, dmsform1_.name as name26_2_, dmsform1_.description as descript3_26_2_, dmsform1_.form_type as form4_26_2_, dmsform1_.access_rule_id as access5_26_2_ from DMS_DOCUMENT this_ inner join DMS_FIELD_VALUE v2_ on this_.id=v2_.document_id inner join DMS_FIELD f3_ on v2_.field_id=f3_.id inner join DMS_FORM dmsform1_ on this_.form_id=dmsform1_.id where dmsform1_.name=? and ((v2_.value_str=? and f3_.name=?) and (v2_.value_str=? and f3_.name=?))

So result didn't good for my request.

I want this logical condition: All documents created by form (Contract) where field (Quantity=1) AND field (State=Active)
So result was empty, because bad select was generated by hibernate by my logical request.

So i want THIS select:

select this_.id as id3_, this_.form_id as form2_20_3_, this_.access_rule_id as access3_20_3_, this_.state_id as state4_20_3_, v2_.id as id0_, v2_.value_str as value2_24_0_, v2_.field_id as field3_24_0_, v2_.document_id as document4_24_0_, f3_.id as id1_, f3_.name as name21_1_, f3_.comment as comment21_1_, f3_.screen_label as screen4_21_1_, f3_.field_type as field5_21_1_, f3_.form_id as form6_21_1_, f3_.field_source_id as field7_21_1_, f3_.value_type_id as value8_21_1_, dmsform1_.id as id2_, dmsform1_.name as name26_2_, dmsform1_.description as descript3_26_2_, dmsform1_.form_type as form4_26_2_, dmsform1_.access_rule_id as access5_26_2_
from
DMS_DOCUMENT this_ inner join DMS_FIELD_VALUE v2_
on this_.id=v2_.document_id inner join DMS_FIELD f3_
on v2_.field_id=f3_.id inner join DMS_FIELD_VALUE x
on this_.id=x.document_id inner join DMS_FIELD y
on x.field_id=y.id inner join DMS_FORM dmsform1_
on this_.form_id=dmsform1_.id
where dmsform1_.name='Contract' and ((v2_.value_str='1' and f3_.name='Quantity') and (x.value_str='Active' and y.name='State'))

in additional X and Y alliases.

My question is how can i build criteria tree with duplicate association ?!

I try this:

Criteria crt = getSession().createCriteria(DMSDocument.class);
crt.createCriteria("form").add(Restrictions.eq("name","Kontrakt"));
crt.createCriteria("values", "v").createCriteria("field", "f");
crt.createCriteria("values", "vv").createCriteria("field", "ff")
.add(Restrictions.conjunction()
.add(
Restrictions.conjunction()
.add(Restrictions.eq("v.valueStr", "1"))
.add(Restrictions.eq("f.name", "Mnozstvo"))
)
.add(
Restrictions.conjunction()
.add(Restrictions.eq("vv.valueStr", "Aktivny"))
.add(Restrictions.eq("ff.name", "Stav"))
)
);

crt.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
result = crt.list();

So this generate hibernate exception:

org.hibernate.QueryException: duplicate association path: values
at org.hibernate.loader.CriteriaQueryTranslator.createAssociationPathCriteriaMap(CriteriaQueryTranslator.java:136)
at org.hibernate.loader.CriteriaQueryTranslator.<init>(CriteriaQueryTranslator.java:78)
at org.hibernate.loader.CriteriaLoader.<init>(CriteriaLoader.java:68)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1248)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:294)

SO PLZ HELP ME, HOW ... THX

I have encountered the same exception and have not been able to come up with a suitable workaround.

It looks like you are joining to a table of name value pairs (not uncommon) and need to join two rows from the same table into the result set.

Maybe a simpler example will illustrate the issue. Apparently, you can't do this:



The exception makes sense if there is no alias, but I don't understand why the translator throws the exception if the association path has a different alias. I am probably just ignorant.

this works, but only with HQL:

String hqlQry = "select distinct doc from DMSDocument as doc " +
"left join doc.values as v1 " +
"left join doc.values as v2 " +
"where (v1.valueStr='Active' and v1.fieldName='State') and " +
"(v2.valueStr='1' and v2.fieldName='Quantity)";
Query qry = getSession().createQuery(hqlQry);
result = qry.list();




Any other idea ? (by criteria?) :)

Well, I found this thread.

http://forum.hibernate.org/viewtopic.php?t=931249

It looks like you will have to wait for Hibernate 3.1 before Criteria will get any kind of enhancement along these lines. I guess we fall back on the HQL you posted... Gawd, I hate writing code to dynamically generate HQL. Criteria is great for ultra dynamic queries.