Hello,
I'm trying to use @Formula to computed a "balance" property, which is the sum of the "amount" property on a set of detail records.
@Formula allows an SQL fragment, including subselects, as a property, so I'm trying to use a correlated subquery as I would in SQL. However I'm unable to refer to the outer table alias. The @Formula SQL fragment behaviour does not match the HQL subquery behaviour, where an unqualified ambiguous name would be resolved to the outer query.
Use case:
Code:
@Entity
public class Account {
@Id @Column(name="_rowid")
int id;
String a;
String b;
String c;
@OneToMany(mappedBy="accountID", fetch=FetchType.LAZY)
List<Fee> fees;
@Formula("(select sum(f.amount) - sum(f.remitted) from Fee f " +
"where f.accountID = _rowid and f.type = 'B')")
BigDecimal balance;
// Getters and setters omitted
}
@Entity
public class Fee {
@Id @Column(name="_rowid")
int id;
@ManyToOne @JoinColumn(name="accountID")
Account account;
BigDecimal amount;
BigDecimal remitted;
char type = 'B';
// Getters and setters omitted
}
In the formula expression, on the balance property of Account, "_rowid" is a reference to the table in the containing query. Unfortunately _rowid is a column name in both the inner and outer tables. I would prefix this with a table alias, but I can't because I do not know the table alias as hibernate builds the query containing the formula.
In the generated SQL, the unqualified "_rowid" refers to the inner query (Fee f._rowid) not the outer query, which causes a horrendous query to be executed.
The hib docs:
http://www.hibernate.org/hib_docs/annot ... ml#d0e2273
state that: "The SQL fragment can be as complex as you want and even include subselects." There is no mention however of correlated subselects.
Subqueries are supported in HQL queries:
http://www.hibernate.org/hib_docs/v3/re ... eries.html
http://docs.huihoo.com/hibernate/hibern ... ryhql.html (heading "Subqueries")
"Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. "
The HQL subselect behaviour is to treat unqualified ambiguous aliases as references to the outer query. Databases seem to do the opposite, and treat them as references to the inner query (at least in Sybase and PostgreSQL).
If the formula could be an HQL scalar query I don't think I'd have this problem. Sadly when it is an SQL fragment it seems I am unable to when the same column name is in both the inner and outer tables.
I only have read-only access to the database in question and I cannot change column names as a workaround.
The only workaround I've found so far is putting the guessed prefix "account0_." on "_rowid" in the formula SQL fragment, which works for simple entity loading but unfortunately fails on criteria queries where the prefix is not "account0_" but instead is "this_.". (In the SQL of em.findById() and em.createQuery().getResults() the prefix hibernate generates is usually "account0_.", whereas in criteria queries it is "this_.".)
My environment is JBoss 4.2.0.GA, EJB3 code (using hibernate extensions).
Sybase 12 database (with "Sybase" dialect).
I'm going to have to remove the @Formulas but I can't think of another way to compute the balance, except by fetching the fees association and computing the sum myself when getBalance() is called.
Can anyone help me refer to the outer table, or suggest an alternative way to compute the balance property of these objects, without fetching all the "fees" detail records and computed the sum myself in code? The balance property is always accessed but the Fees association is rarely used and I don't want to fetch these records if I can avoid it.
If there is no alternative can Hibernate be extended to better support this use case?
Best Regards,
Matt
Login
FAQ
Search