Changing the query of QSqlQueryModel from qml: how to change column heading?

push_back · wrote on 7 Sept 2022, 14:06

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!

SGaist · wrote on 7 Sept 2022, 18:30

Hi,

Did you consider using QSqlTableModel since you want to manipulate tables ?

push_back · wrote on 8 Sept 2022, 08:42

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.

SGaist · wrote on 8 Sept 2022, 20:59

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 ?

push_back · 9 Oct 2022, 09:36

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