hql join @CollectionTable

Question

I have a domain Service with collection tags as below :

@Entity
public class Service extends AbstractEntity<Long> {
            private static final long serialVersionUID = 9116959642944725990L;

        @ElementCollection(fetch = FetchType.EAGER, targetClass = java.lang.String.class)
        @CollectionTable(name = "service_tags", joinColumns = @JoinColumn(name = "s_id"))
        @Column(name = "tag")
        private Set<String> tags;
    }

I want to select Services with particular KEY of Service.tags.

hql joining Service to Service.tags is as below :

select s from Service s INNER JOIN s.tags t where s.status=0 and (s.serviceType=9 or t.tag in ('College'))

But, above hql returns me with following exception :

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: cannot dereference scalar collection element: tag [select s from com.zazzercode.domain.Service s INNER JOIN s.tags t where s.status=0 and (s.serviceType=9 or t.tag in ('College')) ]

select s from Service s INNER JOIN s.tags t where s.status=0 works though.

Looking at JPQL querying a collection of non-entites, I tried like below

"select s from Service s where s.status=0 and s.priviligedUser.priviligedUserType IN (2,4) and (s.serviceType=9 or (KEY(s.tags)='tag' and (VALUE(s.tags)='College'))"

Got following exception :

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: null near line 1, column 188 [select s from com.esewa.server.entity.Service s where s.status=0 and (s.serviceType=9 or (KEY(s.tags)='tag' and (VALUE(s.tags)='College'))]

I have achieved same thing using criteria api some months back.

score 3 · Accepted Answer · edited May 23 '17 at 10:29

3

Thanks to JPQL querying a collection of non-entites

Following code works !!!

"select s from Service s INNER JOIN s.tags t where s.status=0 and and (s.serviceType=9 or  VALUE(s.tags) in ('College')) "

edited May 23 '17 at 10:29

Community

1
1

answered Sep 06 '13 at 12:31

prayagupa

30,204
14
155
192

This accepted answer does not work for me because VALUE is only meant for Maps, but this here is a Collection. – FelixM Aug 08 '14 at 20:23

score 3 · Answer 2 · edited Apr 18 '14 at 11:27

3

"select s from Service s where s.status=0 and (s.serviceType=9 or 'College' in elements(s.tags))"

edited Apr 18 '14 at 11:27

prayagupa

30,204
14
155
192

answered Apr 18 '14 at 10:41

Skeeve

1,205
3
16
31

hql join @CollectionTable

2 Answers2

Following code works !!!

Linked