QSqlTableModel insert and remove row - Comprehension question

? Offline

Hello,

I have a question regarding insertRow() and removeRow() in combination with rowCount() on a QSqlTableModel.

Let's assume the following example. I have a database with 100 records and I want to insert a few more records, then I could do it like this:

int rowCount = myModel->rowCount();
myModel->insertRow(rowCount);
//do something with insertRow and save back to database
myModel->submitAll();

Suppose I run this code snippet three times in a row. Then I would have 103 entries in my database. Let's assume the database has a very simple structure and has only one id (primary key) and one name. Then my last id in the database would be 103.

Now I want to delete a record. Let's assume I want to delete the record with the id 100. I select the record from the QListView and execute the following:

auto index = myView->currentIndex();
myModel->removeRow(index.row());
myModel->submitAll()

Now I want to insert a record again and execute the first code part again. And this is the problem, rowCount() now returns 102. But the ID 102 is already assigned.

So how can I insert data into a QSqlTableModel and delete it again? Is there a possibility to get the last ID from the QSqlTableModel? Or how do I implement something like that?

? Offline

@JonB
Thanks for the tips. It was easier than I thought. I'll just iterate over the model now. Here is my example:

        int lastId = 0;
        int id = 0;
        for (int row= 0; row<mGermanNameFilter->sourceModel()->rowCount(); row++)
        {
            //mGermanNameFilter = QSortFilterProxyModel
            id = mGermanNameFilter->sourceModel()->index(row,0).data().toInt();
            if(id > lastId)
                lastId = id;
        }

SGaist

Hi,

SQL primary keys usually increments. The fact that you delete some row somewhere will not make the ID of that row the "next to use".

JonB

@Gabber
The auto-incrementing ID gets done at the database side. You should not assume, nor need to assume, that the 100th row in your model means the row with ID 100. You get the ID column value for each row in the model, use that if you need to identify the database row to delete/update. And I believe that after submitAll() it re-reads the rows from the database so your insertions/deletions have taken effect.

? Offline

@SGaist @JonB

I'm sorry, I must have expressed myself wrong again. In other words, how do I get the last ID in my QSqlTableModel? I need this to insert it into another model. If we take the example above I would have the IDs: 99, 101, 102, 103, so how do I get the 103 in the QSqlTableModel? Is there a function that I have overlooked?

JonB

@Gabber said in QSqlTableModel insert and remove row - Comprehension question:

so how do I get the 103 in the QSqlTableModel?

How would you get it into your QSqlTableModel? I assume the actual new ID is generated at the SQL database side when an inserted row is committed? So it is there after the submitAll(). As I said, I believe that repopulates your table model, or at least it says

In OnManualSubmit, on success the model will be repopulated

Look in the QSqlTableModel after the submitAll() and see which PK values are there. Repopulate if desired. Or you can always issue a one-off QSqlQuery to ask for the highest ID number at any time.

How would you find it once it is your QSqlTableModel? Look through the rows at the ID column value, discovering the highest. Or let sort() or QSortFilterProxyModel sort it for you so you can pick the highest quickly.

? Offline

@JonB
Thanks for the tips. It was easier than I thought. I'll just iterate over the model now. Here is my example:

        int lastId = 0;
        int id = 0;
        for (int row= 0; row<mGermanNameFilter->sourceModel()->rowCount(); row++)
        {
            //mGermanNameFilter = QSortFilterProxyModel
            id = mGermanNameFilter->sourceModel()->index(row,0).data().toInt();
            if(id > lastId)
                lastId = id;
        }

JonB

@Gabber
Yep, now I understand what you were asking for.

If you want to squeeze some microseconds out of it, if you use QSqlTableModel::setSort() on the ID column that will do an ORDER BY in the query and hence the order of the rows in the model. Then the highest ID should be at rowCount() - 1, to save searching. Would only matter if you had a large number of rows in the table/model.

There is one "gotcha". Are you going to have more than 256 rows in your table?

? Offline

@JonB
Thanks for your hint, but the few microseconds do not matter for me. Yes, there are about 7500 entries.

JonB

@Gabber
Then don't forget when searching a whole QTableModel Qt only reads 256 rows at a time (unless that has changed at 6). You will need to call fetchMore() till all rows have been read if you intend to search them all, e.g. to find the highest ID. That is where the ORDER BY can help if necessary.

mchinand

Will it get the next value correctly if you just don't put anything for the primary ID and let the database handle giving it a value like it would in a normal INSERT command (i.e for Postgres it will get the next value in the sequence associated with that column (values are never re-used), SQLITE I think will be one more than the current max value)?