How to use subquery into "from" clause in hibernate?

Question

I have three tables: class, student, and teacher

table class
{
    class_id(PK)
}

table student
{
    student_id(PK)
    class_id(PK+FK)
}

table teacher
{
    teacher_id(PK)
    class_id(PK+FK)
}

I have a query in SQL, which works fine.

SELECT data.class_id, count(data.class_id) AS count
FROM ((SELECT class_id FROM student)
        union all
        (SELECT class_id FROM teacher)) AS data
GROUP BY data.user_id
ORDER BY count desc

The query contains sub query in the from clause and union operation. I unable to convert it to the HQL.

please give me the efficient HQL query from the above SQL query.

Answer 1

Unfortunately HQL does not support UNION queries. Two alternative strategies to solve your problem are:

• Mapping a Pojo to a a view

• Or Inheritance mapping. Particularly Table per concrete class strategy with an abstract Person superclass inherited both by Student and Teacher seems to fit your problem well:

  select p.id, count(c)
  from Person p join p.classes c
  group by p.id
  order by count(c) desc
  

Answer 2

You cannot place a subquery at the from clause in HQL. Only select or where clauses.