Adding data throught QIdentityModel to QSqlTableModel fails
-
I'm really confused about this issue.
I have a model based on
QSqlTableModel
:class QuestionSqlTableModel : public QSqlTableModel { Q_OBJECT public: explicit QuestionSqlTableModel(QObject *parent = nullptr, const QSqlDatabase &db = QSqlDatabase()); };
QuestionSqlTableModel::QuestionSqlTableModel( QObject *parent, const QSqlDatabase &db) : QSqlTableModel{parent, db} { setEditStrategy(EditStrategy::OnFieldChange); }
Now to map columns to roles to use in qml I install this model into a model derrived from
QIdentityProxyModel
.Everything seems to work fine. I can read the data from an existing database and display it in qml through the
QIdentityProxyModel
.Now from QML I want to add a new row with data. For that I added a method in the child of
QIdentityProxyModel
.The whole code for this model:
#include <QObject> #include <QIdentityProxyModel> class QuestionsProxyModel : public QIdentityProxyModel { Q_OBJECT enum questionRoles { idRole = Qt::UserRole + 1, askedQuestionRole, answer1Role, answer2Role, answer3Role, answer4Role, correctAnswerRole, pictureRole }; public: QuestionsProxyModel(QObject* parent = nullptr); QHash<int, QByteArray> roleNames() const override; Q_INVOKABLE QVariant data(const QModelIndex &index, int role) const override; Q_INVOKABLE bool addNewEntry(const QString& askedQuestion, const QString& answer1, const QString& answer2, const QString& answer3, const QString& answer4, int correctAnswer, const QString& picturePath); private: QModelIndex mapIndex(const QModelIndex &index, int role) const; };
#include <QDebug> #include <QPixmap> #include <QBuffer> #include <QByteArray> namespace QuestionColumn { static constexpr auto id = 0; static constexpr auto askedQuestion = 1; static constexpr auto answer1 = 2; static constexpr auto answer2 = 3; static constexpr auto answer3 = 4; static constexpr auto answer4 = 5; static constexpr auto correct_answer = 6; static constexpr auto picture = 7; } QuestionsProxyModel::QuestionsProxyModel(QObject* parent) :QIdentityProxyModel(parent) { } QHash<int, QByteArray> QuestionsProxyModel::roleNames() const { QHash <int,QByteArray> roles; roles[idRole] = "id"; roles[askedQuestionRole] = "askedQuestion"; roles[answer1Role] = "answer1"; roles[answer2Role] = "answer2"; roles[answer3Role] = "answer3"; roles[answer4Role] = "answer4"; roles[correctAnswerRole] = "correctAnswer"; roles[pictureRole] = "picture"; return roles; } QVariant QuestionsProxyModel::data(const QModelIndex &index, int role) const { QModelIndex newIndex = mapIndex(index, role); if (role == idRole || role == askedQuestionRole || role == answer1Role || role == answer2Role || role == answer3Role || role == answer4Role || role == correctAnswerRole || role == pictureRole) { return QIdentityProxyModel::data(newIndex, Qt::DisplayRole); } return QIdentityProxyModel::data(newIndex, role); } bool QuestionsProxyModel::addNewEntry(const QString &askedQuestion, const QString &answer1, const QString &answer2, const QString &answer3, const QString &answer4, int correctAnswer, const QString &picturePath) { Q_ASSERT(!askedQuestion.isEmpty()); Q_ASSERT(!answer1.isEmpty()); Q_ASSERT(!answer2.isEmpty()); Q_ASSERT(!answer3.isEmpty()); Q_ASSERT(!answer4.isEmpty()); Q_ASSERT(correctAnswer >= 1 && correctAnswer <= 4); auto newRow = rowCount(); if(!insertRow(newRow, QModelIndex{})) { return false; } if(!setData(index(newRow, QuestionColumn::id), newRow + 1)) { removeRow(newRow); return false; } if(!setData(index(newRow, QuestionColumn::askedQuestion), askedQuestion)) { removeRow(newRow); return false; } if(!setData(index(newRow, QuestionColumn::answer1), answer1)) { removeRow(newRow); return false; } if(!setData(index(newRow, QuestionColumn::answer2), answer2)) { removeRow(newRow); return false; } if(!setData(index(newRow, QuestionColumn::answer3), answer3)) { removeRow(newRow); return false; } if(!setData(index(newRow, QuestionColumn::answer4), answer4)) { removeRow(newRow); return false; } if(!setData(index(newRow, QuestionColumn::correct_answer), correctAnswer)) { removeRow(newRow); return false; } if(!setData(index(newRow, QuestionColumn::picture), picturePath)) { removeRow(newRow); return false; } return true; } QModelIndex QuestionsProxyModel::mapIndex(const QModelIndex &source, int role) const { switch(role) { case idRole: return createIndex(source.row(), QuestionColumn::id); case askedQuestionRole: return createIndex(source.row(), QuestionColumn::askedQuestion); case answer1Role: return createIndex(source.row(), QuestionColumn::answer1); case answer2Role: return createIndex(source.row(), QuestionColumn::answer2); case answer3Role: return createIndex(source.row(), QuestionColumn::answer3); case answer4Role: return createIndex(source.row(), QuestionColumn::answer4); case correctAnswerRole: return createIndex(source.row(), QuestionColumn::correct_answer); case pictureRole: return createIndex(source.row(), QuestionColumn::picture); } return source; }
Now if I use the method
QuestionsProxyModel::addNewEntry
I would expect that all this data does get added to the SQL database but it doesn't.The strange thing is in the View in QML I can see the added data but on closing the application It is not stored in the database. Do I have to do something in addion of of using
insertRow
andsetData
to save to the database?Also
addNewEntry
only works the first time. The second timeinsertRow
simply returns false. -
I found that the data is only saved to the database of
QSqlTableModel
whensubmit
is called.So I added to the end of
addNewEntry
ofQuestionsProxyModel
:auto sqlModel = qobject_cast<QSqlTableModel*>(sourceModel()); if(sqlModel) { sqlModel->submit(); }
I find this solution a bit hacky. At least if the underlying model is not a
QSqlTableModel
theQuestionsProxyModel
would still work correct. -
Did you already took a look at QSqlTableModel::EditStrategy ?
-
@sandro4912 said in Adding data throught QIdentityModel to QSqlTableModel fails:
setEditStrategy(EditStrategy::OnFieldChange);
Yes I even set in the code above:
setEditStrategy(EditStrategy::OnFieldChange);
in the constructor. But it looks like calling
setData
alone in the Proxy does not save to the database. Except I addsubmit
-
@sandro4912
So far as I can see, you have written adata()
override, to callmapIndex()
etc., but have not written a similarsetData()
method? -
The missing
setData
seems to have solved half the puzzle. It was really silly to not implement it. Now I can edit and save the edit on single cells like this:bool QuestionsProxyModel::setData( const QModelIndex &index, const QVariant &value, int role) { QModelIndex newIndex = mapIndex(index, role); if (role == idRole || role == askedQuestionRole || role == answer1Role || role == answer2Role || role == answer3Role || role == answer4Role || role == correctAnswerRole || role == pictureRole) { return QIdentityProxyModel::setData(newIndex, value, Qt::EditRole); } return QIdentityProxyModel::setData(newIndex, value, role); }
void QuestionsProxyModel::edit(int row, const QVariant &value, const QString &role) { setData(createIndex(row,0), value, roleNames().key(role.toUtf8())); }
This works well in the SQLTableModel the EditStrategy is set like this:
QuestionSqlTableModel::QuestionSqlTableModel( QObject *parent, const QSqlDatabase &db) : QSqlTableModel{parent, db} { setTable("questions"); setSort(QuestionColumn::id, Qt::AscendingOrder); select(); setEditStrategy(EditStrategy::OnFieldChange); }
What does not work is adding an how new row an populate it like this:
bool QuestionsProxyModel::addNewEntry(const QString &askedQuestion, const QString &answer1, const QString &answer2, const QString &answer3, const QString &answer4, int correctAnswer, const QString &picturePath) { Q_ASSERT(!askedQuestion.isEmpty()); Q_ASSERT(!answer1.isEmpty()); Q_ASSERT(!answer2.isEmpty()); Q_ASSERT(!answer3.isEmpty()); Q_ASSERT(!answer4.isEmpty()); Q_ASSERT(correctAnswer >= 1 && correctAnswer <= 4); auto newRow = rowCount(); if(!insertRows(newRow, 1)) { return false; } if(!setData(createIndex(newRow, QuestionColumn::id), newRow + 1)) { removeRows(newRow, 1); return false; } if(!setData(createIndex(newRow, QuestionColumn::askedQuestion), askedQuestion)) { removeRows(newRow, 1); return false; } if(!setData(createIndex(newRow, QuestionColumn::answer1), answer1)) { removeRows(newRow, 1); return false; } if(!setData(createIndex(newRow, QuestionColumn::answer2), answer2)) { removeRows(newRow, 1); return false; } if(!setData(createIndex(newRow, QuestionColumn::answer3), answer3)) { removeRows(newRow, 1); return false; } if(!setData(createIndex(newRow, QuestionColumn::answer4), answer4)) { removeRows(newRow, 1); return false; } if(!setData(createIndex(newRow, QuestionColumn::correct_answer), correctAnswer)) { removeRows(newRow, 1); return false; } if(!setData(createIndex(newRow, QuestionColumn::picture), picturePath)) { removeRows(newRow, 1); return false; } return true; }
Here I still have to force a submit to make it work. Why is that so? Any Idea how I could prevent that? I really don't get were is the mistake here.
-
@sandro4912
EditStrategy
determines whenUPDATE SET column = value
statements are sent to the database on existing rows. Wouldn't make sense on a new which has yet to beINSERT
ed. You get your column values all right with thesetData()
s before you let it submit theINSERT
. In fact docs state:Note: To prevent inserting only partly initialized rows into the database, OnFieldChange will behave like OnRowChange for newly inserted rows.
To make your code nicer/smaller: because you are using
QSqlTableModel
you can use bool QSqlTableModel::setRecord(int row, const QSqlRecord &values) to compose your rows for insert/update in one go instead of onesetData()
at a time. And use QSqlRecord QSqlTableModel::record(int row) const to read records without needing multipledata()
calls. -
Im aware there exists these
QSQLTableModel
methods. I thougth I could stop using them when adding the proxy model. The problem I see is this way the proxy only works withQSQLTableModel
and not with any other model.But I guess regarding to the database it is alot cleaner because it reduces the queries like you said.
-
@sandro4912
I think I see what you mean. I was not thinking about your use of aQIdentityProxyModel
. I have only ever done updates directly on the source model of a proxy model, not through the proxy itself. Take my comments aboutrecord
in that light, maybe not appropriate for you.I still think my explanation of the need to submit manually when inserting a new row holds, regardless.
-
Yes following your explanation with the
EditStrategy
on inserting new rows the only way to get it to work I guess is to manually call submit on insert. Like I already do now. Good thing is if is not aSQLTableModel
the submit will not be called since theqobject_cast
fails.