我正在写一个医院的网站。这是一个MVC应用程序与数据库。数据库包含有关病人，医生等数据。
我需要得到一个医生列表，它应该按照病人数量排序。我已经尝试过在Java代码中使用比较器来实现这个功能，比如：

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

但我需要Page内容中的排序列表。我不太明白如何使查询与这个示例等效，因为我是SQL新手。

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

患者.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;
    }
}

患者存储库.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();
}

医生资源库.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);
}

感谢您提前回答。