Little problem using formulas with discriminators over foreign keys (maybe isolated to MySQL but the generated SQL look pretty faulty for any db manager). Have seen a couple times on the forum people ask about using subselects on ID foreign keys and using a returned non-keyed string column as the discriminator-value but not gotten a definate answer if I can use the formula listed below to filter out "versions":
Hibernate version: 3.1 beta 3
Mapping documents: <class name="Resurs" table="RESURS">
<id name="id" column="RESURSID"> <generator class="identity"> </generator> </id>
<discriminator formula="(SELECT t1.RESURSTYP FROM RESURSTYP t1 WHERE t1.RESURSTYPID = RESURSTYPID)"/>
<many-to-one name="typ" column="RESURSTYPID" class="Resurstyp" lazy="false" foreign-key="FK_RESURSTYP" not-null="true"/>
....
<subclass name="Version" discriminator-value="version"/>
</class>
....
<class name="Resurstyp" table="RESURSTYP">
<id name="id" column="RESURTYPID"> <generator class="identity"> </generator> </id>
<property name="typ"> <column name="RESURSTYP" length="50" not-null="true"/> </property>
<property name="beteckning"> <column name="BETECKNING" length="50" not-null="true"/> </property>
</class>
Full stack trace of any exception that occurs: [java] Hibernate: select version0_.RESURSID as RESURSID0_, version0_.RESURSTYPID as RESURSTY2_0_, version0_.BETECKNING as BETECKNING0_, version0_.FRANDATUM as FRANDATUM0_, version0_.TOMDATUM as TOMDATUM0_, version0_.TSTAMP as TSTAMP0_ from RESURS version0_ where (SELECT t1.RESURSTYP FROM RESURSTYP t1 WHERE t1.RESURSTYPID = version0_.RESURSTYPID)='version' [java] 09:22:30,651 WARN JDBCExceptionReporter:71 - SQL Error: 1054, SQLState: 42S22 [java] 09:22:30,654 ERROR JDBCExceptionReporter:72 - Unknown column 't1.RESURSTYPID' in 'where clause' [java] Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
Name and version of the database you are using: MySQL, version: 5.0.13-rc-standard
Main problem is that the resulting SQL (from the formula) is concatenated as a where clause rather than as a join between RESURS/RESURSTYP tables. Any ideas? Otherwise I have to reference RESURSTYP in my RESURS table via the RESURSTYP column in the RESURSTYP table (i.e. property-ref) instead of going against the primary key.
|