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

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.

I am also using QSortFilterProxyModel to enable sorting. Therefore, I have overriden the QSqlRelationalDelegate to address the issue with changing indexes.

Here is the main.py that draws the widgets:

import sys

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

from custom_delegates import SortSupportQSqlRelationalDelegate
from custom_qt_models import EmptyTextReplacerQSortFilterProxyModel


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('QPSQL')
        database.setHostName('localhost')
        database.setPort(5432)
        database.setDatabaseName('ars_default')
        database.setUserName('hamlet')
        database.setPassword('qwerty')
        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)
        table_name = 'book_of_accounts'
        self.table_name = table_name
        self.table_model.setTable(table_name)

        self.set_relations()

        m = EmptyTextReplacerQSortFilterProxyModel()
        m.setDynamicSortFilter(True)
        m.setSourceModel(self.table_model)

        self.tableView.setModel(m)

        self.tableView.setSortingEnabled(True)
        self.tableView.setItemDelegate(SortSupportQSqlRelationalDelegate(self.tableView))

        self.table_model.setEditStrategy(QSqlTableModel.EditStrategy.OnFieldChange)
        self.table_model.select()

        self.tableView.hideColumn(0)

        self.retranslateUi(main)
        QtCore.QMetaObject.connectSlotsByName(main)

    def retranslateUi(self, main):
        _translate = QtCore.QCoreApplication.translate
        main.setWindowTitle(_translate("main", "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()

        next_id = self.get_next_id(self.table_name)
        # A workaround to the problem described in my other so question [link is under this code block]
        self.table_model.setData(self.table_model.index(count, 0), next_id, Qt.ItemDataRole.EditRole)
        self.tableView.updateGeometry()
        self.tableView.selectRow(count)

    def get_next_id(self, table_name):
        max_id = -2
        query = QSqlQuery()
        query.exec(f"SELECT MAX(id) FROM {table_name}")
        if query.next():
            print(f"{query.value(0)=}")
            max_id = query.value(0)

        return max_id + 1

    def set_relations(self):
        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'))


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())

Here is the link to the question mentioned in the comment above

The implementation of the Custom delegate (SortSupportQSqlRelationalDelegate.py):

from PyQt6 import QtGui, QtCore
from PyQt6.QtCore import QSortFilterProxyModel
from PyQt6.QtSql import QSqlRelationalDelegate, QSqlRelationalTableModel
from PyQt6.QtWidgets import QComboBox, QStyleOptionViewItem


class SortSupportQSqlRelationalDelegate(QSqlRelationalDelegate):
    """
    When using QSortFilterProxyModel along with QSqlRelationalModel, columns holding relational data need to
    change indexes to source model indexes. That is why override createEditor, setEditorData and setModelData.
    """
    def __init__(self, *args, **kwargs):
        super(SortSupportQSqlRelationalDelegate, self).__init__(*args, **kwargs)

    def createEditor(self, parent, option, index):
        proxy = index.model()
        print(type(proxy))
        if not isinstance(proxy, QSqlRelationalTableModel):
            base_index = proxy.mapToSource(index)
        else:
            base_index = index
        editor = None

        if index.column() in (4, 7, 8, 9):
            editor = super(SortSupportQSqlRelationalDelegate, self).createEditor(parent, option, base_index)
        else:
            editor = super(SortSupportQSqlRelationalDelegate, self).createEditor(parent, option, index)

        return editor

    def setEditorData(self, editor, index):

        if index.column() in (4, 7, 8, 9):
            proxy = index.model()
            base_index = proxy.mapToSource(index)
            return super(SortSupportQSqlRelationalDelegate, self).setEditorData(editor, base_index)
        else:
            return super(SortSupportQSqlRelationalDelegate, self).setEditorData(editor, index)

    def setModelData(self, editor: QComboBox, model: QSortFilterProxyModel, index):

        if index.column() in (4, 7, 8, 9):
            base_model = model.sourceModel()
            base_index = model.mapToSource(index)

            # This shows the row number instead of value when adding new row
            print(base_model.data(base_index))

            return super(SortSupportQSqlRelationalDelegate, self).setModelData(editor, base_model, base_index)
        else:
            return super(SortSupportQSqlRelationalDelegate, self).setModelData(editor, model, index)

    def paint(self, painter: QtGui.QPainter, option: 'QStyleOptionViewItem', index: QtCore.QModelIndex) -> None:
        """
        Align text in cells except for column 2
        """
        if index.column() != 2:
            option.displayAlignment = QtCore.Qt.AlignmentFlag.AlignCenter

        return super().paint(painter, option, index)

I also have used a custom QSortFilterProxyModel to replace some displayed texts. This code does not affect the problem. I have tried to use ordinary QSortFilterProxyModel and even removed it, but the issue remained.

EmptyTextReplacerQSortFilterProxyModel.py:

from PyQt6.QtCore import Qt, QSortFilterProxyModel


class EmptyTextReplacerQSortFilterProxyModel(QSortFilterProxyModel):
    """
    This proxy model makes the '< Ничего >' strings to be displayed as empty strings.
    """
    def __init__(self, *args, **kwargs):
        super(EmptyTextReplacerQSortFilterProxyModel, self).__init__(*args, **kwargs)

    def data(self, index, role=None):

        if super(EmptyTextReplacerQSortFilterProxyModel, self).data(index, Qt.ItemDataRole.DisplayRole) != '< Ничего >':
            return super(EmptyTextReplacerQSortFilterProxyModel, self).data(index, role)

        if role == Qt.ItemDataRole.EditRole or role != Qt.ItemDataRole.DisplayRole:
            return super(EmptyTextReplacerQSortFilterProxyModel, self).data(index, role)

        return ""

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

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