JPA subquery in from clause

Question

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

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)

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.

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

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?

Can you just use a native query? It seems like the end result is more of an aggregate rather than an entity. — wrschneider, Jan 10 '12 at 20:34
We have thinked on that posibility, but if it would be possible we will do with jpa. thanks @wrschneider99 — frayab, Jan 10 '12 at 22:11

frayab · Accepted Answer · 2012-01-11T17:39:17.043

2

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();

edited Jan 11 '12 at 17:39

answered Jan 11 '12 at 16:50

frayab

2,512
20
25

2

This class shouldn't be an entity: it's not persisted by JPA. It's just a DTO. – JB Nizet Jan 11 '12 at 16:56
em.createNativeQuery(query, ModelMatches.class).getResultList(); returns a list of elements of the entity "ModelMatches" you can do it if it isn't an entity – frayab Jan 11 '12 at 17:09
1

Just because this method needs an entity doesn't mean that your class should be an entity. You don't want JPA to persist any change you might do to this object into a table, do you? You may execute a SQL query and create DTO instances yourself from the result of the query (Object[] instances). – JB Nizet Jan 11 '12 at 17:18
I know, your answer is true, but we want to use entity manager and it was a solution for getting the desired result. – frayab Jan 11 '12 at 17:21
2

Why don't you just use the same method without the class argument, and create the list of DTOs from the list of Object[] returned yourself. That's the proper solution. – JB Nizet Jan 11 '12 at 17:27
I have done that, like this: http://stackoverflow.com/questions/3599450/fields-value-of-native-query-in-jpa Thanks @JBNizet i have edited my answer – frayab Jan 11 '12 at 17:40

JPA subquery in from clause

1 Answers1

Linked