[FIXED] QSqlRelationalDelegate displays foreign_key - id of the related record instead of name/value from Combobox

Issue

I am trying to add new rows to QSqlRelationalModel which is represented in QTableView.

I have set proper QSqlRelationalDelegate and proper QSqlRelations in the model. Displaying existing data from the database works fine. Columns with related data change to Comboboxes and I can choose options from the related tables.

However, when I try to create a new record by adding a row to the model, Comboboxes allow me to choose the proper value from the dropdown list, but after choosing it, the value changes to the ID of the related record as if no relational delegate was set.

main.py:

import sys

from PyQt6 import QtCore, QtWidgets
from PyQt6.QtCore import QModelIndex, Qt
from PyQt6.QtSql import QSqlDatabase, QSqlTableModel, QSqlRelationalTableModel, QSqlRelation, QSqlQuery, \
    QSqlRelationalDelegate
from PyQt6.QtWidgets import QPushButton

class Ui_main(object):
    def setupUi(self, main):
        main.setObjectName("main")
        main.resize(781, 652)

        self.verticalLayoutWidget = QtWidgets.QWidget(main)
        self.verticalLayoutWidget.setGeometry(QtCore.QRect(10, 10, 761, 631))
        self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
        self.verticalLayout.setContentsMargins(0, 0, 0, 0)
        self.verticalLayout.setObjectName("verticalLayout")

        # Replace values with your database configurations
        database = QSqlDatabase.addDatabase('QSQLITE')
        database.setDatabaseName('accounting.db')
        database.open()

        button_add = QPushButton("AddRow")
        button_add.clicked.connect(self.addRow)
        self.verticalLayout.addWidget(button_add)

        self.tableView = QtWidgets.QTableView(self.verticalLayoutWidget)
        self.tableView.setObjectName("tableView")
        self.tableView.verticalHeader().setVisible(False)
        self.verticalLayout.addWidget(self.tableView)

        self.table_model = QSqlRelationalTableModel(main, database)
        self.table_model.setJoinMode(QSqlRelationalTableModel.JoinMode.LeftJoin)
        self.table_model.setEditStrategy(QSqlTableModel.EditStrategy.OnFieldChange)

        self.table_model.setTable('book_of_accounts')

        self.table_model.setRelation(4, QSqlRelation('account_type', 'id', 'name'))
        self.table_model.setRelation(7, QSqlRelation('subconto1', 'id', 'name'))
        self.table_model.setRelation(8, QSqlRelation('subconto2', 'id', 'name'))
        self.table_model.setRelation(9, QSqlRelation('subconto3', 'id', 'name'))

        self.table_model.select()

        self.tableView.setModel(self.table_model)
        self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView))
        self.tableView.hideColumn(0)
        QtCore.QMetaObject.connectSlotsByName(main)

    def addRow(self):
        self.tableView.sortByColumn(-1, Qt.SortOrder.AscendingOrder)
        count = self.table_model.rowCount(QModelIndex())
        self.table_model.insertRows(count, 1)
        self.tableView.scrollToBottom()

        self.tableView.updateGeometry()
        self.tableView.selectRow(count)


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    main_window = QtWidgets.QWidget()
    window = Ui_main()
    window.setupUi(main_window)
    main_window.show()
    sys.exit(app.exec())

