Projection from inner join

[Siby George]

Hi All,

I am trying to project fields from 2 tables which are joined and also I need to order based on 2 columns, both from different tables.

The example SQL query is:

SELECT table1.col1, table2.col1, table2.col3 FROM table1 JOIN table2 ON table1.colm = table2.coln
WHERE table1.colm IN (1,2,3) ORDER BY table1.col1, table2.col1

I am trying this:

Example:

Criteria c = session.createCriteria(table2.class);
c.createCriteria(table2.PROP_table1).add(Property.forName(table1.colm).in(1,2,3));
ProjectionList proList = Projections.projectionList();
proList.add(Projections.property(table2.col1));
proList.add(Projections.property(table2.col2));
// proList.add(Projections.property(table1.col1));
c.setProjection(proList);
c.addOrder(Order.asc(table1.col1));
c.addOrder(Order.asc(table2.col1));
List result = c.list();


Any example showing projection from two tables (joined) would do.

Thanks
Siby

[Siby George]

This can be achieved by using alias names for inner table like

criteria.createAlias(table1.table2, "table2");

and then using projection as,
.add(Projections.property("table2."+table2.col1))