How to add new values to QSqlRelationTableModel?

[JonB]

@Gabber
I have glanced through your question, but not looked too deeply.

If table A has a foreign key index into table B, and table B is using a database auto-incrementing number for its PK (is that your situation?) then you first need to add a new row you wish to reference into the referenced table B. Then you should submit that. Then get its newly-generated PK, and use that to make whatever row(s) in table A point to that row in table B, and now submit table A. If you are confident you can calculate what the new incremented number will be yourself then you may be able to submit both tables in one go. I do not know whether Qt guarantees to do both submits in the required order (B follow by A) if you do them both in one go. Don't know whether this helps :)

[A Former User]

@JonB
Thank you for your advice.
I am now so far that it works. My code:

void MainWindow::on_addImageButton_clicked()
{
    QStringList filenames = QFileDialog::getOpenFileNames(this, QString(), QDir::homePath(), "Bilder (*.png *.jpeg *.jpg)", nullptr);
    
    const int fungusId = ui->idEditor->text().toInt();
    mImageTableModel->relationModel(1)->setEditStrategy(QSqlTableModel::OnManualSubmit);
    mImageTableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);

    int rowCount = mImageTableModel->rowCount();
    int imageRowCount = mImageTableModel->relationModel(1)->rowCount();

    if(!mImageTableModel->insertRows(rowCount, filenames.count())) {
        qDebug() << "Insert row Error: " << mImageTableModel->lastError().text();
        return;
    }

    qDebug() <<"imageRowCount: " <<imageRowCount;
    for(int i=0; i<filenames.count(); i++){
        QFile file = filenames.at(i);
        QFileInfo fileInfo(file.fileName());
        if (!file.open(QIODevice::ReadOnly))
            return;
        QByteArray imageByteArray = file.readAll();

        int imageRowCount = mImageTableModel->relationModel(1)->rowCount();
        auto result = mImageTableModel->relationModel(1)->insertRow(imageRowCount);

        auto image = mImageTableModel->relationModel(1)->setData(mImageTableModel->relationModel(1)->index(imageRowCount,1), imageByteArray, Qt::EditRole);

        mImageTableModel->relationModel(1)->submitAll();

        imageRowCount = mImageTableModel->relationModel(1)->rowCount();
        QModelIndex lastIndex = mImageTableModel->relationModel(1)->index(imageRowCount - 1, 0);

        qDebug() << "lastIndex: " << lastIndex.data().toInt();


        auto a = mImageTableModel->setData(mImageTableModel->index(rowCount+i,0), fungusId, Qt::EditRole);
        auto b = mImageTableModel->setData(mImageTableModel->index(rowCount+i,1), lastIndex.data().toInt(), Qt::EditRole);
        auto c = mImageTableModel->setData(mImageTableModel->index(rowCount+i,2), fileInfo.fileName(), Qt::EditRole);
        qDebug() << "a: " << a << ", b: " << b << ", c: " << c << ", image: " << image;
        mImageTableModel->submitAll();
    }
}

The whole thing is also transferred directly to the database.

What I don't know yet: How can I undo the whole thing when I press my "Cancel" button?

[JonB]

@Gabber
As @mrjj said, you cannot revert once you have committed and you cannot database rollback once you have database committed. Your data has gone to storage now.

Cancel should be an alternative to Save. You are not asking to cancel now, you are asking to undo.

You would need to delete the new (record)s from the table(s).

[A Former User]

@JonB
Okay purely out of interest. Is there another way, when I have inserted the data with setData, to submit the whole thing to the view without calling submitAll?

Because then I could call submitAll with the "Save" button and revertAll with the "Cancel" button.

Or how can I cache the whole thing in the model and write it to the database only when I click "Save" and when I press "Cancel" reset the whole thing?

[JonB]

@Gabber said in How to add new values to QSqlRelationTableModel?:

when I have inserted the data with setData, to submit the whole thing to the view without calling submitAll?

I don't know what " submit the whole thing to the view" means to you. submitAll() should only be used/needed to submit all changes to the database. Just doing setData()s should be reflected in views.

[A Former User]

@JonB said in How to add new values to QSqlRelationTableModel?:

I don't know what " submit the whole thing to the view" means to you

Sorry for the poor explanation. Maybe a picture says more than words.

If I now click on "Bild hinzufügen" and select my images, they should automatically appear in the QLabel that currently says "Kein Bild vorhanden".

When I press the "Speichern" button it should be written to the database, when I press "Abbrechen" the freshly added images should be discarded and further on it should say "Kein Bild vorhanden" and no data should be added to database.

[JonB]

@Gabber
So all I can say is:

• "Bild hinzufügen" => call insert...() & setData(), do not call submit...().
• "Speichern" => call submit...()/ commit().
• "Abbrechen" => call revert...().
• "Kein Bild vorhanden" => do nothing.

This does not allow e,g, "Speichern" followed by "Abbrechen" or "Kein Bild vorhanden".

[A Former User]

@JonB
Yes that is exactly what I want. But now when I call insertRow() and setData() and select my images and setData always returns true the images are not displayed. It still says "Kein Bild vorhanden".

But I want to see the images directly after adding them and not when I press save, so that submitAll is called.

[JonB]

@Gabber said in How to add new values to QSqlRelationTableModel?:

But now when I call insertRow() and setData() and select my images and setData always returns true the images are not displayed

Then you should find out why this is the case and do whatever to address it, rather than having to submit to the database to make it work. I do not know what the exact issue is. If you have to submit and re-read to make yours work then you would have to explicitly delete from database to undo/revert, which is not desirable.

[A Former User]

@JonB
Unfortunately I still haven't found a solution why the model is not updated without calling submitAll().

Could this be related to this code section?

        mImageTableModel = mDatabaseManager->imageTableModel(); //mImageTableModel = QSqlRelationTableModel
        mImageTableModel->setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);
        mImageFilterModel->setSourceModel(mImageTableModel); // mImageFilterModel = QSortFilerProxyModel
        mImageMapper->setModel(mImageFilterModel);
        mImageMapper->setItemDelegate(new ImageDelegate(mImageMapper));
        mImageMapper->addMapping(ui->imageView, mImageTableModel->fieldIndex("Bild"));

In my ImageDelegate the setModelData method is never called. The setEditorData is called

ImageDelegate::ImageDelegate(QObject *parent): QStyledItemDelegate(parent) {}

ImageDelegate::~ImageDelegate()
{
    qDebug() << "ImageDelegate deleted";
}

void ImageDelegate::setEditorData(QWidget *editor, const QModelIndex &index) const
{
    QLabel *label = qobject_cast<QLabel *>(editor);
    if (label) {
        QByteArray imageData = index.data(Qt::EditRole).toByteArray();
        QPixmap pixmap;
        if (pixmap.loadFromData(imageData)){
            int width = label->width();
            int height = label->height();
            label->setPixmap(pixmap.scaled(width,height,Qt::KeepAspectRatio));
        }
    }
    QStyledItemDelegate::setEditorData(editor, index);
}

void ImageDelegate::setModelData(QWidget *editor, QAbstractItemModel *model, const QModelIndex &index) const
{
    QLabel *label = qobject_cast<QLabel *>(editor);
    if (label) {
        QBuffer buffer;
        buffer.open(QIODevice::WriteOnly);
        if (label->pixmap(Qt::ReturnByValue).save(&buffer))
            model->setData(index, buffer.data(), Qt::EditRole);
    }
    QStyledItemDelegate::setEditorData(editor, index);
}

Can you please help me again? I find it very unpleasant when I have to write my pictures into the database every time and when I want to cancel I have to delete them from the database again.

The reason why I did this was this thread.