QComboBox populated from a relation model and connected to a QDataWidgetMapperbehaves strangely when setEditable(True)

Question

I have a QComboBox populated from a relationModel of a QSqlRelationalTableModel and connected to a QDataWidgetMapper.

I select a row in the QTableView, this row (record) mapped to the QLineEdit and QComboBox widgets then I make some changes and save.

If I select another row and save without changing the QComboBox value, the value changes and submitted to the model.

I use the editable combobox not for adding items to the list, but to use the completer feature when I have a large list instead of dropping down the combobox view

Creating the db:

import sqlite3

conn = sqlite3.connect('customers.db')
c = conn.cursor()
c.execute("PRAGMA foreign_keys=on;")

c.execute("""CREATE TABLE IF NOT EXISTS provinces (
        ProvinceId TEXT PRIMARY KEY, 
        Name TEXT NOT NULL
        )""")

c.execute("""CREATE TABLE IF NOT EXISTS customers (
        CustomerId TEXT PRIMARY KEY, 
        Name TEXT NOT NULL,
        ProvinceId TEXT,
        FOREIGN KEY (ProvinceId) REFERENCES provinces (ProvinceId) 
                ON UPDATE CASCADE
                ON DELETE RESTRICT
        )""")

c.execute("INSERT INTO provinces VALUES ('N', 'Northern')")
c.execute("INSERT INTO provinces VALUES ('E', 'Eastern')")
c.execute("INSERT INTO provinces VALUES ('W', 'Western')")
c.execute("INSERT INTO provinces VALUES ('S', 'Southern')")
c.execute("INSERT INTO provinces VALUES ('C', 'Central')")

c.execute("INSERT INTO customers VALUES ('1', 'customer1', 'N')")
c.execute("INSERT INTO customers VALUES ('2', 'customer2', 'E')")
c.execute("INSERT INTO customers VALUES ('3', 'customer3', 'W')")
c.execute("INSERT INTO customers VALUES ('4', 'customer4', 'S')")
c.execute("INSERT INTO customers VALUES ('5', 'customer5', 'C')")

conn.commit()
conn.close()

and here is the window:

from PyQt5.QtWidgets import *
from PyQt5.QtSql import *

class Window(QWidget):
    def __init__(self):
        super().__init__()
        self.db = QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("customers.db")
        self.db.open()

        self.model = QSqlRelationalTableModel(self, self.db)
        self.model.setTable("customers")
        self.model.setRelation(2, QSqlRelation("provinces", "ProvinceId", "Name"))
        self.model.setEditStrategy(QSqlTableModel.EditStrategy.OnManualSubmit)
        self.model.select()
        
        self.id = QLineEdit()
        self.name = QLineEdit()
        self.province = QComboBox()
        
        # stuck here
        self.province.setEditable(True)

        self.province.setModel(self.model.relationModel(2))
        self.province.setModelColumn(1)
        self.province.setView(QTableView())

        self.mapper = QDataWidgetMapper()
        self.mapper.setItemDelegate(QSqlRelationalDelegate())
        self.mapper.setModel(self.model)
        self.mapper.addMapping(self.id, 0)
        self.mapper.addMapping(self.name, 1)
        self.mapper.addMapping(self.province, 2)

        save = QPushButton("Save")
        save.clicked.connect(self.submit)

        self.tableView = QTableView()
        self.tableView.setEditTriggers(QAbstractItemView.EditTrigger.NoEditTriggers)
        self.tableView.setSelectionBehavior(QTableView.SelectionBehavior.SelectRows)
        self.tableView.setModel(self.model)

        self.tableView.clicked.connect(lambda: self.mapper.setCurrentModelIndex(self.tableView.currentIndex()))

        vBox = QVBoxLayout()
        vBox.addWidget(self.id)
        vBox.addWidget(self.name)
        vBox.addWidget(self.province)
        vBox.addSpacing(20)
        vBox.addWidget(save)
        vBox.addWidget(self.tableView)

        self.setLayout(vBox)
        self.mapper.toFirst()

    def submit(self):
        self.mapper.submit()
        self.model.submitAll()


def main():
    import sys
    App = QApplication(sys.argv)
    window = Window()
    window.show()
    sys.exit(App.exec_())


if __name__ == '__main__':
    main()

Answer 1

An important thing to consider is that item delegates (and, specifically, QSqlRelationalDelegate) use the widget's user property to read and write data from and to the widget.

The user property of QComboBox is currentText; if it's not editable, its value is an empty string (for -1 index) or the current item's text, and setting that property results in the combo trying to look for the first item that fully matches that text, and changes the current index if a match is found.
When the combo is editable, though, only the text is changed, not the current index, and it's also possible to set

Now, after some digging, I found various "culprits" to the issue you're facing.

QDataWidgetMapper uses the EditRole to both commit data and populate widgets. This clearly represents a problem since the edit role is what the relational model uses for the actual data set on the model (eg. "S" for Southern), while the display role is what is used to display the related value.

