Hibernate version: 3.0.5

Mapping documents:

<hibernate-mapping>
<class name="be.ft.best.BestStreet" table="best_street" schema="public">
<id name="id" type="integer">
<column name="id" />
<generator class="increment" />
</id>
<property name="streetkey" type="string">
<column name="streetkey" length="100" unique="true" />
</property>
<property name="streetnamedu" type="string">
<column name="streetnamedu" length="100" />
</property>
<property name="streetnamefr" type="string">
<column name="streetnamefr" length="100" />
</property>
<property name="streetnameen" type="string">
<column name="streetnameen" length="100" />
</property>
<property name="municipalitykey" type="string">
<column name="municipalitykey" length="10" />
</property>
<property name="startdate" type="timestamp">
<column name="startdate" length="8" />
</property>
<property name="enddate" type="timestamp">
<column name="enddate" length="8" />
</property>
<set name="bestSubstreets" inverse="true" outer-join="true">
<key>
<column name="streetkey" length="100" />
</key>
<one-to-many class="be.ft.best.BestSubstreet" />
</set>
</class>
</hibernate-mapping>


<hibernate-mapping>
<class name="be.ft.best.BestSubstreet" table="best_substreet" schema="public">
<id name="id" type="integer">
<column name="id" />
<generator class="increment" />
</id>
<many-to-one name="bestStreet" class="be.ft.best.BestStreet" fetch="select">
<column name="streetkey" length="100" />
</many-to-one>
<property name="firsthousenumber" type="string" >
<column name="firsthousenumber" length="100" />
</property>
<property name="lasthousenumber" type="string">
<column name="lasthousenumber" length="100" />
</property>
<property name="parity" type="integer">
<column name="parity" />
</property>
<property name="startdate" type="timestamp">
<column name="startdate" length="8" />
</property>
<property name="enddate" type="timestamp">
<column name="enddate" length="8" />
</property>
</class>
</hibernate-mapping>


Name and version of the database you are using:PostgreSQL 8.0

The generated SQL (show_sql=true):



Hello,

I have to use a legacy database schema. I have basically 2 tables.
The first one called best_street contains the following columns
CREATE TABLE best_street
(
id serial NOT NULL,
streetkey varchar(100),
streetnamedu varchar(100),
streetnamefr varchar(100),
streetnameen varchar(100),
municipalitykey varchar(10),
startdate timestamp,
enddate timestamp,
CONSTRAINT best_street_pkey PRIMARY KEY (id),
CONSTRAINT best_street_streetkey_key UNIQUE (streetkey)
)
The second table best_substreet:

CREATE TABLE best_substreet
(
id serial NOT NULL,
streetkey varchar(100),
streetcode varchar(20),
firsthousenumber varchar(100),
lasthousenumber varchar(100),
parity int4,
startdate timestamp,
enddate timestamp,
CONSTRAINT best_substreet_pkey PRIMARY KEY (id),
CONSTRAINT best_substreet_streetkey_fkey FOREIGN KEY (streetkey) REFERENCES best_street (streetkey) ON UPDATE RESTRICT ON DELETE RESTRICT
)

When I try to retrieve a beststreet instance, the associated substreets are not retrieved. I checked the generated SQL and I see that the join is not done on the streetkey column as I would expect but on the id. Can you tell me how I can define the column on which the join should be made.
Here the SQL:

select beststreet0_.id as id3_, beststreet0_.streetkey as streetkey36_3_, beststreet0_.streetnamedu as streetna3_36_3_, beststreet0_.streetnamefr as streetna4_36_3_, beststreet0_.streetnameen as streetna5_36_3_, beststreet0_.municipalitykey as municipa6_36_3_, beststreet0_.startdate as startdate36_3_, beststreet0_.enddate as enddate36_3_, bestsubstr2_.streetkey as streetkey6_, bestsubstr2_.id as id6_, bestsubstr2_.id as id1_, bestsubstr2_.streetcode as streetcode39_1_, bestsubstr2_.streetkey as streetkey39_1_, bestsubstr2_.firsthousenumber as firsthou4_39_1_, bestsubstr2_.lasthousenumber as lasthous5_39_1_, bestsubstr2_.parity as parity39_1_, bestsubstr2_.startdate as startdate39_1_, bestsubstr2_.enddate as enddate39_1_, from public.best_street beststreet0_ left outer join public.best_substreet bestsubstr2_ on beststreet0_.id=bestsubstr2_.streetkey where beststreet0_.id=?

I would like here to have something like
left outer join public.best_substreet bestsubstr2_ on beststreet0_.streetkey=bestsubstr2_.streetkey

Thanks in advance for your help

Try this in BestStreet's <set> mapping: