Hibernate Criteria Left Joining Two Tables

I have two entities, say Business and Area.

Relevant properties:

Business - area, area2, code

Area - areaId, areaName

area and area2 of Bus开发者_开发百科iness map to the id in Area

I'm trying to write a Hibernate criteria that returns all the areas with businesses only.

SQL looks like: FROM area a LEFT OUTER JOIN business b on a.areaId = b.area or a.areaId = b.area2 WHERE b.code != null GROUP BY a.areaName

This is what I have:

DetachedCriteria criteria = DetachedCriteria.forClass(Business.class)
.setProjection(Property.forName("area"))
.setProjection(Property.forName("area2"))
.add(Restrictions.ne("code", null));

Criteria criteriaArea = fullTextSession.createCriteria(Area.class)
.createAlias("areaId", "areaId", CriteriaSpecification.LEFT_JOIN)
.add(Property.forName("areaId").in(criteria));

But this doesn't work, I get a "not an association: areaId" query exception.

Any ideas why this is happening? Thanks.

createAlias() joins another entity using provided property. Hibernate calculates what table to join using mapping of provided property. But areaId isn't mapped as a @ManyToOne or @ManyToMany reference to Business entity. So Hibernate doesn't understand to what table you want to join using Area.areaId.

Your criteria will be translated to SQL like:

select a.* from Area a
left join <here should be table referenced by areaId> b on a.areaId = b.id
where a.areaId in (
  select area, area2 from Business where code <> null
)

You may rewrite query without unused join:

DetachedCriteria criteria1 = DetachedCriteria.forClass(Business.class)
.setProjection(Property.forName("area"));

DetachedCriteria criteria2 = DetachedCriteria.forClass(Business.class)
.setProjection(Property.forName("area2"));

Criteria criteriaArea = fullTextSession.createCriteria(Area.class)
.add(Restrictions.or(
  Property.forName("areaId").in(criteria1),
  Property.forName("areaId").in(criteria2)
);