org.hibernate.QueryException: illegal attempt to dereference collection

Question

I am trying following hql query to execute

SELECT count(*)    FROM BillDetails as bd  WHERE bd.billProductSet.product.id = 1002    AND bd.client.id                 = 1 

But it is showing

org.hibernate.QueryException: illegal attempt to dereference collection  [billdetail0_.bill_no.billProductSet] with element property reference [product]  [select count(*) from iland.hbm.BillDetails as bd where bd.billProductSet.product.id=1001 and bd.client.id=1]     at org.hibernate.hql.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:68)     at org.hibernate.hql.ast.tree.DotNode.checkLhsIsNotCollection(DotNode.java:558) 

Answer 1

billProductSet is a Collection. As such, it does not have an attribute named product.

Product is an attribute of the elements of this Collection.

You can fix the issue by joining the collection instead of dereferencing it:

SELECT count(*)    FROM BillDetails        bd    JOIN bd.billProductSet  bps   WHERE bd.client.id       = 1    AND bps.product.id     = 1002