我的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

09-10 08:05
查看更多