CODEWITHSUNDEEP
springhibernatejpaspring-data-jpahql
agabar
agabar

Reputation: 21

How to join results of multiple tables in Spring JPA repository which has same column names

I'm new to Spring and I'm trying to create my first Spring project. I want to create the query to get appointment data with firstName and lastName of a patient as well as firstName and lastName of the optometrist. The problem is that the fields firstName and lastName are named the same for both patient and optometrist as they are fields from AppUser. Here is the database tables relationship diagram.

There @OneToOne relationships between AppUser and Patient and between AppUser and Optometrist. There are @OneToMany relationships between Patient and Appointment and Optometrist and Appointment.

My DTO that I want to retrieve:

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    public class AppointmentPatientOptometrist {
        private String patientFirstName;
        private String patientLastName;
        private String optometristFirstName;
        private String optometristLastName;
        private String status;
    }

My AppointmentsRepository with the query I tried to create (I tried to use "as" in the query to solve a problem of the same names):

    public interface AppointmentRepository extends JpaRepository<Appointment,Long> {
    
      @Query(value="SELECT new com.mypackage.dto.AppointmentPatientOptometrist"+
                "(up.firstName as patientFirstName, up.lastName as patientLastName, uo.firstName as optometristFirstName, " +
                "uo.lastName as optometristLastName, a.status)" +
                " FROM Appointment a  join a.patient p join a.optometrist o join o.appUser uo join p.appUser up")
        Page<AppointmentPatientOptometrist> loadAppointments(Pageable pageable);
    
    }

I would be grateful for any tips.

Upvotes: 1

Views: 2604

Answers (1)

agabar
agabar

Reputation: 21

Thank you. The Query above is fine. The cause of the problem was that I've implemented the status in the Appointment class as Enum AppointmentStatus. The query was returning AppoitmentStatus type for status, not String as I thought. I've changed the type of the status field from String to AppointmentStatus in my DTO AppointmentPatientOptometrist class and now it works fine. Thank you all for helping

Upvotes: 1

Related Questions