QSortFilterProxyModel sorts only the fetched data

Question

I have a QTableView with a QSortFilterProxyModel as its model which has a QSqlRelationalTableModel as its source model.

When I sort or filter the viewed data through the QSortFilterProxyModel, only the fetched data is affected.

I managed to get it work by fetching all data from the table by using

if model.canfetchMore():
   model.fetchMore()

The problem is the table has a large number of records (200,000 records).

Is there any other way to sort and filter the proxymodel without fetching all the data from the database?

Answer 1

You probably work with Sqlite as you have to use fetchMore().

When you work with large number of records, it's slow to load 200.000 records or so from database at once, and it's not fetchMore() that's problematic, but just loading that quantity of data from database is slow. It is slow even when you work with PostgreSQL where you don't have to use fetchMore(). So I use a trick to have all records, but don't have to wait too long. I split (the same) table into two views, I call it parent and child:

import sys
import random

from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtSql import *

class Window(QWidget):
    def __init__(self, parent=None):
        #QWidget.__init__(self, parent)
        super(Window, self).__init__(parent)

        self.search_label = QLabel("Name:")
        self.search_lineedit = QLineEdit('')

        self.parent_model = QSqlQueryModel(self)
        self.refresh_parent()
        self.parent_proxy_model = QSortFilterProxyModel()
        self.parent_proxy_model.setSourceModel(self.parent_model)
        self.parent_view = QTableView()
        self.parent_view.setModel(self.parent_proxy_model)
        self.parent_view.setSelectionMode(QTableView.SingleSelection)
        self.parent_view.setSelectionBehavior(QTableView.SelectRows)
        self.parent_view.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.parent_view.horizontalHeader().setStretchLastSection(True)
        self.parent_view.verticalHeader().setVisible(False)
        self.parent_view.setSortingEnabled(True)
        self.parent_view.horizontalHeader().setSortIndicator(0, Qt.AscendingOrder)
        self.parent_view.setAlternatingRowColors(True)
        self.parent_view.setShowGrid(False)
        #self.parent_view.verticalHeader().setDefaultSectionSize(24)
        self.parent_view.setStyleSheet("QTableView::item:selected:!active { selection-background-color:#BABABA; }")
        for i, header in enumerate(self.parent_headers):
            self.parent_model.setHeaderData(i, Qt.Horizontal, self.parent_headers[self.parent_view.horizontalHeader().visualIndex(i)])
        self.parent_view.resizeColumnsToContents()

        self.child_model = QSqlQueryModel(self)
        self.refresh_child()
        self.child_proxy_model = QSortFilterProxyModel()
        self.child_proxy_model.setSourceModel(self.child_model)
        self.child_view = QTableView()
        self.child_view.setModel(self.child_proxy_model)
        self.child_view.setSelectionMode(QTableView.SingleSelection)
        self.child_view.setSelectionBehavior(QTableView.SelectRows)
        self.child_view.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.child_view.horizontalHeader().setStretchLastSection(True)
        self.child_view.verticalHeader().setVisible(False)
        self.child_view.setSortingEnabled(True)
        self.child_view.horizontalHeader().setSortIndicator(1, Qt.AscendingOrder)
        self.child_view.setAlternatingRowColors(True)
        self.child_view.setShowGrid(False)
        #self.child_view.verticalHeader().setDefaultSectionSize(24)
        self.child_view.setStyleSheet("QTableView::item:selected:!active { selection-background-color:#BABABA; }")
        for i, header in enumerate(self.child_headers):
            self.child_model.setHeaderData(i, Qt.Horizontal, self.child_headers[self.child_view.horizontalHeader().visualIndex(i)])
        self.child_view.resizeColumnsToContents()

        search_layout = QHBoxLayout()
        search_layout.addStretch()
        search_layout.addWidget(self.search_label)
        search_layout.addWidget(self.search_lineedit)

        view_layout = QHBoxLayout()
        view_layout.addWidget(self.parent_view)
        view_layout.addWidget(self.child_view)

        layout = QVBoxLayout(self)
        layout.addLayout(search_layout)
        layout.addLayout(view_layout)
        #self.setLayout(layout)

        self.parent_view.selectionModel().currentRowChanged.connect(self.parent_changed)
        self.search_lineedit.textChanged.connect(self.search_changed)

        self.parent_view.setCurrentIndex(self.parent_view.model().index(0, 0))
        self.parent_view.setFocus()

    def refresh_parent(self):
        self.parent_headers = ['Category']
        query_string = "SELECT category FROM parent"
        query = QSqlQuery()
        query.exec(query_string)
        self.parent_model.setQuery(query)
        while self.parent_model.canFetchMore():
            self.parent_model.fetchMore()

    def refresh_child(self, category=''):
        self.child_headers = ['Category', 'Name', 'Description']
        query_string = ("SELECT category, name, description FROM child "
            "WHERE child.category = '{category}'").format(category = category)
        query = QSqlQuery()
        query.exec(query_string)
        self.child_model.setQuery(query)
        while self.child_model.canFetchMore():
            self.child_model.fetchMore()

    def parent_changed(self, index):
        if index.isValid():
            index = self.parent_proxy_model.mapToSource(index)
            record = self.parent_model.record(index.row())
            self.refresh_child(record.value("parent.category"))
            #self.child_view.scrollToBottom() # if needed

    def search_changed(self, text):
        query_string = ("SELECT category, name FROM child WHERE name = '{name}'").format(name = text)
        query = QSqlQuery()
        query.exec(query_string)
        if query.next():
            category = query.value('category')
            start = self.parent_proxy_model.index(0, 0)
            matches = self.parent_proxy_model.match(start, Qt.DisplayRole, category, 1, Qt.MatchExactly) # Qt.MatchExactly # Qt.MatchStartsWith
            if matches:
                print('parent matches')
                index = matches[0]
                self.parent_view.selectionModel().select(index, QItemSelectionModel.Select)
                self.parent_view.setCurrentIndex(index)
                self.refresh_child(category)
                #------------------------------------------------
                start = self.child_proxy_model.index(0, 1)
                matches = self.child_proxy_model.match(start, Qt.DisplayRole, text, 1, Qt.MatchExactly) # Qt.MatchExactly # Qt.MatchStartsWith
                if matches:
                    print('child matches')
                    index = matches[0]
                    self.child_view.selectionModel().select(index, QItemSelectionModel.Select)
                    self.child_view.setCurrentIndex(index)
                    self.child_view.setFocus()

