[Solved] QSqlQueryModel for QML TableView

[MrFog]

Hi there,

the last days i searched for a solution to display a QSqlQueryModel in a QML TableView. I found these really helpful links:

"QML and QSqlTableModel":http://qt-project.org/wiki/QML_and_QSqlTableModel
"How to use a QSqlQueryModel":http://qt-project.org/wiki/How_to_use_a_QSqlQueryModel_in_QML
"Using QtSql database access":http://forum.dailymobile.net/forums/qt-development/92556-using-qtsql-qml-database-access.html

So i derived my own class from QSqlQueryModel and reimplemented "roleNames()":http://doc.qt.io/qt-5/qabstractitemmodel.html#roleNames because "setRoleNames()":http://doc.qt.io/qt-5/qabstractitemmodel-obsolete.html#setRoleNames is obsolete in Qt 5.4.

HEADER
@class SQLiteModel : public QSqlQueryModel {
Q_OBJECT

public:
explicit SQLiteModel(QObject *parent = NULL);
~SQLiteModel();

Q_INVOKABLE bool setDatabase(const QString &database);
Q_INVOKABLE bool setQuery(const QString &query);

virtual QHash<int, QByteArray> ​roleNames() const;

static void declareQML();

private:
QSqlDatabase _db;

static QString _connection();

};@

SOURCE
@SQLiteModel::SQLiteModel(QObject *parent) :
QSqlQueryModel(parent) {
_db = QSqlDatabase::addDatabase("QSQLITE", SQLiteModel::_connection());
}

SQLiteModel::~SQLiteModel() {
if (_db.open()) _db.close();
}

bool SQLiteModel::setDatabase(const QString &database) {
if (!QFileInfo(database).exists()) {
qWarning() << "SQLiteModel::setDatabase() - Could not find database '" + database + "'";
return false;
}

if (_db.open()) {
    _db.close();
    this->clear();
}

_db.setDatabaseName(database);

if (!_db.open()) {
    qWarning() << "SQLiteModel::setDatabase() -" << database;
    return false;
}

return true;

}

bool SQLiteModel::setQuery(const QString &query) {
QSqlQueryModel::setQuery(query, _db);

if (this->query().record().isEmpty()) {
    qWarning() << "SQLiteModel::setQuery() -" << this->query().lastError();
    return false;
}

return true;

}

QHash<int, QByteArray> SQLiteModel::​roleNames() const {
QHash<int, QByteArray> roles;
qWarning() << "SQLiteModel::roleNames()";

for (int i = 0; i < this->record().count(); i++) {
    roles[Qt::UserRole + i + 1] = this->record().fieldName(i).toLocal8Bit();
}

return roles;

}

void SQLiteModel::declareQML() {
qmlRegisterType<SQLiteModel>("Extensions", 1, 0, "SQLiteModel");
}

QString SQLiteModel::_connection() {
static int ID = 0; ID++;
return "SQLiteModelConnection" + QString::number(ID);
}@

But the QML TableView does not show anything. I know the class is working, because a QTableView could display the model. (Example for a SQLite database with 'Components' table and 'ID', 'Name', 'Description' and 'Value' header)

MAIN
@int main(int argc, char *argv[]) {
QApplication app(argc, argv);

SQLiteModel::declareQML();

SQLiteModel *model = new SQLiteModel;
model->setDatabase("../../database");
model->setQuery("select * from Components");

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

QQmlApplicationEngine engine;
engine.rootContext()->setContextProperty("SQLite", model);
engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
return app.exec();

}@

QML
@ApplicationWindow {
title: qsTr("SQL Demo")
width: 640
height: 480
visible: true

minimumWidth: 640
minimumHeight: 480

TableView {
    id: tableview
    anchors.fill: parent
    anchors.margins: 10

    TableViewColumn { role: "ID" ; title: "ID"; visible: true }
    TableViewColumn { role: "Name" ; title: "Name" }
    TableViewColumn { role: "Description" ; title: "Description" }
    TableViewColumn { role: "Value" ; title: "Value" }

    model: SQLite
}

}@

