Group / Aggregate QAbstractItemModel

[Nightfox]

Hi Guys,

I've been looking around for a while to see if I can get my QAbstractItemModel to group and/or sum given a defined column in the model. I my case I have a model with three columns from a ledger system - account number, account name, amount. I may have say ten rows in the model, but if those ten rows contains only entries from two accounts I want to show only two rows with the account number, account name and the sum of the amount. Basically exactly like a Group By statement in SQL, but in memory.

I've had a look to the QSortFilterProxyModel to put on top of the model I already have but I can't get it to group/sum - which means I haven't found an example yet that shows this.

I will be truely greatfull if someone sould share his/hers experience with grouping rows in QAbstractItemModel.

Thanks

[fullmetalcoder]

I can only see two ways of doing this :

• using a tree model and grouping items upon insertion into a parent with same name/account and updating the amount of the parent item
• using QAbstractProxyModel and using an internal intermediate data structure to do the actual group tracking/summing

[Nightfox]

Hi fullmetalcoder

Thanks for your reply. Your second way looks appealing can you elaborate in this, please? I guess you'd used the QAbstractItemModel as source for the proxy but how will you get it to group?

[Tanuki-no Torigava]

Hope this helps

[numbat]

OK, here is my attempt at a proxy model that does aggregation. Some notes:
- It assumes the data is unsorted. With sorted data a more efficient implementation could be created.
- It is static. It doesn't update with changes to the source model. This could be fixed by connecting to the appropriate slots.
- The groupby is done as a string. If only grouping by integers, it could be made more efficient.
- It only works on flat models.
- The setup is done in the constructor. It takes a function pointer to an aggregate function (Total and Average are supplied), the groupby column and the aggregate column.
So here is the code:

Header:

#include <QDebug>
#include <QAbstractProxyModel>
#include <QMap>
#include <QVariantList>
#include <QStringList>
 
class AggregateItem
{
public:
    QVariantList items;
    QVariant     result;
    int          sourceRow;
};
 
class Aggregator : public QAbstractProxyModel
{
   Q_OBJECT
 
public:
    Aggregator(QVariant (*aggFunction)(const QVariantList&),
               int colGroup,
               int colAgg,
               int roleGroup = Qt::DisplayRole,
               int roleAgg = Qt::DisplayRole,
               QObject * parent = 0);
 
    virtual int columnCount (const QModelIndex& parent = QModelIndex()) const ;
    virtual int rowCount(const QModelIndex& parent) const ;
    virtual QModelIndex index(int row, int column, const QModelIndex& parent = QModelIndex()) const ;
    virtual QModelIndex parent (const QModelIndex& index) const ;
    virtual QModelIndex mapFromSource(const QModelIndex&) const ;
    virtual QModelIndex mapToSource(const QModelIndex&) const ;
    virtual bool hasChildren ( const QModelIndex & parent = QModelIndex() ) const;
    virtual QVariant data ( const QModelIndex & index, int role = Qt::DisplayRole ) const;
    virtual Qt::ItemFlags flags ( const QModelIndex & index ) const;
 
    void transformData();
 
private:
    QMap< QString, AggregateItem > map;
    QStringList rows;
 
    int m_colGroup, m_colAgg, m_roleGroup, m_roleAgg;
    QVariant (*m_aggFunction)(const QVariantList&);
};

#include <QDebug>
#include <QAbstractProxyModel>
#include <QMap>
#include <QVariantList>
#include <QStringList>

class AggregateItem
{
public:
    QVariantList items;
    QVariant     result;
    int          sourceRow;
};

class Aggregator : public QAbstractProxyModel
{
   Q_OBJECT

public:
    Aggregator(QVariant (*aggFunction)(const QVariantList&),
               int colGroup,
               int colAgg,
               int roleGroup = Qt::DisplayRole,
               int roleAgg = Qt::DisplayRole,
               QObject * parent = 0);

