Search code examples
javahibernatehql

Creating a HQL query which would take each item in a collection and compare it

I have the following HQL query 

SELECT ob FROM objectBase ob WHERE size(ob.listSC) > 0 AND ob.listSC.registrationDate BETWEEN :startDate AND :endDate

What it is supposed to do:

  1. Take all objectBase objects in a database where listSC (a collection of entities) is not null and empty (unfortunately cannot figure out how to make it ingore null values too)
  2. For each item inside listSC check that a particular entity has a property of registrationDate between given dates.

But unfortunately it gives me 

Caused by: org.hibernate.QueryException: illegal attempt to dereference collection [objectbase0_.id.listSC] with element property reference [registrationDate]
at org.hibernate.hql.internal.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:73)

Which is for a good reason I believe. The question is how to implement such behaviour anyway? Preferably, without using JOINs.

Thanks in advance.

Solution

  • Please try below query if you need any help let me know.

    SELECT ob FROM objectBase ob left join ob.listSC sc WHERE size(ob.listSC) > 0 AND sc.registrationDate BETWEEN :startDate AND :endDate