Insert row in QSqlRelationalTableModel while displaying two columns for a foreign key

Question

I have two SQLite tables having the following structure:

CREATE TABLE "log" (
    "id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name"  TEXT,
    "value" INTEGER NOT NULL,
    "category_id"   INTEGER NOT NULL,
    "date"  TEXT NOT NULL,
    FOREIGN KEY("category_id") REFERENCES "category"("id")
);

CREATE TABLE "category" (
    "id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "category_name" TEXT NOT NULL,
    "type"  TEXT NOT NULL
);

I also want to display a QTableView which uses a QSqlRelationalTableModel. I need to display two columns (category_name and type) instead of category_id, although QSqlRelation seems to replace the foreign key with only a single column, I managed to show 2 columns like this:

self.balance_table_model = QSqlRelationalTableModel()
self.balance_table_model.setTable("log")
self.balance_table_model.setRelation(3, QSqlRelation("category", "id", "category_name, type"))
self.balance_table_model.setHeaderData(1, Qt.Horizontal, "Name")
self.balance_table_model.setHeaderData(2, Qt.Horizontal, "Value")
self.balance_table_model.setHeaderData(3, Qt.Horizontal, "Category")
self.balance_table_model.setHeaderData(4, Qt.Horizontal, "Type")
self.balance_table_model.setHeaderData(5, Qt.Horizontal, "Date")
self._ui.balanceTableView.setModel(self.balance_table_model)
self._ui.balanceTableView.setColumnHidden(0, True)

Now, when I want to add a new row, the insert works only if I join the two tables without the type, i.e.

self.balance_table_model.setRelation(3, QSqlRelation("category", "id", "category_name"))

The insert code:

record = self.balance_table_model.record()
record.setGenerated(0, True)
record.setValue(1, log_name)
record.setValue(2, log_value)
record.setValue(3, category_id)
record.setValue(4, date)
self.balance_table_model.insertRecord(-1, record)

How can I make the insert work while still being able to display the type column?

Answer 1

Ran into the exact same issue. Unfortunately Qt doesn't support the multiple columns, so we need to just have a one-to-one relation for each column:

self.balance_table_model.setRelation(3, QSqlRelation("category", "id", "category_name"))

Source: https://forum.qt.io/topic/139659/how-to-insert-data-into-a-qsqlrelationaltablemodel

EDIT: I had a similar example:

create_table_query.exec(
    """CREATE TABLE "appointments" (
        "appt_id"               INTEGER,
        "patient_id"            INTEGER,
        "patient_fname_id"      INTEGER,
        "patient_lname_id"      INTEGER,
        "scheduled_date"        TEXT,
        "scheduled_time"        TEXT,
        "scheduled_duration"    INTEGER,
        "reason"        TEXT,
        "appt_notes"    TEXT,
        PRIMARY KEY("appt_id"),
        FOREIGN KEY("patient_id") REFERENCES "patient_profile"("patient_id")    
    )
    """)

I then set

"patient_id"            INTEGER,
"patient_fname_id"      INTEGER,
"patient_lname_id"      INTEGER,

All to the value of "patient_id" in my QWidget view, so an average database entry would read like this: here

My model and relations were as this:

class ApptTableModel(QSqlRelationalTableModel):
def __init__(self):
    super().__init__()
    self.setTable('appointments')
    self.setRelation(1, QSqlRelation("patient_profile", "patient_id", "patient_id"))
    self.setRelation(2, QSqlRelation("patient_profile", "patient_id", "name_fname"))
    self.setRelation(3, QSqlRelation("patient_profile", "patient_id", "name_lname"))

Bit of a hacky solution (and annoying redundant values in database table) but it works perfect now.