How to filter items of a combobox in tableview and QSqlRelationalTableModel?

[yousef]

Hi everybody. i know this forum is for c++ but as it is related to Qt i asked it here may somebody help.

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))

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))

To copy to clipboard, switch view to plain text mode 

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_())

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_())

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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.

[d_stranz]

i know this forum is for c++ but as it is related to Qt

The forum is for any Qt-related programming question. Because of Qt's history, most of the Q&A is about C++, but Python + Qt is becoming more popular so questions about PyQt (and other bindings) are welcome.

I am neither a Python nor SQL expert, so I hope someone can give you an answer.

[yousef]

Thank you d_stranz

I found a way. everything work as I expected except one thing. I did this by re implementing QSqlRelationalDelegate as this:

class mydelegate(QSqlRelationalDelegate):
    def __init__(self, parent=None):
        super().__init__()
        self.tab=parent
 
    def setEditorData(self,editor,index):           
        if index.column()==2:
            test= ((self.tab.selectionModel().currentIndex().siblingAtColumn(1).data()))
            editor.model().setFilter("type_IDs like '"+test+"%'")
        return super().setEditorData(editor,index)

class mydelegate(QSqlRelationalDelegate):
    def __init__(self, parent=None):
        super().__init__()
        self.tab=parent
    
    def setEditorData(self,editor,index):           
        if index.column()==2:
            test= ((self.tab.selectionModel().currentIndex().siblingAtColumn(1).data()))
            editor.model().setFilter("type_IDs like '"+test+"%'")
        return super().setEditorData(editor,index)

To copy to clipboard, switch view to plain text mode 

the only problem remains is changing an item in column 1 doesn't changes items in column 2 automatically and I have to modify second Column manually else it remains wrong option.
for example if column 1 be "IC" and Column 2 be "Timer" and then i change column 1 to "Transistor" column 2 remains "Timer" unless I edit that too.

how to solve this?