Any suggestions? I also tried to add a Q_INVOKABLE before the roleNames() function, but it didn't changed anything.

Regards,
MrFog

[p3c0]

Hi,

For QML TableView, you also need to reimplement data() and return values as per the roles in it. This is because QML's model calls data() with the roles given by roleNames().

bq. The header sections are attached to values in the model by defining the model role they attach to. Each property in the model will then be shown in their corresponding column.

[MrFog]

Thanks for the reply! I added the following code to the class:

HEADER
@QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;@

SOURCE
@QVariant SQLiteModel::data(const QModelIndex &index, int role) const {
qWarning() << "SQLiteModel::data()";
QVariant value = QSqlQueryModel::data(index, role);

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;

}@

But it did not change anything. I think the data() and roleNames() function does not get called, because there should be a console output. I tried to add the 'override' specifier to data() and roleNames(). It's OK for data(), but not for roleNames() because it isn't a virtual function?

[p3c0]

Ok. It could be that the roles in QML are not matching to that defined in model. Have you made sure they are exactly the same ? They are case-sensitive.

[MrFog]

I'm pretty sure. The roleNames() function creates the following QHash:

@
QHash((259, "Description")(258, "Name")(260, "Value")(257, "ID"))
@

With this short example i could print out the complete table:

@
QHash<int, QByteArray> roles;

for (int i = 0; i < this->record().count(); i++) {
roles[Qt::UserRole + i + 1] = this->record().fieldName(i).toLocal8Bit();
}

qWarning() << roles;

QStringList names;
names.append("ID");
names.append("Name");
names.append("Description");
names.append("Value");

for (int i = 0; i < this->rowCount(); i++) {
for (int j = 0; j < names.count(); j++) {
int row = i;
int col = roles.key(names[j].toLocal8Bit()) - Qt::UserRole - 1;

    QModelIndex index = this->index(row, col);

    qWarning() << QSqlQueryModel::data(index, Qt::DisplayRole);
}

}
@

But i think the real problem is, that data() and roleNames() does not get called.

[p3c0]

Can you try out following example ?

sqlitemodel.cpp

SQLiteModel::SQLiteModel(QObject *parent)
    :QSqlQueryModel(parent)
{
    openDb();
}

SQLiteModel::~SQLiteModel()
{

}

bool SQLiteModel::openDb()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("/root/sample.db");
    return db.open();
}

bool SQLiteModel::setQuery(const QString &query)
{
    QSqlQueryModel::setQuery(query);

    if (this->query().record().isEmpty()) {
        qWarning() << "SQLiteModel::setQuery() -" << this->query().lastError();
        return false;
    }
    m_recordCount = record().count();
    qDebug() << "Records: " << m_recordCount;

    return true;
}

QHash<int, QByteArray> SQLiteModel::roleNames() const
{
    QHash<int, QByteArray> roles;// = QAbstractTableModel::roleNames();
    for( int i = 0; i < record().count(); i++) {
        roles[Qt::UserRole + i + 1] = record().fieldName(i).toLatin1();
    }
    return roles;
}

QVariant SQLiteModel::data(const QModelIndex &index, int role) const
{
    QVariant value = QSqlQueryModel::data(index, role);
    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;
}

Initialize it as

SQLiteModel *sqmodel = new SQLiteModel(this);
sqmodel->setQuery("select * from sample;");
...setContextProperty("tableModel",sqmodel);

main.qml

Item {
    TableView {
        anchors.fill: parent
        model: tableModel
        TableViewColumn{ role: "name"  ; title: "Name" ; width: 100 }
        TableViewColumn{ role: "age"  ; title: "Age" ; width: 100 }
        TableViewColumn{ role: "gender" ; title: "Gender" ; width: 100 }
    }
}

DB looks like:

name|age|gender
abc|25|Male
xyz|20|Female
pqr|12|Male

[MrFog]

Its working! :-D

But i don't know why... now i could override roleNames(), which i couldn't do before...? Crazy...

