Problem with querying and one to many relationship..

[Annie Jones]

Hi,

I have the following mappings in my hibernate hbm.xml file (this cannot be changed, the table structure has been decided by the DBA):

 
 <class name="model.Postcode" table="PCODE"> 
    <composite-id> 
      <key-property name="countryCode" column="CNTRY_CODE" type="string" length="3"/> 
      <key-property name="postcode" column="POSTCODE" type="string" length="4"/> 
      <key-property name="suburb" column="SUBURB" type="string" length="35"/> 
    </composite-id> 
    <property name="state" column="STATE" type="string" length="3"/> 
    <property name="suburbRequiredFlag" column="SUBURB_RQD_FLAG" type="string"/> 
    <property name="validFlag" column="VALID_FLAG" type="string"/> 
  </class> 

  <class name="model.Suburb" table="SUBURB"> 
    <composite-id> 
      <key-property name="countryCode" column="CNTRY_CODE" type="string" length="3"/> 
      <key-property name="postcode" column="POST_CODE" type="string" length="4"/> 
      <key-property name="nzTownCity" column="TOWN_NM" type="string" length="35"/> 
      <key-property name="nzSuburb" column="SUBURB_NM" type="string" length="35"/> 
    </composite-id> 
    <many-to-one name="parentPostcode" class="model.Postcode" insert="false" update="false"> 
            <column name="CNTRY_CODE"/> 
            <column name="POST_CODE"/> 
            <column name="TOWN_NM"/> 
    </many-to-one> 
    <property name="validFlag" column="VALID_FLAG" type="string"/> 
  </class> 

We are passing these Postcode objects through to struts instead of having data transfer objects, this is read only data - the application will never modify any postcode/suburb information.

My problem is that I need a query that gives the same result as the following two queries:

 
String POSTCODES_WITH_SUBURBS_REQUIRED_BY_TOWN = "select suburb from "+Suburb.class.getName() + " suburb, "+Postcode.class.getName() + " pcode where pcode.suburbRequiredFlag = 'Y' and (suburb.nzTownCity like ? or suburb.nzSuburb like ?) and suburb.countryCode = ? and pcode.suburb  = suburb.nzSuburb"; 
  
String POSTCODES_WITH_SUBURBS_NOT_REQUIRED_BY_TOWN = "from pcode in class"+Postcode.class.getName() + " pcode where pcode.suburbRequiredFlag = 'N' and pcode.suburb like ? and pcode.countryCode = ?";

but instead of returning Suburb objects and Postcode objects, only returns Postcode objects. As you can see, the first query will return Suburb objects as I am searching on the nzTownCity or nzSuburb columns because the suburbRequiredFlag is set to 'Y' on the Postcode.

I know this seems fairly complicated but if there is a way that I can get only Postcode objects with the matching Suburb objects (instead of all the Suburb objects that are bound to a Postcode because of the one-to-many relationship) then that would be great.

Thanks in advance,
Annie

[Abraham Gimeno]

I don't know if I understood well but, have you tried

  "select suburb.parentPostcode from "+Suburb.class.getName() + " suburb, "+Postcode.class.getName() + " pcode where pcode.suburbRequiredFlag = 'Y' and (suburb.nzTownCity like ? or suburb.nzSuburb like ?) and suburb.countryCode = ? and pcode.suburb  =  

and since it's ManyToOne you should be able to access Postcode object

  "select suburb.parentPostcode from "+Suburb.class.getName() + " suburb where suburb.parentPostcode.suburbRequiredFlag = 'Y' and (suburb.nzTownCity like ? or suburb.nzSuburb like ?) and suburb.countryCode = ? and suburb.parentPostcode.suburb  = suburb.nzSuburb";