How to use Projection with two tables

bhowal_1981 · **Posted:** Tue Apr 22, 2008 1:43 am

Suppose there are two tables.
1. Person table(columns are personId[pk], personName);
2. Address table( addressId[pk], personId[fk], address1, address2 )

now suppode Person table has one record[11, bikash]

and Address table contains two records
[1(addressId),11(parentId as fk), kolkata, delhi]
[2(addressId),11(parentId as fk), b'lore, mumbai]

now i nedd only personName and latest address1 and address2 for each person record.

I am able to retrive only master record by projection. But how can i retrive recods from child table by projection

Means record should be-->>>
bikash, b'lore, mumbai.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

It is unclear what field of "address" you use to establish the max(). Suppose it is called "addnum"

Create a criteria query joining the two tables, add a projection "max" for that field, and list all the remaining fields you are interested in as "groupProperties" (equivalent to a "group by" in regular SQL).

Code:

      Criteria crit=session.createCriteria(Person.class);
      crit.createAlias("addresses", "b");
      crit.setProjection(Projections.projectionList()
      .add(Projections.max("b.addnum"))
                .add(Projections.groupProperty("name"))
                .add(Projections.groupProperty("address1"))
                .add(Projections.groupProperty("address2"))

                );
      crit.list();