The result of all the above aspects is that, assuming the combo is not changed by the user:

1. the mapper tries to set the data, based on the current delegate editor with setModelData();
2. the delegate uses the current index (not the current text!) to get both the display and edit role to be set on the model;
3. the model tries to set both values, but will only be able to set the edit role due to its relational nature;
4. the data changed causes the mapper to repopulate the widgets;
5. the display value based on the combo index is then set to the widget using setEditorData();

Also note that, until Qt 5.12 (see QTBUG-59632), the above caused a further issue as the default implementation of setEditorData uses the edit role, so the editable combo would also get the related letter instead of the actual value display.

Considering the above, there are two options:

• subclass QSqlRelationalDelegate and properly implement setModelData() by matching the current text and using the relation model

from PyQt5.QtCore import *
_version = tuple(map(int, QT_VERSION_STR.split('.')))

class Delegate(QSqlRelationalDelegate):
    def setModelData(self, editor, model, index):
        if isinstance(editor, QComboBox):
            value = editor.currentText()
            if not value:
                return
            childModel = model.relationModel(index.column())
            for column in range(2):
                match = childModel.match(childModel.index(0, column), 
                    Qt.DisplayRole, value, Qt.MatchStartsWith)
                if match:
                    match = match[0]
                    displayValue = match.sibling(match.row(), 1).data()
                    editValue = match.sibling(match.row(), 0).data()
                    model.setData(index, displayValue, Qt.DisplayRole)
                    model.setData(index, editValue, Qt.EditRole)
                    return
        super().setModelData(editor, model, index)

    if _version[1] < 12:
        # fix for old Qt versions that don't properly update the QComboBox
        def setEditorData(self, editor, index):
            if isinstance(editor, QComboBox):
                value = index.data()
                if isinstance(value, str):
                    propName = editor.metaObject().userProperty().name()
                    editor.setProperty(propName, value)
            else:
                super().setEditorData(editor, index)

• subclass QComboBox and ensure that it properly updates the index with the current text, by using a new user property; this still requires implementing setModelData to override the default behavior for QComboBox

class MapperCombo(QComboBox):
    @pyqtProperty(str, user=True)
    def mapperText(self):
        text = self.currentText()
        if text == self.currentData(Qt.DisplayRole):
            return text
        model = self.model()
        for column in range(2):
            match = model.match(model.index(0, column), 
                Qt.DisplayRole, text, Qt.MatchStartsWith)
            if match:
                self.setCurrentIndex(match[0].row())
                return self.currentText()
        return self.itemText(self.currentIndex())

    @mapperText.setter
    def mapperText(self, text):
        model = self.model()
        for column in range(2):
            match = model.match(model.index(0, column), 
                Qt.DisplayRole, text, Qt.MatchStartsWith)
            if match:
                index = match[0].row()
                break
        else:
            index = 0
        if index != self.currentIndex():
            self.setCurrentIndex(index)
        else:
            self.setCurrentText(self.currentData(Qt.DisplayRole))

    @property
    def mapperValue(self):
        return self.model().data(self.model().index(
            self.currentIndex(), 0), Qt.DisplayRole)


class Delegate(QSqlRelationalDelegate):
    def setModelData(self, editor, model, index):
        if isinstance(editor, MapperCombo):
            model.setData(index, editor.mapperText, Qt.DisplayRole)
            model.setData(index, editor.mapperValue, Qt.EditRole)
        else:
            super().setModelData(editor, model, index)

Finally, a QLineEdit with a proper QCompleter could be used, but this still requires subclassing the delegate, as setModelData needs to use the proper string.

class Delegate(QSqlRelationalDelegate):
    def setModelData(self, editor, model, index):
        if model.relation(index.column()).isValid():
            value = editor.text()
            if value:
                childModel = model.relationModel(index.column())
                match = childModel.match(childModel.index(0, 1), 
                    Qt.DisplayRole, value, Qt.MatchStartsWith)
                if match:
                    childIndex = match[0]
                    model.setData(index, childIndex.data(), Qt.DisplayRole)
                    model.setData(index, 
                        childIndex.sibling(childIndex.row(), 0).data(), Qt.EditRole)
                    editor.setText(childIndex.data())
        else:
            super().setModelData(editor, model, index)

Some further notes and suggestions:

1. if the mapped data is visible, it's preferable to use the ManualSubmit policy (self.mapper.setSubmitPolicy(self.mapper.ManualSubmit)), alternatively, you could subclass the model and find ways to visually display modified cells until the changes are submitted;
2. there's no need for a lambda to update the current index when clicking, since clicked already provides the new index: self.tableView.clicked.connect(self.mapper.setCurrentModelIndex)
3. submitting the model will cause the mapper to reset the current index, with the result that a further editing (without selecting a new item from the table) will be ignored, so you should restore it after changes have been applied:

    def submit(self):
        current = self.mapper.currentIndex()
        self.mapper.submit()
        self.model.submitAll()
        self.mapper.setCurrentIndex(current)