def create_fake_data():
    categories = []
    #import random
    query = QSqlQuery()
    query.exec("CREATE TABLE parent(category TEXT)")
    for i in range(1, 201):
        category = str(i).zfill(3)
        categories.append(category)
        query.prepare("INSERT INTO parent (category) VALUES(:category)")
        query.bindValue(":category", category)
        query.exec()
    query.exec("CREATE TABLE child(category TEXT, name TEXT, description TEXT)")
    counter = 1
    for category in categories:
        for i in range(1, 1001):
            name = str(counter).zfill(6)
            description = str(random.randint(1,100)).zfill(3)
            counter += 1
            query.prepare("INSERT INTO child (category, name, description) VALUES(:category, :name, :description)")
            query.bindValue(":category", category)
            query.bindValue(":name", name)
            query.bindValue(":description", description)
            query.exec()

def create_connection():
    db = QSqlDatabase.addDatabase("QSQLITE")
    #db.setDatabaseName('test.db')
    db.setDatabaseName(":memory:")
    db.open()
    create_fake_data()
    print('database is full, now starting a program...')

app = QApplication(sys.argv)
create_connection()
window = Window()
#window.resize(800, 600)
#window.show()
window.showMaximized()
app.exec()

It is possible only if you have an attribute (or a prefix of an attribute, or more than one attributes) that you can use as a "category".

This program loads slowly, but only database input. Once database is full, the program works fast. This is just an example, in real app you have your database full.

With this trick, you can handle 1.000.000 records, just use 1.000 x 1.000 (or 200 x 5.000, or some other combination). The problem is that search and filter are harder to implement this way. I showed one way how to implement search.

Regarding "Why don't you implement filtering and sorting using SQL" comment, that's because sql can only return data in one order and one (fixed) filter, and with QSortFilterProxyModel you can sort and filter data additionally any way you like and way faster than using sql once you have your data in models.