Hibernate HQL子查询 [英] Hibernate HQL Subquery

问题描述

我有这个MySql查询，这个查询返回一个带有相应指标的标签名称，查询效果很好：

  SELECT标签，m1.metric，m2.metric，m3.metric FROM yp_tag tag 
 LEFT JOIN（
 SELECT yt.id，count（*）作为度量值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（*）作为度量标准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 （*）作为度量标准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 
  

返回query：

 标记，度量标准，度量标准，
'Almuer'，'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 
  

我想用Hibernate做同样的事情，但我无法弄清楚我该怎么做，因为休眠母鹿snt允许在FROM中使用子查询，任何想法我都可以在不使用本地SQL的情况下做到这一点。

谢谢

解决方案

我修正了这个SQL：

  SELECT标记，（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 
  

而HQL是这样的：

$ p $ 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）interestedCount，
+（SELECT count（*）as promotionCount FROM YpPromotionTag ypt
+ INNER JOIN ypt.ypTag yt
+WHERE tag.id = yt.id
+GROUP BY yt.id）作为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
）;


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

解决方案

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子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！