Using QComboBox as a filter for a QTableView

Deever

Hi folks!

I have a simple (sqlite) database:

create table invoice (
  id integer unique not null,
  invoiceDate integer not null,
  amount real not null,
  description text not null
);
create table invoiceItem (
  id integer unique not null,
  invoice integer not null,
  article text not null,
  amount real not null,

The invoiceItem.invoice attribute is obviously a foreign key to the item's invoice.

Now I'd like to have a QComboBox listing all invoice.description values and acting as a filter to a QTableView which is supposed to show only the invoice items for the invoice selected by the QComboBox.
Here is what I already have (using PyQt5):

class DbMvc(QWidget):
	def __init__(self):
		super().__init__()
		db = QSqlDatabase.addDatabase('QSQLITE')
		db.setDatabaseName('invoices.db')
		if not db.open():
			print('error opening db')
			return
		
		cmbInvoice = QComboBox()
		model = QSqlTableModel(self, db)
		model.setTable('invoice')
		model.select()
		cmbInvoice.setModel(model)
		cmbInvoice.setModelColumn(1)
		
		tblInvoiceItems = QTableView()
		model = QSqlTableModel(self, db)
		model.setTable('invoiceItem')
		model.select()
		tblInvoiceItems.setModel(model)
		tblInvoiceItems.hideColumn(0)
		tblInvoiceItems.hideColumn(1)

How do I link the combobox to the tableview now?
Hope it is clear what I'm trying. If not, pleas ask!

gde23

You can use a QSortFilterProxyModel, which serves as a proxy in between you model and the view.
There you need to reimplement filterAcceptsRow() where from the function depending on the QComboBox and Item true / false is returned

Deever

Maybe I don't understand the use of filterAcceptsRow(), but wouldn't it be much easier to qTableView.model().setFilter(QString filter) upon qComboBox.activated(int index)? The question then still is: how can I get the corresponding "id" for the value selected in the combobox?

SGaist

Hi,

Why not use currentTextChanged ? You'll have the text of interest directly.

gde23

filterAccespRow() will filter out the rows in the model that do not fulfill the filter requirements, so if your ID that you specify in the QComboBox is not equal to the ID of the row, then the row will not be shown in the view.

I assume that you have set some IDs to the combobox. eg
combo.addItem("ID23")
combo.addItem("ID42")
...
and so on whatever your ids are, which match the IDs in you items

then in filterAcceptRows just implement soemething like this

if row_id == como.currentText():
    return true
else: 
    return false

where get row_id comes from the model row

Deever

The comboBox is fed by a model too, and setModelColumn(3)ed for showing the invoice description instead of its id.

I've found out that I can get the right SqlRecord via model.record(idx) in the slot. And it seems to work since model.query().lastQuery() prints the expected string. However, after choosing an item from the comboBox, the tableView is cleared completely instead. How can I fix that?

class DbMvc(QWidget):
	def __init__(self):
		super().__init__()
		db = QSqlDatabase.addDatabase('QSQLITE')
		db.setDatabaseName('invoices.db')
		if not db.open():
			print('error opening db')
			return
		
		cmbInvoice = QComboBox()
		model = QSqlTableModel(self, db)
		model.setTable('invoice')
		model.select()
		cmbInvoice.setModel(model)
		cmbInvoice.setModelColumn(3)
		self._invoiceModel = model
		
		tblInvoiceItem = QTableView()
		model = QSqlTableModel(self, db)
		model.setTable('invoiceItem')
		model.select()
		tblInvoiceItem.setModel(model)
		tblInvoiceItem.hideColumn(0)
		tblInvoiceItem.hideColumn(1)
		self._invoiceItemModel = model
		
		vbox = QVBoxLayout()
		vbox.addWidget(cmbInvoice)
		vbox.addWidget(tblInvoiceItem)
		self.setLayout(vbox)

		cmbInvoice.activated.connect(self.activated)
		self.show()
		
	def activated(self, idx):
		record = self._invoiceModel.record(idx)
		invoice = record.value(0)
		model = self._invoiceItemModel
		model.setFilter('where invoice = %d'%invoice)
		model.select()
		print(model.query().lastQuery())

Deever

Could solve it via a QSortFilterProxyModel and filterAcceptRows:

class DbMvc(QWidget):
	def __init__(self):
		super().__init__()
		db = QSqlDatabase.addDatabase('QSQLITE')
		db.setDatabaseName('invoices.db')
		if not db.open():
			print('error opening db')
			return
		
		cmbInvoice = QComboBox()
		model = QSqlTableModel(self, db)
		model.setTable('invoice')
		model.select()
		cmbInvoice.setModel(model)
		cmbInvoice.setModelColumn(1)
		self._invoiceModel = model
		
		tblInvoiceItem = QTableView()
		model = QSqlTableModel(self, db)
		model.setTable('invoiceItem')
		model.select()
		pmodel = InvoiceFilter()
		pmodel.setSourceModel(model)
		model = pmodel
		tblInvoiceItem.setModel(model)
		tblInvoiceItem.hideColumn(0)
		tblInvoiceItem.hideColumn(1)
		self._invoiceItemModel = model
		
		vbox = QVBoxLayout()
		vbox.addWidget(cmbInvoice)
		vbox.addWidget(tblInvoiceItem)
		self.setLayout(vbox)

		cmbInvoice.activated.connect(self.activated)
		self.show()
	def activated(self, idx):
		record = self._invoiceModel.record(idx)
		invoice = record.value(0)
		model = self._invoiceItemModel
		model.invoice = invoice
		model.invalidate()

class InvoiceFilter(QSortFilterProxyModel):
	def __init__(self, *args, **kwargs):
		super().__init__(*args, **kwargs)
		self.invoice = 0;
	def filterAcceptsRow(self, source_row, modelIndex):
		sourceModel = self.sourceModel()
		sourceModelIndex = sourceModel.index(source_row, 1)
		return sourceModel.data(sourceModelIndex) == self.invoice

Thanks for all help!