How can we load a record containing a nested foreign key?
Example (redacted):
CREATE TABLE patients
(
id uuid primary key,
name varchar(32)
);
CREATE TABLE doctors
(
id uuid primary key,
name varchar(32)
);
CREATE TABLE specialties
(
id uuid primary key,
name varchar(32)
);
CREATE TABLE patients_doctors
(
id uuid primary key,
patient_id uuid not null references patients,
doctor_id uuid not null references doctors
);
CREATE TABLE doctors_specialties
(
id uuid primary key,
doctor_id uuid not null references doctors,
specialty_id uuid not null references specialties
);
record Patient(UUID id, String name) {}
record Doctor(UUID id, String name, Specialties[] specialties) {}
record Specialty(UUID id, String name) {}
record PatientDoctor(UUID id, UUID patientId, UUID doctorId, Patient
patient, Doctor doctor);
record DoctorSpecialty(UUID id, UUID doctorId, UUID specialtyId, Doctor
doctor, Specialty specialty);
We'd like to load the PatientDoctor records (POJO) for a given patient.id
and have the specialties of each Doctor object mapped as well.
The following loads the Patient and Doctor records with the PatientDoctor,
but the Doctor object does not have the Specialties loaded.
final List<PatientDoctor> patientDoctors =
dslContext
.select(PATIENTS_DOCTORS.fields())
.select(PATIENTS_DOCTORS.patients().as("patient"))
.select(PATIENTS_DOCTORS.doctors().as("doctor"))
.from(PATIENTS_DOCTORS)
.where(PATIENTS_DOCTORS.PATIENT_ID.eq(patientId))
.fetchInto(PatientTopic.class);
Ultimately, I can do this piece-by-piece with multiple queries but am
curious if there's a better way.
Thanks,
Kevin
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/jooq-user/3adcae5b-5378-461c-baff-078054533f6fn%40googlegroups.com.
