Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. How can I find out if a record already exists in the QSqlTableModel / QSortFilterProxyModel?
QtWS25 Last Chance

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

Scheduled Pinned Locked Moved Unsolved General and Desktop
6 Posts 3 Posters 313 Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • ? Offline
    ? Offline
    A Former User
    wrote on last edited by
    #1

    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:

    1. where do I implement this? In the delegate?
    2. 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.

    1 Reply Last reply
    0
    • Christian EhrlicherC Online
      Christian EhrlicherC Online
      Christian Ehrlicher
      Lifetime Qt Champion
      wrote on last edited by
      #2

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

      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
      Visit the Qt Academy at https://academy.qt.io/catalog

      ? 1 Reply Last reply
      0
      • Christian EhrlicherC Christian Ehrlicher

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

        ? Offline
        ? Offline
        A Former User
        wrote on last edited by
        #3

        @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);
        }
        
        JonBJ 1 Reply Last reply
        0
        • ? A Former User

          @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);
          }
          
          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by
          #4

          @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.

          ? 1 Reply Last reply
          2
          • JonBJ 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
            ? Offline
            A Former User
            wrote on last edited by
            #5

            @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?

            JonBJ 1 Reply Last reply
            0
            • ? A Former User

              @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?

              JonBJ Offline
              JonBJ Offline
              JonB
              wrote on last edited by JonB
              #6

              @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.

              1 Reply Last reply
              0

              • Login

              • Login or register to search.
              • First post
                Last post
              0
              • Categories
              • Recent
              • Tags
              • Popular
              • Users
              • Groups
              • Search
              • Get Qt Extensions
              • Unsolved