QSqlTableModel::insertRows, using the row parameter

Hello

I have a QTableView with a QSqlTableModel as its model.

The problem is that the newly added rows will be displayed at the end of the view, no matter what is the value of row.

Documentation of QSqlTableModel::insertRows says: "Inserts count empty rows at position row." and in QAbstractItemModel::insertRows it says: "inserts count rows into the model before the given row."

If "into the model" means it has no predicted effect on the view, what's the use of row?

The function does exactly what it says it does. The view may be sorting the rows, you may be using an invalid row number etc. We don't know unless you can provide a small example that demonstrates the problem. This, for example, works fine:

Code:

---

#include <QtGui>
#include <QtSql>
#include <QDebug>
 
void createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (db.open()) {
        QSqlQuery query;
        query.exec("create table person (id int, "
                   "firstname varchar(20), lastname varchar(20), primary key(firstname, lastname))");
        query.exec("insert into person values(101, 'Danny', 'Young')");
        query.exec("insert into person values(102, 'Christine', 'Holand')");
        query.exec("insert into person values(103, 'Lars', 'Gordon')");
        query.exec("insert into person values(104, 'Roberto', 'Robitaille')");
        query.exec("insert into person values(105, 'Maria', 'Papadopoulos')");
    }
}
 
int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    createConnection();
 
    QSqlTableModel model;
    model.setTable("person");
    model.setEditStrategy(QSqlTableModel::OnManualSubmit);
    model.select();
 
    QTableView view;
    view.setModel(&model);
    view.resize(640, 480);
    view.show();
 
    model.insertRow(2);
    model.insertRow(5);
 
    return app.exec();
}

---

If you use the other edit strategies then you can only insert one row at a time (see QSqlTableModel::insertRows()) .

Thanks

Now I know QSqlTableModel::insertRows() adds a new empty row where it is given by the row parameter, but when I set data for that row, the position of the row changes and it will appear at the end (actually it happens when I set data for the path column in the table, you can see table creation command below).
I think the view is not sorted because QTableView::isSortingEnabled() returns false.

The code for creating table:

Code:

---

bool LibMan::createLib(const QString &libName)
{
    QSqlQuery query;
 
    return query.exec("CREATE TABLE " + libName + "(\
                                                 id INTEGER PRIMARY KEY AUTOINCREMENT,\
                                                 title VARCHAR(255),\
                                                 path VARCHAR(255) NOT NULL)");
}

---

Inserting new data:

Code:

---

void LibMan::insertRecords()
{
    lastIndex = 0;
 
    if (insertionList->count() > 1)
    {
        tabModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    }
 
    tabModel->insertRows(0, insertionList->count());
 
    tabModel->submitAll();
 
    tabModel->setEditStrategy(QSqlTableModel::OnRowChange);
}

---

Initializing:

Code:

---

tabModel = new SqlTableModel(this);
    tabModel->setTable("pap");
    tabModel->setHeaderData(1,Qt::Horizontal, QObject::tr("Title"));
    tabModel->setHeaderData(2, Qt::Horizontal, QObject::tr("Location"));
    tabModel->setEditStrategy(QSqlTableModel::OnRowChange);
 
    proxyModel = new SortFilterProxyModel(this);
    proxyModel->setSourceModel(tabModel);
    proxyModel->setDynamicSortFilter(true);
    proxyModel->setFilterKeyColumn(1);
 
    ui->tableView->setModel(tabModel);
//    ui->tableView->setModel(proxyModel);
 
    ui->tableView->setColumnHidden(0, true);
    ui->tableView->resizeColumnsToContents();
 
//    if (ui->tableView->isSortingEnabled()) return;
 
    QObject::connect(tabModel, SIGNAL(primeInsert(int,QSqlRecord&)), this, SLOT(fill(int,QSqlRecord&)));
    QObject::connect(proxyModel, SIGNAL(dataChanged(QModelIndex,QModelIndex)), this, SLOT(selectEdited(QModelIndex,QModelIndex)));
 
//    tabModel->insertRows(24,1);

---

SqlTableModel is a subclass of QSqlTableModel, does nothing special except coloring empty fields.

And the slot fill():

Code:

---

void LibMan::fill(int row, QSqlRecord &record)
{
    record.setValue(2, insertionList->at(lastIndex++));
}

---

---

Added after 55 minutes:

ChrisW67, I have changed your example and now it is a working example that demonstrates the problem:

Code:

---

#include <QtGui>
#include <QtSql>
#include <QDebug>
 
void createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("D:\\tempDB\\mydb5");
    if (db.open()) {
        QSqlQuery query;
        QString str = "pap";
 
   query.exec("CREATE TABLE " + str + "(\
                                        id INTEGER PRIMARY KEY,\
                                        title VARCHAR(255),\
                                        path VARCHAR(255) NOT NULL)");
 
        query.exec("insert into pap values('1', 't1', 'test1')");
        query.exec("insert into pap values('2', 't2', 'test2')");
        query.exec("insert into pap values('3', 't3', 'test3')");
        query.exec("insert into pap values('4', 't4', 'test4')");
        query.exec("insert into pap values('5', 't5', 'test5')");
    }
}
 
int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    createConnection();
 
    QSqlTableModel model;
    model.setTable("pap");
    model.setEditStrategy(QSqlTableModel::OnRowChange);
    model.select();
 
    QTableView view;
    view.setModel(&model);
    view.setEditTriggers(QAbstractItemView::DoubleClicked);
    view.resize(640, 480);
    view.show();
 
    model.insertRows(2,1);
    model.setData(model.index(0,2), "temp");
 
    return app.exec();
}

---

Now if you change the data in the incomplete row, it will appear at the end of the list. It seems that it is sorted by column id, but sorting is not enabled

Row numbers are meaningful inside model, not inside the underlying database. The order in which records retrieved from database affects the row numbers in model (view). Therefore, submitting data into DB may change the row number.
Is it true?

Quote:

---

Is it true?

---

Yes. There is no notion of a "row number" in the SQL table. When the data is submitted the model re-runs a select query ('select * from table' without sorting) and the table rows are returned in a system dependent indeterminate order. In many cases the rows are returned in creation order or order of an internal id (like you see) but some databases return rows in order of primary key if there is one.

You can use QSqlTableModel::setSort() to impose a simple sort order on retrieval, i.e. it adds an ORDER BY clause to the query, but you will need to have a column to sort in your data. Alternatively you can use the view to sort based on the same column data.

Well, using QSqlTableModel::insertRows() one can only place non-submitted records in the given row, when this might be useful? I mean usually for inserting new rows we use the value of 0 for the row why should one want to change this value to e.g 10 when both might be changed after submitting?

It is useful if the displayed position of the new row reflects where it will be seen after it is committed (because you are sorting in a consistent way), or indicates some relationship to the rows around it, or puts the new row near another for ease of reference while filling the row before commit, or provides a consistent location that a new row appears e.g always at the top/bottom of the table/visible rows, or the new row uses primeInsert() to gain default values and appears in the expected place for those values...

Not all models have the behaviours imposed on QSqlTableModel by the underlying SQL engine. Other models insert item at row n, and that's where it stays.