How to prevent QTableView/QTreeView doing query/sorting multiple times?



  • I am getting frustrated by the behaviour of views executing data queries "behind the scenes", without being documented, and repeatedly/unnecessarily. (My examples use Qt 5.7 & PyQt 5.7.)

    Following the standard example at http://doc.qt.io/qt-5/qtwidgets-itemviews-customsortfiltermodel-example.html (section Window Class Implementation), I have code like:

    self.model = QtSql.QSqlTableModel("table", self)
    self.view = QtWidgets.QTableView(self)
    self.view.setModel(self.model)
    self.view.setSortingEnabled(True)
    self.view.sortByColumn(col, Qt.DescendingOrder)
    

    So we have setSortingEnabled() & sortByColumn().

    What they don't tell you is that each of these statements performs a full table populate query. By tracing calls to MySQL I see that the setSortingEnabled() executes a SELECT * FROM table and then the sortByColumn() executes a SELECT * FROM table ORDER BY col DESC. (Reversing the order of the statements does not help: then the latter query with ORDER BY col DESC is simply executed twice in a row.)

    Apart from the fact that this is not documented, performance is obviously compromised, and this is given as a standard example. I cannot find any way to execute these two statements without performing two SELECTs. How are you supposed to do so?


  • Lifetime Qt Champion

    Hi,

    The only thing that comes to mind to try is to invert the setSortingEnabled and sortByColumn calls.

    In any case, there's no way for Qt to know that it should refrain from doing these queries. These methods might be call at any time thus requiring these updates.



  • @SGaist said in How to prevent QTableView/QTreeView doing query/sorting multiple times?:

    Hi,

    The only thing that comes to mind to try is to invert the setSortingEnabled and sortByColumn calls.

    I already stated: "Reversing the order of the statements does not help: then the latter query with ORDER BY col DESC is simply executed twice in a row"

    In any case, there's no way for Qt to know that it should refrain from doing these queries.
    These methods might be call at any time thus requiring these updates.

    So you can't enable sorting and do an initial sort without fetching the data twice from the database. I wonder how many people are aware their code is doing that. That's simply wrong.


  • Lifetime Qt Champion

    My bad, I misunderstood the "reversing the order" part.

    In any case, how would you propose that a generic component be aware of the fact that you are going to show data from a database but it should wait for you to both enable sorting as well as configure it before starting to fetch data ?



  • @SGaist
    I would simply like to present a grid to the user with columns he can click on to sort. And I'd like the grid to start out sorted by some column. Just what a QTableView is supposed to be for. Does that sound reasonable? It's what hundreds of applications do.

    OK, and I expect to achieve the above by having one SQL query like SELECT * FROM table ORDER BY col executed. Not two. That's all. Pretend my query takes 10 seconds to execute. How do I achieve that, please?

    The way I see it at present, with Qt that requires the data to be fetched twice? Either that is not the case, or it's wrong.

    [BTW, I haven't got the reference, but something like QSqlTableModel specifically has a call to let you set the sort attributes before the SELECT gets executed.]


  • Lifetime Qt Champion

    What you want to do is clear. However, your expectations about how QTableView should be working are wrong. That's a generic component that should work with all possible implementations of a QAbstractTableModel. It's not particularly optimised for the SQL use case.

    What happens currently is basically:

    1. You set a model on the table view -> the table view loads the data so it can show something
    2. You modify the view to enable sorting -> the model should be sorted based on the default values
    3. You modify again the view by changing the sorting -> the model should again be sorted but with the new values

    You are likely referring to QSqlTableModel::setSort which requires a call to select for the update to happen.

    So what you would need is a variant of QTableView that would setup itself based on the sorting you did first on your QSqlTableModel when you set the model on that view.

    Note that QSortFilterProxyModel might also be of interest.



  • @SGaist
    Thank you for your reply, it's late tonight here, I shall look into it tomorrow!



  • @SGaist said in How to prevent QTableView/QTreeView doing query/sorting multiple times?:

    Note that QSortFilterProxyModel might also be of interest.

    Big up for this line. QSortFilterProxyModel is definitely what you need



  • @SGaist

    Thank you for your clarification on the behaviour of QTableView.

    Your description implies there should actually be 3 queries against the data, then. I only see 2. However, I admit I would need more time to be sure.

    Let me see if I have understood the behaviour of setSortingEnabled() & sortByColumn():

    • setSortingEnabled() is what makes the View show sort indicators on column headers and allow clicking. It also executes a data fetch query, passing whatever (if anything) is currently defined as the sort order (from sortByColumn()) into an ORDER BY.
    • sortByColumn() determines the ORDER BY. It too executes a data fetch.

    (Please correct me if I am wrong.) Now, after (just, i.e. no sortByColumn() yet) a setSortingEnabled() I will see the sort indicator in some direction on some column. I am guessing this would be Ascending on column #0 initially(?) However, the query it issues has no ORDER BY at all, so by definition the rows are in indeterminate order. This seems simply inconsistent to me.

    What I would have expected/hoped is that setSortingEnabled() would have passed an ORDER BY col0 ASC if that is how the data is supposed to be sorted to correspond to the initial sort indicator state, and/or an optional parameter to allow specification of the initial sort order desired in the View and to be passed in the initial query --- in effect, combining the setSortingEnabled() with an initial sortByColumn(). Or even, as you say, an equivalent of QSqlTableModel.setSort(). As it stands it seems you must fetch the data twice simply to correspond to the View's initial state.

    I have inherited 32K lines of code which uses every kind of Qt SQL/model/view architecture all over the place, it's a mess. I will indeed investigate QSortFilterProxyModel. I don't mind which code I use to achieve it, I just don't expect any necessity to fetch the data multiple times for the initial situation.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.