Eclipse Community Forums: EMF » [Hibernate] HQL statement for one-to-many

Home » Modeling » EMF » [Hibernate] HQL statement for one-to-many

[Hibernate] HQL statement for one-to-many [message #681821]

Thu, 09 June 2011 18:42

Eclipse User

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

Re: [Hibernate] HQL statement for one-to-many [message #683500 is a reply to message #681821]

Mon, 13 June 2011 17:42

Eclipse User

Hi Folks,

I'm still struggling with this one. Any kind souls out there with a suggestion or two? It would be most appreciated.

Cheers,
mike

Re: [Hibernate] HQL statement for one-to-many [message #683501 is a reply to message #683500]

Mon, 13 June 2011 17:45

Eclipse User

Hi,

i have no idea why you are asking this in the Eclipse Modeling Framework forum.

~Christian

Re: [Hibernate] HQL statement for one-to-many [message #685080 is a reply to message #683501]

Thu, 16 June 2011 15:16

Eclipse User

Here the answer I gave earlier but which disappeared in the void (there are apparently problems with the newsgroup/forum to sync up):

I think you should make this a join to the MobileProvider entity (assuming that's the name), and use the special elements function, something like this:
select count(asset.ID) from Asset as asset, MobileProvider as mp where mp in elements(asset.StateInfo) and mp.Name='Vodafone'

Hibernate/HQL has some nice collection functions which you can use in the where clause.

gr. Martin

[Updated on: Thu, 16 June 2011 15:16] by Moderator

Re: [Hibernate] HQL statement for one-to-many [message #685183 is a reply to message #685080]

Thu, 16 June 2011 21:35

Eclipse User

Hi Martin,

Thanks again for the info. One question, how do I limit the data being counted (selected) to just the last entry of StateInfo.MobileProvider? In other words... there are multiple StateInfo rows for each Asset. The last/most recent StateInfo is the only one I want to count/select.

Thanks again.

mike

Re: [Hibernate] HQL statement for one-to-many [message #685236 is a reply to message #685183]

Fri, 17 June 2011 01:01

Eclipse User

I have not tried it but see this part of the HQL chapter in the Hibernate docs:

HQL functions that take collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex(), along with the special elements() and indices functions that can be quantified using some, all, exists, any, in.

you can try maxelement I guess in the whereclause:
select count(asset.ID) from Asset as asset, MobileProvider as mp where mp = maxelement(asset.StateInfo) and mp.Name='Vodafone'

Can you let me know if this works (cause then I know it to)?

gr. Martin

Re: [Hibernate] HQL statement for one-to-many [message #687176 is a reply to message #681821]

Fri, 10 June 2011 16:44

Eclipse User

I think you should make this a join to the MobileProvider entity (assuming that's the name), and use the special
elements function, something like this:
select count(asset.ID) from Asset as asset, MobileProvider as mp where mp in elements(asset.StateInfo) and
mp.Name='Vodafone'

Hibernate/HQL has some nice collection functions which you can use in the where clause.

gr. Martin

On 06/10/2011 12:42 AM, forums-noreply@eclipse.org wrote:
> 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
>

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org

Re: [Hibernate] HQL statement for one-to-many [message #687193 is a reply to message #683500]

Tue, 14 June 2011 00:12

Eclipse User

Hi Mike,
I answered your post on the 10th of June, or do you mean that my reply did not help?

gr. Martin

On 06/13/2011 11:42 PM, No real name wrote:
> Hi Folks,
>
> I'm still struggling with this one. Any kind souls out there with a suggestion or two? It would be most appreciated.
>
> Cheers,
> mike
>

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org

Re: [Hibernate] HQL statement for one-to-many [message #687453 is a reply to message #683500]

Tue, 14 June 2011 00:12

Eclipse User

Re: [Hibernate] HQL statement for one-to-many [message #688553 is a reply to message #687453]

Fri, 24 June 2011 18:42

Eclipse User

Hi Martin,

Just getting caught up from other business and returning to this. I didn't see your Jun 10th post until a few days ago. Very strange.

Anyway, the

select count(asset.ID) from Asset as asset, MobileProvider as mp where mp in elements(asset.StateInfo) and
mp.Name='Vodafone'

query seems to return the count of MobileProvider entrys matching Name='Vodafone'. 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

The documentation for "elements" appears to be a bit brief from what I found on jboss.org.

Any suggestions would be greatly appreciated.

mike

Re: [Hibernate] HQL statement for one-to-many [message #688559 is a reply to message #688553]

Fri, 24 June 2011 18:56

Eclipse User

Just figured it out. This works for me

select count(ID) from Asset as asset where asset.StateInfo[ maxindex(asset.StateInfo) ].MobileProvider.Name='%s'

Much more simple too!

mike

Previous Topic:	(no subject)
Next Topic:	(no subject)

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 15 16:12:30 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter