I need to sort records displayed in a QTableView that uses a QSqlRelationalTableModel with QSqlRelationalDelegate. I want the values in the pull down list and the table view to be sorted.
I've done a lot of searching but have not found examples that use QSortFilterProxyModel with QSqlRelationalTableModel and QSqlRelationalDelegate. My attempts have not provided the sorting and have disabled the read-write feature that I require from QSqlRelationalTableModel and have disabled the drop down list also.
Can the table and drop down list be sorted?
import sys
from PyQt5.QtCore import QSortFilterProxyModel
from PyQt5.QtWidgets import QApplication, QDialog, QTableView, QHBoxLayout
from PyQt5.QtSql import (QSqlDatabase, QSqlTableModel, QSqlRelationalTableModel,
QSqlQuery, QSqlRelation, QSqlRelationalDelegate)
def createDb():
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
if not db.open():
print("Cannot establish a database connection.")
return False
query = QSqlQuery()
query.exec("DROP TABLE IF EXISTS zoos")
query.exec("CREATE TABLE zoos (id INTEGER PRIMARY KEY, zoo_name CHAR)")
query.exec("INSERT INTO zoos (zoo_name) VALUES ('Animal Town')")
query.exec("INSERT INTO zoos (zoo_name) VALUES ('Wild Things')")
query.exec("INSERT INTO zoos (zoo_name) VALUES ('Creatures Everywhere')")
query.exec("DROP TABLE IF EXISTS animals")
query.exec("CREATE TABLE animals (id INTEGER PRIMARY KEY, animal_type CHAR)")
query.exec("INSERT INTO animals(animal_type) VALUES('cougar')")
query.exec("INSERT INTO animals(animal_type) VALUES('cobra')")
query.exec("INSERT INTO animals(animal_type) VALUES('buzzard')")
query.exec("INSERT INTO animals(animal_type) VALUES('anteater')")
query.exec("INSERT INTO animals(animal_type) VALUES('baboon')")
query.exec("INSERT INTO animals(animal_type) VALUES('aardvark')")
query.exec("INSERT INTO animals(animal_type) VALUES('skunk')")
query.exec("INSERT INTO animals(animal_type) VALUES('sheep')")
query.exec("INSERT INTO animals(animal_type) VALUES('skink')")
query.exec("INSERT INTO animals(animal_type) VALUES('ferret')")
query.exec("INSERT INTO animals(animal_type) VALUES('swallow')")
query.exec("INSERT INTO animals(animal_type) VALUES('swan')")
query.exec("INSERT INTO animals(animal_type) VALUES('gecko')")
query.exec("INSERT INTO animals(animal_type) VALUES('gerbil')")
query.exec("DROP TABLE IF EXISTS residences")
query.exec("CREATE TABLE residences (id INTEGER PRIMARY KEY, "
"zoo_id INTEGER, animal_id INTEGER, "
"FOREIGN KEY (zoo_id) REFERENCES zoo(id), "
"FOREIGN KEY (animal_id) REFERENCES animals(id))")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (1,1)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (1,2)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (1,3)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (1,4)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (2,5)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (2,6)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (2,7)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (2,8)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (3,9)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (3,10)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (3,11)")
query.exec("INSERT INTO residences(zoo_id, animal_id) VALUES (3,12)")
return True
class MainForm(QDialog):
def __init__(self):
super().__init__()
self.zoosModel = QSqlTableModel(self)
self.zoosModel.setTable("zoos")
self.zoosModel.select()
self.zoosView = QTableView()
self.zoosView.setModel(self.zoosModel)
self.zoosView.setColumnHidden(0, True)
self.zoosView.selectionModel().currentRowChanged.connect(self.zoosRowChanged)
self.residencesModel = QSqlRelationalTableModel()
self.residencesModel.setTable("residences")
# self.sort_animals = QSortFilterProxyModel(self)
# self.sort_animals.setSourceModel(self.residencesModel)
# self.sort_animals.sort(1)
self.residencesModel.setRelation(2, QSqlRelation("animals", "id", "animal_type"))
self.residencesModel.select()
self.residencesView = QTableView()
self.residencesView.setModel(self.residencesModel)
# self.residencesView.setModel(self.sort_animals)
self.residencesView.setItemDelegate(QSqlRelationalDelegate(self.residencesView))
self.residencesView.setColumnHidden(0, True)
self.residencesView.setColumnHidden(1, True)
hbox = QHBoxLayout()
hbox.addWidget(self.zoosView)
hbox.addWidget(self.residencesView)
self.setLayout(hbox)
def zoosRowChanged(self, index):
if index.isValid():
record = self.zoosModel.record(index.row())
id = int(record.value("id"))
self.residencesModel.setFilter("zoo_id = {}".format(id))
else:
self.info_detailsModel.setFilter("zoo_id = -1")
if __name__ == "__main__":
app = QApplication(sys.argv)
createDb()
w = MainForm()
w.show()
sys.exit(app.exec_())
