Group / Aggregate QAbstractItemModel

[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!