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.

score 8 · Accepted Answer · edited May 23 '17 at 11:46

8

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
```

edited May 23 '17 at 11:46

Community

1
1

answered Jan 03 '13 at 16:27

Anthony Accioly

21,918
9
70
118

Thanks for the documentation links. Re-reading those sections was worth it. – carbontax Jan 04 '13 at 14:49

Roman C · Answer 2 · 2013-01-03T15:35:10.497

7

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

edited Jan 03 '13 at 15:35

answered Jan 03 '13 at 15:04

Roman C

49,761
33
66
176

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

2 Answers2