    virtual int columnCount (const QModelIndex& parent = QModelIndex()) const ;
    virtual int rowCount(const QModelIndex& parent) const ;
    virtual QModelIndex index(int row, int column, const QModelIndex& parent = QModelIndex()) const ;
    virtual QModelIndex parent (const QModelIndex& index) const ;
    virtual QModelIndex mapFromSource(const QModelIndex&) const ;
    virtual QModelIndex mapToSource(const QModelIndex&) const ;
    virtual bool hasChildren ( const QModelIndex & parent = QModelIndex() ) const;
    virtual QVariant data ( const QModelIndex & index, int role = Qt::DisplayRole ) const;
    virtual Qt::ItemFlags flags ( const QModelIndex & index ) const;

    void transformData();

private:
    QMap< QString, AggregateItem > map;
    QStringList rows;

    int m_colGroup, m_colAgg, m_roleGroup, m_roleAgg;
    QVariant (*m_aggFunction)(const QVariantList&);
};

To copy to clipboard, switch view to plain text mode 

Source:

#include <QtGui/QApplication>
#include <QStandardItemModel>
#include <QTreeView>
#include <QObject>
 
#include "aggregator.h"
 
 
 
Aggregator::Aggregator(QVariant (*aggFunction)(const QVariantList&),
                       int colGroup, int colAgg,
                       int roleGroup, int roleAgg, QObject *parent) :
    QAbstractProxyModel(parent),
    m_colGroup(colGroup), m_colAgg(colAgg),
    m_roleGroup(roleGroup), m_roleAgg(roleAgg),
    m_aggFunction(aggFunction)
{ }
 
QVariant Total(const QVariantList & vars)
{
    int total = 0;
 
    foreach(QVariant var, vars)
    {
        total += var.toInt();
    }
 
    return QVariant(total);
}
 
QVariant Average(const QVariantList & vars)
{
    int total = 0;
 
    foreach(QVariant var, vars)
    {
        total += var.toInt();
    }
 
    return QVariant(total / vars.count());
}
 
void Aggregator::transformData()
{
    QAbstractItemModel * m = sourceModel();
    int rowCount = m->rowCount();
 
    for (int i = 0; i < rowCount; i++)
    {
        QString group = m->data(m->index(i, m_colGroup), m_roleGroup).toString();
        QVariant agg =  m->data(m->index(i, m_colAgg),   m_roleAgg);
 
        if (!map.contains(group))
        {
            AggregateItem item;
            item.sourceRow = i;
            map.insert(group, item);
            rows.push_back(group);
        }
 
        map[group].items.push_back(agg);
    }
 
    foreach (QString str, map.keys())
    {
        map[str].result = m_aggFunction(map.value(str).items);
        map[str].items.clear(); /* No longer needed. */
    }
}
 
QVariant Aggregator::data ( const QModelIndex & index, int role ) const
{
    if (index.column() == columnCount() - 1)
    {
        if ((index.row() < rows.count() && index.row() >= 0) &&
            (role == Qt::DisplayRole || role == Qt::EditRole))
            return map.value(rows.at(index.row())).result;
        else
            return QVariant();
    }
 
    return QAbstractProxyModel::data(index, role);
}
 
Qt::ItemFlags Aggregator::flags ( const QModelIndex & index ) const
{
    if (index.column() == columnCount() - 1)
        return Qt::ItemIsSelectable | Qt::ItemIsEnabled;
    else
        return QAbstractProxyModel::flags(index);
}
 
int Aggregator::columnCount(const QModelIndex& parent) const
{
    return sourceModel()->columnCount(QModelIndex()) + 1;
}
 
int Aggregator::rowCount(const QModelIndex& parent) const
{
    return rows.count();
}
 
QModelIndex Aggregator::index(int row, int column, const QModelIndex& parent) const
{
    if (row >= rowCount(QModelIndex()) || row < 0)
        return QModelIndex();
 
    return createIndex(row, column, map[rows.at(row)].sourceRow);
}
 
QModelIndex Aggregator::parent(const QModelIndex& index) const
{
    return QModelIndex();
}
 
QModelIndex Aggregator::mapFromSource(const QModelIndex& index) const
{
    if (index.isValid())
    {
        QString groupby = sourceModel()->data(sourceModel()->index(index.row(), m_colGroup)).toString();
        for (int i = 0; i < rows.count(); i++)
        {
            if (rows.at(i) == groupby)
               return createIndex(i, index.column(), map[groupby].sourceRow);
        }
    }
    return QModelIndex();
}
 
QModelIndex Aggregator::mapToSource(const QModelIndex& index) const
{
    if (index.isValid() && index.column() != columnCount(QModelIndex()) - 1)
        return sourceModel()->index(index.internalId(), index.column(), QModelIndex()) ;
    else
        return QModelIndex();
}
 
bool Aggregator::hasChildren(const QModelIndex &parent) const
{
    return !parent.isValid();
}

#include <QtGui/QApplication>
#include <QStandardItemModel>
#include <QTreeView>
#include <QObject>

#include "aggregator.h"



Aggregator::Aggregator(QVariant (*aggFunction)(const QVariantList&),
                       int colGroup, int colAgg,
                       int roleGroup, int roleAgg, QObject *parent) :
    QAbstractProxyModel(parent),
    m_colGroup(colGroup), m_colAgg(colAgg),
    m_roleGroup(roleGroup), m_roleAgg(roleAgg),
    m_aggFunction(aggFunction)
{ }

QVariant Total(const QVariantList & vars)
{
    int total = 0;

    foreach(QVariant var, vars)
    {
        total += var.toInt();
    }

    return QVariant(total);
}

QVariant Average(const QVariantList & vars)
{
    int total = 0;

    foreach(QVariant var, vars)
    {
        total += var.toInt();
    }

    return QVariant(total / vars.count());
}

void Aggregator::transformData()
{
    QAbstractItemModel * m = sourceModel();
    int rowCount = m->rowCount();

    for (int i = 0; i < rowCount; i++)
    {
        QString group = m->data(m->index(i, m_colGroup), m_roleGroup).toString();
        QVariant agg =  m->data(m->index(i, m_colAgg),   m_roleAgg);

        if (!map.contains(group))
        {
            AggregateItem item;
            item.sourceRow = i;
            map.insert(group, item);
            rows.push_back(group);
        }

        map[group].items.push_back(agg);
    }

    foreach (QString str, map.keys())
    {
        map[str].result = m_aggFunction(map.value(str).items);
        map[str].items.clear(); /* No longer needed. */
    }
}

QVariant Aggregator::data ( const QModelIndex & index, int role ) const
{
    if (index.column() == columnCount() - 1)
    {
        if ((index.row() < rows.count() && index.row() >= 0) &&
            (role == Qt::DisplayRole || role == Qt::EditRole))
            return map.value(rows.at(index.row())).result;
        else
            return QVariant();
    }

    return QAbstractProxyModel::data(index, role);
}

Qt::ItemFlags Aggregator::flags ( const QModelIndex & index ) const
{
    if (index.column() == columnCount() - 1)
        return Qt::ItemIsSelectable | Qt::ItemIsEnabled;
    else
        return QAbstractProxyModel::flags(index);
}

int Aggregator::columnCount(const QModelIndex& parent) const
{
    return sourceModel()->columnCount(QModelIndex()) + 1;
}

int Aggregator::rowCount(const QModelIndex& parent) const
{
    return rows.count();
}

QModelIndex Aggregator::index(int row, int column, const QModelIndex& parent) const
{
    if (row >= rowCount(QModelIndex()) || row < 0)
        return QModelIndex();

    return createIndex(row, column, map[rows.at(row)].sourceRow);
}

QModelIndex Aggregator::parent(const QModelIndex& index) const
{
    return QModelIndex();
}

QModelIndex Aggregator::mapFromSource(const QModelIndex& index) const
{
    if (index.isValid())
    {
        QString groupby = sourceModel()->data(sourceModel()->index(index.row(), m_colGroup)).toString();
        for (int i = 0; i < rows.count(); i++)
        {
            if (rows.at(i) == groupby)
               return createIndex(i, index.column(), map[groupby].sourceRow);
        }
    }
    return QModelIndex();
}

QModelIndex Aggregator::mapToSource(const QModelIndex& index) const
{
    if (index.isValid() && index.column() != columnCount(QModelIndex()) - 1)
        return sourceModel()->index(index.internalId(), index.column(), QModelIndex()) ;
    else
        return QModelIndex();
}

bool Aggregator::hasChildren(const QModelIndex &parent) const
{
    return !parent.isValid();
}

To copy to clipboard, switch view to plain text mode 

and usage:

int main(int argc, char *argv[])
 
{
    QApplication app(argc, argv);
 
    QStandardItemModel model(8, 4);
    for (int row = 0; row < 4; ++row) {
 
        QStandardItem *item = new QStandardItem(QString("test"));
        model.setItem(row, 0, item);
 
        item = new QStandardItem(QString("%1").arg(1));
        model.setItem(row, 1, item);
 
        for (int column = 2; column < 4; ++column) {
            QStandardItem *item = new QStandardItem(QString("row %0, column %1").arg(row).arg(column));
            model.setItem(row, column, item);
        }
    }
 
 
    for (int row = 4; row < 8; ++row) {
 
        QStandardItem *item = new QStandardItem(QString("test2"));
        model.setItem(row, 0, item);
 
        item = new QStandardItem(QString("%1").arg(3));
        model.setItem(row, 1, item);
 
        for (int column = 2; column < 4; ++column) {
            QStandardItem *item = new QStandardItem(QString("row %0, column %1").arg(row).arg(column));
            model.setItem(row, column, item);
        }
    }
 
 
 
    QTreeView * tv = new QTreeView;
    Aggregator * ag = new Aggregator(Total, 0, 1);
 
    ag->setSourceModel(&model);
    ag->transformData();
    tv->setModel(ag);
    tv->show();
 
    return app.exec();
}

int main(int argc, char *argv[])

{
    QApplication app(argc, argv);

    QStandardItemModel model(8, 4);
    for (int row = 0; row < 4; ++row) {

        QStandardItem *item = new QStandardItem(QString("test"));
        model.setItem(row, 0, item);

        item = new QStandardItem(QString("%1").arg(1));
        model.setItem(row, 1, item);

        for (int column = 2; column < 4; ++column) {
            QStandardItem *item = new QStandardItem(QString("row %0, column %1").arg(row).arg(column));
            model.setItem(row, column, item);
        }
    }


    for (int row = 4; row < 8; ++row) {

        QStandardItem *item = new QStandardItem(QString("test2"));
        model.setItem(row, 0, item);

        item = new QStandardItem(QString("%1").arg(3));
        model.setItem(row, 1, item);

        for (int column = 2; column < 4; ++column) {
            QStandardItem *item = new QStandardItem(QString("row %0, column %1").arg(row).arg(column));
            model.setItem(row, column, item);
        }
    }



    QTreeView * tv = new QTreeView;
    Aggregator * ag = new Aggregator(Total, 0, 1);

    ag->setSourceModel(&model);
    ag->transformData();
    tv->setModel(ag);
    tv->show();

    return app.exec();
}

To copy to clipboard, switch view to plain text mode 

[Nightfox]

Hi numbat

This looks exiting. I'm getting an error though when trying to compile. It's in the declaration of ag (see above line 39 in main.cpp) and the error description is: 'Total' was not declared in this scope. Are you passing the right argument to the constructor?

[numbat]

Sorry, add the following two lines to the end of the header file:

QVariant Average(const QVariantList & vars);
QVariant Total(const QVariantList & vars);

QVariant Average(const QVariantList & vars);
QVariant Total(const QVariantList & vars);

To copy to clipboard, switch view to plain text mode 

[Nightfox]

I'm still getting the same error after pasting the two function prototypes to the class. The error message is the same as before: 'Total' was not declared in this scope. I tried to work around it but I must admit that I don't understand the following argument for the constructor: QVariant (*aggFunction)(const QVariantList&) and you have it later in the class under the private section as QVariant (*m_aggFunction)(const QVariantList&). Does this mean casting QVariantList Items as pointer to m_aggFunction or what? I don't really follow?!

[numbat]

It works for me. I'll attach a zip.

I tried to work around it but I must admit that I don't understand the following argument for the constructor: QVariant (*aggFunction)(const QVariantList&) and you have it later in the class under the private section as QVariant (*m_aggFunction)(const QVariantList&).

That is a function pointer. Specifically, it is a pointer to a function that takes a const QVariantList& as argument and returns a QVariant. I use function pointers so that the programmer can provide another function as an argument to the constructor and this function will be used as the aggregate function. This probably signals my C heritage, in C++ we should probably use a class.

[numbat]

Here is another simpler, although far less efficient way of doing it:

#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlTableModel>
#include <QAbstractItemModel>
#include <QStringList>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QStandardItemModel>
#include <QTreeView>
#include <QHeaderView>
 
 
/* Given a data model, a column to group by, a column to aggregate and an
   aggregate function, this function will return a model with the given
   transformation. Available aggregate function include SUM, AVG, COUNT, MAX
   and MIN.
 */
QAbstractItemModel* doGroupby(const QAbstractItemModel & m,
                              unsigned int groupByColumn,
                              unsigned int aggregateColumn,
                              const QString & aggregateFunction = "SUM")
{
    /* Open an in-memory database. */
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    bool ok = db.open();
 
    /* Create a table with the correct number of columns. */
    QStringList columns;
    for (int i = 0; i < m.columnCount(); i++)
        columns.push_back(QString("F%1").arg(i));
 
    QString sql = QString("CREATE TABLE T1(%1)").arg(columns.join(","));
    QSqlQuery query(db);
    ok = query.exec(sql);
 
    /* Set up a table model of our newly created table. */
    QSqlTableModel  tbl(0, db);
    tbl.setTable("T1");
    tbl.setEditStrategy(QSqlTableModel::OnManualSubmit);
    tbl.insertRows(0, m.rowCount());
 
    /* Now copy accross our model to the newly created table. */
    for (int i = 0; i < m.rowCount(); i++)
        for (int j = 0; j < m.columnCount(); j++)
            tbl.setData(tbl.index(i, j), m.data(m.index(i, j)));
 
    /* Submit changes to database. */
    ok = tbl.submitAll();
 
    /* Finally, query our database for the grouped by table. */
    QSqlQueryModel * out = new QSqlQueryModel;
    QString sql2 = QString("SELECT %3(F%2), * FROM T1 GROUP BY F%1").
                   arg(groupByColumn).
                   arg(aggregateColumn).
                   arg(aggregateFunction);
 
    out->setQuery(sql2, db);
    return out;
}
 
 
 
 
int main(int argc, char * argv[])
{
    QApplication a(argc, argv);
 
 
    QStandardItemModel model(8, 3);
    for (int row = 0; row < 4; ++row)
    {
        QStandardItem *item = new QStandardItem(QString("test"));
        model.setItem(row, 0, item);
 
        item = new QStandardItem(QString("%1").arg(1));
        model.setItem(row, 1, item);
 
        item = new QStandardItem(QString("row %0").arg(row));
        model.setItem(row, 2, item);
    }
 
    for (int row = 4; row < 8; ++row)
    {
        QStandardItem *item = new QStandardItem(QString("test2"));
        model.setItem(row, 0, item);
 
        item = new QStandardItem(QString("%1").arg(5));
        model.setItem(row, 1, item);
 
        item = new QStandardItem(QString("row %0").arg(row));
        model.setItem(row, 2, item);
    }
 
    QTreeView tv;
    QAbstractItemModel * tbl = doGroupby(model, 0, 1, "SUM");
    tbl->setHeaderData(0, Qt::Horizontal, "Total");
    tv.setModel(tbl);
    tv.header()->moveSection(0, 3);
    tv.show();
 
    return a.exec();
}

