CODEWITHSUNDEEP
selectjpqljava-ee-7
perotom
perotom

Reputation: 923

JPQL SELECT ElementCollection

I have an entity "Post" with this property:

@ElementCollection
@CollectionTable(name ="tags")
private List<String> tags = new ArrayList<>();

Then i have a native select query with a group by. The problem is now how can i select the property tags?

My select query:

Query query = em.createQuery("SELECT p.id,MAX(p.createdAt),MAX(p.value) FROM Post p JOIN p.tags t WHERE t IN (?1,?2,?3) GROUP BY p.id ORDER BY COUNT(p) DESC");
    /* ...

    query.setFirstResult(startIndex);
    query.setMaxResults(maxResults);
    List<Object[]> results = query.getResultList();

    List<Post> posts = new ArrayList<>();
    for (Object[] result : results) {
        Post newPost = new Post();
        newPost.setId(((Number) result[0]).longValue());
        newPost.setCreatedAt((Date) result[1]);
        newPost.setValue((String) result[2]);
        posts.add(newPost);
    }
    return posts;

how to select the property tags?

Upvotes: 1

Views: 1788

Answers (1)

NoDataFound
NoDataFound

Reputation: 11979

Don't know if it will help, but in JPA2.1 Spec, part 4.4.6 Collection Member Declarations, you can do that: 

SELECT DISTINCT o
FROM Order o, IN(o.lineItems) l
WHERE l.product.productType = ‘office_supplies’

So I guess with your case, you could try: 

SELECT p.id,MAX(p.createdAt),MAX(p.value), t
FROM Post p, IN(p.tags) t 
WHERE t IN (?1,?2,?3) 
GROUP BY p.id, t
ORDER BY COUNT(p) DESC

Note: I added t to the GROUP BY, since it would not work with the query without using an aggregate function.

Upvotes: 2

Related Questions