python-3.xpyqt5qtableviewqsortfilterproxymodelqsqltablemodel

How to search data QSqlTableModel, Qtableview from QlineEdit with the help of QcomboBox?


I am using QSqlTableModel, (FilterProxyModel) as custom proxy modelwith QtCore.QSortFilterProxyModel, QTableView,Qcombobox.

self.lineEdit       = QtWidgets.QLineEdit(self.centralwidget)
self.view           = QtWidgets.QTableView(self.centralwidget)
self.comboBox       = QtWidgets.QComboBox(self.centralwidget)

Below code is the custom filter class to get menu of Columns from tableview :

class FilterProxyModel(QtCore.QSortFilterProxyModel):
    def __init__(self, parent=None):
        super().__init__(parent)
        self._filter_value = None

    @property
    def filter_value(self):
        return self._filter_value

    @filter_value.setter
    def filter_value(self, value):
        self._filter_value = value
        self.invalidateFilter()

    def filterAcceptsRow(self, sourceRow, sourceParent):
        if self.filter_value is None:
            return True
        value = (
            self.sourceModel()
            .index(sourceRow, self.filterKeyColumn(), sourceParent)
            .data(self.filterRole())
        )
        return value == self.filter_value

Below is the database model:

    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("model.db")
    db.open()

    self.model = QSqlTableModel(self)
    self.model.setTable("sheet")
    self.model.select()
    self.view.setModel(self.model)

    self.proxy = FilterProxyModel(self)###
    self.proxy.setSourceModel(self.model)

    self.view.setModel(self.proxy)

Below is the details of my question

    column_names = (["All","Name", "Age", "Adress"])
    self.comboBox.addItems([x for x in column_names])


@QtCore.pyqtSlot(str)
def msa_lineEdit_textChanged(self, text):
    self.proxy = QtCore.QSortFilterProxyModel(self)
    self.proxy.setSourceModel(self.model)
    self.view.setModel(self.proxy)
    search = QtCore.QRegExp(    text,
                                QtCore.Qt.CaseInsensitive,
                                QtCore.QRegExp.RegExp
                                )

    self.proxy.setFilterRegExp(search)

for example : I tryied with above function with QtCore.QRegExp.RegExp its showing the data in first column, selected "all" in Qcombobox.

I added items "All","Name", "Age", and "Adress" in the combobox, and I am trying to search or filter data from QlineEdit with using of QcomboBox as if I select "All" in cobobox and when I am typing words in QlineEdit that should be filter data from all columns.

If I select "Name" or "Age", or "Adress" on QcomboBox that should be filter data as per the column name selection in the Qcombobox. My database have String and as well as Int Values. Are there any examples available for this?

enter image description here


Solution

  • You have to use the filterKeyColumn properly:

    This property holds the column where the key used to filter the contents of the source model is read from.

    The default value is 0. If the value is -1, the keys will be read from all columns.

    So you can connect both the textChanged signal of the line edit and currentIndexChanged of the combo to the function, then set the filter accordingly:

    def msa_lineEdit_textChanged(self):
        search = QtCore.QRegExp(self.lineEdit.text(), 
            QtCore.Qt.CaseInsensitive, QtCore.QRegExp.RegExp)
        self.proxy.setFilterKeyColumn(self.combo.currentIndex() - 1)
        self.proxy.setFilterRegExp(search)
    

    Note that your custom proxy won't work as expected if you're using setFilterRegExp and that's because you have overridden the filterAcceptsRow method.

    You should change the implementation like this:

        def filterAcceptsRow(self, sourceRow, sourceParent):
            if self.filter_value is None:
                return super().filterAcceptsRow(sourceRow, sourceParent)
            if self.filterKeyColumn() >= 0:
                value = (
                    self.sourceModel()
                    .index(sourceRow, self.filterKeyColumn(), sourceParent)
                    .data(self.filterRole())
                )
                return value == self.filter_value
            for column in range(self.columnCount()):
                value = (
                    self.sourceModel()
                    .index(sourceRow, column, sourceParent)
                    .data(self.filterRole())
                )
                if value == self.filter_value:
                    return True
            return False
    

    You should also clear the property whenever the regex filter is applied:

        def setFilterRegExp(self, filter):
            self.filter_value = None
            super().setFilterRegExp(filter)