insert row into QSqlTableModel from QAbstractProxyModel(TreeModel)

[sadeq]

I have subclassed QAbstractProxyModel to make a Treemodel out of a QSqlTableModel
the SQL table structure is
category,ID,parentID
ID is autoincrement
if parentID=0 then it's a root category

the model works fine in viewing the Data in a tree structure but I'm having a trouble in inserting rows
I have no clue about how to do it
I tried something like

       QSqlTableModel *mdl= static_cast<QSqlTableModel *>(sourceModel());
       QSqlRecord record=mdl->record();
       record.setValue(0,"category");
       record.setValue(2,0); //parentID is 0 so it's a root category
       mdl->insertRecord(-1,record)
        mdl->select();
       emit layoutChanged();

I've also tried reimplementing insertRows() and using beginInsertRows() and endInsertRows() while inserting the record between the calls but none of the solutions above worked the way I expected
the record is inserted into the sourceModel and the Database but the proxyModel either updates and puts the new row in the wrong index or replaces an existing row
if I restart the APP everything is displayed fine until I begin inserting rows

so what is the correct way to inform the proxymodel that the sourceModel changed ?

qsqltreemodel.h

#ifndef QSQLTREEMODEL_H
#define QSQLTREEMODEL_H
#include <QAbstractProxyModel>


class QSqlTreeModel : public QAbstractProxyModel
{
    Q_OBJECT
public:
    QSqlTreeModel(QObject *parent=nullptr);
    virtual QModelIndex mapFromSource(const QModelIndex &sourceIndex) const;
    virtual QModelIndex mapToSource(const QModelIndex &proxyIndex) const;
    virtual QModelIndex parent(const QModelIndex &child) const;

    virtual QModelIndex index(int row, int column, const QModelIndex &parent) const;
    virtual int rowCount(const QModelIndex &parent) const;
    virtual int columnCount(const QModelIndex &parent) const { return sourceModel()->columnCount(parent); }

    virtual QVariant headerData(int section, Qt::Orientation orientation, int role) const { return sourceModel()->headerData(section,orientation,role); }
    virtual bool setHeaderData(int section, Qt::Orientation orientation, const QVariant &value, int role) { return sourceModel()->setHeaderData(section,orientation,value,role); }

    virtual bool hasChildren(const QModelIndex &parent) const;
    virtual Qt::ItemFlags flags(const QModelIndex &index) const override;




private:

    int getParentId(int childId) const;

    int childNumber(int ID) const;
    int GetID(int ParentID, int row) const;
    int  IDColumn=1;
    int pIDColumn=2;
    int DataColumn=0;
   


};

#endif // QSQLTREEMODEL_H

qsqltreemodel.cpp

#include "qsqltreemodel.h"
#include <QSqlTableModel>
#include <QDebug>
QSqlTreeModel::QSqlTreeModel(QObject *parent) : QAbstractProxyModel(parent)
{

}

QModelIndex QSqlTreeModel::mapFromSource(const QModelIndex &sourceIndex) const
{
    if(!sourceIndex.isValid())
        return QModelIndex();

    int id = (sourceIndex.column() == IDColumn) ? sourceIndex.data().toInt() : sourceIndex.siblingAtColumn(IDColumn).data().toInt();    
    int row=childNumber(id);
    return createIndex(row, sourceIndex.column(), id);
    //return index(row,sourceIndex.column(),QModelIndex());
}

QModelIndex QSqlTreeModel::mapToSource(const QModelIndex &proxyIndex) const {
    if(!proxyIndex.isValid())
        return QModelIndex();
    int id = proxyIndex.internalId();
    int row = -1;

    for(int i=0 ; i<sourceModel()->rowCount() ; i++)
        if(sourceModel()->index(i,IDColumn).data().toInt()==id)
        {
            row=i;
            break;
        }
    return sourceModel()->index(row, proxyIndex.column());
}

bool QSqlTreeModel::hasChildren(const QModelIndex &parent) const
{
    int ID=parent.internalId();
    for(int i=0; i<sourceModel()->rowCount() ; i++)
        if(sourceModel()->index(i,pIDColumn).data().toInt()==ID)
            return true;

    return false;
}

QModelIndex QSqlTreeModel::parent(const QModelIndex &child) const
{
    int childId  = child.internalId();
    int parentId = getParentId(childId);
    if(parentId == 0)
        return QModelIndex();
    int parentRow =childNumber(parentId);
    return createIndex(parentRow, child.row(), parentId);
}

QModelIndex QSqlTreeModel::index(int row, int column, const QModelIndex &parent) const
{
    if(row < 0 || column < 0)
        return QModelIndex();

    int id=GetID(parent.internalId(),row);
    return createIndex(row, column, id);
}

int QSqlTreeModel::rowCount(const QModelIndex &parent) const
{
    int ID=0;
    if(parent.isValid())
        ID=parent.internalId();

    int count=0;
    for(int i=0 ; i<sourceModel()->rowCount() ; i++)
        if(sourceModel()->index(i,pIDColumn).data().toInt()==ID)
            count++;

    return count;
}

int QSqlTreeModel::getParentId(int childId) const
{
    for(int i=0; i<sourceModel()->rowCount() ; i++)
        if(sourceModel()->index(i,IDColumn).data().toInt()==childId)
            return sourceModel()->index(i,pIDColumn).data().toInt();

    return -1; //remove
}


int QSqlTreeModel::childNumber(int ID) const
{
    int ParentID=getParentId(ID);
    int n=-1;
    for(int i=0 ; i<sourceModel()->rowCount() ; i++)
    {
        if(sourceModel()->index(i,pIDColumn).data().toInt()==ParentID)
        {
            n++;
            if(sourceModel()->index(i,IDColumn).data().toInt()==ID)
                break;
        }
    }
    return n;
}



int QSqlTreeModel::GetID(int ParentID,int row) const
{
    int count=-1;
    for(int i=0 ; i<sourceModel()->rowCount() ; i++)
    {
        if(sourceModel()->index(i,pIDColumn).data().toInt()==ParentID)
            count++;
        if (count==row)
            return sourceModel()->index(i,IDColumn).data().toInt();
    }
    return -1; //remove this
}



Qt::ItemFlags QSqlTreeModel::flags(const QModelIndex &index) const
{
    if(index.isValid())
    {
        return (Qt::ItemIsSelectable | Qt::ItemIsEnabled | Qt::ItemIsEditable);
    }


        return 0;
}

[SGaist]

Hi and welcome to devnet,

Why are you modifying the source model within the proxy ? That's rather fishy.

[sadeq]

@SGaist
what I'm trying to create is a complete proxy model that takes a flat SQL table(through QSqlTableModel) and turns it into a Tree Model so that I can use it in QTreeView
the proxyModel must be able to perform all the operations that QSqlTableModel can do(view,insert,remove,modify)
so far with the code above I can view the model and modify the data from the QTreeView
I didn't even need to subclass setData() function to be able to modify the data
the only thing that was needed is to pass Qt::ItemIsEditable flag
the only operations left are insert and remove rows
I guess remove rows is easy even though I haven't tried it yet, but inserting rows is a little bit difficult
how do use suppose to modify the source model then ?
i know I can modify the source from outside but the proxy just won't update

[JonB]

@sadeq
I'm not an expert, like @SGaist is, and haven't followed your code! But one thought: when your new row is committed to the database it gets an auto-incremented, unique ID. Where do you generate this before commit/refresh? If your view is sorted by ID this will matter, won't it?

[VRonin]

I built something like this in the past: https://github.com/VSRonin/QtModelCategorizer

It was plagued by a bug in a specific use case but now I totally forgot what it was.
Feel free to give it a try, if you find out what the bug was open a ticket in github please

[sadeq]

@JonB
the ID is generated automatically since I left the ID field blank
see, I didn't type "record.setValue(1,ID);" so field 1(ID field) will be null an it will be generated when I call
"mdl->insertRecord(-1,record)"
the proxyModel itself has no sorting capabilities, but the SQL table is sorted by the autoincremented ID

[sadeq]

@VRonin
that's really nice, looking at the source, it's quite a complected implementation, I'll try it out when I have the time and see how you Implemented some of the methods

[sadeq]

I solved it guys, all I had to do was disable sorting in the QTreeView and emit layoutChanged(); after insertRecord(-1,record); and select(); and everything worked fine
now whenever I insert a row it is inserted at the end and it doesn't override any existing row

[JonB]

@sadeq
I think you'll find somewhere in the documentation that you always have to disable sorting in a model/proxy before you do any inserts which include a "position row", else it goes wrong.