PostGraphile: One-To-Many relationships

Question

I inherited a database structure which works like this: I have two tables, one for Appointments and one for Visits. When someone makes an Appointment, they can describe certain Problems (a third table) to be addressed during a Visit. An Appointment can be converted into a Visit and carries over all of its Problems (and depending on the user's setup may always preserve the Appointment, always delete the Appointment, or user's choice).

Besides an internal primary key, Problem also has an external key which refers back to the primary key of either Appointment or Visit. There's a secondary field indicating which table the key refers to. Looking at existing code, it seems like Problems are converted by changing both the key and the secondary field from Appointment to Visit.

Now, if I put a constraint on Problem such that its external key must reference Appointment, Postgraphile will give me the option of pulling problemsByAppointmentKey with an Appointment and if I force it to reference Visit, it will give me the option of pulling problemsByVisitKey when I pull a Visit.

As far as I can tell, I can't force Problems to reference both tables. I can make a custom query to pull the problems for one or the other, but that's another call in addition to the one for Visit and Appointment. Also, it is kind of nice to have the entire Visit or Appointment returned in a single structure. (Also, there's another table Recommendation that's set up exactly the same way as Problem.)

Is there a way to describe this in PG in such a way that Postgraphile will pick it up and make the nested object calls like it does with a foreign-key constraint? I can also, at least theoretically, radically change the DB, though the more radical the changes, the harder the conversion process.

Answer 1

It appears you are saying that refactoring your tables may be problematic due to some legacy logic. So I think the simplest solution would be to add generated columns for appointment_id and visit_id with relevant foreign keys on those columns.

So assuming your schema looks something like:

CREATE TABLE ex_schema.appointments(
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    scheduled_for timestamptz NOT NULL,
    is_cancelled boolean NOT NULL
);

CREATE TABLE ex_schema.visits(
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    start_time timestamptz NOT NULL,
    end_time timestamptz NOT NULL
);

CREATE TABLE ex_schema.problems(
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ref_id bigint NOT NULL,
    ref_type varchar(63) check ( ref_type in ('VISIT','APPOINTMENT') ),
    UNIQUE (ref_id,ref_type)
);

you can run:

ALTER TABLE ex_schema.problems
    ADD COLUMN visit_id bigint generated always as ( case when ref_type = 'VISIT' then ref_id else null end ) STORED REFERENCES ex_schema.visits UNIQUE ,
    ADD COLUMN appointment_id bigint generated always as ( case when ref_type = 'APPOINTMENT' then ref_id else null end ) STORED REFERENCES ex_schema.appointments UNIQUE;

Postgraphile will immediately pick up the appropriate relationships. Since the new columns are generated, no triggers or legacy application logic should break due to their addition.