How to use QSortFilterProxyModel + QSqlTableModel to limit rows?



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



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



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



  • 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



  • 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



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


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.