Paging using QSqlQueryModel

[Mandeep.Chaudhary]

Hi,

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

So far in the project what i have done is that i am connecting to a sql db file and qt (C++) reading the data from sql db. Then this is passed to qml and in qml the respective information is shown using listview. So far it is working.

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 get any simple example of making me aware of how to use QSQLQuerymodel for paging. Neither I found any information on this forum.

Please help me to find a suitable example of paging via QSqlQueryModel. Or please tell me which property of QsqlQuerymodel is responsible for controlling the number of data to be fetched at one time.

For example 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.

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

[Mandeep.Chaudhary]

Update

I found the information that QSQLQueryModel fetches only 256 rows, #define QSQL_PREFETCH 255 .

But why or where it is described. I found this information from a random post https://stackoverflow.com/questions/42286016/qsqlrelationaltablemodel-only-populates-first-256-records

There should be proper documentation atleast by QT to explain this thing ?

If I am wrong and I missed something to read please let me know where to find it.

Only the closest thing i can find is https://code.woboq.org/qt5/qtbase/src/sql/models/qsqlquerymodel.cpp.html

[JonB]

@Mandeep-Chaudhary
It is described, that's just the way the class works internally a buffer at a time, that's why there is canFetchMore() and fetchMore().

You implement your own paging on top of this. You could pick your own size, or you might utilize the default 256 and maybe an optimization on that, but you don't have to. As the solution in the SO post says you could subclass fetchMore() for convenience.

[Mandeep.Chaudhary]

@JonB Thank you for the reply. How can i customise the query to fetch more than 256 (which is default), for example lets say 512. Which parameter for example in setQuery() is responsible for that.

Thank You !!!

[JonB]

@Mandeep-Chaudhary
I'm on vacation, so I'm not going to write an essay! [I did write a mini essay ;-) ] In outline:

As you can see, there isn't such a parameter to setQuery(), nor elsewhere. The SO post has said that Qt hard-codes fetching 256 rows at a time, and you cannot alter that. You will thus want something like:

model->setQuery();
while (model->rowCount() < 512 && model->canFetchMore())
    model()->fetchMore()

That will get you one of the buffer's-worth you want. Then when the user goes to the next page you would need to do the while part again. Because you can't clear out the rows already fetched you'd now have 1024 rows in your buffer to display to the user. That may or may not be acceptable.

However, you must also read the the documentation https://doc.qt.io/qt-5/qsqlquerymodel.html#fetchMore. How that behaves depends on your database driver, QSQLITE.

If you really want it to be proper "pages", with only 512 rows at a time in memory/shown to user, then so far as I know from Qt you must adopt one of two different techniques:

1. Use the rows fetched by QSqlQuery as a "staging" area. Copy desired buffer's-worth of rows out of that into, say, your own QStandardItemModel, so that the model really only contains 512 rows at any time. Connect your view to that model.

2. Append the SQL ORDER BY ... LIMIT 512 to your setQuery() string. That will make the database only return max 512 rows (you [may] still need to use the fetchMore() to get that many). That gets the first 512 page. To move to the next page, you must look at the primary key value of the last record in the current buffer and then make a new query which restarts at that point. So if the first query is:

model->setQuery("select * from events order by event_id limit 512");

the next buffer must be fetched via something like:

last_pk_value = model->rows[model->rowCount() - 1].event_id;
model->setQuery("select * from events where event_id > " + last_pk_value + " order by event_id limit 512");

This is not exact syntax! The important is the where clause part, to make the next 512 start from just beyond where you got to in the previous page.

All of the above is given that I don't use SQLite and is generic SQL principles. I see SQLite may have an optional OFFSET for LIMIT to help where to (re-)start from. You may want to look that up to help. However, I believe with OFFSET it may have to re-read from the beginning each time to reach the offset, this can be slow if many records as you get to later buffers.