jpajpql

JPQL : geting results from a multi expression select


Here is my JPQL query:

SELECT p, 
   exists( select dp from DocumentPublication dp where dp.documentVersion = p) 
FROM 
   DocumentVersion p where document.id = :id

Here is the code to get the result:

   Query query =   
     getEntityManager().createNamedQuery("DocumentVersion.findByDocumentId");

   query.setParameter("id", docsFilter.getProjectId());

   List<Object[]>  res;
   try
   {
       res = query.getResultList();
   }
   catch (NoResultException e)
   {
       return null;
   }
   // res only contains a list of DocumentVersion / No 'boolean'

I want to retrieve the list of results but when I perform a "getResultList" on my query, I only see the first part of the select ( a list of DocumentVersion), I don't see the boolean that I would like to get.

I am using one of the latest Hibernate version as a persistence provider.


Solution

  • As SJuan pointed out, exist() cannot be used in the select expression. So I changed the query with a left join which works well. Here is the query:

    SELECT p, count(dp.id) 
    FROM DocumentVersion p left join  p.documentPublications dp 
    where p.document.id  = :id 
    group by p.id
    

    With the code to retreive the result:

     List<Object[]>  res;
       try
       {
           res = query.getResultList();
       }
       catch (NoResultException e)
       {
           return null;
       }
    
       List<DocumentVersion> documentVersions = new ArrayList<>();
       for(Object[] objects : res)
       {
          DocumentVersion documentVersion = (DocumentVersion) objects[0];
          documentVersion.setPublished( (Long) objects[1] >  0);
          documentVersions.add( documentVersion );
       }