• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA join fetch query with many to many

 
Panos Ath
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have an object Favourite. This object has a many to many connection with Colors. Lets say that some favourites have many colors, 2-3 etc. When I search for a favourite like:

.

The resulting object contains all the colors related to this favourite. My problem is when I want to search for a favourite that has a certain color. For example:



Then the resulting object contains only the red color. I want to get as a result the fav objects that contain the "red" color, but also show all the related colors. Any suggestions? Thanks in advance.
 
Bill Gorder
Bartender
Posts: 1682
7
Android IntelliJ IDE Linux Mac OS X Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
but also show all the related colors.


Sorry I am not following you. You might need to give more details of what you mean by all related colors. Your query clearly states WHERE cl.name = "red". Why would you expect to get others?
 
Panos Ath
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bill Gorder wrote:
but also show all the related colors.


Sorry I am not following you. You might need to give more details of what you mean by all related colors. Your query clearly states WHERE cl.name = "red". Why would you expect to get others?


I want to search for ALL the favourite objects that CONTAIN the color red. Now I have it the way I mentioned and it brings only color"red".

When a favourite object with id = 5 has colors = [red, green, yellow] and I search for favourite item with color "red", now I get the object with id =5 and colors = [red]. I want to get the full list of colors, object id = 5, colors = [red, green, yellow].
 
Bill Gorder
Bartender
Posts: 1682
7
Android IntelliJ IDE Linux Mac OS X Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well I assume you have something similar to this in your Favorite class



Now if you are using hibernate (at least hibernate 3.x not sure about 4) there is a bug with ELEMENT OF so you will need something like



Otherwise if you are using a different persistence provider you can try this (which should work in hibernate as well but does not)

 
Panos Ath
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bill Gorder wrote:Well I assume you have something similar to this in your Favorite class



Now if you are using hibernate (at least hibernate 3.x not sure about 4) there is a bug with ELEMENT OF so you will need something like



Otherwise if you are using a different persistence provider you can try this (which should work in hibernate as well but does not)



I am using JPA over Hibernate 3.3.1 . Which should I use?
 
Bill Gorder
Bartender
Posts: 1682
7
Android IntelliJ IDE Linux Mac OS X Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The first one. The member of one is a known bug in hibernate 3.x
 
Panos Ath
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bill Gorder wrote:The first one. The member of one is a known bug in hibernate 3.x


It almost worked!! The problem is that fav.colors in not a List of Strings, but a list of Objects "Color" which cantains Id, and Name. I want to search by the name of the color. If I do it like



It tries to equal the string "Red" with the Object Color and it throws exception.
 
Bill Gorder
Bartender
Posts: 1682
7
Android IntelliJ IDE Linux Mac OS X Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not use an Enum to represent Color as I showed above. All it has is a name. If you wanted a print friendly name you could add a label to the enum.

Anyway if you want color to be its own Entity then you will need to compare entity to entity.




In this case color is a parameter of type Color.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic