Populate QTreeView as parent and child nodes from database

Question

I have data stored in the following columns in the database

|AcName|ActCod|GroupCode|

|parent1| |1|   |0|

|child1|  |101| |1|

|parent2| |2|   |0|

|child2|  |201| |2|

I am using a QTreeView, QStandardItemModel, and QStandardItem to create this treeview however I don't know how to append the child to the parent. I stored QStandardItem items into a QMap but how do I append child nodes to parent and parent nodes to rootNode?

the code.

standardModel = new QStandardItemModel(this);
QStandardItem *rootNode = standardModel->invisibleRootItem();
QSqlQuery *itemqry = new QSqlQuery("SELECT GroupCode, AcName, ActCod from adm_ac");
while(itemqry->next()){
    int groupcode =itemqry->value(0).toInt();
    QString acname = itemqry->value(1).toString();
    int ActCod = itemqry->value(2).toInt();
    QStandardItem *itemmap = new QStandardItem(acname);
    rowItemMap.insert(groupcode, itemmap);

    }
}
 ui->treeView->setModel(standardModel);

the header file.

QStandardItemModel *standardModel;
QStandardItem *acName1;
QStandardItem *acName2;
QMap<int, QStandardItem*> rowItemMap;

How do you identify that one row is a child of another row ?, We could assume many things with the table you show, but it is better if you indicate it clearly — eyllanesc, Nov 21 '18 at 18:46
GroupCode of child element matches the ActCod of parent element. — awaisharoon, Nov 21 '18 at 19:00

eyllanesc · Answer 1 · 2018-11-25T05:53:30.470

One possible is to save the ActCod in a role and then do a parent search through GroupCode using the match() method:

#include <QtWidgets>
#include <QtSql>

static bool createConnection(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        qDebug()<<"Cannot open database\n"
                  "Unable to establish a database connection.\n"
                  "This example needs SQLite support. Please read "
                  "the Qt SQL driver documentation for information how "
                  "to build it.\n\n"
                  "Click Cancel to exit.";
        return false;
    }
    QSqlQuery query;
    if(!query.exec("CREATE TABLE adm_ac("
                   "AcName TEXT,"
                   "ActCod INTEGER,"
                   "GroupCode INTEGER"
                   ")"))
        qDebug()<<query.lastError().text();
    query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"parent1\", 1, 0)");
    query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"child1\", 101, 1)");
    query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"parent2\", 2, 0)");
    query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"child2\", 201, 2)");
    query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"A\", 10000, 101)");
    query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"B\", 10001, 201)");
    query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"C\", 100000, 10000)");
    query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"D\", 100001, 10001)");
    return true;
}

enum RelationRoles{
    CodeRole = Qt::UserRole + 1000,
};

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    if(!createConnection())
        return -1;
    QStandardItemModel model;
    QSqlQuery  query("SELECT GroupCode, AcName, ActCod from adm_ac");
    const QSqlRecord rec = query.record();
    while (query.next()) {
        QString AcName = query.value(rec.indexOf("AcName")).toString();
        int GroupCode = query.value(rec.indexOf("GroupCode")).toInt();
        int ActCod = query.value(rec.indexOf("ActCod")).toInt();
        QStandardItem *it = new QStandardItem(AcName);
        it->setData(ActCod, RelationRoles::CodeRole);
        if(GroupCode == 0)
            model.invisibleRootItem()->appendRow(it);
        else{
            QModelIndexList ixs = model.match(model.index(0, 0),
                                              RelationRoles::CodeRole,
                                              GroupCode,
                                              1,
                                              Qt::MatchExactly| Qt::MatchRecursive);
            if(ixs.size() > 0){
                QStandardItem *parent = model.itemFromIndex(ixs.first());
                parent->appendRow(it);
            }
        }
    }
    QTreeView w;
    w.setModel(&model);
    w.expandAll();
    w.show();
    return a.exec();
}

In your case:

// ...
enum RelationRoles{
    CodeRole = Qt::UserRole + 1000,
};
// ...
standardModel = new QStandardItemModel(this);
QSqlQuery  query("SELECT GroupCode, AcName, ActCod from adm_ac");
const QSqlRecord rec = query.record();
while (query.next()) {
    QString AcName = query.value(rec.indexOf("AcName")).toString();
    int GroupCode = query.value(rec.indexOf("GroupCode")).toInt();
    int ActCod = query.value(rec.indexOf("ActCod")).toInt();
    QStandardItem *it = new QStandardItem(AcName);
    it->setData(ActCod, RelationRoles::CodeRole);
    if(GroupCode == 0)
        standardModel->invisibleRootItem()->appendRow(it);
    else{
        QModelIndexList ixs = standardModel->match(model.index(0, 0),
                                          RelationRoles::CodeRole,
                                          GroupCode,
                                          1,
                                          Qt::MatchExactly| Qt::MatchRecursive);
        if(ixs.size() > 0){
            QStandardItem *parent = standardModel->itemFromIndex(ixs.first());
            parent->appendRow(it);
        }
    }
}

