QSqlQueryModel and QTreeView - part 3

Submitted by mimec on 2011-07-06

In the previous post I started describing the SqlTreeModel which combines multiple QSqlQueryModels so that they can be used in a QTreeView.

We already know how to map items from multiple SQL models in order to create a hierarchy of items, how to map rows and columns from the SqlTreeModel into the SQL models, and how to implement the five pure virtual methods of QAbstractItemModel.

Our tree model already works, but items cannot be sorted by clicking on the column header. There are several ways to do that. One could use the QSqlTableModel which has built-in sorting support, but I find it easier to use the more generic QSqlQueryModel as it is more flexible and allows writing SQL queries by hand. Although it doesn't support sorting directly, the query can be modified so that it includes different ORDER BY clauses to achieve the same effect.

QAbstractItemModel has a virtual method called sort, which is called by the view when the user clicks on a column header. The default implementation does nothing, so we should provide a custom implementation in our derived class, SqlTreeModel. We should determine the order clauses based on the passed sort column index and order, pass new queries to QAbstractItemModels and rebuild the tree.

In fact, since QSqlTableModel is generic and can be reused by multiple different models with different queries, the sort only remembers the sort column and order and calls another virtual method, updateQueries, which is implemented by actual models. Individual models can extend this mechanism, and call this method, for example, when some filtering criteria are changed.

Some columns may be irrelevant for specific levels of items. For example, in WebIssues, the projects tree has two columns, Name and Type, but Type is only relevant for folders, and not for projects or alerts. When sorting by Type, the queries for projects and alerts are not modified, so the previous sorting order is retained at these levels, which produces logical and predictable results.

The last problem is that when the tree is rebuilt (for example, after changing the sort order), the view should be updated, but we don't wan't the current selection or expanded state of nodes to be lost, so we shouldn't simply call reset (which is what the standard SQL models do). Instead we should use the layoutAboutToBeChanged and layoutChanged signals.

The first signal indicates that the view should prepare for changes in the model. The second signal indicates that the changes are completed and the view should update itself. We also have to retrieve persistent indexes just after signalling layoutAboutToBeChanged and update these indexes to reflect changes in the model just before signalling layoutChanged.

How to map old indexes to new indexes? While updating the model, some rows may be added, some may be deleted, and some may be reordered. However, the identifiers (i.e. primary keys) of existing items will not change. So we should first map old indexes to levels and identifiers of corresponding items. After updating the tree, we should map levels and identifiers back to new indexes. To do that, we need a function which will find the index of the item with specified identifier at the specified level, but with the internal structures used to index the tree, it's an easy task. Such function is also useful, for example, if we wan't to select an item with given identifier.

The complete SqlTreeModel class is part of version 1.0 of the WebIssues client and it's available under the terms of the GNU General Public License. You can also use it, along with this series of posts, as a starting point for creating a custom model that goes beyond the standard functionality provided by the Qt framework.