How to use QSortFilterProxyModel + QSqlTableModel to limit rows?

[soroush]

Hi

I'm playing with QtSQL to learn more about. Just created a MySQL database with 100000 rows (foo, bar, baz). Simply trying to load whole table into a QSqlTableModel, the gui refuses to response (normally). I wonder it's possible to use a QSortFilterProxyModel to apply SQL limit on resulting set of query or not?

Well, that won't help much because data is actually loaded into original model. I should append LIMIT statement to select phrase of original model. Though I wonder if it's possible to get something like "paging" with a proxy or not? You know, first n rows in page 1, second n rows on page 2, ... and then may control pages over a nice GUI navigator.

[lgeyer]

QSqlTableModel (more specifically QSqlQueryModel) does automatically limit the number of fetched rows (to currently 255).

Can you share some code which reproduces the behavior?

[soroush]

[quote author="Lukas Geyer" date="1358791758"]QSqlTableModel (more specifically QSqlQueryModel) does automatically limit the number of fetched rows (to currently 255).

Can you share some code which reproduces the behavior?[/quote]

I didn't noticed about the 255 limit. I thought GUI can't show all results. Though that was too slow. Didn't profile exact time, but I can say it takes more than 2 seconds.

Currently I don't have access to that code.

[eJinn]

Hi, all!

I want to continue this theme.

[quote author="Lukas Geyer" date="1358791758"]QSqlTableModel (more specifically QSqlQueryModel) does automatically limit the number of fetched rows (to currently 255).[/quote]
Unfortunately this is not quite true.
It's true, if I'm using Firebird/iBase database and, accordingly, qsqlibase driver.
But if I want use MySQL database, and qsqlmysql driver, QSqlQueryModel reads the entire database, in model setting:
@ QTableView* view = new QTableView;
QSqlQueryModel *model = new QSqlQueryModel;
model->setQuery("SELECT * employee");
view->setModel(model);
view->show();
@
Model reads all my 300 000 rows in database and it takes about 10 seconds. My database is expected to have a size of 10 million records. It takes about 300 seconds, which is unacceptable.
How to make sure that it reads not all at once, and in parts.

Use MySQL - a prerequisite.

[htettey]

Hi all,

I have just run into the problem explain by ejin.
Was there a resolution to this? I am using MySQL and my model reads all my records (about 100,000) . This is using about 1Gig of RAM.

Please advise.

Thank you

[VRonin]

The way I solved this is run the query manually and then inserting the values into a QStandardItemModel. The downside is that your DB might remain busy untill you read the entire query

bool fillModel(QAbstractItemModel* model, QSqlQuery& query, unsigned int maxRows)
{
    // insert up to maxRows row in the model with data from query
    for (unsigned int i = 0U; i < maxRows; ++i) {
        if (!query.next())
            return false;
        const QSqlRecord currRec = query.record();
        if (i == 0U) {
            // Clear the model
            model->removeRows(0, model->rowCount());
            model->removeColumns(0, model->columnCount());
            model->insertColumns(0, currRec.count());
            for (int j = 0; j < currRec.count(); ++j)
                model->setHeaderData(j, Qt::Horizontal, currRec.fieldName(j));
        }
        model->insertRow(i);
        for (int j = 0; j < currRec.count(); ++j) {
            model->setData(model->index(i, j), currRec.value(j));
        }
    }
    return true;
}

to move to the next page call fillModel again. if the function returns false there are no more pages

[Mark Ch]

I have been able to achieve this quite easily in Qt 5.7 by subclassing QSqlRelationalTableModel and reimplementing selectStatement() as

QString NewModel::selectStatement() const
{
    return QSqlRelationalTableModel::selectStatement() + QString(" LIMIT 100");
}

The same should work for QSqlTableModel.