QML Combobox with QSqlModel

Question

I'm currently facing problems with populating a QML Combobox with a QSqlTableModel.

Example Database:

  Table Customer                    Table Project
|   id   |   name   |      |   id   |   name  |  fk_customer  | 
|........|..........|      |........|.........|...............|
|    1   |  name1   |      |    1   |  pro1   |      1        |
|    2   |  name2   |      |    2   |  pro2   |      1        |
|    3   |  name3   |      |    3   |  pro3   |      3        |

I want to show a form with a QML Combobox to select the customer by name.
Therefore I am setting the combobox model to a QSqlTableModel with table="customer" and textRole="name".

My Problem now resides in setting the Combobox.currentindex to the correct value from the database and of course to read the selected ID back from the combobox.
The Comboboxes documentation states that whenever the combobox is populated with a new model, its current index is set to 1.
I tried to set the currentindex with Component.onCompleted Signal from combobox and its parent but the selected index was always set to 1.

So I think I might have made conceptional mistake when implementing the model or the QML-file.

Does anyone know a suggested way when and how to pre set a QML Combobox with a given value from c++ model?

Answer 1

I do not understand what the problem is since you do not provide an MCVE, so my response will try to show the correct solution.

Assuming you understand that the QSqlTableModel can not be used directly in QML but you have to add roles that correspond to the fields and overwrite the data() and roleNames() method.

To obtain the information of the given ID the currentIndex of the view must use the data() method of the model so the corresponding QModelIndex and the role must be created, in this case to simplify that task I have implemented a function that given the row and the name of the field returns the data.

Using the above I have implemented the following class:

sqltablemodel.h

#ifndef SQLTABLEMODEL_H
#define SQLTABLEMODEL_H

#include <QSqlTableModel>
#include <QSqlRecord>

class SqlTableModel : public QSqlTableModel
{
    Q_OBJECT
    Q_PROPERTY(QStringList fieldNames READ fieldNames)
public:
    using QSqlTableModel::QSqlTableModel;
    QHash<int, QByteArray> roleNames() const
    {
       QHash<int, QByteArray> roles;
       for (int i = 0; i < record().count(); i ++) {
           roles.insert(Qt::UserRole + i + 1, record().fieldName(i).toUtf8());
       }
       return roles;
   }
    QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const
    {
        QVariant value;
        if (index.isValid()) {
            if (role < Qt::UserRole) {
                value = QSqlQueryModel::data(index, role);
            } else {
                int columnIdx = role - Qt::UserRole - 1;
                QModelIndex modelIndex = this->index(index.row(), columnIdx);
                value = QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
            }
        }
        return value;
    }
    Q_INVOKABLE QVariant data(int row, const QString & fieldName){
        int col = record().indexOf(fieldName);
        if(col != -1 && 0 <= row && row < rowCount()){
            QModelIndex ix = index(row, col);
            return ix.data();
        }
        return QVariant();
    }
    QStringList fieldNames() const{
        QStringList names;
        for (int i = 0; i < record().count(); i ++) {
            names << record().fieldName(i);
        }
        return names;
    }
};

#endif // SQLTABLEMODEL_H

So you must create the model and export it to QML:

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);
    if(!createConnection()) // open the connection with the DB
        return -1;

    SqlTableModel model;
    model.setTable("Customer");
    model.select();

    QQmlApplicationEngine engine;
    engine.rootContext()->setContextProperty("cppmodel", &model);
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    if (engine.rootObjects().isEmpty())
        return -1;

    return app.exec();
}

And the connection is made in QML:

import QtQuick 2.9
import QtQuick.Window 2.2
import QtQuick.Controls 2.4

Window {
    visible: true
    width: 320
    height: 240
    title: qsTr("ComboBox with SqlTableModel")
    ComboBox {
        anchors.centerIn: parent
        model: cppmodel
        textRole: "name"
        Component.onCompleted: currentIndex = 4
        onCurrentIndexChanged: {
            var id = cppmodel.data(currentIndex, "id");
            var name = cppmodel.data(currentIndex, "name");
            console.log(qsTr("currentIndex: %1, id: %2, name: %3").arg(currentIndex).arg(id).arg(name))
        }
    }
}

_{The complete example can be found in the following link.}