0

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()
embabi
  • 11
  • 1
  • 4
  • Could you explain better, what is the problem? You could place in detail the procedure to reproduce the error, it would also be useful to indicate what you hope to obtain. What is the purpose of the editable QComboBox? It is understood that a QComboBox in editable mode serves to add items to the model (in this case to the database) and it does so but the problem (at least when viewing the database) is that a valid "provinceId" is not created – eyllanesc Aug 03 '21 at 15:46
  • @eyllanesc I update the post with more details to produce the issue. This reproducible example is not exactly what I'm implementing, but it reproduces exactly the issue I'm facing. – embabi Aug 03 '21 at 16:18
  • I think it is better to use QLineEdit + QCompleter instead of an editable QComboBox but I have a question: let's say that the user does not write the whole word for example "Eas" or write an unrelated word for example "xxx", when is pressed the save button then what should happen? Should the change be saved or not? – eyllanesc Aug 03 '21 at 16:28
  • @eyllanesc If the entered word is not in the list, it shouldn't be saved. – embabi Aug 03 '21 at 16:35
  • @embabi what version of Qt do you have? Is it older than 5.12? – musicamante Aug 06 '21 at 21:10
  • @musicamante I'm using Qt 5.15.2 – embabi Aug 07 '21 at 07:02

1 Answers1

0

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)
musicamante
  • 41,230
  • 6
  • 33
  • 58