QSqlQueryModel - and fetchMore?

apaczenko1993

Hello, I have little problem with QSqlQueryModel class, I don't know why after query, in the model I have all +7k rows (all rows from table). I didn't call fetchMore() function. My Qt version is 5.15.2, MinGW compilator, ale database is Postgre. Below my code:

model = new QSqlQueryModel(this);

    QHeaderView * header = ui->tableView->horizontalHeader();
    connect(header, SIGNAL(sectionResized(int,int,int)), this,
            SLOT(headerResized(int, int, int)));

    m_connection_name = aptool->readSettingsFile(
                QSettings::IniFormat, "Connection", "ConName", "label_db", "settings.ini").toString();

    if(m_archive){
        model->setQuery("SELECT * FROM jtools.a_faktury_view", QSqlDatabase::database(m_connection_name));
    }
    else{
        model->setQuery("SELECT * FROM jtools.faktury_view", QSqlDatabase::database(m_connection_name));
    }

    model->setHeaderData(0, Qt::Horizontal, tr("Nr aktu"));
    model->setHeaderData(1, Qt::Horizontal, tr("Nazwa firmy"));
    model->setHeaderData(2, Qt::Horizontal, tr("Data dodania"));
    model->setHeaderData(3, Qt::Horizontal, tr("Data faktury"));
    model->setHeaderData(4, Qt::Horizontal, tr("Typ"));
    model->setHeaderData(5, Qt::Horizontal, tr("Nr faktury"));
    model->setHeaderData(6, Qt::Horizontal, tr("Kwota"));
    model->setHeaderData(7, Qt::Horizontal, tr("Waluta"));
    model->setHeaderData(8, Qt::Horizontal, tr("Osoba opisująca"));
    model->setHeaderData(9, Qt::Horizontal, tr("Dział"));
    model->setHeaderData(10, Qt::Horizontal, tr("Data zwrotu"));
    model->setHeaderData(11, Qt::Horizontal, tr("Uwagi"));

    for(int a=0; a<model->columnCount(); a++){
        ui->tableView->setColumnWidth(a, aptool->readSettingsFile(QSettings::NativeFormat,"HRBazaTable",
    "Col:" + QString::number(a),100,QString()).toInt());
    }
    ui->tableView->setModel(model);
    ui->tableView->hideColumn(12);
    ui->tableView->show();

BR
Michał

JonB

@apaczenko1993
I don't that it's specified how many rows QSqlQueryModel may/will return. It only says "if canFetchMore() is true then you can fetch more with fetchMore(). Furthermore I see

Fetches more rows from a database. This only affects databases that don't report back the size of a query (see QSqlDriver::hasFeature()).

Also see QSqlQueryModel::rowCount().

This may depend on behaviour of your PostgreSQL database backend/driver.

Try knocking up a tiny program which uses QSqlTableModel instead of QSqlQueryModel. I happen to know that is hard-coded somewhere in Qt code to fetch rows in batches of 256. See how that behaves for your database?

apaczenko1993

@JonB The same result on QSqlTableModel. So I see, that can by problem with my postgres driver? I will try what happen with Oracle Database

Unfortunately the same result :( with Oracle database, in previous Qt version the QsqlQueryModel works fine

apaczenko1993

Does anyone know how solve this problem :(, why in older verson qt, QSqlQueryModel the model loads 256 rows and loding next while scrolling the QtableView but in 5.15.2 don't do this? Maybe I should write custom model?

Bonnie

First of all I don't know the answer to your question. Maybe you can debug into Qt's source code by yourself to find out.

I just want to say that "return all rows from table" would be my expected behavior when executing "SELECT * FROM ...".
If you only want the first N rows, why don't you add that query condition in your query string?

JonB

@Bonnie
Firstly, Qt code has always fetched in blocks of 256 rows. It would be "surprising" if a particular release did not do that.

Secondly, "why don't you add that query condition in your query string?" is not what the OP wants. If you go SELECT * FROM table ORDER BY ... LIMIT 256 that means the query only delivers the first 256 rows; you cannot use it to get the next 256 rows. But that is what OP needs. The point is, if table contains 1,000,000 rows he does not want all million read across connection/into memory in one go, he wants to fetch incrementally in blocks e.g. as user scrolls or pages through view. And that is fair enough.

Bonnie

@JonB But didn't OP just say he get more than 256 rows? Since I don't quite know about that part of Qt code so I think debug into the source would be a good idea.
And about the query I think we can use LIMIT 256 OFFSET n to get the remaining rows, isn't that true? I remember that's how I wrote pages functions.

JonB

@Bonnie said in QSqlQueryModel - and fetchMore?:

And about the query I think we can use LIMIT 256 OFFSET n to get the remaining rows, isn't that true?

And which SQLs do you think that applies to? I grew up on MS SQL Server initially, that does not even use LIMIT, it's syntax is/was SELECT TOP n FROM ..., and did not even have a "continuation" syntax element/option?

The OP wrote

I don't know why after query, in the model I have all +7k rows (all rows from table). I didn't call fetchMore() function.

So far as I know, Qt has always allowed queries to work by opening an on-going SELECT statement which delivers further rows on-demand from the result set (assuming backend supports it). It would be a significant change in behaviour if existing code suddenly found it was waiting while a million rows were being read....

Bonnie

@JonB said in QSqlQueryModel - and fetchMore?:

And which SQLs do you think that applies to?

I was using sqlite (not quite large amout of data), and I just searched and found that postgre also supports LIMIT and OFFSET, but as stated

The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.

So it is true that better not using this if there's other more efficient solution :)

I don't work with sql models a lot. As I read from the doc,

If the database doesn't return the number of selected rows in a query, the model will fetch rows incrementally. See fetchMore() for more information.

and in QSqlQueryModel::rowCount

If the database supports returning the size of a query (see QSqlDriver::hasFeature()), the number of rows of the current query is returned. Otherwise, returns the number of rows currently cached on the client.

also in QSqlQueryModel::fetchMore

This only affects databases that don't report back the size of a query (see QSqlDriver::hasFeature()).

So I guess maybe this 256 rows thing happens when QSqlDriver::hasFeature(QSqlDriver::QuerySize) return false?
But I've checked the source code, QPSQLDriver::hasFeature(QSqlDriver::QuerySize) should always return true since Qt 5.0.

Christian Ehrlicher

QSqlQueryModel::fetchMore() is not used for database drivers returning the query size like postgresql does. Therefore it's not called / called but does nothing because the size (bottom) is already known.

QSqlQueryModel::data() calls QSqlQuery::seek() to get the data from the correct row. This also means that the underlying driver might already transferred all the data from the server to the client at this time (which is what the op sees). there is no way to tell the driver to do a lazy fetch (at least I'm not aware of a way for postgresql) except a forward-only query which is not what you want here.

Therefore you have to implement the paging by yourself.