Hibernate Criteria Left Joining Two Tables

Question

I have two entities, say Business and Area.

Relevant properties:
Business - area, area2, code
Area - areaId, areaName

area and area2 of Business 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.

Answer 1

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)
);