What is the best way to use QSortFilterProxyModel + QSqlTableModel to make pagination with filtering?

michaeldev

Hi.

I have DB with, for example, row count > 100000. In short words - there are many rows.
I'd like to make QTableView with pagination and filtering.
And there should be label "page n from m".
So, what is the best way to achieve this?

VRonin

@michaeldev said in What is the best way to use QSortFilterProxyModel + QSqlTableModel to make pagination with filtering?:

And there should be label "page n from m".

This is just a division based on https://doc.qt.io/qt-5/qabstractscrollarea.html

Is your problem performance or are you looking for suggestions on how to set it up from scratch?

michaeldev

@VRonin said in What is the best way to use QSortFilterProxyModel + QSqlTableModel to make pagination with filtering?:

@michaeldev said in What is the best way to use QSortFilterProxyModel + QSqlTableModel to make pagination with filtering?:

And there should be label "page n from m".

This is just a division based on https://doc.qt.io/qt-5/qabstractscrollarea.html

Is your problem performance or are you looking for suggestions on how to set it up from scratch?

I mean, when I press button Next or Prev on paged view, some label should show current page and pages count.

VRonin

view->setVerticalScrollBarPolicy(Qt::ScrollBarAlwaysOff);
connect(buttonNextPage,&QPushButton::clicked,view,[=](){auto scrollBar = view->verticalScrollBar(); scrollBar->setValue(scrollBar->value()+scrollBar->pageStep();});
connect(buttonPrevPage,&QPushButton::clicked,view,[=](){auto scrollBar = view->verticalScrollBar(); scrollBar->setValue(scrollBar->value()-scrollBar->pageStep();});
connect(view->verticalScrollBar(),&QAbstractSlider::valueChanged,[=](int val){auto scrollBar = view->verticalScrollBar(); label->setText(QStringLiteral("page %1 of %2").arg(val/scrollBar->pageStep()).arg(scrollBar->maximum()/scrollBar->pageStep()));}); (you might need to round up the last division)

JonB

@michaeldev
Just to be clear: the "page n of m" and scrolling is the lest of your problems. The harder code to write is for the pagination from the database, which Qt SQL classes don't give you. Right?

michaeldev

@JonB said in What is the best way to use QSortFilterProxyModel + QSqlTableModel to make pagination with filtering?:

@michaeldev
Just to be clear: the "page n of m" and scrolling is the lest of your problems. The harder code to write is for the pagination from the database, which Qt SQL classes don't give you. Right?

From the beginning I had code with filtering by QSortFilterProxyModel. Then I added pagination by editing sql-code. My pagination doesn't make scrolling, only show some records by editing sql-code (limit and offset). And I see that my filtering and my pagination poorly interact with each other.

So, I'm deciding now, should I make filtering by sql-code (without QSortFilterProxyModel), or maybe there are better way.

JonB

@michaeldev
In some shape or form you need to implement the "page n of m" in your SQL calls (don't know what you mean by "editing sql-code", you want your SQL code to support dynamic "page" fetching). That will control which "window/buffer"'s-worth of the records are fetched from server to client.

Then you need to implement however that is to interface with your view/model/proxy to display the rows and allow the user to move by pages. Qt does not support this "out of the box". I don't know, but sub-classing from QSortFilterProxyModel might work nicely, or you might find you're better offer throwing that layer away and do the lot yourself.