ui->treeView->setModel(standardModel);

The advantage is that we do not have to create a container like QMap, and thus we can avoid problems of accessing not allowed memory as well as duplicity of elements.

UPDATE1:

// ...
QSqlQuery query;
if(!query.exec("CREATE TABLE adm_ac("
               "AcName TEXT,"
               "ActCod INTEGER,"
               "GroupCode INTEGER"
               ")"))
    qDebug()<<query.lastError().text();
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"EXPENSES\", 5, 0)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"SALES\", 4, 0)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"ASSETS\", 1, 0)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"CAPITAL\", 3, 0)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"LIAILITIES\", 2, 0)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"CURRENT ASSETS\", 102, 1)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"FIXED ASSETS\", 101, 1)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"INTANGIBLE FIXED ASSETS\", 10102, 101)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"ACCUM.DEP. FIXED ASSETS\", 10103, 101)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"TANGIBLE FIXED ASSETS\", 10101, 101)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"MACHINERY\", 1010102, 10101)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"COMPUTERS\", 1010103, 10101)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"LAND\", 1010101, 10101)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"OFFICE EQUIPMENTS\", 1010104, 10101)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"MOTOR VEHICLES\", 1010105, 10101)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"COMPUTER SOFTWARE\", 1010203, 10102)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"GOODWILL\", 10102001, 10102)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"PATENTS & TRADE MARKS\", 10102002, 10102)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"ACC.DEP- MOTOR VEHICLES\", 10103004, 10103)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"ACC.DEP- OFFICE EQUIPMENTS\", 10103003, 10103)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"ACC.DEP- MACHINERY\", 10103001, 10103)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"ACC.DEP- COMPUTERS\", 10103002, 10103)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"ACCOUNTS RECEIVABLE\", 10205, 102)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"STOCK\", 1010105, 102)");
query.exec("insert into adm_ac(AcName, ActCod, GroupCode) values(\"DEPOSITS & PREPAYMENTS\", 10212, 102)");
return true;
// ...

UPDATE2:

I forgot to mention that in my solution I assumed that the data was ordered since in my algorithm I consider that the parent is before the children but in the general case it is not correct, so it must be ordered using: ORDER BY ActCod ASC

QSqlQuery query("SELECT GroupCode, AcName, ActCod from adm_ac ORDER BY ActCod ASC");

