From SQL to HQL/Criteria

obourdon · **Joined:** Sat Jan 05, 2008 7:33 am **Posts:** 26

Hello all

I have a "quite simple" SQL query that I would like to propose both as HQL AND Criteria
accessible
Tried several things without any success, looked into the forums without any more luck and googled for more infos on subqueries without being able to find anything which could lead to working code

Could anyone help please
Many thanks

Here is the "beast" which is supposed to return the latest demo and test files in a given directory in one shot

Code:

select db_id,shortname,fullname,file_date from
    (select db_id,shortname,fullname,file_date from Files
            where folder_id=1 and shortname in ('demo','test')
            order by shortname,file_date desc)
    as subquery
group by name

which can also be rewritten as

Code:

select db_id,shortname,fullname,file_date from
    (select name as xname,max(file_date) as last from Files
             where folder_id=1 and name in ('demo','test')
            group by name)
    as subquery inner join Files as result
    where subquery.xname = result.shortname and subquery.last = result.file_date

mmerder · **Posted:** Wed Jan 28, 2009 5:09 am

It is not possible to use a subselect in a from-clause, I think. Alternatively you could create a view, which does the subselect and map another entity to this view. This entity could be used in your queries.

btw: is it not possible to rewrite your query as follows (HQL):

Code:

select f from File f where f.fileDate = (select max(f2.fileDate) from File f2 where f2.shortname in (...)) and f.shortname in (...)

This query should also return the latest files.