How to filter items of a combobox in tableview and QSqlRelationalTableModel?
-
Hi everybody.
I am trying learning PyQt5. I made an app. In my app I have a tableview that is set as below:
self.table = QTableView() self.model = QSqlRelationalTableModel(self) self.model.setTable('main') self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.setRelation(1, QSqlRelation("Names", "nameOfCm", "nameOfCm")) self.model.setRelation(2, QSqlRelation("Types", "type_IDs", "typeOfCm")) self.model.setRelation(8, QSqlRelation("Places", "placeName", "placeName")) self.model.select() self.table.setModel(self.model) self.table.setItemDelegate(QSqlRelationalDelegate(self.table))
By using QSqlRelationalDelegate I have comboboxes in some columns of the table.
Column No:1 is the name of some electronic components (like Transistor Or IC etc.).
Column No:2 is the type of this electronic components (For example PNP, NPNOp-Amp, Micro and so on).
When I click on combobox in column "type"(Column No:2) it shows all type of electronic components no matter what I have selected in combobox column "name" (Column No:1).
I want to filter these items in such a way that only related items for combobox "name" show in the combobox "type".For example If I choose transistor from "name", combobox "type" shows: NPN, PNP.
And if I choose IC, combobox "type" shows: Micro, Op_Amp.what should I do ?
This is simplified code( you need to ad components by clicking on "add components" button above the table:
import sys from PyQt5.QtCore import Qt from PyQt5.QtWidgets import * from PyQt5.QtSql import * connectDB = QSqlDatabase.addDatabase("QSQLITE") connectDB.setDatabaseName('test.db') connectDB.open() query = QSqlQuery() createNameTable='CREATE TABLE IF NOT EXISTS "Names" ("nameOfCm" TEXT, PRIMARY KEY("nameOfCm"))' createTypeTable='CREATE TABLE IF NOT EXISTS "Types" ("type_IDs" TEXT, "typeOfCm" TEXT,PRIMARY KEY("type_IDs"))' createMainTable='CREATE TABLE IF NOT EXISTS "main" ("partnumber" TEXT,"name" TEXT,"type" TEXT,PRIMARY KEY("partnumber"))' query.exec(createNameTable) query.exec(createTypeTable) query.exec(createMainTable) query.exec("INSERT INTO Names (nameOfCm) VALUEs ('IC')") query.exec("INSERT INTO Names (nameOfCm) VALUEs ('Transistor')") query.exec("INSERT INTO Types (type_IDs,typeOfCm) VALUEs ('Transistor1','NPN')") query.exec("INSERT INTO Types (type_IDs,typeOfCm) VALUEs ('Transistor2','PNP')") query.exec("INSERT INTO Types (type_IDs,typeOfCm) VALUEs ('IC1','Op_AMP')") query.exec("INSERT INTO Types (type_IDs,typeOfCm) VALUEs ('IC2','Micro')") class main (QMainWindow): def __init__(self): super(main,self).__init__() self.setWindowTitle('My Electronic Inventory v: 0.1') self.setGeometry(10, 10, 400 , 500) self.UI() self.show() self.databaseIsConect() def UI(self): self.mainCentralWidget = QWidget() self.mainLayout = QVBoxLayout() self.setCentralWidget(self.mainCentralWidget) self.centralWidget().setLayout(self.mainLayout) self.newComponentButton= QPushButton("Add component") self.newComponentButton.clicked.connect(self.newComponentButtonClicked) self.table= QTableView() self.model = QSqlRelationalTableModel(self) self.model.setTable('main') self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.setRelation(1, QSqlRelation("Names", "nameOfCm", "nameOfCm")) self.model.setRelation(2, QSqlRelation("Types", "type_IDs", "typeOfCm")) self.model.select() self.table.setModel(self.model) self.table.setItemDelegate(QSqlRelationalDelegate(self.table)) self.table.setEditTriggers(QAbstractItemView.DoubleClicked|QAbstractItemView.AnyKeyPressed) self.model.setHeaderData(0,Qt.Horizontal,('Part Number')) self.model.setHeaderData(1,Qt.Horizontal,('Name')) self.model.setHeaderData(2,Qt.Horizontal,('Type')) self.mainLayout.addWidget(self.newComponentButton) self.mainLayout.addWidget(self.table) def databaseIsConect(self): if connectDB.isOpen(): print("Databsae Is Connected") else: print("Erorr!. Databsae Is Not Connected") def newComponentButtonClicked(self): row = self.model.rowCount() self.model.insertRow(row) if __name__ == '__main__': app = QApplication(sys.argv) ex = main() sys.exit(app.exec_())
After some try, I managed to solve part of my problem by sub-classing QSqlRelationalTableModel:
class mysqlmodel(QSqlRelationalTableModel): def __init__(self, parent): super().__init__() def setData(self, item, value, role): super().setData(item,value, role) a= item.siblingAtColumn(1).data() self.relationModel(2).setFilter("type_IDs like '"+a+"%'") return True
This works for my purpose, but there are issues:
At first run, if you want to change an item in combobox 2, it shows all options, but after modifications it shows just item belongs to item in combobox 1.
If you change an item in combobox 2 of row no 2, then you want to change an item in combobox 2 of row 3, it shows options for the "PREVIOUS" row unless you submit that(for example by pressing enter button or clicking somewhere else within the table) and then automatically it changes to options for current row, and now you can select the right item. -
hi
i managed to solve my problem by re implementing QSqlRelationalTableModel::class mydelegate(QSqlRelationalDelegate): def __init__(self, parent=None): super().__init__() self.tab=parent def setEditorData(self,editor,index): if index.column()==1: pass if index.column()==2: test= ((self.tab.selectionModel().currentIndex().siblingAtColumn(1).data())) editor.model().setFilter("type_IDs like '"+test+"%'") return super().setEditorData(editor,index)
there is only one thing remain. what i can write after first condition (instead of pass) to set the items for second column ??