org.hibernate.hql Errors in HQL subqueries

Question

I have a Table which has meta-data of the files. The Table has columns id, path, originalSize, lastModified and info_id;

id, path,  lastModified,  originalSize, infoid
01, A.txt,   25/10/2014,  25,                 0
02, A.txt,   26/10/2014,  30,                 0
03, B.txt,   25/10/2014,  40,                 0
04, B.txt,   27/10/2014,  50,                 0
05, C.txt,   25/10/2014,  10,                 0
06, C.txt,   26/10/2014,  20,                 0
07, D.txt,   25/10/2014,  5,                  0
08, D.txt,   27/10/2014,  10,                 0

I need a sum on the originalSize column where the path is one of some given list of paths and the lastModified is maximum of all the rows with that `path';

I could not achieve the result in a single HQL Query so I did it in three steps:

STEP 1: In step 1, I am trying to get those rows which matches the criteria where the path is one of some given list of paths and the lastModified is maximum of all the rows with that `path'.

STEP 2: In step 2, I simply collecting only ids from the above result set for further Query.

STEP 3: I am trying to get the sum on size which matches with above ids.

In detail with code and example;

STEP 1:

I want to select all rows where the path is one of some given list of paths and the lastModified is maximum of all the rows with that `path';

id, filename, lastmodified
    02, A.txt,   26/10/2014 
    04, B.txt,   27/10/2014 
    06, C.txt,   26/10/2014 
    08, D.txt,   27/10/2014

I have written the following method using HQL which returns the above result;

public List<ObjectMetadata> getListOfLatestModifiedFiles(String infoId) {

        List<ObjectMetadata> objectMetadataList =null;
        try {



            String sQuery =  "SELECT om.id, om.path, om.originalSize, MAX(om.lastModified) "+
                    "FROM "+
                    "ObjectMetadata om "+
                    "WHERE om.infoId = '"+infoId+"' "+
                    "GROUP BY om.id, om.path, om.originalSize";
            org.hibernate.Query query = getEm().getSession().createQuery(sQuery);
            objectMetadataList = query.list();

            } catch (Exception nre) {
            System.out.println(nre.toString());

        }
        return objectMetadataList;
    }

STEP 2:

Now, I need only IDs from the above result;

    id
    02
    04
    06
    08 

To get only IDs, I have written another method which traverse the List<ObjectMetadata> and return only the id;

public List<String> getIds(List<ObjectMetadata> objectMetadataList) {
        List<String> ids = new ArrayList<String>();
       try{
           Iterator itr = objectMetadataList.iterator();
           while(itr.hasNext()) {
               Object[] obj = (Object[]) itr.next();
               String id = String.valueOf(obj[0]);
               ids.add(id);
           }
        } catch (Exception nre) {
            System.out.println(nre.toString());
        }
        return ids;
    }

STEP 3:

Finally, I need the sum on originalSize column that matches with these IDs;

id, originalSize

02, 30
04, 50
06, 20
08, 10

SUM = 110

For that I have written another method:

public long getSizeOfAllLatestModifiedFiles(List<String> list) {

        Long size  = 0l;
             String sQuery =  "SELECT SUM(om.originalSize) "+
                    "FROM "+
                    "ObjectMetadata om "+
                    "WHERE om.id IN (:ids) ";

        org.hibernate.Query query = (Query) getEm().getSession().createQuery(sQuery);
        query.setParameterList("ids", list);
        size  = (Long) query.uniqueResult();
        return size;
    }

NOW MY ISSUE IS: I COULD NOT MAKE ALL THESE 3 STEPS, RUN IN A SINGLE QUERY

I tried the below single query;

    String sSubQuery =  "SELECT om.path, om.originalSize, MAX(om.lastModified) "+
            "FROM "+
            "ObjectMetadata om "+
            "WHERE om.infoId = '"+infoId+"' "+
            "GROUP BY om.path, om.originalSize";

     String sQuery =  "SELECT SUM(o.originalSize) "+
            "FROM "+
            "("+
            " "+sSubQuery+
            " ) "+
            "o";

But on the below line of code;

 Query q = getEm().createQuery(sQuery);

I am getting the following Errors.

ERROR [2014-10-27 11:17:24,264] org.hibernate.hql.internal.ast.ErrorCounter: line 1:33: unexpected token: (
ERROR [2014-10-27 11:17:24,304] org.hibernate.hql.internal.ast.ErrorCounter: line 1:33: unexpected token: (
! antlr.NoViableAltException: unexpected token: (

ERROR [2014-10-27 11:17:24,306] org.hibernate.hql.internal.ast.ErrorCounter: line 1:68: unexpected token: MAX
ERROR [2014-10-27 11:17:24,312] org.hibernate.hql.internal.ast.ErrorCounter: line 1:68: unexpected token: MAX
! antlr.NoViableAltException: unexpected token: MAX 

Please help me to run all above 3 steps into a single HQL query