QSqlTableModel to qml list coming through multiple times

Question

I am new and trying to learn qt and qml but I cannot find a way to solve this problem.

I have a QSqlTableModel with only 4 rows of test data, but I would like to convert distinct values from 1 column (of which there is only 1 value) into a list for a qml ListModel.

I have this Q_PROPERTY(QStringList distinctSemesters READ getSemesterList NOTIFY semesterChanged) in my SqlDataModel.h file, with this in my sqldatamodel.cpp

QStringList  SqlDataModel::getSemesterList() const
{
    QStringList  mySemesters;
    QSqlQuery query;
    query.exec("SELECT DISTINCT Semester FROM results");
    while (query.next()) {
        QString currentSemester = query.value(0).toString();
        mySemesters << currentSemester;
    }
    return mySemesters;
}

Which returns a QStringList of length 1. I am trying to read this into my qml with

ListView {
    model: SqlDataModel {
                    id: myModel
                }
    delegate: ItemDelegate {
                width: parent.width
                text: myModel.distinctSemesters
            }
}

Which works in getting the String from the getSemesterList() function. But the String gets repeated in the listview 4 times (the size of my tablemodel). I have checked this and added more rows to the test table, which continues to repeat the same string more times in my listview.

From my limited understanding, I am guessing that this will always be the case because the listview is taking its size from the model - which is the size of the qsl table. I have no idea about how to go about this, can anyone else point me in the correct direction?

My current thoughts are to create a new sqlmodel to bring into the qml (but I need to read+write so I believe sqltablemodel is good for that), or should I try to fix qml side with javascript. I looked into it in javascript but when I tried

property ListModel distinctSemesters;
Component.onCompleted: {
              console.log(distinctSemesters)

I kept getting errors that the distinctSemesters was undefined.

Any help is greatly appreciated thank you.

below (i hope) is a reproducible example. I have used some online resources to guide this. main.cpp:

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

    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QApplication app(argc, argv);

    qmlRegisterType<SqlDataModel>("GradesSqlDataModel", 1, 0, "SqlDataModel");


    QQmlApplicationEngine engine;
    const QUrl url(QStringLiteral("qrc:/main.qml"));
    QObject::connect(&engine, &QQmlApplicationEngine::objectCreated,
                     &app, [url](QObject *obj, const QUrl &objUrl) {
        if (!obj && url == objUrl)
            QCoreApplication::exit(-1);
    }, Qt::QueuedConnection);
    engine.load(url);

    return app.exec();
}

SqlDataModel.h

class SqlDataModel : public QSqlTableModel
{
    Q_OBJECT
    Q_PROPERTY(QString semester READ getsemester WRITE setSemester NOTIFY semesterChanged)
    Q_PROPERTY(QStringList distinctSemesters READ getSemesterList NOTIFY semesterChanged)

public:
    SqlDataModel(QObject *parent = 0);

    QStringList getSemesterList() const;
    QString getsemester() const;

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

signals:
    void semesterChanged();
};

SqlDataModel.cpp

SqlDataModel::SqlDataModel(QObject *parent) :
    QSqlTableModel(parent)
{
    createTable();
    setTable("Results");
    setEditStrategy(QSqlTableModel::OnManualSubmit);
    select();

}

QStringList  SqlDataModel::getSemesterList() const
{
    QStringList  mySemesters;
    QSqlQuery query;
    query.exec("SELECT DISTINCT Semester FROM results");
    while (query.next()) {
        QString currentSemester = query.value(0).toString();
        mySemesters << currentSemester;
        qDebug() << currentSemester;
    }
    qDebug() << "length :" <<mySemesters.length();
    return mySemesters;
}

QHash<int, QByteArray> SqlDataModel::roleNames() const
{
    QHash<int, QByteArray> dataNames;
    dataNames[Qt::UserRole] = "ID";
    dataNames[Qt::UserRole + 1] = "Semester";
    dataNames[Qt::UserRole + 2] = "CourseTitle";
    dataNames[Qt::UserRole + 3] = "TestWeight";
    dataNames[Qt::UserRole + 4] = "TestName";
    dataNames[Qt::UserRole + 5] = "Result";
    dataNames[Qt::UserRole + 6] = "OutOf";
    qDebug() << "DataNames" << dataNames;
    return dataNames;
}
QVariant SqlDataModel::data(const QModelIndex &index, int role) const
{
    qDebug() << "settingData";
    if (role < Qt::UserRole)
        return QSqlTableModel::data(index, role);

    const QSqlRecord sqlRecord = record(index.row());
    return sqlRecord.value(role - Qt::UserRole);
}

