Hi all
version: 1.2.0.beta2
Mapping documents:
Object class
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
<class name="T3.BLL.Object, T3_BLL" table="T3_OBJECT">
<id name="ID" column="T3OBJ_OBJECT_ID" type="Int64">
<generator class="sequence">
<param name="sequence">T3_ALLOCATEID</param>
</id>
<!-- cut...-->
</class>
</hibernate-mapping>
Relation class
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
<class name="T3.BLL.Relation, T3_BLL" table="T3_RELATION">
<id name="ID" column="T3RTN_RELATION_ID" type="Int64">
<generator class="sequence">
<param name="sequence">T3_ALLOCATEID</param>
</generator>
</id>
<!-- cut...-->
</class>
</hibernate-mapping>
ObjToRtn class: Object to Relation mapping (many to many)
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
<class name="T3.BLL.ObjToRtn, T3_BLL" table="T3_OBJ_RTN">
<id name="ID" column="T3OBR_OBJ_RTN_ID" type="Int64">
<generator class="sequence">
<param name="sequence">T3_ALLOCATEID</param>
</generator>
</id>
<many-to-one name="Right"
column="T3RTN_RELATION_ID"
not-null="true"
foreign-key="FK_OBR_RTN"
cascade="delete"/>
<many-to-one name="Left"
column="T3OBJ_OBJECT_ID"
not-null="true"
foreign-key="FK_OBR_OBJ"
cascade="delete"/>
</class>
</hibernate-mapping>
and several classes derived from Object (table t3_rea_component, t3_lnd_component)
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
<joined-subclass
extends="T3.BLL.Object, T3_BLL"
name="T3.BLL.LandComponent, T3_BLL"
table="T3_LND_COMPONENT"
proxy="T3.BLL.LandComponent, T3_BLL" lazy="true">
<key column="T3OBJ_OBJECT_ID"/>
</joined-subclass>
</hibernate-mapping>
and class derived fron Relation (table t3_acr_component)
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
<joined-subclass
name="T3.BLL.AcrComponent, T3_BLL"
extends="T3.BLL.Relation, T3_BLL"
proxy="T3.BLL.AcrComponent, T3_BLL"
table="T3_ACR_COMPONENT" lazy="true">
<key column="T3RTN_RELATION_ID"/>
</joined-subclass>
</hibernate-mapping>
What I wantI want to generate complex queries using CreateCriteria technique.
For example, I want get all relations (Relation class, not derived!) for specific object (
param in query).
What I tried:
Code:
DetachedCriteria o2o = DetachedCriteria.For(ObjToRtn, "o2o")
.Add(NHibernate.Expression.Expression.Eq("o2o.Left", param))
.SetProjection(Projections.Property("o2o.Right"))
;
DetachedCriteria right = DetachedCriteria.For(Relation)
.Add(Property.ForName("id").In(o2o));
ICriteria iCriteria = right.GetExecutableCriteria(session);
and I got these generated SQL:
Code:
SELECT this_.t3rtn_relation_id AS t1_4_3_, this_.t3rtn_number AS t2_4_3_,
this_.t3rtn_description AS t3_4_3_, this_.t3rtn_date AS t4_4_3_,
this_.t3rnc_rtn_category_id AS t5_4_3_,
this_.t3sts_status_id AS t6_4_3_, this_1_.t3acr_inv_number AS t2_32_3_,
this_1_.t3sbj_holder_id AS t3_32_3_,
this_1_.t3acr_end_date AS t4_32_3_,
this_1_.t3rtn_use_right_id AS t5_32_3_,
this_1_.t3obj_object_id AS t6_32_3_,
this_1_.t3acr_actual_cost AS t7_32_3_,
DECODE (this_.t3rtn_relation_id,
this_1_.t3rtn_relation_id, 1,
0
) AS clazz_3_,
subject2_.t3sbj_subject_id AS t1_17_0_,
subject2_.t3sbj_number AS t2_17_0_,
subject2_.t3sbj_description AS t3_17_0_,
subject2_.t3sbj_date AS t4_17_0_,
subject2_.t3sbc_sbj_category_id AS t5_17_0_,
relation3_.t3rtn_relation_id AS t1_4_1_,
relation3_.t3rtn_number AS t2_4_1_,
relation3_.t3rtn_description AS t3_4_1_,
relation3_.t3rtn_date AS t4_4_1_,
relation3_.t3rnc_rtn_category_id AS t5_4_1_,
relation3_.t3sts_status_id AS t6_4_1_,
relation3_1_.t3acr_inv_number AS t2_32_1_,
relation3_1_.t3sbj_holder_id AS t3_32_1_,
relation3_1_.t3acr_end_date AS t4_32_1_,
relation3_1_.t3rtn_use_right_id AS t5_32_1_,
relation3_1_.t3obj_object_id AS t6_32_1_,
relation3_1_.t3acr_actual_cost AS t7_32_1_,
DECODE (relation3_.t3rtn_relation_id,
relation3_1_.t3rtn_relation_id, 1,
0
) AS clazz_1_,
object4_.t3obj_object_id AS t1_9_2_, object4_.t3obj_number AS t2_9_2_,
object4_.t3obj_description AS t3_9_2_, object4_.t3obj_date AS t4_9_2_,
object4_.t3obc_obj_category_id AS t5_9_2_,
object4_.t3sts_status_id AS t6_9_2_, object4_1_.t3rea_name AS t2_33_2_,
object4_1_.t3rea_build_date AS t3_33_2_,
object4_1_.t3rea_out_date AS t4_33_2_,
object4_1_.t3cfv_turnover_limitation_id AS t5_33_2_,
object4_1_.t3oki_component_id AS t6_33_2_,
object4_1_.t3cfv_sol_type_id AS t7_33_2_,
object4_1_.t3rea_sol_doc_number AS t8_33_2_,
object4_1_.t3rea_sol_doc_date AS t9_33_2_,
object4_1_.t3rea_target_purpose AS t10_33_2_,
object4_1_.t3rea_actual_purpose AS t11_33_2_,
object4_2_.t3lnd_target AS t2_34_2_,
object4_2_.t3lnd_purpose AS t3_34_2_,
object42_.t3lnd_square AS t4_34_2_,
object4_2_.t3oki_component_id AS t5_34_2_,
object4_2_.t3cfv_turnover_limitation_id AS t6_34_2_,
object4_2_.t3cfv_use_category_id AS t7_34_2_,
DECODE (object4_.t3obj_object_id,
object4_1_.t3obj_object_id, 1,
object4_2_.t3obj_object_id, 2,
0
) AS clazz_2_
FROM t3_relation this_,
t3_acr_component this_1_,
t3_subject subject2_,
t3_relation relation3_,
t3_acr_component relation3_1_,
t3_object object4_,
t3_rea_component object4_1_,
t3_lnd_component object4_2_
WHERE this_1_.t3sbj_holder_id = subject2_.t3sbj_subject_id(+)
AND this_1_.t3rtn_use_right_id = relation3_.t3rtn_relation_id(+)
AND relation3_.t3rtn_relation_id = relation3_1_.t3rtn_relation_id(+)
AND relation3_1_.t3obj_object_id = object4_.t3obj_object_id(+)
AND object4_.t3obj_object_id = object4_1_.t3obj_object_id(+)
AND object4_.t3obj_object_id = object4_2_.t3obj_object_id(+)
AND this_.t3rtn_relation_id = this_1_.t3rtn_relation_id(+)
AND this_.t3rtn_relation_id IN (SELECT this0__.t3rtn_relation_id AS y0_
FROM t3_obj_rtn this0__
WHERE this0__.t3obj_object_id = 25344906);
Questions for my example:
1) how to retrive objects using CreateCriteria in parent class (Relation)? NHiberanates tries to generate results as derived AcrComponent and LandComponent.2) How to join Object and ObjToRtn classes using CreateCriteria\SetProjection\etc functions?The following HQL, created manually working well (and ideally I want to have SQL like this one):
objs = session.CreateQuery("select _relation from ObjToRtn _o2o, Relation _relation where _relation = _o2o.Right and _o2o.Left=:obj")
.SetParameter("obj", param)
.List();
SQL:
Code:
SELECT reacompone1_.t3obj_object_id AS t1_9_,
reacompone1_.t3rea_name AS t2_33_,
reacompone1_.t3rea_build_date AS t3_33_,
reacompone1_.t3rea_out_date AS t4_33_,
reacompone1_.t3cfv_turnover_limitation_id AS t5_33_,
reacompone1_.t3oki_component_id AS t6_33_,
reacompone1_.t3cfv_sol_type_id AS t7_33_,
reacompone1_.t3rea_sol_doc_number AS t8_33_,
reacompone1_.t3rea_sol_doc_date AS t9_33_,
reacompone1_.t3rea_target_purpose AS t10_33_,
reacompone1_.t3rea_actual_purpose AS t11_33_,
reacompone1_1_.t3obj_number AS t2_9_,
reacompone1_1_.t3obj_description AS t3_9_,
reacompone1_1_.t3obj_date AS t4_9_,
reacompone1_1_.t3obc_obj_category_id AS t5_9_,
reacompone1_1_.t3sts_status_id AS t6_9_
FROM t3_obj_obj objtoobj0_,
t3_rea_component reacompone1_,
t3_object reacompone1_1_
WHERE reacompone1_.t3obj_object_id = reacompone1_1_.t3obj_object_id
AND ( (reacompone1_.t3obj_object_id = objtoobj0_.t3obb_child_id)
AND (objtoobj0_.t3obb_parent_id = 25344906)
)
any help???
thanks in advance