Hi there I am experiencing something strage when a try to join two tables referenced by a composite foreign key: here are the mappings:
<class name="bbc.monitoring.dynamite.Episode" table="episode"> <id column="pid" name="pid" type="java.lang.String" /> <property column="play_version_id" name="playVersionId" type="java.lang.String" /> <property column="title" name="title" type="java.lang.String" /> <property column="media_type" name="mediaType" type="java.lang.String" /> <property column="master_brand" name="masterBrand" type="java.lang.String" /> <property column="is_critical" name="isCritical" type="boolean" /> <property column="is_available" name="isAvailable" type="boolean" /> <property column="start_time" name="startTime" type="java.sql.Timestamp" /> <set name="ondemands" table="ondemands"> <key column="FK_pid" /> <element column="oid" type="java.lang.String" /> </set> <set name="media" table="media" lazy="false" cascade="all" inverse="true"> <key column="FK_pid" /> <one-to-many class="bbc.monitoring.mediaselector.Media" /> </set> </class>
<class name="bbc.monitoring.mediaselector.Media" table="media" dynamic-insert="true" dynamic-update="true"> <composite-id name="MediaPK" class="bbc.monitoring.mediaselector.MediaPK"> <key-property column="service" name="service" type="java.lang.String" /> <key-property column="FK_pid" name="pid" type="java.lang.String" /> </composite-id> <property column="type" name="type" type="java.lang.String" /> <property column="expires" name="mediaExpire" type="java.sql.Timestamp" /> <set name="connection" table="connection" lazy="false" cascade="all" inverse="true"> <key> <column name="media_service" /> <column name="media_FK_pid" /> </key> <one-to-many class="bbc.monitoring.mediaselector.Connection" /> </set> <many-to-one name="episode" class="bbc.monitoring.dynamite.Episode" not-null="true" insert="false" update="false"> <column name="FK_pid"></column> </many-to-one> </class>
<class name="bbc.monitoring.mediaselector.Connection" table="connection" dynamic-insert="true" dynamic-update="true"> <composite-id name="ConnectionPK"> <key-property column="identifier" name="identifier" type="java.lang.String" /> <key-property column="media_service" name="mediaService" type="java.lang.String" /> </composite-id> <property column="href" name="href" type="java.lang.String" /> <property column="protocol" name="protocol" type="java.lang.String" /> <property column="kind" name="kind" type="java.lang.String" /> <property column="originServerURI" name="originServerURI" type="java.lang.String" /> <property column="responseCode" name="responseCode" type="integer" /> <property column="lastTimeFetched" name="lastTimeFetched" type="java.sql.Timestamp" /> <!-- need to add media_FK_pid as the many-to-one states that insert and update is false --> <property column="media_FK_pid" name="episodePid" type="java.lang.String" /> <many-to-one name="media" class="bbc.monitoring.mediaselector.Media" not-null="true" insert="false" update="false"> <column name="media_FK_pid"></column> <column name="media_service"></column> </many-to-one> </class>
this is the hql query:
Query query = session.createQuery("select ep from Episode ep join ep.media m join m.connection c where c.responseCode = 404 ");
this is the output of hibernate:
select episode0_.pid as pid0_, episode0_.play_version_id as play2_0_, episode0_.title as title0_, episode0_.media_type as media4_0_, episode0_.master_brand as master5_0_, episode0_.is_critical as is6_0_, episode0_.is_available as is7_0_, episode0_.start_time as start8_0_ from episode episode0_ inner join media media1_ on episode0_.pid=media1_.FK_pid inner join connection connection2_ on media1_.service=connection2_.media_FK_pid and media1_.FK_pid=connection2_.media_service where connection2_.responseCode=404
which does not work but if I manually map correctly the join :
media1_.service=connection2_.media_service and media1_.FK_pid=connection2_.media_FK_pid
it does work. has someone come across similar behaviour?
kind regards
coco
|