JPA与许多人一起加入获取查询 [英] JPA join fetch query with many to many

问题描述

我有一个对象喜欢。该对象与Colors有多对多的连接。可以说，一些收藏夹有很多颜色，2-3等。当我搜索喜欢的喜欢：

  SELECT fav FROM Favorite fav加入fetch fav.colors作为cl WHERE fav.name =blabla。 
  

结果对象包含与此收藏夹相关的所有颜色。我的问题是，当我想要搜索具有特定颜色的最爱时。例如：

  SELECT fav FROM Favorite fav连接读取fav.colors as cl WHERE cl.name =red
  

然后生成的对象只包含红色。我想得到包含红色颜色的fav对象，并显示所有相关的颜色。有什么建议么？最后，它使用了一个内部查询和EXISTS运算符。 （颜色是int id和字符串名称的对象）。

  SELECT fav FROM Favorite fav加入读取fav.colors as cl WHERE EXISTS（SELECT fav2 FROM Favorite fav2 join fetch fav2.colors as cl2 WHERE fav2.id = fav.id AND cl2.name =red）
  

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.

解决方案

Well finally it worked with an inner query and "EXISTS" operator. (color is an object of , int id and string name).

SELECT fav FROM Favourite fav join fetch fav.colors as cl WHERE EXISTS ( SELECT fav2 FROM Favourite fav2 join fetch fav2.colors as cl2 WHERE fav2.id = fav.id AND cl2.name = "red" )

这篇关于JPA与许多人一起加入获取查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！