QT paging using QSqlQueryModel

Question

I am looking for a solution of creating paging using QSqlQueryModel.

Problem I am having is I want to use paging in extracting the data from sql db file , for this i have chosen QSqlQueryModel. But now the problem is that I cannot control of how many records it will fetch.

Basically if there are 1000 of records in database and I want only 20 entries to be fetched initially and rest on another call 20 more entries and later on another call 20 more entries and so on. How to do that ?

So far the codes are

QSqlQueryModel *model = new QSqlQueryModel;
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("/Users/xyz/events.db");

if (db.isValid())
{
    db.open();
    if (db.isOpen())
    {
        model->setQuery("select * from events");

        qDebug() << "I m Working";
        QSqlRecord rec = model->record(0);
        qDebug() << rec;
        qDebug() << model->canFetchMore();

        db.close();

    } else {
        qDebug() << "DB is not open";
    }
} else {
    qDebug() << "DB is not valid";
}

There is canFetchMore() function in QSqlQueryModel. So if setQuery fetches all the data than it will always be false than how to adjust it that i can use fetchmore() to fetch more data in a controlled manner.

Thank You

If you want to know how `QSqlQueryModel` works, you should look at the source code: https://code.woboq.org/qt5/qtbase/src/sql/models/qsqlquerymodel.cpp.html. The class handles (should handle) the partial fetching out of box, and you don't need to implement your own mechanism. — vahancho, Sep 24 '19 at 12:07
But where do they explain this thing i.e. there mechanism. However, thank you for replying. — Mandeep, Sep 24 '19 at 12:16
The explanation is in the source code. Basically if view needs to show more items due to scrolling it down, model fetches another set (255) rows and appends them to the bottom. — vahancho, Sep 24 '19 at 12:34
@vahancho Yes thank you I found that from your first reply only. If you could put all this as answer i would have ticked it as solved. A quick question for paging, Is this the only class with the built in functions or are there any more classes for doing paging efficiently and avoiding to make our own custom methods. Thank you — Mandeep, Sep 24 '19 at 12:35

score 1 · Accepted Answer · answered Sep 24 '19 at 12:53

If you want to know how QSqlQueryModel works, you should look at the source code. The class handles (should handle) the partial fetching out of box, and you don't need to implement your own mechanism.
Basically if the view needs to show more items due to scrolling it down, model fetches another set (255 according to implementation) rows and appends them to the bottom.

Basically all models are aimed to provide performance efficient fetching. The main idea is that the view requests more data from its model as soon as it needs to show it. If you have 1000 data items and the view is not able to show them at once, it doesn't need all 1000. If you debug your model you will see how the QAbstractItemModel::data() function called and with which arguments: only visible model indexes provided.

score 0 · Answer 2 · answered Sep 24 '19 at 09:48

What do you want is pagination/paging, isn't it? From https://doc.qt.io/qt-5/qsqlquerymodel.html#details

QSqlQueryModel is a high-level interface for executing SQL statements and traversing the result set. It is built on top of the lower-level QSqlQuery and can be used to provide data to view classes such as QTableView

Here there is a questions with paging in sqlite. Efficient paging in SQLite with millions of records

with sql https://learn.microsoft.com/en-us/previous-versions/sql/compact/sql-server-compact-4.0/gg699618(v=sql.110)?redirectedfrom=MSDN

LOL i know that using Limit Thing in SQL statement. What I was or I am asking is that SQLQUERYMODEL uses in built functions / methods such as canFetchMore() and fetchmore(). How do they work in regard to paging ? — Mandeep, Sep 24 '19 at 09:53

QT paging using QSqlQueryModel

2 Answers2