How to relate two tablemodels together properly?

judethedude

Hey everyone,
I have two tableviews, each with their respective QSqlTableModel (or QSqlRelationalTableModel). One for appointments, the other for patients. The sqlite tables are set up like this:

patient_profile

patient_id (primary key)
first_name
last_name

appointments

appt_id (primary key)
patient_id (foreign key to patient_profile)
first_name
last_name
appointment_date_time

Is there a proper way to tie the patient_id, first and last names to the appointment tablemodel?
Looking through the documentation I see I can set a relation to a field, but will a change to the patient_profile table in the database trigger the appointment tablemodel to update? Or should I just carefully tell the model to update (select) when I suspect a change has happened?

I hope my question is clear, thanks for your time.

judethedude

@judethedude

A follow-up question, since I want first_name and last_name to be mapped as well as patient_id, but only patient_id is guaranteed to be unique...how would I set that up?

appt_model.setRelation(1, QSqlRelation("patient_profile", "patient_id", "patient_id"))

That would map patient_id from appointments to patient_profile, but how would I set up the first_name and last_name...would I need to restructure my appointment table to something like this?
appointments

appt_id
patient_id
patient_id_first_name (value is the same as patient_id)
patient_id_last_name (value is the same as patient_id)

appt_model.setRelation(2, QSqlRelation("patient_profile", "patient_id", "first_name"))

I'm a bit hazy on this subject so any advice would be appreciated.

JonB

@judethedude said in How to relate two tablemodels together properly?:

Looking through the documentation I see I can set a relation to a field, but will a change to the patient_profile table in the database trigger the appointment tablemodel to update?

Absolutely not! Neither in Qt models nor in the SQL database.

What you are supposed to do with a foreign key is not store further information from foreign table columns also back in the referencing appointments table. This is one of the "fundamentals" and relational databases.

An appointment does not have a first or last name. Only a patient has those. If looking at an appointment and you want patient information you are supposed to use the foreign key to go get that.
,
For your second question, Google for qsqlrelationaltablemodel multiple columns, that is what you are trying to do here. I don't think Qt/QSqlRelation handles this for you. Have a look through the suggestions at e.g. https://stackoverflow.com/questions/36437055/qsqlrelationaltablemodel-additional-related-columns. There are others (e.g. https://stackoverflow.com/questions/35536730/qsqlrelationaltablemodel-two-references-to-the-same-table-same-foreign-keys), so far as I can see all in a similar vein. Note that with these solutions you end up with your appointments table being not editable. Or perhaps, per the approach in https://stackoverflow.com/a/69987711/489865

There is only one problem if you want to edit values through a QTableView: You can edit other columns, but if you try to edit the related column, the model does not know what to do.

judethedude

@JonB thanks so much for the answer Jon. I will take a look at those links. I figured the appointment table shouldn't carry redundant information. Appreciate your time.

EDIT: To anyone that finds this via search, the last link Jon provided (https://stackoverflow.com/questions/36437055/qsqlrelationaltablemodel-additional-related-columns/69987711#69987711) seems like the best approach

EDIT2: Unfortunately, although though the link above provides a great way to view data, there is no easy way to edit (or insert into model). Recommend going with a one-to-one relation.