Got a newbie HQL/SQL question for you all.

I have the following HQL statement

select count(ID) from Asset as asset where asset.StateInfo.MobileProvider.Name='Vodafone'

The Asset->StateInfo reference is one-to-many and the rest are one-to-one. This query generates a QueryException:

org.hibernate.QueryException: illegal attempt to dereference collection [asset0_.`id`.StateInfo] with element property reference [MobileProvider] [select count(ID) from Asset as asset where asset.StateInfo.MobileProvider.Name='Vodafone']

I'm sure this is because I'm doing the asset.StateInfo reference in the query all wrong. Would some kind soul please set me on the right path?

Cheers,
mike

You'll need to use a join:

Thanks very much! Unfortunately this seems to count all instances of MobileProvider. I'm trying to count only the last instance of StateInfo.MobileProvider.

Here is the exact query I tried:



As you may recall I'm trying to count the number of instances where the last member/entry of Asset.StateInfo.MobileProvider.Name equals a given string. Here's a breakdown of the references

Asset--[one-to-many]-->StateInfo--[one-to-one]-->MobileProvider

Somebody else suggested using "elements" as such



Unfortunately this appears to return the count of MobileProvider rows instead of the number of asset's that reference MobileProvider in the last member of StateInfo.

Any other suggestions would be greatly appreciated!

Just figured it out. This works for me



Much more simple too!

mike