# Subquery on element collection

**URL:** <https://discourse.hibernate.org/t/subquery-on-element-collection/4763>\
**Category:** Hibernate ORM\
**Created:** [November 11, 2020, 6:24pm UTC](https://discourse.hibernate.org/t/subquery-on-element-collection/4763 "2020-11-11T18:24:54Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![zeigler](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@zeigler](https://discourse.hibernate.org/u/zeigler)\
**Post date:** [November 11, 2020, 6:24pm UTC](https://discourse.hibernate.org/t/subquery-on-element-collection/4763/1 "2020-11-11T18:24:54Z")

</div>

I am trying to build a query on an entity which contains an elementcollection. I have reduced my query down to only the relevant parts to make it simple. I would like to find all documents that have an entry in the elementcollection that is “like” a search term. Below is my code:

```
CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
CriteriaQuery<Document> criteriaQuery = criteriaBuilder.createQuery(Document.class);
Root<Document> root = criteriaQuery.from(Document.class);
criteriaQuery.select(root);
Predicate constraints = cb.conjunction();
constraints.getExpressions().add(cb.equal(root.get("deleted"), false));

Subquery sub = cq.subquery(Document.class);
Root subRoot = sub.from(Document.class);
sub.select(subRoot.get("id"));
sub.where(cb.like(subRoot.get(Document.DOCTAGS_PROPERTY), "%mysearchtoken%"));

constraints.getExpressions().add(root.get("id").in(sub));
Query<HesDocument> query = session.createQuery(criteriaQuery);
    List<Document> documents = query.getResultList();

```

It translates to the following query:

```
select documen0_.id as col_0_0_ 
from document documen0_ 
where documen0_.deleted=? and (documen0_.id in (
	    select documen1_.id 
    from document documen1_ cross join document_tag doctags2_ 
    where documen1_.id=doctags2_.doc_id and (. like ?)
))

```

WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - logExceptions: SQL Error: 0, SQLState: null  
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - logExceptions: The value is not set for the parameter number 2.

I do not understand how I am not setting a search parameter. Can anyone tell me what is wrong with my code?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 12, 2020, 9:03am UTC](https://discourse.hibernate.org/t/subquery-on-element-collection/4763/2 "2020-11-12T09:03:20Z")

</div>

I would recommend you to use an exists predicate instead like this:

```auto
CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
CriteriaQuery<Document> criteriaQuery = criteriaBuilder.createQuery(Document.class);
Root<Document> root = criteriaQuery.from(Document.class);
criteriaQuery.select(root);
Predicate constraints = cb.conjunction();
constraints.getExpressions().add(cb.equal(root.get("deleted"), false));

Subquery sub = cq.subquery(Document.class);
Root subRoot = sub.correlate(Document.class);
sub.select(cb.literal(1));
sub.where(cb.like(subRoot.get(Document.DOCTAGS_PROPERTY), "%mysearchtoken%"));

constraints.getExpressions().add(cb.exists(sub));
Query<HesDocument> query = session.createQuery(criteriaQuery);
    List<Document> documents = query.getResultList();

```

---

<div class="post-metadata">

**Author:** ![zeigler](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@zeigler](https://discourse.hibernate.org/u/zeigler)\
**Post date:** [November 13, 2020, 3:40pm UTC](https://discourse.hibernate.org/t/subquery-on-element-collection/4763/3 "2020-11-13T15:40:16Z")

</div>

Thanks for the suggestion. I gave it a try but it didn’t solve my problem. The error is coming from the line above the EXISTS:

sub.where(cb.like(subRoot.get(Document.DOCTAGS\_PROPERTY), “%mysearchtoken%”));

If I remove this line my query runs without error. I do not understand how any parameter is not being bound. And is it the DOCTAGS\_PROPERTY that is not being populated or the parameter for the like?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 16, 2020, 4:03pm UTC](https://discourse.hibernate.org/t/subquery-on-element-collection/4763/4 "2020-11-16T16:03:20Z")

</div>

Which Hibernate version are you using?

There is a configuration property `hibernate.criteria.literal_handling_mode` in Hibernate that allows to configure if a JPA Criteria literals should be rendered as parameters or as literals. This might be the source of the problem. Try using the `INLINE` configuration to see if that fixes your issue. Also see the documentation for more information: [https://docs.jboss.org/hibernate/stable/orm/userguide/html\_single/Hibernate\_User\_Guide.html#configurations-query](https://docs.jboss.org/hibernate/stable/orm/userguide/html_single/Hibernate_User_Guide.html#configurations-query)