java - 如何根据 Spring JPA 中的参数使用计算字段发出分页动态请求

Question

给定一个幻灯片，其中包含幻灯片和另一个表 (SeenSlideshow)，该表存储有关观看幻灯片的员工的信息。

@Data
@Entity
@Wither
@Table(name = "slideshow")
public class Slideshow {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Enumerated(EnumType.STRING)
    @Column(name = "status", nullable = false)
    private SlideshowStatus status;

    @Enumerated(EnumType.STRING)
    @Column(name = "persona", nullable = false)
    private SlideshowPersona persona;

    @Embedded
    private Post post;

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "slideshow_id")
    @OrderBy("slide_order asc")
    private List<Slide> slides;

    @Embedded
    private Conclusion conclusion;

    @CreationTimestamp
    private LocalDateTime createdAt;

    private LocalDateTime publishedAt;

    @Transient
    private boolean seen;

    public Slideshow() {
    }

    public Slideshow(Integer id, SlideshowStatus status, SlideshowPersona persona, Post post, Conclusion conclusion, LocalDateTime createdAt, LocalDateTime publishedAt, boolean seen) {
        this.id = id;
        this.status = status;
        this.persona = persona;
        this.post = post;
        this.conclusion = conclusion;
        this.createdAt = createdAt;
        this.publishedAt = publishedAt;
        this.seen = seen;
    }
}

观看幻灯片:

@Data
@Builder
@AllArgsConstructor
@Entity
@Table(name = "seen_slideshow")
public class SeenSlideshow {
    @EmbeddedId
    private SeenSlideshowIdentity pk;


    @MapsId("employeeId")
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "employee_id")
    private Employee employee;

    @MapsId("slideshowId")
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "slideshow_id")
    private Slideshow slideshow;

    public SeenSlideshow() { }

    public SeenSlideshow(Employee employee, Slideshow slideshow) {
        this.pk = SeenSlideshowIdentity.builder()
                                       .employeeId(employee != null ? employee.getId() : null)
                                       .slideshowId(slideshow != null ? slideshow.getId() : null)
                                       .build();
        this.slideshow = slideshow;
        this.employee = employee;
    }
}

我如何进行查询:

1. 已分页
2. 填写seen Slideshow的 transient 场类
3. 允许按 seen 排序字段，基于给定的员工(查询的参数)
4. 同时获取幻灯片列表
5. 具有动态过滤器(我通常使用示例或规范)

我成功满足了前 3 个要求，但未能满足第 4 个和第 5 个要求。

我可以使用 4 的解决方法，但是当我尝试使用示例或规范查询时，它不起作用，因为我使用的请求如下所示:

    @Query(
        value =  "select new Slideshow(s.id, s.status, s.persona, s.post, s.conclusion, s.createdAt, s.publishedAt, (SELECT COUNT(ss.pk.slideshowId) > 0 FROM SeenSlideshow ss WHERE s.id = ss.pk.slideshowId and ss.pk.employeeId = :employeeId) as seen ) from Slideshow s",
        countQuery = "select count(s) from Slideshow s"
    )
    Page<Slideshow> findForEmployee(String employeeId, Pageable pageable);

因为我需要一个参数来计算 seen字段，我无法添加任何 Example<Slideshow>或Specification<Slideshow> .

当我添加规范时，Spring JPA 将其视为简单的未使用参数，并且不应用动态过滤器。

如何在 select 子句中使用子查询进行动态查询？

Answer 1

我设法使用艾伦的想法实现了一种解决方法(这与我想要的并不完全匹配，因为我的值(value)取决于连接的用户)。

这不是一个理想的解决方案，但我创建了一个包含幻灯片列 + employee_id + 计算出的 seen 字段的 View 。

create view employee_slideshow as
(select s.*, e.id as employee_id, (select COUNT(ss.slideshow_id) > 0 from seen_slideshow ss where ss.slideshow_id = s.id and ss.employee_id = e.id) as seen
from slideshow s, employee e
group by s.id, e.id)

该 View 可由员工分页、排序和过滤。

然后，我创建了一个与此 View 匹配的实体及其存储库:

@Entity
@Table(name = "employee_slideshow")
public class EmployeeSlideshow {
    @Id
    private Integer id;

    // and the other slideshow fields

    private boolean seen;

    private String employeeId;
}

这不是我对 Spring JPA 的期望，但是如果体积较小，那就没问题了。