Search code examples
c++qtqtsqlqsqlquerymodel

Pagination using QSqlQueryModel

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

The problem I am having is I want to use paging in extracting the data from an 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, I have:

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. Then, how do I adjust it so that I can use fetchmore() to fetch more data in a controlled manner.

Solution

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