英文:
JQPL custom query, having trouble returning list of objects in @ManyToMany
问题
以下是翻译好的内容：
我有两个模型和一个 @ManyToMany 表。一个用户表和一个图片表。用户表中有一个特定用户关注的其他用户列表。这些用户与每张图片都有 @OneToMany 关联。
用户模型：
@OneToMany(mappedBy="user", cascade=CascadeType.ALL, fetch=FetchType.LAZY)
private List<Picture> pics;
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(
name="follows",
joinColumns = @JoinColumn(name="user_id"),
inverseJoinColumns = @JoinColumn(name = "following_id")
)
private List<User> following;
图片模型：
private String image_url;
private String description;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="user_id")
private User user;
获取与所有用户关联的所有图片的 SQL 查询语句如下：
SELECT * FROM `pictures` picture
WHERE `user_id` = '1' OR `user_id` IN (
SELECT `following_id` FROM `follows` WHERE `user_id`= 1
)
但是 follows 表不是一个独立的实体，我正在努力想象如何将这个 SQL 转换为自定义查询的 HQL。我仍然无法弄清楚，我得到的最接近的是：
@Query(value="SELECT p FROM Picture p WHERE p.user = :user OR p.user IN(SELECT u FROM User u JOIN u.following f WHERE f = :user)")
List<Picture> findByPictureFollower(@Param("user") User user);
我不确定我是否走对了方向。我想检索与用户模型中的 Following 列表数组中的每个用户对象关联的每个图片对象。欢迎提出您的想法。
英文:
I have 2 Models, and @ManyToMany table. A User table, and a Picture table. The user table has a list of other users that specific one follows. The users have a @OneToMany association with each Picture.
User
@OneToMany(mappedBy="user", cascade=CascadeType.ALL, fetch=FetchType.LAZY)
private List<Picture> pics;
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(
name="follows",
joinColumns = @JoinColumn(name="user_id"),
inverseJoinColumns = @JoinColumn(name = "following_id")
)
private List<User> following;
Picture
private String image_url;
private String description;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="user_id")
private User user;
The SQL Query to get all pictures associated with a all users (in User.following) is:
SELECT * FROM `pictures` picture
WHERE `user_id` = '1' OR `user_id` IN (
SELECT `following_id` FROM `follows` WHERE `user_id`= 1
)
But the follows table is not it's own Entity, I'm racking my brain on how to translate that SQL into HQL for the custom query. I'm still not able to figure it out and the closest I've gotten
@Query(value="SELECT p FROM Picture p WHERE p.user = :user OR p.user IN(SELECT p.followers FROM p.followers WHERE user = :user)")
List<Picture> findByPictureFollower(@Param("user") User user);
I'm not sure if I'm going about this the right way. I want to retrieve every picture object that's associated with each user object in the Following List Array on the User model. Any thoughts would be appreciated.
答案1
得分: 1
以下对您是否有效？
@Query(value="SELECT p FROM Picture p WHERE p.user = :user OR p.user IN(SELECT f FROM User u JOIN p.followers f WHERE u = :user)")
List<Picture> findByPictureFollower(@Param("user") User user);
英文:
Does the following work for you?
@Query(value="SELECT p FROM Picture p WHERE p.user = :user OR p.user IN(SELECT f FROM User u JOIN p.followers f WHERE u = :user)")
List<Picture> findByPictureFollower(@Param("user") User user);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论