Hibernate HQL Subquery

Question

I have this MySql Query, this query return a tag name with respective metrics, the query works great:

SELECT tag,m1.metric,m2.metric,m3.metric FROM yp_tag tag
LEFT JOIN (
SELECT yt.id,count(*) as metric FROM yp_user_tag yut
INNER JOIN yp_tag yt ON yut.tag_id=yt.id
GROUP BY tag_id) AS m1 ON tag.id=m1.id
LEFT JOIN (
SELECT yt.id,count(*) as metric FROM  yp_promotion_tag ypt
INNER JOIN yp_tag yt ON ypt.tag_id=yt.id
GROUP BY tag_id) AS m2 ON tag.id=m2.id
LEFT JOIN (
SELECT yt.id,count(*) as metric FROM yp_user_promo yup
INNER JOIN yp_promotion_tag ypt ON yup.promotion_id=ypt.promotion_id
INNER JOIN yp_tag yt ON ypt.tag_id=yt.id
WHERE action='ACTION_VIEW'
GROUP BY tag_id) as m3 ON tag.id=m3.id

Return of the query:

tag, metric, metric, metric
'Almuerzo', '1', '1', NULL
'Banamex', NULL, '2', NULL
'Bar', '1', '2', NULL
'Bebida', '1', '13', '1'
'Boutique', NULL, '1', NULL
'Caf', NULL, '2', NULL
'Cena', '1', '7', NULL
'Comida', '1', '5', NULL
'Ejercicio', '1', '2', NULL
'Enchilada', '1', NULL, NULL
'Entrenamiento', '1', '15', NULL
'Halcón', '1', NULL, NULL
'Helado', '1', NULL, NULL
'KFC', NULL, NULL, NULL
'Latte', NULL, '2', NULL
'Mañana', '1', '4', NULL
'Nutrisa', '1', '6', '1'
'Pizza', '2', '33', '10'
'Ropa', '1', '4', NULL
'Salud', '1', '3', NULL
'Santa Fe', NULL, NULL, NULL
'Tecnologia', NULL, '5', NULL
'Valores', '1', '2', NULL

I want to do the same with Hibernate, but i cant figure out how can i do this because hibernate doesnt allow subquery in FROM, any ideas how i can do this without using native SQL of course.

Thanks

Ignacio · Accepted Answer · 2015-05-21T19:54:17.633

I fixing doing this SQL:

SELECT tag, (SELECT count(yt.id) FROM yp_user_tag yut
            INNER JOIN yp_tag yt ON yut.tag_id=yt.id
            WHERE tag.id=yt.id
            GROUP BY tag_id) as m,
            (SELECT count(*) as metric FROM  yp_promotion_tag ypt
            INNER JOIN yp_tag yt ON ypt.tag_id=yt.id
            WHERE tag.id=yt.id
            GROUP BY tag_id) AS m2,
            (SELECT count(*) as metric FROM yp_user_promo yup
            INNER JOIN yp_promotion_tag ypt ON yup.promotion_id=ypt.promotion_id
            INNER JOIN yp_tag yt ON ypt.tag_id=yt.id
            WHERE action='ACTION_VIEW'
            AND tag.id=yt.id
            GROUP BY tag_id) as m3
            FROM yp_tag tag

And the HQL is like this:

Query q = sessionFactory.getCurrentSession().createQuery(
        "SELECT tag.tag, "
                + " (SELECT count(*) as interestedCount FROM YpUserTag yut"
                + " INNER JOIN yut.ypTag yt"
                + " WHERE tag.id=yt.id"
                + " GROUP BY yt.id) as interestedCount, "
                + " (SELECT count(*) as promotionCount FROM YpPromotionTag ypt"
                + " INNER JOIN ypt.ypTag yt"
                + " WHERE tag.id=yt.id"
                + " GROUP BY yt.id) as promotionCount, "
                + " (SELECT count(*) as viewsCount FROM YpPromotionTag ypt"
                + " INNER JOIN ypt.ypTag yt"
                + " INNER JOIN ypt.ypPromotion.ypUserPromos yup"
                + " WHERE tag.id=yt.id"
                + " AND action='ACTION_VIEW'"
                + " GROUP BY yt.id) as viewsCount"                      
        + " FROM YpTag tag"
        + " ORDER BY tag.tag"
        );

Hibernate HQL Subquery

1 Answers1

Linked