PDA

View Full Version : Group / Aggregate QAbstractItemModel



Nightfox
9th December 2009, 15:27
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
13th December 2009, 12:55
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
8th January 2010, 00:22
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
8th January 2010, 02:06
Hope this (http://subcommanderblog.wordpress.com/2009/06/03/creating-a-custom-qabstractproxymodel-google-mock/) helps

numbat
8th January 2010, 13:40
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&);
};

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();
}

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();
}

Nightfox
9th January 2010, 06:36
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
10th January 2010, 09:18
Sorry, add the following two lines to the end of the header file:


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

Nightfox
10th January 2010, 13:27
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
10th January 2010, 17:01
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
10th January 2010, 19:45
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();
}

Nightfox
10th January 2010, 20:02
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
10th January 2010, 20:09
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!