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;

Answer 1

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"); 

Answer 2

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 :

1. Load all items to the map
2. 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 :