时间戳

首页 > 解决方案 > Spring boot JPA - 按实体集合排序

java - Spring boot JPA - 按实体集合排序

问题描述

我试图在一段时间内按集合大小对数据进行排序。

我有

标题实体


@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;

TitleVisitor 实体

@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);
    }

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

谢谢。

标签： javaspring-bootspring-data-jpaspring-data

解决方案

由于您使用自定义顺序，因此您可以ORDER BY在 JPQL 中使用而不是使用 sort 参数。pageable

@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最后一天/一周/一个月/总体

推荐阅读