SQLiteModel.h
@
class SQLiteModel : public QSqlQueryModel {
Q_OBJECT

public:
explicit SQLiteModel(QObject *parent = NULL);
~SQLiteModel();

Q_INVOKABLE bool setDatabase(const QString &database);
Q_INVOKABLE bool setQuery(const QString &query);

QHash<int, QByteArray> roleNames() const override;
QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const override;

static void declareQML();

private:
QSqlDatabase _db;

static QString _connection();

};
@

SQLiteModel.cpp
@
SQLiteModel::SQLiteModel(QObject *parent)
: QSqlQueryModel(parent) {
_db = QSqlDatabase::addDatabase("QSQLITE", _connection());
}

SQLiteModel::~SQLiteModel() {
if (_db.isOpen()) _db.close();
}

bool SQLiteModel::setDatabase(const QString &database) {
if (!QFileInfo(database).exists()) {
qWarning() << "SQLiteModel::setDatabase() - Could not find database '" + database + "'";
return false;
}

if (_db.isOpen()) {
    _db.close();
    this->clear();
}

_db.setDatabaseName(database);

if (!_db.open()) {
    qWarning() << "SQLiteModel::setDatabase() -" << _db.lastError().text();
    return false;
}

return true;

}

bool SQLiteModel::setQuery(const QString &query) {
QSqlQueryModel::setQuery(query, _db);

if (this->query().record().isEmpty()) {
    qWarning() << "SQLiteModel::setQuery() -" << this->query().lastError();
    return false;
}

return true;

}

QHash<int, QByteArray> SQLiteModel::roleNames() const {
QHash<int, QByteArray> roles;

for ( int i = 0; i < this->record().count(); i++) {
    roles[Qt::UserRole + i + 1] = this->record().fieldName(i).toLocal8Bit();
}

return roles;

}

QVariant SQLiteModel::data(const QModelIndex &index, int role) const {
QVariant value = QSqlQueryModel::data(index, role);

if (role < Qt::UserRole)
    value = QSqlQueryModel::data(index, role);
else {
    int row = index.row();
    int col = role - Qt::UserRole - 1;

    QModelIndex modelIndex = this->index(row, col);

    value = QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
}

return value;

}

void SQLiteModel::declareQML() {
qmlRegisterType<SQLiteModel>("Extensions", 1, 0, "SQLiteModel");
}

QString SQLiteModel::_connection() {
static int ID = 0; ID++;
return "SQLiteModelConnection" + QString::number(ID);
}
@

main.cpp
@
#include "SQLiteModel.h"

int main(int argc, char *argv[]) {
QApplication app(argc, argv);

SQLiteModel::declareQML();

QQmlApplicationEngine engine;
engine.load(QUrl(QStringLiteral("qrc:/main.qml")));

return app.exec();

}
@

main.qml
@
import QtQuick 2.4
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1

import Extensions 1.0

ApplicationWindow {
title: qsTr("SQLite Demo")
width: 640
height: 480

minimumWidth: 640
minimumHeight: 480

visible: true

TableView {
    id: tableview
    anchors.fill: parent
    anchors.margins: 10

    TableViewColumn { role: "ID" ; title: "ID"; visible: true }
    TableViewColumn { role: "Name" ; title: "Name" }
    TableViewColumn { role: "Description" ; title: "Description" }
    TableViewColumn { role: "Value" ; title: "Value" }
}

SQLiteModel {
    id: model

    Component.onCompleted: {
        model.setDatabase("../../database");
        model.setQuery("select * from Components");

        tableview.model = model;
    }
}

}
@

SQLite database named database including Components table with ID, Name, Description and Value header.

Big thanks to p3c0 for his help!!!!

[p3c0]

You're Welcome :)
You can mark the post as solved by editing the post title and prepending [solved] so that others would come to know. I have already tagged it as solved.

[Ankalagon]

Hi, I just wanted to thank for the complete working example. I was searching hours for exactly that situation!

[israelins85]

I think this need be done by the standard class... Then I fill a BUG, please upvote then: https://bugreports.qt.io/browse/QTBUG-85237