from子句中的JPA子查询 [英] JPA subquery in from clause

问题描述

我们正在开发一个使用EJB连接到数据库的Web应用程序.

We're developing a web application which uses EJB to connect to a database.

在我们的数据库模型中，我们有一个移动设备表，另一个有功能表，最后一个有手机模型映射功能值. 型号(id_model，...) 功能(id_feature等) model_features(id_model，id_feature，值)

In our DB model, we have a mobile devices table, another one with features, and the last one that maps the values of the features with the phone models. models (id_model,...) features (id_feature, ...) model_features (id_model, id_feature, value)

我们想执行一个查询，以按匹配特征的数量对模型进行排序.也就是说，我们传递要匹配的功能列表(即从1到9)， 并且我们希望所有包含"yes"的设备至少是其中一项功能的值，并按照前面所述对它们进行排序.

We want to perform a query that gets the models ordered by the number of matching features. It's to say, we pass a list of features to match (i.e. from 1 to 9), and we want all the devices containing 'yes' as the value of at least one of those features, and order them as previously said.

我们创建了一个执行该任务的SQL查询，该查询有效:

We created an SQL query that makes that job, and it works:

SELECT CONCAT(subquery.numberf*100/9,"%") AS "Features", subquery.idModel AS "ID model"
FROM (select count(*) AS numberf, id_model AS idModel
        FROM model_features
        WHERE value LIKE '%Yes%' AND id_feature IN(1,2,3,4,5,6,7,8,9)
        GROUP BY id_model) subquery
WHERE subquery.numberf > 0
ORDER BY subquery.numberf DESC

由于我们使用的是JPA，因此必须构建JPQL查询，因此无法在FROM子句中包含子查询，并且我们想知道是否 我们可以将子查询传递"到WHERE子句是可能的，并且不会以不良的方式影响性能.我们该怎么办?

Since we're using JPA and therefore we've got to build JPQL querys, it's not possible to include subquerys in the FROM clause, and we're wondering whether we can "pass" the subquery to the WHERE clause is possible and does not affect the performance in a bad way. How can we do?

推荐答案

最后，我们使用本机查询解决了该问题:

Finally we solved using a native query:

 String query = "SELECT ROUND(subquery.numberf*100/" + features + ",0) AS \"matches\", m.id_model AS \"id_model\",  m.name AS \"name\", m.brand AS \"brand\", m.url_pict AS \"url_picture\""
                + " FROM (select count(*) AS numberf, id_model AS idModel FROM model_features WHERE value LIKE '%Yes%' "
                + "AND id_feature IN(" + idFeatures + ") GROUP BY id_model) subquery, Models m WHERE subquery.numberf > 0 "
                + "AND subquery.idModel = m.id_model ORDER BY subquery.numberf DESC, m.name";
return em.createNativeQuery(query).getResultList();

这篇关于from子句中的JPA子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！