JPA join fetch query with many to many

[Panos Ath]

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:

SELECT fav FROM Favourite fav join fetch fav.colors as cl WHERE fav.name = "blabla"

.

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:

SELECT fav FROM Favourite fav join fetch fav.colors as cl WHERE cl.name = "red" 

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]

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]

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]

Well I assume you have something similar to this in your Favorite class

	@ElementCollection
	@Column(name = "color")
	@Enumerated(EnumType.STRING)
	private Collection<Color> colors = new HashSet<Color>();

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

SELECT DISTINCT fav FROM Favorite fav join fetch fav.colors WHERE 'RED' in elements(fav.colors)

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

SELECT DISTINCT fav FROM Favorite fav join fetch fav.colors WHERE 'RED' MEMBER OF fav.colors

[Panos Ath]

Bill Gorder wrote:Well I assume you have something similar to this in your Favorite class

	@ElementCollection
	@Column(name = "color")
	@Enumerated(EnumType.STRING)
	private Collection<Color> colors = new HashSet<Color>();

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

SELECT DISTINCT fav FROM Favorite fav join fetch fav.colors WHERE 'RED' in elements(fav.colors)

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

SELECT DISTINCT fav FROM Favorite fav join fetch fav.colors WHERE 'RED' MEMBER OF fav.colors

I am using JPA over Hibernate 3.3.1 . Which should I use?

[Bill Gorder]

The first one. The member of one is a known bug in hibernate 3.x

[Panos Ath]

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

    SELECT DISTINCT fav FROM Favorite fav join fetch fav.colors WHERE 'RED' in elements(fav.colors)  

It tries to equal the string "Red" with the Object Color and it throws exception.

[Bill Gorder]

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.

SELECT DISTINCT fav FROM Favorite fav join fetch fav.colors WHERE :color in elements(fav.colors)

In this case color is a parameter of type Color.