Thanks, Lukas! With multiset, I land on the code below. Is there a way to
simplify?
final DSLContext dslContext = DSL.using(this.jooqConf);
final Table<Record> doctorsTable =
dslContext.select(DOCTORS.fields())
.select(
multiset(
select(SPECIALTIES.fields())
.from(SPECIALTIES)
.join(DOCTORS_SPECIALTIES)
.on(DOCTORS_SPECIALTIES.SPECIALTY_ID.eq(SPECIALTIES.ID))
).as("specialties")
)
.from(DOCTORS)
.asTable("doctor");
return dslContext
.select(PATIENTS_DOCTORS.fields())
.select(PATIENTS_DOCTORS.patients().as("patient"))
.select(doctorsTable)
.from(PATIENTS_DOCTORS)
.innerJoin(doctorsTable)
.on(PATIENTS_DOCTORS.DOCTOR_ID.eq(DSL.field("doctor.id", UUID.class)))
.where(PATIENTS_DOCTORS.PATIENT_ID.eq(patientId))
.fetchInto(PatientDoctor.class);
On Fri, Feb 21, 2025 at 1:53 AM Lukas Eder <[email protected]> wrote:
> jOOQ doesn't "magically" populate your fields unless you explicitly load
> them. You're not loading (nor mapping) any specialty data.
>
> I'd go about this by being explicit rather than trying to rely on any
> DefaultRecordMapper functionality, which is always implicit,
> reflection-based, type unsafe, etc. Read about the MULTISET operator and
> ad-hoc converters in this blog post to start:
>
> https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/
>
> You'll see how simple it is to do all of your specific mappings in a type
> safe, explicit way.
>
> On Thu, Feb 20, 2025 at 10:59 PM Kevin Henkener <[email protected]>
> wrote:
>
>> 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
>> <https://groups.google.com/d/msgid/jooq-user/3adcae5b-5378-461c-baff-078054533f6fn%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
> --
> 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/CAB4ELO4%2BccRG5kBKakUtP%2BdmN5DhP6wQQfmdSDLtv%2BipV5n1uA%40mail.gmail.com
> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO4%2BccRG5kBKakUtP%2BdmN5DhP6wQQfmdSDLtv%2BipV5n1uA%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
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/CAKRHrx-trzygKWh9unFt92NC_VMBjvrKJo6wqcg4Mmuc4Kuv3g%40mail.gmail.com.
