• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Jpql where clause with @OneToMany

 
Ranch Hand
Posts: 262
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi guys,
I have a problem with a filter for a one to many Relationship.

I have 2 tables PROPERTY_GROUP(ID, KEY) and PROPERTY(ID, GROUP_KEY, NAME, VALUE)
2 entities PropertyGroup and Property





When i filter 1 name and 1 value all OK:

So i can find all property group with property position >= 10

In the table PROPERTY i have two entry with name position and priority.
I want to retreive the group with both properties position >= 10 and priority <= 10

Can some one help me ?
Thanks!
 
Bartender
Posts: 2421
13
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How about this?


select pg from PropertyGroup pg inner join pg.properties pr where (pr.name = 'position' and pr.value >= 10 ) or (pr.name='priority' and pr.value<=10)



 
Collins Mbianda
Ranch Hand
Posts: 262
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Himai Minh wrote:How about this?


select pg from PropertyGroup pg inner join pg.properties pr where (pr.name = 'position' and pr.value >= 10 ) or (pr.name='priority' and pr.value<=10)





Hello Himai . Thanks for your reply.
I try your it but it not returning the result i want. If i insert in the db two properties position=10 and priority=11.
In the result i the PropertyGroup. I think because of the or.

When i try something like



I get the correct result.
But i don't like it because i i have to filter on more property, i have to add inner join pg.properties primany times.
 
Himai Minh
Bartender
Posts: 2421
13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, Collins,


select pg from PropertyGroup pg inner join pg.properties pr inner join pg.properties pr2 where pr.name = 'position' and pr.value >= 10 pr2.name = 'priority' and pr2.value >= 10



should it be

select pg from PropertyGroup pg inner join pg.properties pr inner join pg.properties pr2 where pr.name = 'position' and pr.value >= 10 and pr2.name = 'priority' and pr2.value >= 10



?
 
Collins Mbianda
Ranch Hand
Posts: 262
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Himai Minh wrote:Hi, Collins,

should it be

select pg from PropertyGroup pg inner join pg.properties pr inner join pg.properties pr2 where pr.name = 'position' and pr.value >= 10 and pr2.name = 'priority' and pr2.value >= 10



?



Hi Himai !
Yes that the correct query. Thanks !
 
reply
    Bookmark Topic Watch Topic
  • New Topic