This works but it does not go for deeper menus. e.g. rootNode only has parent items which is correct, but every parrent can have multiple child, and each child can also have multiple children of it's own. Your code somewhat works but it also misplaces the nodes under other nodes which do not have any parent-child relationship that is matching the `GroupCode` to `ActCod` see this image: [link](https://imgur.com/a/ubHJFhM) — awaisharoon, Nov 22 '18 at 18:43
@user2185284 Strange, just in my example I implemented a similar example to show that my solution works for several levels, if you are using sqlite you could share the .db, or if you are using another database you could share a .sql that contains the data. Are you using my last code? — eyllanesc, Nov 22 '18 at 18:48
@user2185284 I see that there are Group Code empty but in the table that you show in your question are 0, do not you think that would bring problems? — eyllanesc, Nov 22 '18 at 18:51
@user2185284 I just tested your data and it works correctly as my update shows — eyllanesc, Nov 22 '18 at 19:13
@user2185284 If you have used my second code I just realized that I had errors, please try it again :-) — eyllanesc, Nov 22 '18 at 19:31
I still cannot produce the result as yours. Is it because I have more child elements attached to the elements that I provided screenshot of? btw syntax `MyEnum::SomethingElse` is not standard c++. see here[link](https://stackoverflow.com/questions/5188554/my-enum-is-not-a-class-or-namespace). I used `RelationRoles coderole = CodeRole;` and replaced `RelationRoles::CodeRole` with `CodeRole`. — awaisharoon, Nov 23 '18 at 09:55
I exported sql file but I don't know how do I send it to you not publicly? — awaisharoon, Nov 23 '18 at 10:44
group code of root element(s) is empty. So it is assumed that it is zero, and this shouldn't pose a problem because there are no other nodes with groupcode zero or empty. — awaisharoon, Nov 23 '18 at 10:46
@user2185284 actually both forms are correct, I just wanted to be explicit, try not using the enumeration and replace `RelationRoles::CodeRole` with `Qt::UserRole +1000` — eyllanesc, Nov 23 '18 at 14:59
you can get the sql file [here](https://drive.google.com/open?id=1sdd2k7WzJGsVXDXRVlgvyaUoVgNOStlP) I will delete it once you have obtained it. — awaisharoon, Nov 25 '18 at 05:16
@user2185284 Add an update where I explain the solution, the problem is that the data is not sorted, so you must do it with `ORDER BY ActCod ASC`: `QSqlQuery query("SELECT GroupCode, AcName, ActCod from adm_ac ORDER BY ActCod ASC"); ` — eyllanesc, Nov 25 '18 at 05:41

tunglt · Accepted Answer · 2018-11-23T15:55:12.017

Here is an other way to build your tree. This code take care also the case that child nodes were loaded before its parent. The tree will be built in two pass :

Load all items to the map
Build the tree from the map

EDIT: We use setData() function instead of setProperty since QStardardItem is not QObject derived.

    enum GroupCodeRoleEnum{
        GroupCodeRole = Qt::UserRole + 1001,
    };

    while(itemqry->next())
    {
        int groupcode =itemqry->value(0).toInt();
        QString acname = itemqry->value(1).toString();
        int ActCod = itemqry->value(2).toInt();
        QStandardItem *itemmap = new QStandardItem(acname);

        itemMap->setData(groupcode, GroupCodeRole );

        //map of ActCod to itemmap, not GroupCode
        rowItemMap.insert(ActCod, itemmap) );

    } //End of while -- every node were now loaded.

    //build the tree
    rowItemMap.insert (0, rootNode ) ;

    foreach( QStandardItem * p, rowItemMap.values() ){
        int groupCode = p->data( GroupCodeRole ).toInt();

        //find the parent from the map
        if( p != rootNode ){

            QMap<int, QStandardItem* >::iterator it = rowItemMap.find( groupCode );

            if( it != rowItemMap.end() ){

                QStandardItem* pParent = it.value();
                pParent->appendRow( p );
            }else {
                qDebug() << "Parent not exist for groupCode" << groupCode; 
            }

        }//nomal node

    } //foreach  

    ui->treeView->setModel(standardModel);

TEST CODE:

        QStandardItemModel *standardModel = new QStandardItemModel(this);
        QStandardItem *rootNode = standardModel->invisibleRootItem();

        //Simulate your while() loop.
        addItem(1, 0, "parent 1");
        addItem(101, 1, "child 1");
        addItem(2, 0, "parent 2");
        addItem(201, 2, "child 2");

        //childs were loaded before parents

        addItem(301, 3, "child 3");
        addItem(401, 4, "child 4");
        addItem(501, 5, "child 5");

        addItem(3, 2, "parent 3");
        addItem(4, 2, "parent 4");
        addItem(5, 2, "parent 5");

        //build the tree
        m_map.insert (0, rootNode ) ;

        foreach( QStandardItem * p, m_map.values() ){
            int groupCode = p->data( GroupCodeRole ).toInt();

            //find the parent from the map
            if( p != rootNode ){

                QMap<int, QStandardItem* >::iterator it = m_map.find( groupCode );

                if( it != m_map.end() ){

                    QStandardItem* pParent = it.value();
                    pParent->appendRow( p );
                }else {
                    qDebug() << "Parent not exist for groupCode" << groupCode;
                }

            }//nomal node

        } //foreach

        ui->treeView->setModel(standardModel);
    }

    void MainWindow::addItem(int Act, int nGroupCode, QString szName)
    {
            QStandardItem *itemmap = new QStandardItem( szName );

            itemmap->setData(nGroupCode, GroupCodeRole );

            //map of ActCod to itemmap, not GroupCode
            m_map.insert(Act, itemmap);

    }

The tree was built :

`class QStandardItem has no member named setProperty, Property`. Variable `pNode` is also not declared, did you mean to use the variable `p`? — awaisharoon, Nov 22 '18 at 18:00
@user2185284: yes, pNode mean p, QStandardItem is not QObject derived so setProperty does not work. I modified my answer to correct it, please give it a try. — tunglt, Nov 22 '18 at 18:26
@user2185284: the root's groupCode is 0 as described in your question ? or my setData() does not work :( — tunglt, Nov 23 '18 at 11:22
root's groupcode is empty. so it is zero. How does the test code still work? — awaisharoon, Nov 24 '18 at 14:09
@user2185284: do you insert the rootNode into the map with key 0 as the code shown above ? Without it, the tree cannot be built. — tunglt, Nov 24 '18 at 22:06
@user2185284 : once the rootNode was added to the map, its childs can find it and will be appended correctly. — tunglt, Nov 24 '18 at 22:22
@user2185284: the test code works for you but did not work with your data ? We have apps with same kind of data set as your with 40k+ items and it works without problem. Did you try to add some debug information ? — tunglt, Nov 25 '18 at 08:41
I revisited this and this solution works better. My understanding of qt programming wasn't good enough to understand this solution before -:) — awaisharoon, Jan 21 '19 at 12:27
@user2185284 : Nice to hear that the solution worked for you. — tunglt, Jan 21 '19 at 15:20

Populate QTreeView as parent and child nodes from database

2 Answers2