Convert SQL-query with subselect in select section to HQL

Question

Hello.

I have trouble with converting native SQL query to HQL. Query is something like follows:

select count(*)
, sum(select count(*) from employee e where e.company_id=c.id)) 
from company c where c.id = someID

First returned value is count of companies, second - amount of employees for specified company.

I.e. I have to get this two values for company with id=someID.

The trouble is hibernate doesn't support subselects in SELECT section, only in WHERE - by specification.

Actually I can:

1) use native query to run this through EntityManager

2) do the same dividing this "complex" query to two simpler SQL queries

But may be there are exist more convenient methods to realize initial query in HQL? - this is a question.

Thank you.

Answer 1

this might work:

select count(c.*) as count_companies
     , c as count_emp
from company c 
inner join 
(
     select company_id, count(*) as c
     from employee 
     where employee.company_id = someID 
     group by company_id
) e
on e.company_id=c.id
group by c.id