problem with "select new Object ... join ... where"

Question

Hi, I'm having a problem with an HQL query

Three classes

ClassOne is my BusinessObject

public class ClassOne {  
  private int id;  
  private int status;
  private Set<ClassTwo> classTwos;  
  + other fields/getters/setters/constructor etc  
}

ClassTwo is referenced in a set of ClassOne and is kind of the history of an object of ClassOne

public class ClassTwo {  
  private int id;  
  private int oldStatus;  
  private int newStatus;  
  private String message;  
  //+ getters/setters/constructor etc  
}

ClassThree is my DTO/VO with just one classTwo (not the whole history)

public class ClassThree {  
  private int id;  
  private int status;  
  private ClassTwo classTwo;  
  public ClassThree(int pId, int pStatus, ClassTwo pClassTwo) {  
    id=pId;  
    status=pStatus;  
    classTwo=pClassTwo;   
  }
  //+ getters/setters etc
}

Now I'd like to create an HQL query like this:
I'd like to get all objects of ClassThree with a certain status and if it exists the newest ClassTwo with a certain newStatus.
For example:
I'd like to get all the DTOs (ClassThree) of ClassOne whose status is now 1, but earlier in their history it has been 2 and I'd like to have the latest ClassTwo object which has 2 as newStatus.

SELECT new ClassThree(c1.id, c1.status, c2)  
FROM ClassOne c1  
LEFT JOIN c1.classtwos c2 (...)

and (...) is where I don't know what to do, I'm not even sure if it's a join / join fetch

Looked around and tried quite a lot already, but no clue. Especially with the join fetch I get some Hibernate errors like org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list.

Fetching the BusinessObject like that is no problem

SELECT distinct(c1)
FROM ClassOne c1  
LEFT OUTER JOIN FETCH c1.classtwos c2  

and I get the ClassTwos as my field.

Thanks in advance,
Jacob

P.S.: One thing might be important, ClassTwo has no reference to ClassOne!!

P.P.S : The simple SQL query which resolves my problem looks more or less like that:

select * from classone as c1 left join (select * from classtwo where newstatus = 2) c2 on c1.id=c2.id_classone whete c1.status = 1 

This query works and gets all the information needed on my PostGreSQL DB, but I'd really like to have an HQL to continue to work with, especially for maintenance reasons and so on...

Update with workaround solution:

Getting the ids of all the ClassOnes with a status 1

Collection<Integer> ids = null;
ids = (Collection<Integer>) getHibernateTemplate().execute(
  new HibernateCallback() {
    public Object doInHibernate(Session pSession) throws HibernateException, SQLException {
      return getDocumentIds(pSession, pStatus);
    }
  }
);

Now I get all the DTOs which have been in status 2 (thanks to Ivan) with:

Named query Document.dto.with.transfer

SELECT new DocumentDTO(d.id, d.status, histo)
FROM Document d
LEFT JOIN d.histories histo
WHERE 
  d.id in (:ids)
AND
  (histo.id = 
    SELECT MAX(innerhisto.id) 
    FROM Document innerd 
    JOIN innerd.histories innerhisto
    WHERE d.id = innerd.id AND innerhisto.newStatus = 21)

(in my code I use some named queries)

List<DocumentDTO> lRes = new ArrayList<DocumentDTO>();
Query lQuery = getSession(false).getNamedQuery("Document.dto.with.transfer");
lQuery.setParameterList("ids", ids);
lResultList.addAll(lQuery.list());

afterwards I remove all the IDs already found from my list ids

for (DocumentDTO dto : lResultList) {
  ids.remove(dto .getId());
}

I do a third query using a second constructor for the DTO, initializing my history with a dummy-object.

Named query Document.dto.simple

SELECT new DocumentDTO(d.id, d.status)
FROM Document d
WHERE 
  d.id in (:ids)

(another named query)

lQuery = getSession(false).getNamedQuery("Document.dto.simple");
lQuery.setParameterList("ids", ids);
lResultList.addAll(lQuery.list());

and it's done.

Answer 1

To include Documents with no history we should use LEFT JOIN and test for empty collection, then we use subquery (SELECT COUNT(...)) to detect all documents that have never been in status 2. The last OR-clause is for fetching the last history with the specified status.

Here is the HQL query:

SELECT new DocumentDto(doc.id, doc.status, hist) 
FROM Document doc 
LEFT JOIN doc.histories hist 
WHERE doc.status = :docStatus 
AND (size(doc.histories) = 0 
OR (SELECT COUNT(innerhist) 
    FROM Document innerdoc JOIN innerdoc.histories innerhist 
    WHERE innerdoc.id=doc.id AND innerhist.newStatus = :historyStatus) = 0
OR (hist.newStatus = :historyStatus AND hist.id = 
    (SELECT max(innerhist.id) 
     FROM Document innerdoc 
     JOIN innerdoc.histories innerhist 
     WHERE innerdoc.status = :docStatus AND innerhist.newStatus = :historyStatus))

Then call setParameter("historyStatus", 2) and setParameter("docStatus", 1) on your query to get the correct result.

That's it!

Please note, I've made an assumption, that we can use a value of id attribute of History as an indicator of the order in which objects were put in your database.