Question

I m trying to build a query using HQL and OUTER JOINs and just can t get it work. Consider the following mapping

<class name="Parent">
    <id name="id" type="integer">
      <generator class="native" />
    </id> 
</class>

<class name="Child">
    <id name="id" type="integer">
      <generator class="native" />
    </id> 
    <many-to-one name="parent"/>
</class>

Now I"d like to get a list of all Parents and the amount of the Parents children. Suppose I have one Parent with two children and one Parent with no children at all. I d expect an output like

+-------------------+
| parent | children |
+--------+----------+
|  1     | 2        |
|  2     | 0        |
+--------+----------+

using plain SQL it isn t a problem at all, I ll get this output doing something like

SELECT p.id as parent, count(c.id) as children from parents p LEFT OUTER JOIN children c on c.parent_id = p.id group by p.id;

However it doesn t seem to be possible using HQL as one needs a path from Parent to Child when using a OUTER JOIN, which I obviously don t have (and also don t want to add).

Any ideas how to get the query working using HQL or is it really - I couldn t believe it - a missing hibernate feature?

Answer 1

Switching the query should help

SELECT p.id as parent, count(c.id) as children from children c right outer join c.parent p group by p.id;

友情链接