JPA findAll order by a linked list size

Question

I am facing a problem and I have no idea if is possible to do it using JPA.

I am trying to make a query using JPA and make the following:

Get all courses with the course entity fields (id, name) but also with a non persistent field (@Transient) that will be filled with the count of all students related with this course

Something like this:

List<Course> courses = courseRepository.findAll();

but instead get (representing as json for example purposes)

[{1, soccer}, {2, art}, {3, singing}]

I need something like this

[{1, soccer, 2}, {2, art, 0}, {3, singing, 1}]

As you can see the values 2, 0 a 1 is the count of the table students of all the related rows

Student table
| id | name | description | course |
|  1 | thg1 | a thing     | 1      |
|  2 | thg2 | another one | 1      |
|  3 | thg3 | one more    | 3      |   

Course Table
| id | name | 
|  1 | soccer |     
|  2 | art |     
|  3 | singing |     

So, the restriction is that one student can attend one course ONLY.

Using JPA I want to select all the courses but due I am using pagination there is no way I can do it on the spring part (I mean as a service), I am trying to do it directly with JPA, is there any way I can achieve this? Maybe with specification? Ideas?

Thanks

Answer 1

You can use the @Formula annotation from Hibernate:

@Formula("select count(*) FROM student s WHERE s.course = id")
private int totalStudents;

Sometimes, you want the Database to do some computation for you rather than in the JVM, you might also create some kind of virtual column. You can use a SQL fragment (aka formula) instead of mapping a property into a column. This kind of property is read only (its value is calculated by your formula fragment).

@Formula("obj_length * obj_height * obj_width")
public long getObjectVolume()

The SQL fragment can be as complex as you want and even include subselects.

hibernate reference

Alternative you can use a bidirectional relation to count the students:

@OneToMany(mappedBy="course")
private List<Student> students;

public int getTotalStudents() {
    return students.size();
}

Or with a transient field:

@OneToMany(mappedBy="course")
private List<Student> students;

@Transient
private int studentCount;

@PostLoad
public void setStudentCount() {
    studentCount = students.size();
}

To avoid the N+1 issue that was mentioned by Cepr0 you can set the fetch mode to join:

@OneToMany(mappedBy="course")
@Fetch(FetchMode.JOIN)
private List<Student> students;

Answer 2

You can use projections to achieve what you need.

Assuming that you are using the following entities:

@Entity
@Data
@AllArgsConstructor
public class Course implements Serializable {

    @Id
    @GeneratedValue
    private Integer id;

    private String name;

    @Transient
    private Long total;

    public Course() {
    }

    public Course(String name) {
        this.name = name;
    }
}

@Entity
@Data
@AllArgsConstructor
public class Student implements Serializable {
    @Id
    @GeneratedValue
    private Integer id;

    private String name;

    @ManyToOne(optional = false)
    private Course course;

    public Student() {
    }

    public Student(String name, Course course) {
        this.name = name;
        this.course = course;
    }
}

1) Then you can create the interface based projection

public interface CourseWithCountProjection {
    Integer getId();
    String getName();
    Long getTotal();
}

and the following query method in the Course repository:

public interface CourseRepo extends JpaRepository<Course, Integer> {
    @Query(value = "" +
            "select " +
            "  c.id as id, " +
            "  c.name as name, " +
            "  count(s) as total " +
            "from " +
            "  Course c " +
            "  left join Student s on s.course.id = c.id " +
            "group by " +
            "  c " +
            "order by " +
            "  count(s) desc" +
            "", countQuery = "select count(c) from Course c")
    Page<CourseWithCountProjection> getProjectionWithCount(Pageable pageable);    
}

In this case you don't need the transient total field in the Course and you can remove it.

Note that you have to add the extra countQuery parameter to the @Query annotation because the main query has the grouping.

Also pay attention on aliases in the query (c.id as id etc) - they are necessary when you are using projections.

2) Another way is to use the the Course constructor in the JPQL query as @KarolDowbecki has already shown. You can use it with almost the same query:

public interface CourseRepo extends JpaRepository<Course, Integer> {
    @Query(value = "" +
        "select " +
        "  new Course(c.id, c.name, count(s)) " +
        "from " +
        "  Course c " +
        "  left join Student s on s.course.id = c.id " +
        "group by " +
        "  c " +
        "order by " +
        "  count(s) desc" +
        "", countQuery = "select count(c) from Course c")
    Page<Course> getCoursesWithCount(Pageable pageable);
}

UPDATED

The first option is more preferable, because it divides the model (Course) and the view (CourseWithCountProjection) from each other.

UPDATED 2

To get dynamic sorting you can exclude order by from the query and provide sorting in the Pageable parameter of the query method, for example:

@Query(value = "" +
        "select " +
        "  c.id as id, " +
        "  c.name as name, " +
        "  count(s) as total " +
        "from " +
        "  Course c " +
        "  left join Student s on s.course.id = c.id " +
        "group by " +
        "  c " +
        "", countQuery = "select count(c) from Course c")
Page<CourseWithCountProjection> getProjectionWithCount(Pageable pageable);

Page<CourseWithCountProjection> result = parentRepo.getProjectionWithCount(PageRequest.of(0, 20, Sort.by(Sort.Direction.DESC, "total")));

Working example is here: sb-jpa-orderby-related

Answer 3

You can use a custom GROUP BY query that will create a projection:

@Query("SELECT new full.path.to.Course(c.id, c.name, count(s)) " +
       "FROM Course c " +
       "LEFT JOIN Student s " +
       "GROUP BY c.id")
List<Course> findCourseWithStudentCount();

Assuming that you have the corresponding constructor in the Course class this will return projection objects. They won't be managed as entitites but if you don't plan to modify them later they will do.

Answer 4

You could provide a new view in your DB, which collects all the information you need (e.g. using a group-by statement). The view should then contain data like this, in exactly the form you need it later:

CourseWithStudents view
| id | course  | students
|  1 | soccer  | 2
|  2 | art     | 0
|  3 | singing | 1  

Creating a new class (maybe extending your current Course class) could be mapped to this view:

@Entity
@Table(name="CourseWithStudents")
class CourseWithStudents extends Course {
    // Insert Mapping
}

You can then select all the information from the new view:

List<CourseWithStudents> courses = courseStudentRepository.findAll();

But since you can't update a view, this solution only works if all you need is read-access. If you want to change your Course/Student object and update it in the DB after this selection, this approach does not work.