Subquery in Projection and Order By
I have a situation in which I can not create a Criteria or Query to represent the SQL that I need without resorting to a hack.
I am using Hibernate Core 3.2.5 on Oracle 9i. The tables and mappings in question are:
Code:
CREATE TABLE zai.ideas (
id_idea_id_pk NUMBER(19) NOT NULL,
id_author_id_fk VARCHAR2(11) NOT NULL,
id_category_id_fk NUMBER(19) NOT NULL,
id_confidential_yn VARCHAR2(3) NOT NULL,
id_content VARCHAR2(4000) NOT NULL,
id_email VARCHAR2(70) NOT NULL,
id_status_cd VARCHAR2(16) NOT NULL,
id_submit_ts DATE NOT NULL,
id_topic VARCHAR2(128) NOT NULL,
id_revenue_yn VARCHAR2(3) NOT NULL,
id_version NUMBER(10) NOT NULL
);
CREATE TABLE zai.ballots (
ba_idea_id_ck NUMBER(19) NOT NULL,
ba_voter_id_ck VARCHAR2(11) NOT NULL,
ba_votes NUMBER(10) NOT NULL,
ba_closed_yn VARCHAR2(3) NOT NULL,
ba_version NUMBER(10) NOT NULL
);
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="domain"
schema="ZAI">
<class name="Idea" table="IDEAS">
<id name="id"
column="ID_IDEA_ID_PK"
type="long"
access="field">
<generator class="sequence">
<param name="sequence">ZAI_ID</param>
</generator>
</id>
<version name="version"
column="ID_VERSION"
type="integer"
access="field" />
<many-to-one name="author"
column="ID_AUTHOR_ID_FK"
fetch="join"
access="field"
update="false" />
<set name="ballots"
access="field"
cascade="all, delete-orphan"
inverse="true" >
<key column="BA_IDEA_ID_CK"/>
<one-to-many class="Ballot" />
</set>
<many-to-one name="category"
column="ID_CATEGORY_ID_FK"
fetch="join"
access="field" />
<property name="content"
column="ID_CONTENT"
type="string"
access="field" />
<property name="confidential"
column="ID_PRIVATE_YN"
type="yes_no"
access="field" />
<property name="email"
column="ID_EMAIL"
type="string"
access="field" />
<property name="revenueGenerating"
column="ID_REVENUE_YN"
type="yes_no"
access="field" />
<property name="status"
column="ID_STATUS_CD"
type="idea_status"
access="field" />
<property name="submitDate"
column="ID_SUBMIT_TS"
type="timestamp"
access="field"
update="false" />
<property name="topic"
column="ID_TOPIC"
type="string"
access="field" />
</class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="domain"
schema="ZAI">
<class name="Ballot" table="BALLOTS">
<composite-id name="id"
class="Ballot$Id"
access="field">
<key-property name="ideaId"
column="BA_IDEA_ID_CK"
type="long"
access="field" />
<key-property name="voterId"
column="BA_VOTER_ID_CK"
type="encrypted_string"
access="field" />
</composite-id>
<version name="version"
column="BA_VERSION"
type="integer"
access="field" />
<property name="votes"
column="BA_VOTES"
type="int"
access="field" />
<property name="closed"
column="BA_CLOSED_YN"
type="yes_no"
access="field" />
<many-to-one name="idea"
column="BA_IDEA_ID_CK"
access="field"
insert="false"
update="false" />
<many-to-one name="voter"
column="BA_VOTER_ID_CK"
access="field"
insert="false"
update="false" />
</class>
</hibernate-mapping>
What I need to do is select Ideas that could match any of the following: status, category, confidential, or revenue generating if provided. I wrote the following criteria to retrieve the matching Ideas:
Code:
Criteria criteria = getSession().createCriteria(getPersistentClass());
criteria.setFirstResult(firstResult);
criteria.setMaxResults(pageSize);
if (status != null) {
criteria.add(Restrictions.eq("status", status));
}
if (category != null) {
criteria.add(Restrictions.eq("category", category));
}
if (excludeConfidential) {
criteria.add(Restrictions.eq("confidential", false));
}
if (excludeNonRevenueGenerating) {
criteria.add(Restrictions.eq("revenueGenerating", true));
}
The problem comes in when I want to sort the results. I want to configure the criteria to implement sorting by submitDate, total votes and submitDate, or ballot count and submitDate. I would like to be able to do something like the following:
Code:
if (IdeaSortBy.MOST_POPULAR.equals(sortBy)) {
criteria.addOrder(Order.desc("sum(ballots.votes)"));
}
else if (IdeaSortBy.MOST_ACTIVE.equals(sortBy)) {
criteria.addOrder(Order.desc("ballots.size"));
}
criteria.addOrder(Order.desc("submitDate"));
This, of course, is not possible. Order only accepts a property name. If Order could accept a sql fragment then I could at least do something like the following:
Code:
if (IdeaSortBy.MOST_POPULAR.equals(sortBy)) {
criteria.addOrder(Order.sqlFragment("select nvl(sum(b.ba_votes),0) from zai.ballots b where b.ba_idea_id_ck = id_idea_id_pk").desc());
}
else if (IdeaSortBy.MOST_ACTIVE.equals(sortBy)) {
criteria.addOrder(Order.sqlFragment("select count(b.ba_idea_id_ck) from zai.ballots b where b.ba_idea_id_ck = id_idea_id_pk) as ballots").desc());
}
criteria.addOrder(Order.desc("submitDate"));
To get around this problem I created two private fields on Idea named votesHack and ballotCountHack. These fields have no accessors. The mappings for these fields look like this:
Code:
<property name="votesHack"
formula="(select nvl(sum(b.ba_votes),0) from zai.ballots b where b.ba_idea_id_ck = id_idea_id_pk)"
type="int"
access="field" />
<property name="ballotCountHack"
formula="(select count(b.ba_idea_id_ck) from zai.ballots b where b.ba_idea_id_ck = id_idea_id_pk)"
type="int"
access="field" />
Having done this I am able to write the order by on the criteria as:
Code:
if (IdeaSortBy.MOST_POPULAR.equals(sortBy)) {
criteria.addOrder(Order.desc("votesHack"));
}
else if (IdeaSortBy.MOST_ACTIVE.equals(sortBy)) {
criteria.addOrder(Order.desc("ballotCountHack"));
}
criteria.addOrder(Order.desc("submitDate"));
The net affect of all of this is that votesHack and ballotCountHack formulas are added to the projection and to the order by.
I didn't like this hack so I tried to find another way to implement the required query. I turned to DetachedCriteria but that only works for Restrictions and not Projections. Using HQL or SQL wouldn't work due to the dynamic nature of the query. By that I mean the restrictions and order by are dynamic based on the input specification. Furthermore, this hack solution would not allow formula with input parameters.
Finally the questions...
Has anybody encountered a situation similar to this one and found a solution other than the hack I used?
To the Hibernate team...
It is possible to update Order to include a sqlFragment and/or DetachedCriteria?
It is possible to update Projections to use a DetachedCriteria as a property? This would useful when mapping to non-entities.
It is possible to update Projections to accept input parameters in a sqlFragment as Restrictions does? This would be an alternative to the question above using DetachedCriteria.
In general, is anybody else interested in these features? If so I will submit to JIRA.
Samer Kanjo
Login
FAQ
Search
