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.