Spring boot JPA - Order by Entity collection

Question

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.

what do you mean by *I need paginated data sorted by count( title visits in the last day/week/month/overall (createdAt attribute in TitleVisit entity) )*. you want to do it application level or you are suggesting with a query? — Aman, Oct 26 '20 at 11:58
A am using JPA Repository for select data. I need select paginated data (Title Entity) and sort all title entities by count of title visits in the last day/week/month/overall — andrej_14, Oct 26 '20 at 12:03
I added more details. I'm a beginner in spring boot and I have no idea how to do it. — andrej_14, Oct 26 '20 at 12:18
Do you know how to do it in SQL? If you know that then it will much easier for you to write it in JPQL. Can you show us ? — Eklavya, Oct 26 '20 at 12:26
Can you show us how you can do it SQL? Show us some effort. SO is not code service. Though this link will help you https://stackoverflow.com/questions/23136469/order-by-count-using-spring-data-jparepository/23160019#23160019 — Eklavya, Oct 26 '20 at 12:32
`SELECT *, count(title_visitor.visitor_id) as visitors FROM title LEFT JOIN title_visitor ON title.id = title_visitor.title_id WHERE title_visitor.created_at >= DATEADD(day,-7, NOW()) GROUP BY title.id ORDER BY count(title_visitor.visitor_id) DESC` — andrej_14, Oct 26 '20 at 13:22
You can read about JPA repository here https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.repositories and how to write custom query in JPQL and use `@Query` look here https://www.baeldung.com/spring-data-jpa-query. Sol: Use `@Query` to run native query that you have already for an easier way. — Eklavya, Oct 26 '20 at 14:19
I updated the repository class in the question. Lists the results but does not sort them. — andrej_14, Oct 26 '20 at 14:24

score 0 · Accepted Answer · answered Oct 26 '20 at 14:46

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

Spring boot JPA - Order by Entity collection

1 Answers1