Database:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "book_of_accounts" (
    "id"    INTEGER PRIMARY KEY AUTOINCREMENT,
    "code"  varchar(4) NOT NULL,
    "belongs_to_id" bigint,
    "name"  varchar(1024) NOT NULL,
    "account_type_id"   bigint NOT NULL,
    "quantitative"  boolean NOT NULL,
    "monetary"  boolean NOT NULL,
    "subconto_1_id" bigint,
    "subconto_2_id" bigint,
    "subconto_3_id" bigint,
    CONSTRAINT "book_of_accounts_subconoto_3_id_fkey" FOREIGN KEY("subconto_3_id") REFERENCES "subconto3" on delete cascade,
    CONSTRAINT "book_of_accounts_subconoto_2_id_fkey" FOREIGN KEY("subconto_2_id") REFERENCES "subconto2" on delete cascade,
    CONSTRAINT "book_of_accounts_subconoto_1_id_fkey" FOREIGN KEY("subconto_1_id") REFERENCES "subconto1" on delete cascade,
    CONSTRAINT "book_of_accounts_account_type_id_fkey" FOREIGN KEY("account_type_id") REFERENCES "account_type",
    CONSTRAINT "book_of_accounts_belongs_to_id_fkey" FOREIGN KEY("belongs_to_id") REFERENCES "book_of_accounts"
);
CREATE TABLE IF NOT EXISTS "subconto3" (
    "id"    bigserial,
    "name"  varchar(1024) NOT NULL,
    CONSTRAINT "subconto3_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "subconto2" (
    "id"    bigserial,
    "name"  varchar(1024) NOT NULL,
    CONSTRAINT "subconto2_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "subconto1" (
    "id"    bigserial,
    "name"  varchar(1024) NOT NULL,
    CONSTRAINT "subconto1_pkey" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "account_type" (
    "id"    bigserial,
    "name"  varchar(1024) NOT NULL,
    CONSTRAINT "account_type_pkey" PRIMARY KEY("id")
);

INSERT INTO "subconto3" ("id","name") VALUES (1,'< Ничего >');
INSERT INTO "subconto3" ("id","name") VALUES (2,'Цены');
INSERT INTO "subconto3" ("id","name") VALUES (3,'Подразделения');
INSERT INTO "subconto2" ("id","name") VALUES (1,'< Ничего >');
INSERT INTO "subconto2" ("id","name") VALUES (2,'Места хранения');
INSERT INTO "subconto2" ("id","name") VALUES (3,'Статьи затрат');
INSERT INTO "subconto2" ("id","name") VALUES (4,'Договора');
INSERT INTO "subconto2" ("id","name") VALUES (5,'Виды налогооблагаемого дохода');
INSERT INTO "subconto2" ("id","name") VALUES (6,'Движение денежных средств');
INSERT INTO "subconto2" ("id","name") VALUES (7,'Виды выбытия');
INSERT INTO "subconto1" ("id","name") VALUES (1,'< Ничего >');
INSERT INTO "subconto1" ("id","name") VALUES (2,'Основные средства');
INSERT INTO "subconto1" ("id","name") VALUES (3,'Нематериальные активы');
INSERT INTO "subconto1" ("id","name") VALUES (4,'Товаро-материальные запасы');
INSERT INTO "subconto1" ("id","name") VALUES (5,'Статьи затрат');
INSERT INTO "subconto1" ("id","name") VALUES (6,'Расходы будущих периодов');
INSERT INTO "subconto1" ("id","name") VALUES (7,'Контрагенты');
INSERT INTO "subconto1" ("id","name") VALUES (8,'Сотрудники');
INSERT INTO "subconto1" ("id","name") VALUES (9,'Налоги и отчисления');
INSERT INTO "subconto1" ("id","name") VALUES (10,'Движение денежных средств');
INSERT INTO "subconto1" ("id","name") VALUES (11,'Расчетные счета наши');
INSERT INTO "subconto1" ("id","name") VALUES (12,'Контрагенты и Сотрудники');
INSERT INTO "account_type" ("id","name") VALUES (1,'Активный');
INSERT INTO "account_type" ("id","name") VALUES (2,'Забалансовый');
INSERT INTO "account_type" ("id","name") VALUES (3,'Контр. Активный');
INSERT INTO "account_type" ("id","name") VALUES (4,'Контр. Пассивный');
INSERT INTO "account_type" ("id","name") VALUES (5,'Пассивный');
INSERT INTO "account_type" ("id","name") VALUES (6,'Транзитный');

INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (1,'0000',NULL,'Остатки',5,0,0,1,1,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (2,'0100',NULL,'СЧЕТА УЧЕТА ОСНОВНЫХ СРЕДСТВ',3,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (3,'0110',2,'Земля',3,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (4,'0111',2,'Благоустройство земли',1,0,0,2,2,1);
INSERT INTO "book_of_accounts" ("id","code","belongs_to_id","name","account_type_id","quantitative","monetary","subconto_1_id","subconto_2_id","subconto_3_id") VALUES (5,'0112',2,'Благоустройство основных средств, полученных по договору долгосрочной аренды',1,0,0,2,2,1);

CREATE UNIQUE INDEX IF NOT EXISTS "bookofaccounts_code" ON "book_of_accounts" (
    "code"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_belongs_to_id" ON "book_of_accounts" (
    "belongs_to_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_name" ON "book_of_accounts" (
    "name"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_account_type_id" ON "book_of_accounts" (
    "account_type_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_1_id" ON "book_of_accounts" (
    "subconto_1_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_2_id" ON "book_of_accounts" (
    "subconto_2_id"
);
CREATE INDEX IF NOT EXISTS "bookofaccounts_subconoto_3_id" ON "book_of_accounts" (
    "subconto_3_id"
);
CREATE INDEX IF NOT EXISTS "subconto3_name" ON "subconto3" (
    "name"
);
CREATE INDEX IF NOT EXISTS "subconto2_name" ON "subconto2" (
    "name"
);
CREATE INDEX IF NOT EXISTS "subconto1_name" ON "subconto1" (
    "name"
);
CREATE INDEX IF NOT EXISTS "accounttype_name" ON "account_type" (
    "name"
);
COMMIT;

This problem might be related to an unanswered 6-years-old so question.

Solution

It looks like this issue comes from the use of EditStrategy.OnFieldChange.

In the documentation it says that

To prevent inserting only partly initialized rows into the database, OnFieldChange will behave like OnRowChange for newly inserted rows

But it looks like this behaviour isn't complete & the "invalid" state of the row is still preventing the lookups for the related fields. You'll notice that once the row is complete and written, it actually works fine.

If you switch the edit strategy to EditStrategy.OnRowChange it works as expected.

self.table_model.setEditStrategy(QSqlTableModel.EditStrategy.OnRowChange)

Below is an in-progress edit, where I've exited the 4th column drop down. It continues to show the value of the related field lookup.

Answered By - mfitzp