Hi,

I am new to 3.0.4, and the formula attribute/nested element seems really handy. Here is how I'm trying to use it:

Hibernate version:
3.0.4

Mapping documents:
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.cc.rewards.InterviewerReward" table="supervisor_reward" mutable="true" polymorphism="explicit" lazy="true">
<composite-id>
<key-many-to-one name="employee" class="com.cc.rewards.RewardsEmployee" column="emp_id"/>
<key-property name="date" type="date" column="date"/>
</composite-id>

formula="get_total_sr_points(interviewer_reward)"/>

</class>
</hibernate-mapping>

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

The generated SQL (show_sql=true):
select interviewe0_.emp_id as emp1_, interviewe0_.date as date, get_total_ir_points(interviewe0_.interviewer_reward) as formula0_ from interviewer_reward interviewe0_

As you can see, for the formula, it has added the alias interviewe0_. in front of my interviewer_reward record, which causes an error as there is no such column interviewer_reward. How can I prevent this?

does anyone have any ideas at all?

I have a very similar problem (if not the exact same one)

When using a SQL statement with a subquery in the many-to-one formula property, the parser will add the table qualifier to one of the expressions of the where clause.

For example:


formula="(select * from (select a.pk_id from table1 a where a.fk_id=fk_id order by a.pk_id desc) where rownum = 1)"

Which results in:

...(select * from (select a.pk_id from table1 a where a.fk_id=table20_.cat_id order by a.pk_id desc) where rownum = 1) ...


When I manually remove hibernate's alias in Oracle, the query works fine.

Does anyone else found any workarounds?

I want to think that perhaps I'm doing something wrong rather than there being a bug in hibernate. I'd have a hard time believing that no one else is using formula this way.




Other references of the same problem:
http://forum.hibernate.org/viewtopic.php?t=949494

Here's a workaround, although kinda vague:
http://thebogles.com/blog/2005/08/hibernate-formula-properties-bug-and/

I had some success using this

/** The getter method for this Item's type
*
* @hibernate.property
* formula="(select count(*) from ttemplateelements te where te.templateelementid IN (select pe.copiedfrom_portfolioelementid from tPortfolioelements pe where pe.itemid=itemid and pe.copiedfrom_portfolioelementid IS NOT NULL) and te.allow_edit='no' )"
*/


hope that helps