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)

Solution 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

Solution 2

because billProduct is one to many mapping and there is many billProduct entity from one BillDetails entity you can't dereference it in query.you must join BillDetails model to billProduct and filter result with where cluase.

Comments

[Stony]

It doesn't work in my testing, if billProductSet is got be @JoinTable, and the relation is ManyToMany.

[Andrea Ligios]

@Stony It does work with @JoinTable and @ManyToMany. I've that running right now.

[Cavaleiro]

Just for reference: in my case I was already doing the join of the collection but without giving it an alias it won't work. Thanks!