Spring boot JPA - Order by Entity collection

I am try to sort the data by collection size for a certain period.

I have

Title Entity

@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

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

Title repository

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

Title service

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

I need select paginated data (Title Entity) and sort all title entities by count of title visits in the last day/week/month/overall

Thanks.

You can use ORDER BY in JPQL rather than use sort param in pageable since you use custom order.

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

Here, calculate dateTime for last day/week/month/overall