java Spring引导JPA按实体排序集合

Question

我试图在一定时期内按收集量对数据进行排序

我有

标题实体

@Entity
@Getter
@Setter
public class Title {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(unique = true, nullable = false)
    private Long id;

    @Column(length=120, nullable=false)
    private String name;

    @OneToMany(mappedBy = "title", cascade = CascadeType.MERGE, orphanRemoval = true)
    private Set<TitleVisitor> visitors = new HashSet<>();

    @CreatedDate
    private LocalDateTime createdAt;
    @LastModifiedDate
    private LocalDateTime updatedAt;

所有权人实体

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class TitleVisitor implements Serializable {

    @EmbeddedId
    private TitleVisitorId id = new TitleVisitorId();

    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("titleId")
    private Title title;

    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("visitorId")
    private Visitor visitor;

    @CreatedDate
    private LocalDateTime createdAt;

标题存储库

@Repository
public interface MovieRepository extends JpaRepository<Movie, Long> {
    @Override
    Optional<Movie> findById(Long aLong);

    @Query(
            value = "SELECT m FROM Movie m LEFT JOIN TitleVisitor tv ON m.id = tv.id.titleId WHERE tv.createdAt >= DATEADD(day,-7, NOW()) GROUP BY tv.id.titleId",
            countQuery = "select count(tv.id.titleId) from TitleVisitor tv"
    )
    Page<Movie> findAllWithTitleVisitorCountOrderByCountDesc(Pageable pageable);
}

标题服务

    public Page<Title> findPaginated(int page, int size) {
        Pageable paging = PageRequest.of(page, size, Sort.by(Sort.Direction.DESC, "visitors"));

        return this.titleRepository.findAll(paging);
    }

我需要选择分页数据（标题实体）并按最后一天/周/月/总标题访问次数对所有标题实体进行排序

谢谢

Answer 1

您可以在JPQL中使用ORDER BY，而不是在pageable中使用sort param，因为您使用的是自定义顺序

@Query(value = "SELECT t FROM Title t LEFT JOIN t.visitors tv "
         + "WHERE tv.createdAt >= :dateTime GROUP BY t ORDER BY COUNT(tv.visitor.id) DESC",
            countQuery = "select count(t.id) from Title t")
    Page<Title> findByTitleVisitorCountCount(LocalDateTime dateTime, Pageable pageable);

这里，计算最后一天/周/月/总的dateTime