[FIXED] Query to sort data based on another entity

Issue

I'm writing a site of a hospital. This is an MVC application with database. Database contains data about patients, doctors etc.

I need to get List of doctors which should be sorted by patient count. I have already tried to do this with Comparator inside Java code like this example:

Page<Doctor> pageDoctor = doctorRepository.findAll(pageable);
List<Doctor> doctorList = pageDoctor.getContent();
doctorList.sort(Comparator.comparing(o -> patientRepository.findAllByDoctor(o).size()));

but I need the sorted list inside Page content. I don't really understand how to make the query equivalent to this example, because I'm new to SQL. Here are my entity and repository classes.

Doctor.java

@Entity
@Table(name = "doctors")
public class Doctor {
    @Id
    @Column(name = "id", nullable = false, unique = true)
    @SequenceGenerator(name="doctors_generator", sequenceName = "doctors_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "doctors_generator")
    private Long id;
    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private User user;
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "doctors_type_id")
    private DoctorsType doctorsType;

    public Doctor(User user, DoctorsType doctorsType) {
        this.user = user;
        this.doctorsType = doctorsType;
    }

    public Doctor() {
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public DoctorsType getDoctorsType() {
        return doctorsType;
    }

    public void setDoctorsType(DoctorsType doctorsType) {
        this.doctorsType = doctorsType;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}

Patient.java

@Entity
@Table(name = "patients")
public class Patient {
    @Id
    @Column(name = "id", nullable = false, unique = true)
    @SequenceGenerator(name="patients_generator", sequenceName = "patients_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "patients_generator")
    private Long id;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private User user;
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "doctor_id")
    private Doctor doctor;
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "treatment_id")
    private Treatment treatment;

    public Patient(User user, Doctor doctor, Treatment treatment) {
        this.user = user;
        this.doctor = doctor;
        this.treatment = treatment;
    }

    public Patient() {
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Doctor getDoctor() {
        return doctor;
    }

    public void setDoctor(Doctor doctor) {
        this.doctor = doctor;
    }

    public Treatment getTreatment() {
        return treatment;
    }

    public void setTreatment(Treatment treatment) {
        this.treatment = treatment;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}

PatientRepository.java

@Repository
public interface PatientRepository extends JpaRepository<Patient, Long> {
    Patient findPatientByUser(User user);

    List<Patient> findAllByDoctor(Doctor doctor);
    Patient findPatientById(long id);
    Page<Patient> findAllByOrderByIdAsc(Pageable pageable);
    List<Patient> findAllByOrderByIdAsc();
}

DoctorRepository.java

@Repository
public interface DoctorRepository extends JpaRepository<Doctor, Long> {
    Doctor findDoctorById(long id);
    Doctor findDoctorByUser(User user);

    @Query(
            //sql query there
    )
    Page<Doctor> findAllByPatientCountAsc(Pageable pageable);
    Page<Doctor> findAll(Pageable pageable);

    List<Doctor> findAllByOrderByIdAsc();
    List<Doctor> findAllByDoctorsTypeNot(DoctorsType doctorsType);
    List<Doctor> findAllByDoctorsType(DoctorsType doctorsType);
}

Thanks for your answers in advance.

Solution

Check this one:

SELECT d FROM Doctor d,
LEFT JOIN Patient p ON d.id = p.doctor.id
GROUP BY d
ORDER BY COUNT(p.id)

Answered By - Kamil W
Answer Checked By - Katrina (JavaFixing Volunteer)