How to show QSqlQueryModel in QML?

Question

I want to show QSqlQueryModel in qml TableView but I don't Wan't to create separate QML file for each new query cause I can't create infinite qml files as given here . Also question is not working for me for dynamic number of columns (could be version difference as I am using 5.11) .I just want something like:-

QTableView *view = new QTableView;
view->setModel(model);
view->show();

in QML.

I am new to qml. So far I am able show QSqlQueryModel as guided in first link but my user may enter any SQL Query.

Answer 1

Using information from A more generic approach of How to Use a QSqlQueryModel in QML you can build a general model, to make it easy to use from QML you can create a property to pass the query.

sqlquerymodel.h

#ifndef SQLQUERYMODEL_H
#define SQLQUERYMODEL_H

#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlRecord>

class SqlQueryModel : public QSqlQueryModel
{
    Q_OBJECT
    Q_PROPERTY(QString query READ queryStr WRITE setQueryStr NOTIFY queryStrChanged)
    Q_PROPERTY(QStringList userRoleNames READ userRoleNames CONSTANT)
public:
    using QSqlQueryModel::QSqlQueryModel;
    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) 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;
    }
    QString queryStr() const{
        return query().lastQuery();
    }
    void setQueryStr(const QString &query){
        if(queryStr() == query)
            return;
        setQuery(query);
        emit queryStrChanged();
    }
    QStringList userRoleNames() const {
        QStringList names;
        for (int i = 0; i < record().count(); i ++) {
            names << record().fieldName(i).toUtf8();
        }
        return names;
    }
signals:
    void queryStrChanged();
};
#endif // SQLQUERYMODEL_H

main.cpp

#include "sqlquerymodel.h"

#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QDebug>
#include <QSqlError>

static bool createConnection()
{

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        qDebug()<<"Cannot open database\n"
                  "Unable to establish a database connection.\n"
                  "This example needs SQLite support. Please read "
                  "the Qt SQL driver documentation for information how "
                  "to build it.\n\n"
                  "Click Cancel to exit.";
        return false;
    }

    QSqlQuery query;
    if(!query.exec("CREATE TABLE COMPANY("
                   "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                   "NAME           TEXT    NOT NULL,"
                   "AGE            INT     NOT NULL,"
                   "SALARY         REAL"
                   ")")){
        qDebug()<<query.lastError().text();
    }
    for(int i=0; i < 10; i++){
        query.prepare("insert into COMPANY(NAME, AGE, SALARY) values(:name, :age, :salary)");
        query.bindValue(":name", QString("name-%1").arg(i));
        query.bindValue(":age",  (i+1)*1000);
        query.bindValue(":salary", (11-i)*11.5);
        if(!query.exec()){
            qDebug()<<query.lastError().text();
        }
    }
    return true;
}


int main(int argc, char *argv[])
{
    qmlRegisterType<SqlQueryModel>("Foo", 1, 0, "SqlQueryModel");
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);
    if(!createConnection())
        return -1;

    QQmlApplicationEngine engine;
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    if (engine.rootObjects().isEmpty())
        return -1;

    return app.exec();
}

main.qml

import QtQuick 2.9
import QtQuick.Window 2.2
import QtQuick.Controls 1.4

import Foo 1.0

Window {
    visible: true
    width: 640
    height: 480
    title: qsTr("SqlQueryModel")
    SqlQueryModel{
        id: sqlmodel
        query: "select * from COMPANY"
    }
    Component{
        id: columnComponent
        TableViewColumn{width: 100 }
    }
    TableView {
        id: view
        anchors.fill: parent
        resources:{
            var roleList = sqlmodel.userRoleNames
            var temp = []
            for(var i in roleList){
                var role  = roleList[i]
                temp.push(columnComponent.createObject(view, { "role": role, "title": role}))
            }
            return temp
        }
        model: sqlmodel
    }
}