Search code examples
sqlhibernateleft-joincriteria

Multiple association on same entity with hibernate Criteria

I would like to use this query in Hibernate criteria 

SELECT a.a_id, b.b_id, b.b_description, sum(c1.c_score) AS score1, sum(c2.c_score) AS score2
FROM b, a, d, c

LEFT OUTER JOIN c AS c1 ON c1.c_id = c.c_id AND c1.c_comment = 'good'
LEFT OUTER JOIN c AS c2 ON c2.c_id = c.c_id AND c2.c_comment = 'nogood'

WHERE b.b_Id = d.d_id
AND d.d_id = c.c_id
AND c.c_foreignkey_a_id = a.a_id

GROUP BY a.a_id, b.b_id, b.b_description

My problem, in this case, is the 2 associations on the same entity ( C => C1 / C => C2).

And I want to know if there is a way to do this with criteria ?

Thanks ! - EZ

Solution

  • It's a serious limitation when using Hibernate Criteria (Now Deprecated).

    You can try one the two "solutions" :

    • You can map the association twice ( in read only) ... not really clean
    • You can try to express you request with criteria subqueries. Example