CODEWITHSUNDEEP
javahibernatespring-data-jpa
Nikolas
Nikolas

Reputation: 2432

Complicated query with jparepository. Three tables join

I want to filter entities with next way: get distinct 10 top disciplines, which are ordered by students count(attendance) for sessions between some period of time.

So, Student didn't know anything about Discipline, only via Session. And I want to write using Spring JpaRepository something like:

List<Discipline> findTop10DisciplinesDistinctOrderBySessionsStudentsCountAndBySessionsBetween(Date from, Date to);

but there are some restrictions. It didn't work for me

No property count found for type Student! Traversed path: Discipline.sessions.students.

Query looks like this(mysql):

select d.*
from session as ses 
inner join    
(select st.*
from student as st 
inner join session as ses on ses.id = st.session_id
where ses.time > (curdate() - interval 30 day)
group by st.session_id
order by count(st.id) desc 
limit 10) as st2
on ses.id = st2.session_id
inner join discipline as d on ses.discipline_id = d.id

But how to add this query to jpa repository?

Upvotes: 0

Views: 74

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81970

Derived queries, those that get created from the method name are not suitable for use cases as yours. Even if it would work it would result in an unwieldy method name. Use an @Query with JPQL or SQL query instead.

Upvotes: 1

Related Questions