QSqlRelationalModel and multiple foreign keys/ComboBoxes
-
I'm reasonably new to PySide6 & I'm having major problems getting my head around QSqlRelationalTable Model, in particular with ComboBoxes. I'm using MariaDB & running under Ubuntu22.04. I have a form as follows.
As far as I can tell, under linux at least, a combobox displays as many records as will fit on the screen, regardless of the size of the parent window (looking really ugly) unless you set it as editable.
I would like each combobox to display in alphabetical order. The code below works but if I set the comboboxes to be editable (which I don't really want or need), clicking on the genre combo changes the album combo to display its first alpha record. Just the act of opening the genre combo causes this to happen.
What have I got wrong? Why is this happening & how can I prevent it, short of putting up with ugly comboboxes? Even apart from this particular issue I am not keen on editable comboboxes so is there another way I can limit the display of the combo to say 10 records (I've tried MaxSize on both combo & view & MaxCount without any luck).class MyCombo(QComboBox): update_signal = Signal(str) def __init__(self, object_name: str): super().__init__() self.object_name = object_name self.view().setMouseTracking(False) # self.setEditable(True) def mousePressEvent(self, e): self.update_signal.emit(self.object_name) super().mousePressEvent(e) class Form_RelationWidgetMapper(QMainWindow): # uses QDataWidgetMapper to map fields to form def __init__(self): max_spin = 2147483647 super().__init__() # gui layout = QVBoxLayout() form = QFormLayout() self.track_id = QSpinBox() self.track_id.setRange(0, max_spin) self.track_id.setDisabled(True) self.name = QLineEdit() self.cbo_album = MyCombo('cbo_album') self.media_type = QComboBox() self.cbo_genre = MyCombo('cbo_genre') self.composer = QLineEdit() form.addRow(QLabel('Track ID'), self.track_id) form.addRow(QLabel('Album'), self.cbo_album) form.addRow(QLabel('Track Name'), self.name) form.addRow(QLabel('Genre'), self.cbo_genre) form.addRow(QLabel('Composer'), self.composer) # set models & relationships self.track_model = QSqlRelationalTableModel(db=db) self.track_model.setTable('Track') album_idx = self.track_model.fieldIndex('AlbumId') self.track_model.setRelation(album_idx, QSqlRelation('Album', 'AlbumId', 'Title')) genre_idx = self.track_model.fieldIndex('GenreId') self.track_model.setRelation(genre_idx, QSqlRelation('Genre', 'GenreId', 'Name')) # set relationship models self.genre_model = self.track_model.relationModel(genre_idx) self.cbo_genre.setModel(self.genre_model) genre_display = self.genre_model.fieldIndex('Name') self.cbo_genre.setModelColumn(genre_display) self.genre_model.sort(genre_display,Qt.SortOrder.AscendingOrder) self.cbo_genre.update_signal.connect(self.combo_selected) self.album_model = self.track_model.relationModel(album_idx) self.cbo_album.setModel(self.album_model) album_display = self.album_model.fieldIndex('Title') self.cbo_album.setModelColumn(album_display) self.album_model.sort(album_display, Qt.SortOrder.AscendingOrder) self.cbo_album.update_signal.connect(self.combo_selected) # map fields self.mapper = QDataWidgetMapper(self) self.mapper.setModel(self.track_model) delegate = QSqlRelationalDelegate(self) #.table) self.mapper.setItemDelegate(delegate) self.mapper.addMapping(self.track_id, 0) # map col 0 of table to form variable(QSpinbox) Track ID self.mapper.addMapping(self.name,1) # map col 1 of table to QLineEdit self.name & so on self.mapper.addMapping(self.cbo_album, 2) #album_idx) self.mapper.addMapping(self.cbo_genre, 4) #genre_idx) self.mapper.addMapping(self.composer, 5) # self.track_model.sort(0, Qt.SortOrder.AscendingOrder) self.track_model.select() # run the select self.mapper.toFirst() #First() #set to first record self.setMinimumSize(QSize(400, 400)) controls = QHBoxLayout() first_rec = QPushButton("First") first_rec.setShortcut('Ctrl+Home') first_rec.clicked.connect(self.mapper.toFirst) prev_rec = QPushButton("Previous") prev_rec.setShortcut('Ctrl+Up') prev_rec.clicked.connect(self.mapper.toPrevious) next_rec = QPushButton("Next") next_rec.setShortcut('Ctrl+Down') next_rec.clicked.connect(self.mapper.toNext) last_rec = QPushButton("Last") last_rec.setShortcut('Ctrl+End') last_rec.clicked.connect(self.mapper.toLast) save_rec = QPushButton("Save Changes") save_rec.clicked.connect(self.mapper.submit) controls.addWidget(first_rec) controls.addWidget(prev_rec) controls.addWidget(next_rec) controls.addWidget(last_rec) controls.addWidget(save_rec) layout.addLayout(form) layout.addLayout(controls) widget = QWidget() widget.setLayout(layout) self.setCentralWidget(widget) def combo_selected(self, object_name: str): match object_name: case 'cbo_genre': old_text = self.cbo_genre.currentText() self.genre_model.select() self.cbo_genre.setCurrentText(old_text) case 'cbo_album': old_text = self.cbo_album.currentText() self.album_model.select() self.cbo_album.setCurrentText(old_text) print('selected album', self.cbo_album.currentIndex()) if __name__ == '__main__': app = QApplication(sys.argv) if not createConnection(): sys.exit(1) win = Form_RelationWidgetMapper() win.show() app.exec()
-
I'm reasonably new to PySide6 & I'm having major problems getting my head around QSqlRelationalTable Model, in particular with ComboBoxes. I'm using MariaDB & running under Ubuntu22.04. I have a form as follows.
As far as I can tell, under linux at least, a combobox displays as many records as will fit on the screen, regardless of the size of the parent window (looking really ugly) unless you set it as editable.
I would like each combobox to display in alphabetical order. The code below works but if I set the comboboxes to be editable (which I don't really want or need), clicking on the genre combo changes the album combo to display its first alpha record. Just the act of opening the genre combo causes this to happen.
What have I got wrong? Why is this happening & how can I prevent it, short of putting up with ugly comboboxes? Even apart from this particular issue I am not keen on editable comboboxes so is there another way I can limit the display of the combo to say 10 records (I've tried MaxSize on both combo & view & MaxCount without any luck).class MyCombo(QComboBox): update_signal = Signal(str) def __init__(self, object_name: str): super().__init__() self.object_name = object_name self.view().setMouseTracking(False) # self.setEditable(True) def mousePressEvent(self, e): self.update_signal.emit(self.object_name) super().mousePressEvent(e) class Form_RelationWidgetMapper(QMainWindow): # uses QDataWidgetMapper to map fields to form def __init__(self): max_spin = 2147483647 super().__init__() # gui layout = QVBoxLayout() form = QFormLayout() self.track_id = QSpinBox() self.track_id.setRange(0, max_spin) self.track_id.setDisabled(True) self.name = QLineEdit() self.cbo_album = MyCombo('cbo_album') self.media_type = QComboBox() self.cbo_genre = MyCombo('cbo_genre') self.composer = QLineEdit() form.addRow(QLabel('Track ID'), self.track_id) form.addRow(QLabel('Album'), self.cbo_album) form.addRow(QLabel('Track Name'), self.name) form.addRow(QLabel('Genre'), self.cbo_genre) form.addRow(QLabel('Composer'), self.composer) # set models & relationships self.track_model = QSqlRelationalTableModel(db=db) self.track_model.setTable('Track') album_idx = self.track_model.fieldIndex('AlbumId') self.track_model.setRelation(album_idx, QSqlRelation('Album', 'AlbumId', 'Title')) genre_idx = self.track_model.fieldIndex('GenreId') self.track_model.setRelation(genre_idx, QSqlRelation('Genre', 'GenreId', 'Name')) # set relationship models self.genre_model = self.track_model.relationModel(genre_idx) self.cbo_genre.setModel(self.genre_model) genre_display = self.genre_model.fieldIndex('Name') self.cbo_genre.setModelColumn(genre_display) self.genre_model.sort(genre_display,Qt.SortOrder.AscendingOrder) self.cbo_genre.update_signal.connect(self.combo_selected) self.album_model = self.track_model.relationModel(album_idx) self.cbo_album.setModel(self.album_model) album_display = self.album_model.fieldIndex('Title') self.cbo_album.setModelColumn(album_display) self.album_model.sort(album_display, Qt.SortOrder.AscendingOrder) self.cbo_album.update_signal.connect(self.combo_selected) # map fields self.mapper = QDataWidgetMapper(self) self.mapper.setModel(self.track_model) delegate = QSqlRelationalDelegate(self) #.table) self.mapper.setItemDelegate(delegate) self.mapper.addMapping(self.track_id, 0) # map col 0 of table to form variable(QSpinbox) Track ID self.mapper.addMapping(self.name,1) # map col 1 of table to QLineEdit self.name & so on self.mapper.addMapping(self.cbo_album, 2) #album_idx) self.mapper.addMapping(self.cbo_genre, 4) #genre_idx) self.mapper.addMapping(self.composer, 5) # self.track_model.sort(0, Qt.SortOrder.AscendingOrder) self.track_model.select() # run the select self.mapper.toFirst() #First() #set to first record self.setMinimumSize(QSize(400, 400)) controls = QHBoxLayout() first_rec = QPushButton("First") first_rec.setShortcut('Ctrl+Home') first_rec.clicked.connect(self.mapper.toFirst) prev_rec = QPushButton("Previous") prev_rec.setShortcut('Ctrl+Up') prev_rec.clicked.connect(self.mapper.toPrevious) next_rec = QPushButton("Next") next_rec.setShortcut('Ctrl+Down') next_rec.clicked.connect(self.mapper.toNext) last_rec = QPushButton("Last") last_rec.setShortcut('Ctrl+End') last_rec.clicked.connect(self.mapper.toLast) save_rec = QPushButton("Save Changes") save_rec.clicked.connect(self.mapper.submit) controls.addWidget(first_rec) controls.addWidget(prev_rec) controls.addWidget(next_rec) controls.addWidget(last_rec) controls.addWidget(save_rec) layout.addLayout(form) layout.addLayout(controls) widget = QWidget() widget.setLayout(layout) self.setCentralWidget(widget) def combo_selected(self, object_name: str): match object_name: case 'cbo_genre': old_text = self.cbo_genre.currentText() self.genre_model.select() self.cbo_genre.setCurrentText(old_text) case 'cbo_album': old_text = self.cbo_album.currentText() self.album_model.select() self.cbo_album.setCurrentText(old_text) print('selected album', self.cbo_album.currentIndex()) if __name__ == '__main__': app = QApplication(sys.argv) if not createConnection(): sys.exit(1) win = Form_RelationWidgetMapper() win.show() app.exec()
@whitelegs said in QSqlRelationalModel and multiple foreign keys/ComboBoxes:
As far as I can tell, under linux at least, a combobox displays as many records as will fit on the screen, regardless of the size of the parent window (looking really ugly) unless you set it as editable.
All my work tested under Ubuntu 24.04, Qt 6.4.2. I use default Wayland (rather than xcb), probably because I was wicked in a previous life and must be punished...
This is because Linux is using the
Fusion
style and per https://doc.qt.io/qt-6/qcombobox.html#maxVisibleItems-propNote: This property is ignored for non-editable comboboxes in styles that returns true for QStyle::SH_ComboBox_Popup such as the Mac style or the Gtk+ Style.
Now, I don't know whether it's a good idea to alter the default windowing style because you don't like something it does when it may be "standard" for that style, but let's allow you to alter this behaviour.
My first thought was to make your combobox editable and then alter things so it still looks and behaves non-editable. Python code for this is given at https://forum.qt.io/post/753422. I confirm this works (PySide6).
Alternatively, if you find this quite a bit of code and pretty "hacky", inspired by https://stackoverflow.com/a/11254459/489865 from 12 years ago I find the following is just 2 lines and seems to work fine (with
setMaxVisibleItems()
but notsetEditable()
):combo.setStyleSheet("QComboBox { combobox-popup: 0; }") combo.view().setVerticalScrollBarPolicy(QtCore.Qt.ScrollBarPolicy.ScrollBarAsNeeded)
(Don't ask me where
combobox-popup: 0;
is documented though!)The code below works but if I set the comboboxes to be editable (which I don't really want or need), clicking on the genre combo changes the album combo to display its first alpha record. Just the act of opening the genre combo causes this to happen.
I have not looked at this. It is not so quick for me to have to set up a database for your multiple
QSqlRelationalModel
s and without data. You seem to indicate this only happens if the combo is editable, is that the case? Then the first solution above may have the same issue, and need examining: let us know if you adopt that and still need this investigated. But maybe if you are happy with my second solution the combo remains non-editable, does that mean this problem does not arise for you? -
@whitelegs said in QSqlRelationalModel and multiple foreign keys/ComboBoxes:
As far as I can tell, under linux at least, a combobox displays as many records as will fit on the screen, regardless of the size of the parent window (looking really ugly) unless you set it as editable.
All my work tested under Ubuntu 24.04, Qt 6.4.2. I use default Wayland (rather than xcb), probably because I was wicked in a previous life and must be punished...
This is because Linux is using the
Fusion
style and per https://doc.qt.io/qt-6/qcombobox.html#maxVisibleItems-propNote: This property is ignored for non-editable comboboxes in styles that returns true for QStyle::SH_ComboBox_Popup such as the Mac style or the Gtk+ Style.
Now, I don't know whether it's a good idea to alter the default windowing style because you don't like something it does when it may be "standard" for that style, but let's allow you to alter this behaviour.
My first thought was to make your combobox editable and then alter things so it still looks and behaves non-editable. Python code for this is given at https://forum.qt.io/post/753422. I confirm this works (PySide6).
Alternatively, if you find this quite a bit of code and pretty "hacky", inspired by https://stackoverflow.com/a/11254459/489865 from 12 years ago I find the following is just 2 lines and seems to work fine (with
setMaxVisibleItems()
but notsetEditable()
):combo.setStyleSheet("QComboBox { combobox-popup: 0; }") combo.view().setVerticalScrollBarPolicy(QtCore.Qt.ScrollBarPolicy.ScrollBarAsNeeded)
(Don't ask me where
combobox-popup: 0;
is documented though!)The code below works but if I set the comboboxes to be editable (which I don't really want or need), clicking on the genre combo changes the album combo to display its first alpha record. Just the act of opening the genre combo causes this to happen.
I have not looked at this. It is not so quick for me to have to set up a database for your multiple
QSqlRelationalModel
s and without data. You seem to indicate this only happens if the combo is editable, is that the case? Then the first solution above may have the same issue, and need examining: let us know if you adopt that and still need this investigated. But maybe if you are happy with my second solution the combo remains non-editable, does that mean this problem does not arise for you? -
Beautiful
combobox-popup: 0;
works perfectly. I'm still curious about the wholeQSqlRelational
thing & whether my code is the best way of working with this but that can wait till the next 'insurmountable' problem surfaces. Thanks for your help.@whitelegs said in QSqlRelationalModel and multiple foreign keys/ComboBoxes:
Beautiful combobox-popup: 0; works perfectly
That's great, but
- Be aware that this is not documented anywhere. It seems to have been there and worked for a very long time (Qt4?) but could presumably be removed at any time, I guess "use at your own risk". Someone who looked at the sources says it implements "don't do combobox dropdown as a popup".
- By ceasing to have to make the combo editable in order to get rid of the enormous dropdown, are you saying that as a side-effect this makes "clicking on the genre combo changes the album combo to display its first alpha record. Just the act of opening the genre combo causes this to happen" goes away?
-
- Fair enough, I'll worry about it if/when it happens
- Correct, all seems to work properly now which I guess is what I don't understand & why I thought my code must be suspect. Why should clicking on one combo box influence the display in another unrelated combo? if you wanted to play around (not that I expect you to) the database is the readily available chinook.sqlite which I converted to MariaDb 'cos that's what I prefer, however I did test it in sqlite & had exactly the same issue there.
-
- Fair enough, I'll worry about it if/when it happens
- Correct, all seems to work properly now which I guess is what I don't understand & why I thought my code must be suspect. Why should clicking on one combo box influence the display in another unrelated combo? if you wanted to play around (not that I expect you to) the database is the readily available chinook.sqlite which I converted to MariaDb 'cos that's what I prefer, however I did test it in sqlite & had exactly the same issue there.
@whitelegs said in QSqlRelationalModel and multiple foreign keys/ComboBoxes:
the database is the readily available chinook.sqlite
Yes, I was planning (generously ;-) )to have a look at this in a while this morning :) Where do I get "chinook.sqlite" from, should it work for me under Linux (don't even know whether that comes with the SQLite driver, my problem) and how much do I need to add your code to test it (just
createConnection()
?)? -
That is very kind of you. You can get it from here [https://github.com/lerocha/chinook-database]. I see they have a MySql version which would have saved me a small amount of work but I got mine included in a book I bought to learn PyQt. I'm working under Linux so you should have no problems in that regard. Apart from the code that I posted before all you need is the connection and the various imports as follows
from PySide6.QtWidgets import (QApplication, QMainWindow, QWidget, QTableView, QMessageBox, QVBoxLayout, QHBoxLayout, QLineEdit, QComboBox, QLabel,QPushButton, QDataWidgetMapper, QFormLayout, QSpinBox) from PySide6.QtCore import Qt, QSize, Signal from PySide6.QtGui import * from PySide6.QtSql import (QSqlDatabase, QSqlTableModel, QSqlRelation, QSqlRelationalTableModel, QSqlRelationalDelegate, QSqlQuery, QSqlQueryModel) import os import sys
Good luck & thanks once again for your interest in this.