How can I find out if a record already exists in the QSqlTableModel / QSortFilterProxyModel?

? Offline

Hello all,

I'm facing a programming problem that I still have no idea how to implement. Maybe the experienced here can give me a tip. I try to show my problem with an example.

I have a QSqlTableModel that is assigned to a QSortFilterProxyModel. The QSortFilterProxyModel is in turn assigned to a QListView.

Via a button I want to add a record (keyword: insertRow), which already works. Now let's assume my model looks like this:


+----+-------+
| ID | Name  |
+----+-------+
|  1 | Name1 |
|  2 | Name2 |
|  3 | Name3 |
| .. | ...   |
+----+-------+

If I now insert the insertRow via the button and enter a new name, I want to check in advance whether the name is already available in the model. If it is available, a QMessageBox should appear asking if the ID of the existing name should be linked to another ID from another model.

As an example: I press the button and insert a new row and enter the name "Name2". Then I want to get the feedback that the name already exists in the model and whether this ID 2 should be linked with the ID from another model.

The same should happen if I take an already existing name and change it to an already existing one. E.g. if I rename "Name3" to "Name2" then the same message should appear.

I hope I could express what I want to achieve.

My question:

where do I implement this? In the delegate?
how do I implement this?

Maybe it is quite simple and I think too complicated or I am just too stupid to come up with the solution.

Christian Ehrlicher

Iterate through the model and see if there's already a name you're currently trying to add.

? Offline

@Christian-Ehrlicher

And where? In the Delegate or afterwards? Here is my code that does the inserting and setting of the data.

connect(mGermanNameEditor->itemDelegate(), &QAbstractItemDelegate::closeEditor, this, &MainWindow::germanNameEditingFinished);

void MainWindow::on_germanNameButton_clicked()
{
    if(mGermanNameEditor->selectionModel()->hasSelection()){

        QModelIndex index = mGermanNameEditor->currentIndex();
        const QModelIndex sourceIndex = mGermanNameFilter->mapToSource(index);

        const int id = mGermanNameFilter->sourceModel()->index(sourceIndex.row(),0).data().toInt();

        if(mGermanNameIdList.contains(id))
            mGermanNameIdList.removeOne(id);

        mGermanNameFilter->setIdFilterList(mGermanNameIdList);

        mGermanNameFilter->sourceModel()->removeRow(sourceIndex.row());

        index = mGermanNameEditor->currentIndex();
        mGermanNameEditor->selectionModel()->select(index, QItemSelectionModel::Deselect);
    } else {

        const int row = mGermanNameFilter->sourceModel()->rowCount();

        if(mGermanNameFilter->sourceModel()->insertRow(row)){

            const QModelIndex idSourceIndex = mGermanNameFilter->sourceModel()->index(row,0);
            const QModelIndex nameSourceIndex = mGermanNameFilter->sourceModel()->index(row,1);

            int lastId = -1;
            int id = -1;
            for (int row=0; row<mGermanNameFilter->sourceModel()->rowCount(); row++){
                id = mGermanNameFilter->sourceModel()->index(row,0).data().toInt();
                if(id > lastId)
                    lastId = id;
            }
            mGermanNameFilter->sourceModel()->setData(idSourceIndex, lastId+1, Qt::EditRole);
            mGermanNameIdList.append(idSourceIndex.data().toInt());
            mGermanNameFilter->setIdFilterList(mGermanNameIdList);

            const QModelIndex index = mGermanNameFilter->mapFromSource(nameSourceIndex);
            if(index.isValid()) {
                mIsNameAdded = true;
                editGermanName(index);
            }
        }
    }
}

void MainWindow::editGermanName(const QModelIndex &index)
{
    ui->germanNameButton->setDisabled(true);
    mGermanNameEditor->setCurrentIndex(index);
    mGermanNameEditor->edit(index);
}

void MainWindow::germanNameEditingFinished()
{
    const QModelIndex index = mGermanNameEditor->currentIndex();
    const QModelIndex sourceIndex = mGermanNameFilter->mapToSource(index);
    if(mIsNameAdded){
        if(index.data().toString().isEmpty()){
            mGermanNameFilter->sourceModel()->removeRow(sourceIndex.row());
        } else {
            
            const int lastId = sourceIndex.siblingAtColumn(Column::ID).data().toInt();
            const int row = mGermanFungusNameFilter->sourceModel()->rowCount();

            if(mGermanFungusNameFilter->sourceModel()->insertRow(row)){
                mGermanFungusNameFilter->sourceModel()->setData(mGermanFungusNameFilter->sourceModel()->index(row,0), lastId, Qt::EditRole);
                mGermanFungusNameFilter->sourceModel()->setData(mGermanFungusNameFilter->sourceModel()->index(row,1), ui->idEditor->text(), Qt::EditRole);
            }
       }
       mIsNameAdded = false;
    }


    ui->germanNameButton->setDisabled(false);
    mGermanNameEditor->selectionModel()->select(index, QItemSelectionModel::Deselect);
}

JonB

@Gabber
As @Christian-Ehrlicher says, you have to search the model yourself if you want to know whether something is/is not there. Note a link wrinkle: if you have a QSortFilterProxyModel and if that is sorted by the lookup column (e.g. Name here) you can do a binary search for a key value if you go via the QSFPM's rows/indexes rather than the source model's. Maybe not for you here, but if you have a lot of rows it could be important to know.

For your where/when: if you want something to be checked at the end of editing in a QStyledItemDelegate you can do it in a slot on closeEditor() as you have done or in subclassed methods.

? Offline

@JonB
Thanks for the feedback. However, one thing is not clear to me. Here's what else I found out: I could intercept the signal commitData which says the following:

CommitData:

This signal must be emitted when the editor widget has completed editing the data, and wants to write it back into the model.

If I intercept this signal and iterate over my model then it always tells me "record exists".

connect(mGermanNameEditor->itemDelegate(), &QAbstractItemDelegate::commitData, this, &MainWindow::commitData);

void MainWindow::commitData(QWidget *editor)
{
    qDebug() << "Commit data entered";
    const QLineEdit *line = qobject_cast<const QLineEdit*>(editor);
    auto text = line->text();
    for (int row=0; row<mGermanNameFilter->sourceModel()->rowCount(); row++){
        const QString name = mGermanNameFilter->sourceModel()->index(row,1).data().toString();
        if(name == text)
            qDebug() << "Record exist: " << line->text();

    }

}

But why is not clear to me? I thought the whole thing is taken from the editor before it is written into the model. Any idea?

JonB

@Gabber said in How can I find out if a record already exists in the QSqlTableModel / QSortFilterProxyModel?:

I thought the whole thing is taken from the editor before it is written into the model.

That is how I interpret the documentation. For all I know, your name does already exist in the model and this is expected behaviour, that's exactly what the topic is about. Or you have done something to create the item in the model already. Why don't you start from a model with 0 rows and check the logic?

You can also possibly understand more if you subclass QStyledItemDelegate, you can override its virtual setModelData() method.