Re: Nested Foreign Keys (PostgreSQL)

Alf Lervåg Sat, 22 Feb 2025 11:36:25 -0800

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>:

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

--
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+unsubscr...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4%2BccRG5kBKakUtP%2BdmN5DhP6wQQfmdSDLtv%2BipV5n1uA%40mail.gmail.com.

--
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+unsubscr...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAKRHrx-trzygKWh9unFt92NC_VMBjvrKJo6wqcg4Mmuc4Kuv3g%40mail.gmail.com.

--
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+unsubscr...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/ED66E0E7-6E3D-4572-90A0-1C4C5783285B%40lervag.net.

Re: Nested Foreign Keys (PostgreSQL)

Reply via email to