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

Question

I have a problem with 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 the ClassOne collection and is a kind of history of the ClassOne object

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 one class (not the whole story)

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 would like to create an HQL query as follows:
I would like to get all ClassThree objects with a specific status and if it exists with the newest ClassTwo with a specific newStatus.
For example:
I would like to get all the DTOs (ClassThree) of the ClassOne class, whose status is now 1, but previously in their history was 2, and I would like to have the last ClassTwo object that has 2 as newStatus.

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

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

, . , Hibernate, org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list.

BusinessObject

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

ClassTwos .

,
Jacob

P.S.: , ClassTwo ClassOne!!

P.P.S: SQL-, , :

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

, PostGreSQL, , HQL , ..

:

ClassOnes 1

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

DTO, 2 ( ):

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)

( )

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

, ids

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

, DTO, .

Document.dto.simple

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

( )

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

.

Answer 1

, LEFT JOIN , (SELECT COUNT(...)) , 2. OR-clause .

HQL:

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))

setParameter("historyStatus", 2) setParameter("docStatus", 1) , .

!

, , id History , .