80

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)
Andrea Ligios
  • 49,480
  • 26
  • 114
  • 243
xrcwrn
  • 5,339
  • 17
  • 68
  • 129

2 Answers2

162

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
kostja
  • 60,521
  • 48
  • 179
  • 224
  • 1
    It doesn't work in my testing, if billProductSet is got be @JoinTable, and the relation is ManyToMany. – Stony Aug 28 '14 at 02:42
  • 3
    @Stony It **does** work with `@JoinTable` and `@ManyToMany`. I've that running right now. – Andrea Ligios Jul 24 '15 at 12:16
  • 2
    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! – Cavaleiro Jun 27 '20 at 09:53
1

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.

J.K
  • 11
  • 2