main.qml

ApplicationWindow {
    id: window;
    visible: true;
    width: 640;
    height: 600;
    title: qsTr("TEST")

    Drawer {
        id: drawer
        width: Math.min(window.width, window.height) / 3 * 2
        height: window.height
        interactive: true

        ListView {
                  model: SqlDataModel {
                    id: myModel
                }
                delegate: ItemDelegate {
                         width: parent.width
                         text: myModel.distinctSemesters
               }
       }
}

and my Results sql table looks like this (only with an ID primary key first):

query.exec("INSERT INTO results (Semester, CourseTitle, TestWeight, TestName, Result, OutOf) VALUES ('Spring 2020', 'Course 1', '10', 'Exam 1', 50, 100)");
    query.exec("INSERT INTO results (Semester, CourseTitle, TestWeight, TestName, Result, OutOf) VALUES ('Spring 2020', 'Course 1', '33', 'Exam 2', 70, 100)");
    query.exec("INSERT INTO results (Semester, CourseTitle, TestWeight, TestName, Result, OutOf) VALUES ('Spring 2020', 'Course 2', '25', 'Exam 1', 0, 100)");
    query.exec("INSERT INTO results (Semester, CourseTitle, TestWeight, TestName, Result, OutOf) VALUES ('Spring 2020', 'Course 2', '5', 'Quiz 1', 5, 20)");

Do you also observe repeated elements after the while: `while(query.next())){...}` `qDebug() << mySemesters;`? — eyllanesc, Jan 21 '20 at 19:16
Yes, I do. I don't know how much of an example to give, Ill try to get the other bits of code together when I get a chance — Adam Laird, Jan 21 '20 at 20:51

score 2 · Accepted Answer · answered Jan 22 '20 at 01:07

Explanation:

In your case the QSqlTableModel loads all the rows of the table since there is no filter so it will have 4 rows so any view that uses it will also show those 4 rows so the ListView shows 4 rows. Why does each row show the same information? Well, because in each item you have the delegate show the different "Semester" that are always "Spring 2020".

Solution:

In this case it is not necessary to use a QSqlTableModel since a QSqlQueryModel is enough to allow filtering from QML, so for this you can use the model of my other answer. So it will only be enough to place it in export to QML and use it.

qmlRegisterType<SqlQueryModel>("GradesSqlDataModel", 1, 0, "SqlQueryModel");

import QtQuick 2.14
import QtQuick.Window 2.14
import QtQuick.Controls 2.14
import GradesSqlDataModel 1.0

Window {
    id: window
    visible: true
    width: 640;
    height: 600;

    SqlQueryModel{
        id: sqlquerymodel
        query: "SELECT DISTINCT Semester FROM results"
    }
    Drawer {
        id: drawer
        width: Math.min(window.width, window.height) / 3 * 2
        height: window.height
        interactive: true
        ListView{
            anchors.fill: parent
            model: sqlquerymodel
            delegate: ItemDelegate {
                width: parent.width
                text: model.Semester
            }
        }
    }
    Component.onCompleted: drawer.visible = true
}

_{The full example can be found here.}

Thank you very much! This makes total sense. I looked at QSqlQueryModel, but from what I can see the QSqlQueryModel is read-only, and I would like to be able to write as well in other areas of my app(and hopefully update this list)? Or am I able to write to the database directly with SQL language in the query? — Adam Laird, Jan 22 '20 at 04:02
@AdamLaird QSqlTableModel is not the only way to write, as you seem to know you can use QSqlQuery. Within your requirements you do not indicate any of it so I will ignore it in my answer. If you have new problems as it seems then create a new post as indicated by the SO rules. — eyllanesc, Jan 22 '20 at 04:06
Sorry, I should have made it clearer in the question that I need it to write as well (it was in the original question though). Thanks for your help though, I was not aware that I can write within the QSqlQueryModel. — Adam Laird, Jan 22 '20 at 13:31

QSqlTableModel to qml list coming through multiple times

1 Answers1