outer joins and property projections

diduknow · **Joined:** Wed Jun 21, 2006 2:25 pm **Posts:** 15

hi all

We are trying using hibernate criteria mechanism to fetch data from table.

We have 2 entities PROPERTY and PROPERTY_RATING mapped as a one to one relationship in hibernate mapping files. We need to fetch the OVERALLUSERRATING alongwith some other attributes of the PROPERTY table.

Hibernate by default uses an outer join to retrieve data from the PROPERTY_RATING table. However we have failed to retrieve the OVERUSERRATING value from the criteria.

I am attaching the

Can any one of you please offer any direction on what could be done here. Any pointers would be much appreciated. Apologies if this has been asked before

Code:

Criteria criteria = session.createCriteria(Property.class)
  .setProjection(Projections.distinct(Projections.projectionList()
  .add(Projections.property("propertyId"))
  .add(Projections.property("longitude"))
  .add(Projections.property("userRating.overallUserRating"))  //this line fails
  .add(Projections.property("sequenceNumber"))))
  .add(Restrictions.between("latitude",
      Double.valueOf(53.264923095703125),
      Double.valueOf(53.72837829589844)))
   .add(Restrictions.between("longitude",
      Double.valueOf(-2.650641918182373),
      Double.valueOf(-1.8715579509735107)))
   .add(Restrictions.eq("statusCode", SearchConstants.PROPERTY_STATUSCODE_ACTIVE))
   .createAlias("suppliers", "propertySuppliers")
   .add(Restrictions.eq("propertySuppliers.statusCode",
                        SearchConstants.PROPERTY_STATUSCODE_ACTIVE))
   .add(Restrictions.ne("sequenceNumber", 0))
   .add(Restrictions.ne("marketingLevel", 0));

Code:

select distinct this_.PROPERTYID as y0_, this_.LATITUDE as y1_, this_.LONGITUDE as y2_, this_.RATING as y3_, this_.LOWRATE as y4_, this_.HIGHRATE as y5_, this_.CURRENCYCODE as y6_, this_.SequenceNumber as y7_ from PROPERTY this_ left outer join PROPERTY_RATING ratingvo3_ on this_.PROPERTYID=ratingvo3_.propertyId inner join MTM_PROPERTY_SUPPLIER propertysu1_ on this_.PROPERTYID=propertysu1_.PROPERTYID where this_.LATITUDE between ? and ? and this_.LONGITUDE between ? and ? and this_.StatusCode=? and propertysu1_.STATUSCODE=? and this_.SequenceNumber<>? and this_.MARKETINGLEVEL<>?

Thanks
anand raman

lili_met · **Joined:** Tue May 23, 2006 10:53 am **Posts:** 24

Try to use createAlias() for that field.

diduknow · **Joined:** Wed Jun 21, 2006 2:25 pm **Posts:** 15

createAlias() uses an inner join and not an outer join. Hence it is not a valid option.

thanks
anand

lili_met · **Joined:** Tue May 23, 2006 10:53 am **Posts:** 24

maybe use subselect instead of outerjoin?

gajini · **Posted:** Thu Jun 22, 2006 10:38 am

try,

createAlias("","",JoinFragment.LEFT_OUTER_JOIN)

;-)