我的JPA实体类如下所示:
@Entity
@Table(name = "users")
public class User implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private Long id;
...
// bi-directional many-to-many association to Tag
@ManyToMany
@JoinTable(name = "user_tags_preferences", joinColumns = {
@JoinColumn(name = "user_id") },
inverseJoinColumns = {
@JoinColumn(name = "tag_id") })
private List<Tag> tags;
@Entity
@Table(name = "tags")
public class Tag implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private Long id;
private String name;
...
// bi-directional many-to-many association to CookEvent
@ManyToMany(mappedBy = "tags")
private List<CookEvent> cookEvents;
// bi-directional many-to-many association to User
@ManyToMany(mappedBy = "tags")
private List<User> users;
@Entity
@Table(name = "cook_events")
public class CookEvent implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private Long id;
@Column(name = "takes_place_on")
private LocalDateTime takesPlaceOn;
...
// bi-directional many-to-many association to Tag
@ManyToMany
@JoinTable(name = "cook_events_tags", joinColumns = {
@JoinColumn(name = "cook_event_id") },
inverseJoinColumns = {
@JoinColumn(name = "tag_id") })
@OrderBy("name")
private List<Tag> tags;
因此,在我的数据库中,我具有“用户”,“ cook_events”,“标签”,“ user_tags_preferences”,“ cook_events_tags”表。
我将需要执行以下操作的JPQL查询:
在我的前端,我有user_id。
我想要一个查询,用于过滤所有
takesPlaceOn > CURRENT_TIMESTAMP
的cook_event,以及我的user_id中至少有一个user_tags_preferences与cook_event_tags匹配的查询。联接是否有可能过滤所有这些,最终最终得到我需要的
List<CookEvent>
。甚至更容易使用List<Tag>
。甚至可能是不同的查询,我自己在后端对其进行过滤,但我想尝试过滤查询中的所有内容。
最佳答案
这可能就是答案吗?
SELECT DISTINCT(c) from User u JOIN u.tags t JOIN t.cookEvents c
WHERE u.id = :id AND c.takesPlaceOn > CURRENT_TIMESTAMP