I've got the following SQL query which works. I've got a Project object mapped to the project table with a collection mapped to Status table as StatusHistory, and another 1-1 mapping to the Status table as currentStats. I have been trying to figure out how to represent this query in either HQL or the Criteria API. I would prefer the Criteria API, but I have not been able to figure out how to get it done.

SELECT P.UID AS Project_ID,
(SELECT MAX(dbo.javaToSqlDate(S1.CREATED_DATE))
FROM STATUS S1
WHERE P.UID = S1.FK_PROJECT_UID AND S1.NAME = 'PRE-DESIGN WAITING SALES APPROVAL') AS CREATE_DATE
FROM dbo.PROJECT P INNER JOIN
dbo.STATUS S ON P.FK_STATUS_UID = S.UID
WHERE (S.NAME = 'WAITING FOR DESIGN')
ORDER BY 'CREATE_DATE', 'Project_ID'


The following hql query comes close, but fails on the order by, because I am trying to order on a column alias that I define which gets overwritten by hibernate.

I could sure use some help on this problem. -- Shon


StringBuffer q = new StringBuffer("select proj.id, (select max(sl.createdDate) from proj.statusList sl where sl.name = 'PRE-DESIGN WAITING SALES APPROVAL') as createdDate");
q.append(" from Project proj");
q.append(" left outer join proj.status where proj.status.name in (:status) ");
q.append(!type.equalsIgnoreCase( Project.EITHER_TYPE ) ? "and proj.type = :type " : "and :type = :type " );

if ( user != null ) {
q.append(" and (proj.dealer = :userId or proj.salesRep = :userId or proj.techsupportrep = :userId)");
}
if ( company != null ) {
q.append(" and proj.company = :companyId");
}

q.append(" order by createdDate desc");

Query query = session.createQuery(q.toString());

query.setParameterList("status", status);
query.setString("type", type);
if ( user != null) {
query.setLong("userId", user.getUid());
}
if ( company != null ) {
query.setLong("companyId", company.getUid());
}

List l = query.list();

Hi Shon. I'm afraid I'm not going to answer your question directly, because I've got no CLUE how to set up that complex Order By in HQL or in a Criteria query. ;) However, maybe a different perspective on things will help you work it out.

First of all, let me restate what I think you're trying to do. You want a list of Projects, all of which are in the "WAITING FOR DESIGN" status, and you want them ordered by their creation date (which is determined by a status record), and then their ID number.

If I understood that correctly, here's how I'd go about it.

First of all, just query a list of Projects in the right state.

HQL:


I don't use criteria queries often, but that should look something like this:


Now, you've got a list of Project objects, and you need them ordered before you do anything to them, right? First of all, create a helper method on Project called getCreateDate that will find your creation date using the list of Status objects in your statusHistory collection. Once you've got that, all you need is a comparator that will do your order-by:


Then, when you need the result list sorted:


Is that the best way to do it? I've got no clue. But, since I don't know how to set up the complex HQL that you'd need to have Hibernate determine the creation date so the database can order it, this way would work for me. :)

-- Kin

Kin,

Thanks for all the thought you put into this! I was hoping to avoid the Comparator route since I think it may have some performance implications, but I may in fact need to go that direction. Another idea I had was to just do a JDBC query using the query I posted and get the id's and then query each of the project objects in a loop using hibernate. I would rather stay away from that, because then I expose my code to potential bugs if the database changes etc. but it may be the simplest route.

Shon

I understand the concern. And I played around with criteria queries for a bit, which has increased my hope that there's a solution to your problem. As I get time come Monday and Tuesday, I'll keep poking at 'em and let you know if I come up with anything useful. :)

-- Kin