Changing the query of QSqlQueryModel from qml: how to change column heading?
-
Hello.
My question is a bit complex. I hope that you will understand.I try to make a SQL database viewer with qml.
What I've done so far is an app that shows one table from the database. And I want to give the user the possibility to change the table.
I currently use TableView from QtQuick.Controls 1.4, but I can change to the "new" TableView. I use it because I want the TableView to be able to be filtered and sorted, and there is no sortIndicatorVisible in the new TableView.
I also use some Kirigami especially KSortFilterProxyModel because I don't find something equivalent in Qt QML.
I put everything on GitHub for you to try and see.
Here is a screenshot of the app:
So what I need is a way to change the SQL table by interacting with the top right combobox (that will be done with the setQuery method) but then I need to change the columns headers and I don't know how to do that programmatically, plus I need to change the model of the top middle combobox to contains the new columns headers for the filter.
I hope I'm clear enough. If you have some tips to give me to improve coding I will be glade to receive them!
-
Hi,
Did you consider using QSqlTableModel since you want to manipulate tables ?
-
Indeed it is much simpler by changing to a QSqlTableModel .
I manage to dynamically create TableViewColumn with an Instantiator.
I still have some question. Here is my code:
Kirigami.Page { id: page Layout.fillWidth: true title: "Main Page" ListModel { id: columnListCustomer ListElement { role: "code" title: "Code" } ListElement { role: "name" title: "Name" } ListElement { role: "address" title: "Address" } } ListModel { id: listOfAvailableTable ListElement { role: "customer" title: "Customer" } ListElement { role: "item" title: "Item" } } header: QQC2.ToolBar { RowLayout { anchors.right: parent.right QQC2.Label { text: "Search for:" } Kirigami.SearchField { id: searchField Layout.fillWidth: true onAccepted: { //databaseQueryModel.setFilter("name='Paul'") databaseQueryModel.setFilter(columnListCustomer.get( comboBoxList.currentIndex).role + " LIKE " + searchField.text + "%'") } } QQC2.Label { text: "in:" } QQC2.ComboBox { id: comboBoxList textRole: "title" valueRole: "role" model: columnListCustomer Component.onCompleted: currentIndex = indexOfValue("name") } QQC2.ToolButton { icon.name: "list-add-symbolic" text: "Add" //display: AbstractButton.TextBesideIcon } QQC2.ToolButton { icon.name: "edit-entry" text: "Edit" //display: AbstractButton.TextBesideIcon } QQC2.ToolButton { icon.name: "edit-delete-remove" text: "Remove" //display: AbstractButton.TextBesideIcon } QQC2.Label { text: "Table:" } QQC2.ComboBox { textRole: "title" valueRole: "role" model: listOfAvailableTable Component.onCompleted: currentIndex = indexOfValue("customer") } } } QQC1.TableView { id: tableViewOne sortIndicatorVisible: true anchors.fill: parent model: databaseQueryModel onSortIndicatorOrderChanged: databaseQueryModel.sort(tableViewOne.sortIndicatorColumn, tableViewOne.sortIndicatorOrder) // KSortFilterProxyModel { // sourceModel: databaseQueryModel // sortOrder: tableViewOne.sortIndicatorOrder // sortCaseSensitivity: Qt.CaseInsensitive // //sortRole: tableViewOne.getColumn(tableViewOne.sortIndicatorColumn).role // filterRole: comboBoxList.currentValue // filterRegularExpression: { // if (searchField.text === "") // return new RegExp() // return new RegExp("%1".arg(searchField.text), "i") // } // } Instantiator { model: columnListCustomer.count onObjectAdded: tableViewOne.addColumn(object) onObjectRemoved: tableViewOne.removeColumn(object) QQC1.TableViewColumn { role: columnListCustomer.get(index).role title: columnListCustomer.get(index).title width: title.length * Kirigami.Units.gridUnit } } } } }
Questions:
- with the Instantiator I create the headers of the TableView from a ListModel. It's working as I want, but I have this error (repeated for each column) and I don't know why:
qml: TableView::insertColumn(): you cannot add a column to multiple views
- The table could get filled with lots of data. I want them to have the ability to be sorted/filtered, I hesitate between a KSortFilterProxyModel in between the view and the model or using the sort() and setFilter(QString &filter) from the QSqlTableView. What is the best option for performance?
- Moreover concerning the sort() function from QSqlTableView how to set the case sensitiveness to false?
- Concerning setFilter(QString &filter) I would like the filter to use a wildcard. In SQL it should be for example name LIKE 'Pa%' to show every data where the name start with pa. but it doesn't work.
-
For the sorting and filtering it depends on whether you have a big dataset that might benefit from server side filtering.
The sort proxy will do a better job since you want to configure the sorting.
Did you check whether you have an error in your model for your filter query ?
-
I may had an error in my model, but I chose to keep KSortFilterProxyModel for filtering and sorting because it works and as you said it makes a better job and it is simpler.
So there is still that warning from qml when I dynamically build TableViewColumn using an Instantiator:
qml: TableView::insertColumn(): you cannot add a column to multiple views
Here is a code to reproduce that error: (Data are taken from Qt's table view documentation)
import QtQuick 2.15 import QtQuick.Window 2.15 import QtQuick.Controls 1.4 Window { id: window width: 640 height: 480 visible: true title: qsTr("Hello World") ListModel { id: libraryModel ListElement { title: "A Masterpiece" author: "Gabriel" } ListElement { title: "Brilliance" author: "Jens" } ListElement { title: "Outstanding" author: "Frederik" } } ListModel { id: headerModel ListElement { role: "title" title: "Title" } ListElement { role: "author" title: "Author" } } TableView { width: window.width id: tableView model: libraryModel Instantiator { model: headerModel.count onObjectAdded: tableView.addColumn(object) onObjectRemoved: tableView.removeColumn(object) TableViewColumn { role: headerModel.get(index).role title: headerModel.get(index).title } } } }