Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Modeling » EMF » [Hibernate] HQL statement for one-to-many
[Hibernate] HQL statement for one-to-many [message #681821] Thu, 09 June 2011 22:42 Go to next message
Mike Cooper is currently offline Mike CooperFriend
Messages: 59
Registered: February 2011
Member
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

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #683500 is a reply to message #681821] Mon, 13 June 2011 21:42 Go to previous messageGo to next message
Mike Cooper is currently offline Mike CooperFriend
Messages: 59
Registered: February 2011
Member
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

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #683501 is a reply to message #683500] Mon, 13 June 2011 21:45 Go to previous messageGo to next message
Christian Dietrich is currently offline Christian DietrichFriend
Messages: 13354
Registered: July 2009
Senior Member
Hi,

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

~Christian

Need professional support for Xtext, Xpand, EMF?
Go to: https://www.itemis.com/en/xtext/
Twitter : @chrdietrich
Blog : https://www.dietrich-it.de

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #685080 is a reply to message #683501] Thu, 16 June 2011 19:16 Go to previous messageGo to next message
Martin Taal is currently offline Martin TaalFriend
Messages: 5468
Registered: July 2009
Senior Member
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 19:16]

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #685183 is a reply to message #685080] Fri, 17 June 2011 01:35 Go to previous messageGo to next message
Mike Cooper is currently offline Mike CooperFriend
Messages: 59
Registered: February 2011
Member
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

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #685236 is a reply to message #685183] Fri, 17 June 2011 05:01 Go to previous messageGo to next message
Martin Taal is currently offline Martin TaalFriend
Messages: 5468
Registered: July 2009
Senior Member
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

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #687176 is a reply to message #681821] Fri, 10 June 2011 20:44 Go to previous messageGo to next message
Martin Taal is currently offline Martin TaalFriend
Messages: 5468
Registered: July 2009
Senior Member
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

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #687193 is a reply to message #683500] Tue, 14 June 2011 04:12 Go to previous messageGo to next message
Martin Taal is currently offline Martin TaalFriend
Messages: 5468
Registered: July 2009
Senior Member
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

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #687453 is a reply to message #683500] Tue, 14 June 2011 04:12 Go to previous messageGo to next message
Martin Taal is currently offline Martin TaalFriend
Messages: 5468
Registered: July 2009
Senior Member
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

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #688553 is a reply to message #687453] Fri, 24 June 2011 22:42 Go to previous messageGo to next message
Mike Cooper is currently offline Mike CooperFriend
Messages: 59
Registered: February 2011
Member
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

Report message to a moderator

Re: [Hibernate] HQL statement for one-to-many [message #688559 is a reply to message #688553] Fri, 24 June 2011 22:56 Go to previous message
Mike Cooper is currently offline Mike CooperFriend
Messages: 59
Registered: February 2011
Member
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

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Feb 18 17:41:51 GMT 2020

Powered by FUDForum. Page generated in 0.04560 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly