Criteria and joins

[Alex Armenteros]

I have a DB model with 4 tables related and I'm trying this...

cr = ses.createCriteria(tableA.class);
 //Filtering fields on tableA
				Criteria cr2 = cr.createAlias("tableB", "B")
                                                    .createAlias("B.tableC", "C")
                                                    .createAlias("C.tableD","D")
                                                    .setFetchMode("B", FetchMode.JOIN)
                                                    .setFetchMode("C", FetchMode.JOIN)
                                                    .setFetchMode("D", FetchMode.JOIN);

cr2.add(Restrictions.or(
                        Restrictions.and(
                                       Restrictions.eq("D.fieldD",val1D)
                                       ,Restrictions.eq("C.fieldC",val1C))
                       ,Restrictions.and(
                                       Restrictions.eq("D.fieldD",val3D)
                                      ,Restrictions.eq("C.fieldC",val3C)));


					tableA val = (tableA) cr2.list().get(0);

Relations

tableA n:1 tableB
tableB 1:n tableC
tableC n:1 tableD


Now I have these registries


A1 ----> B1 ----> C1 ----> D1
----> C2 ----> D2


I want to filter and get only C1 and D1

But when I do

System.out.println(val.tableB.tableC.size());

The console output shows 2 instead of 1.

[Alex Armenteros]

Solved like this.

Change line 3 to:


Criteria cr2 = cr.createCriteria("tableB", CriteriaSpecification.LEFT_JOIN).createCriteria("tableC", CriteriaSpecification.LEFT_JOIN);

and then

D.fieldD to tableD.fieldD

C.fieldC to fieldC