How to properly use QSqlTableModel

Oak77

Hello community!

The documentation clearly states:

You should normally not call it on a QSqlTableModel. Instead, use setTable(), setSort(), setFilter(), etc., to set up the query.

That sounds very clear and it makes a lot of sense. Contrary to this statement, the very same documentation page uses setQuery() right in the Description section multiple times for very simple examples. Also, I came to some limitations of provided methods and I'm now unsure how to proceed.

First, if I need to fetch only specific columns and define their order, how do I set them with QSqlTableModel? I didn't gave it much thought at first and used setHeaderData(), but that does not afffect what columns are fetched (all of them are obtained).
Secondly, if we need pagination, there are no methods provided by Qt to achieve that, correct? So we have to use setQuery() in this case and compile appropriate query ourselves, right?
Then, if we have to use setQueary, we shouldn't use setFilter(), setSort() and such, correct? Those should be included in the query? Filter and Sort methods wouldn't be applied, if I'm correct.
Finally, if we have a table (QTableView), that has both view (read-only) and edit mode, is it OK to use QSqlTableModel for both modes? Just turning the table read-only when in view mode? I suppose it is, but I would like to be sure.

I tried to find answers in the documentation and forums, but this seems to be above particular technical details commonly elaborated.

JonB

@Oak77

If you want to use your setQuery() on a QSqlTableModel that's OK. Most people don't need to.
Any pagination must indeed be done by your own code. There are the fetchMore() methods to aid. Note that QSqlTableModel reads 256 rows at a time via this, that number is hard-coded somewhere in Qt code.
setFilter/Sort() can be used, even if you're doing your own setQuery. It should append the WHERE/ORDER BY to your query. You should verify that.
It's a bit harder using edit mode against tables with your own query, but again should work if you're careful. Partly depends what you want to do about inserting/updating columns which are not fetched.

For your case, where you only want to display certain columns/change the order they are displayed. Consider using QTableView::setHidden() for just hiding columns which are fetched. Consider using QAbstractProxyModel to re-order fetched columns as well as hiding them. Consider using QSortFilterProxyModel on top of QSqlTableModel if & only if your data is small/fast/local if you want/can afford to fetch all the data rows/columns from the database and do your filtering/sorting in client memory instead of at the database.

Oak77

@JonB said in How to properly use QSqlTableModel:

@Oak77

If you want to use your setQuery() on a QSqlTableModel that's OK. Most people don't need to.

Any pagination must indeed be done by your own code. There are the fetchMore() methods to aid. Note that QSqlTableModel reads 256 rows at a time via this, that number is hard-coded somewhere in Qt code.

setFilter/Sort() can be used, even if you're doing your own setQuery. It should append the WHERE/ORDER BY to your query. You should verify that.

It's a bit harder using edit mode against tables with your own query, but again should work if you're careful. Partly depends what you want to do about inserting/updating columns which are not fetched.

For your case, where you only want to display certain columns/change the order they are displayed. Consider using QTableView::setHidden() for just hiding columns which are fetched. Consider using QAbstractProxyModel to re-order fetched columns as well as hiding them. Consider using QSortFilterProxyModel on top of QSqlTableModel if & only if your data is small/fast/local if you want/can afford to fetch all the data rows/columns from the database and do your filtering/sorting in client memory instead of at the database.

Thank you @JonB, your answer will help me to follow right tracks. The documentation alone doesn't always give all the answers to a rookie :-).

In most cases I need to use pagination and fetch only certain amount of rows. When using query, I can define the columns too, since fetching them all is not a good practise either, while not necessarily harming performance significantly. I'll port my old & proven winform pagination solution into Qt and QSqlTableModel, combining setQuery(), setFilter() and setSort(), should be interesting :-). At this stage I have no understanding of proxy models, but I'll study their application and see, whether they will be helpful.