QSqlQueryModel and QTreeView - part 1

Qt provides an easy way of populating a table view using an SQL query by using the QSqlQueryModel. It can also be used with a QTreeView, but only for displaying flat lists. If we want to display a hierarchy of items based on a set of SQL queries, we have to subclass the QAbstractItemModel on our own, which is not an easy task.

There are many ways in which hierarchies of items can be created and it's probably difficult to abstract it and create an universal solution. The first category is a "homogeneous" tree, in which all items represent the same class of entities, and which could be populated using a single query with Id and ParentId columns. Items whose ParentId is NULL represent top-level items, and items with given ParentId are children of the corresponding parent item. An example would be a tree of categories, which can have sub-categories, sub-sub-categories, etc.

Another category is a "heterogeneous" tree, where each level of the hierarchy corresponds to a different class of entities (i.e. a different table). That's the case which I faced in WebIssues. One of the trees contains projects, which can have child folders, and folders in turn can have child alerts. Another tree contains issue types and their corresponding attributes. There can also be various combinations of these two categories, and more complex scenarios, but they usually can be derived in one way or another from the "heterogeneous" solution, so I will focus only on that case.

To create a tree model based on multiple related SQL queries, we need the following components:

• A subclass of QAbstractItemModel providing an implementation of all its abstract methods
• A collection of QSqlQueryModels, each of which contains items at a different level of hierarchy
• Some internal data structures used for tracking dependencies between items

For now I will skip the implementation of the model's abstract methods, as this is a topic for a separate post. Let's assume that we want to represent the following simple tree of items:

+ Project 1
| + Folder 1
| | + Alert 1
| | + Alert 2
| + Folder 2
+ Project 2
  + Folder 3

The first level of the hierarchy will be populated using an SQL query returning identifiers and names of projects, for example:

+-----------+-----------+
| ProjectId | Name      |
+-----------+-----------+
|         1 | Project 1 |
+-----------+-----------+
|         2 | Project 2 |
+-----------+-----------+

The second level of hierarchy will be based on the following query results:

+----------+-----------+----------+
| FolderId | ProjectId | Name     |
+----------+-----------+----------+
|        1 |         1 | Folder 1 |
+----------+-----------+----------+
|        2 |         1 | Folder 2 |
+----------+-----------+----------+
|        3 |         2 | Folder 3 |
+----------+-----------+----------+

As you can see, the first two folders belong to the first project, and the third folder belongs to the second project, just like on the first diagram. A similar query, this time with AlertId, FolderId and Name columns, can be used to populate alerts, i.e. the third level of hierarchy, and this could be continued to create even more levels.

We assumed that the first column is always the primary key, and the second column is the foreign key related to the parent table (except for the first, root level). So these columns constitute the structure of the tree. Subsequent columns contain data which is displayed in the tree view (there can be more columns than just the name, but that's also a topic for another post).

Now let's get to the internal structures which are needed to keep track of items and relations between them. As you know, each item, or rather cell, of the Qt data model, is represented by an index. The index consists of a row index, column index and an optional parent index. In case of a flat list or table, there are no parent/child relations between items, so the row and column indexes are sufficient to describe a cell. In a tree model, the index must also contain a pointer to some internal data structure, which is necessary to determine the parent index and to keep track of the child items.

At the minimum, the internal data structure (let's call it a "node" for simplicity) should store the level of hierarchy, at which it is located, and the index of the parent item in the parent node. Note that the node does not correspond to a single item in the model, but rather to a group of items with the same parent.

For example, in the model shown on the first diagram, there would be four nodes. The first node, or the root node, represents all top level items, i.e. all projects. It has a level of 0 and the parent index is irrelevant since it has no parent. The second node represents two folders which belong to Project 1, with a level of 1 and the parent index of 0 (i.e. the index of Project 1 in the first SQL model). The third node represents the single folder belonging to Project 2 (with a level of 1 and parent index of 1). The fourth node represents the alerts belonging to Folder 1 (with a level of 2 and parent index of 0).

Obviously the model needs to keep track of the node structures, so at each level of the tree there should be some kind of a container for its nodes. Also, to make navigation around the tree easier and more efficient, we need some additional data structures serving as sort of indexes. We need to be able to find both parent and child indexes of a given index, and to map indexes of our tree model to indexes of the associated QSqlQueryModels and vice versa, and it's not as easy as it seems.

In the next post I will describe some of the nuances of implementing the model itself.