Nested Foreign Keys (PostgreSQL)

[Kevin Henkener]

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

[Lukas Eder]

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.

--
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 jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/3adcae5b-5378-461c-baff-078054533f6fn%40googlegroups.com.

[Kevin Henkener]

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

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4%2BccRG5kBKakUtP%2BdmN5DhP6wQQfmdSDLtv%2BipV5n1uA%40mail.gmail.com.

[Alf Lervåg]

jOOQ is designed for writing SQL. SQL is designed to let you ask for the data you need in a shape suited for your task. Usually you will end up writing lots of different SQL queries against the same tables to cater for different tasks.

There are lots of ways to simplify your code, but we don’t know what you need. Your query as it stands seems to fetch all the data from your database. That’s seldom the correct solution. Thus simplifying this will probably not be of actual help to you.

If you write more about what you’re trying to accomplish and why then it will be easier to help. We don’t even have the name of your function to hint at your intention.

I suggest you read the book https://theartofpostgresql.com/ to get an insight into how the database can be used to simplify your life as a developer.

Alf Lervåg

22. feb. 2025 kl. 19:56 skrev Kevin Henkener <ke...@formahealth.io>:



To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAKRHrx-trzygKWh9unFt92NC_VMBjvrKJo6wqcg4Mmuc4Kuv3g%40mail.gmail.com.

[Lukas Eder]

Kevin,

When requesting "a way to simplify," do state why you're unhappy with the status quo. There's probably a reason. I could tell you 20 things I'd do differently, e.g.

- I would never use plain SQL templates to dereference columns, unless absolutely necessary, and that isn't the case in your case).

- I would never rely on reflection / DefaultRecordMapper for mildly complex queries, because as soon as your types are erased (e.g. List<Specialties> instead of Specialties[]), reflection stops working. I'd use type safe ad-hoc converters as suggested in earlier links. It will be much easier to refactor your query and/or record types

So, what kind of simplification are you looking for?

Best Regards,

Lukas

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAKRHrx-trzygKWh9unFt92NC_VMBjvrKJo6wqcg4Mmuc4Kuv3g%40mail.gmail.com.