#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlTableModel>
#include <QAbstractItemModel>
#include <QStringList>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QStandardItemModel>
#include <QTreeView>
#include <QHeaderView>


/* Given a data model, a column to group by, a column to aggregate and an
   aggregate function, this function will return a model with the given
   transformation. Available aggregate function include SUM, AVG, COUNT, MAX
   and MIN.
 */
QAbstractItemModel* doGroupby(const QAbstractItemModel & m,
                              unsigned int groupByColumn,
                              unsigned int aggregateColumn,
                              const QString & aggregateFunction = "SUM")
{
    /* Open an in-memory database. */
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    bool ok = db.open();

    /* Create a table with the correct number of columns. */
    QStringList columns;
    for (int i = 0; i < m.columnCount(); i++)
        columns.push_back(QString("F%1").arg(i));

    QString sql = QString("CREATE TABLE T1(%1)").arg(columns.join(","));
    QSqlQuery query(db);
    ok = query.exec(sql);

    /* Set up a table model of our newly created table. */
    QSqlTableModel  tbl(0, db);
    tbl.setTable("T1");
    tbl.setEditStrategy(QSqlTableModel::OnManualSubmit);
    tbl.insertRows(0, m.rowCount());

    /* Now copy accross our model to the newly created table. */
    for (int i = 0; i < m.rowCount(); i++)
        for (int j = 0; j < m.columnCount(); j++)
            tbl.setData(tbl.index(i, j), m.data(m.index(i, j)));

    /* Submit changes to database. */
    ok = tbl.submitAll();

    /* Finally, query our database for the grouped by table. */
    QSqlQueryModel * out = new QSqlQueryModel;
    QString sql2 = QString("SELECT %3(F%2), * FROM T1 GROUP BY F%1").
                   arg(groupByColumn).
                   arg(aggregateColumn).
                   arg(aggregateFunction);

    out->setQuery(sql2, db);
    return out;
}




int main(int argc, char * argv[])
{
    QApplication a(argc, argv);


    QStandardItemModel model(8, 3);
    for (int row = 0; row < 4; ++row)
    {
        QStandardItem *item = new QStandardItem(QString("test"));
        model.setItem(row, 0, item);

        item = new QStandardItem(QString("%1").arg(1));
        model.setItem(row, 1, item);

        item = new QStandardItem(QString("row %0").arg(row));
        model.setItem(row, 2, item);
    }

    for (int row = 4; row < 8; ++row)
    {
        QStandardItem *item = new QStandardItem(QString("test2"));
        model.setItem(row, 0, item);

        item = new QStandardItem(QString("%1").arg(5));
        model.setItem(row, 1, item);

        item = new QStandardItem(QString("row %0").arg(row));
        model.setItem(row, 2, item);
    }

    QTreeView tv;
    QAbstractItemModel * tbl = doGroupby(model, 0, 1, "SUM");
    tbl->setHeaderData(0, Qt::Horizontal, "Total");
    tv.setModel(tbl);
    tv.header()->moveSection(0, 3);
    tv.show();

    return a.exec();
}

To copy to clipboard, switch view to plain text mode 

[Nightfox]

Hi numbat
It just compiled the zip and it works like a charm! Last time I added the two lines inside the class scope so that's why I could''t get it to work before. I must say I'm impressed with your post - so little code and yet so powerfull. This is exactly what I wanted to do. Thanks!

[Nightfox]

Hi numbat
It just compiled the zip and it works like a charm! Last time I added the two lines inside the class scope so that's why I could''t get it to work before. I must say I'm impressed with your post - so little code and yet so powerfull. This is exactly what I wanted to do. Thanks!