QsqlQueryModel Subclass

This question may have been answered somewhere else already, but I just can't seem to find the answer. I'm using the model to display a set of data from a sql database. I'm using the QSqlQueryModel to retrieve the data.

In the QSqlQueryModel I need to add a column that is editable for the user. The data needs to be saved in the model and not committed to the database. This means that the setData function from QSqlQueryModel needs to be implemented and change the internal data. Is this possible? And is there an example?

Any help is appreciated, thanks.

The better way is make use of a custom proxy model, and not disturb the QSqlQueryModel. Moreover implementing QSqlQueryModel::setData() may not be practical as you don't know how the QSqlQueryModel stores the data internally.

The proxy model will add the extra column.

Thanks for your replay,

So one of the correct ways, would be to create a QAbstractProxyModel subclass with the QSqlQuery model as sourcemodel.

which functions do i need to implement/reimplement? by for of them i have no idea.

Any help is appreciated, thanks.

Code:

---

class SqlProxyModelAddColumn : public QAbstractProxyModel
{
public:
    SqlProxyModelAddColumn() : QAbstractProxyModel(){
 
    }
 
    virtual ~SqlProxyModelAddColumn(){
 
    }
 
    virtual QModelIndex mapFromSource( const QModelIndex & sourceIndex) const{
 
        // what to do here?
    }
    virtual QModelIndex mapToSource( const QModelIndex & proxyIndex) const{
 
       // what to do here?
    }
 
    virtual QModelIndex index(int row, int column, const QModelIndex &parent = QModelIndex()) const{
 
        // what to do here?
 
        if (row < 0 || column < 0)
               return QModelIndex();
 
        return createIndex(row, column); 
    }
    virtual QModelIndex parent(const QModelIndex &child) const{
 
        // what to do here?
    }
 
    virtual Qt::ItemFlags flags ( const QModelIndex & index ) const{
 
        if (index.column() == sourceModel()->columnCount()) // extra colum
            return Qt::ItemIsEditable | Qt::ItemIsSelectable;
        return sourceModel()->flags(index);
 
    }
 
    virtual bool setData( const QModelIndex & index, const QVariant & value, int role = Qt::EditRole ){
 
        if (role != Qt::EditRole)
            return false;
 
        if (!index.isValid())
            return false;
 
        if (index.column() == sourceModel()->columnCount()){ // extra column
 
            for ( int i = 0; i < LastColumn.count();++i){
 
                if (LastColumn[i].first == index.row()){
                    LastColumn.removeAt(i);
                    LastColumn.append(qMakePair(index.row(),value.toString()));
                    return true;
                }
 
               LastColumn.append(qMakePair(index.row(),value.toString()));
               return true;
            }
        }
 
        return sourceModel()->setData(index, value, role);
    }
 
    virtual QVariant data ( const QModelIndex & proxyIndex, int role = Qt::DisplayRole ) const{
 
        if (role != Qt::DisplayRole)
            return false;
 
        if (!proxyIndex.isValid())
            return false;
 
        if (proxyIndex.column() == sourceModel()->columnCount()){ // extra column
 
            for ( int i = 0; i < LastColumn.count();++i){
 
                if (LastColumn[i].first == proxyIndex.row())
                    return LastColumn[i].second;
            }
 
            return QString();
        }
 
        return sourceModel()->data(proxyIndex, role);
    }
 
    virtual int rowCount(const QModelIndex &parent = QModelIndex()) const{
 
        return sourceModel->rowCount()+1;
    }
 
    virtual int columnCount(const QModelIndex &parent = QModelIndex()) const{
 
        return sourceModel->columnCount()+1;
    }
 
private:
    QList<QPair<int,QString> > LastColumn;
 
};

---

Here is an example
Attachment 9034

Code:

---

#include <QtGui>
#include <QtSql>
#include <QApplication>
 
// Add one extra column at the beginning
class ProxyModel : public QAbstractProxyModel
{
    Q_OBJECT
public:
    explicit ProxyModel(QObject * parent = 0)
        : QAbstractProxyModel(parent)
    {
        ;
    }
 
    void setSourceModel(QAbstractItemModel *sourceModel)
    {
        if(this->sourceModel() != sourceModel)
        {
            if(this->sourceModel() != 0)
            {
                disconnect(this->sourceModel(), SIGNAL(rowsAboutToBeInserted(const QModelIndex &, int, int)),
                           this, SIGNAL(rowsAboutToBeInserted(const QModelIndex &, int, int)));
                disconnect(this->sourceModel(), SIGNAL(rowsInserted(const QModelIndex &, int, int)),
                           this, SIGNAL(rowsInserted(const QModelIndex &, int, int)));
                disconnect(this->sourceModel(), SIGNAL(columnsAboutToBeInserted(const QModelIndex &, int, int)),
                           this, SIGNAL(columnsAboutToBeInserted (const QModelIndex &, int, int)));
                disconnect(this->sourceModel(), SIGNAL(columnsInserted(const QModelIndex &, int, int)),
                           this, SIGNAL(columnsInserted(const QModelIndex &, int, int)));
                disconnect(this->sourceModel(), SIGNAL(modelAboutToBeReset()),
                           this, SIGNAL(modelAboutToBeReset()));
                disconnect(this->sourceModel(), SIGNAL(modelReset()),
                           this, SIGNAL(modelReset()));
                disconnect(this->sourceModel(), SIGNAL(layoutAboutToBeChanged()),
                           this, SIGNAL(layoutAboutToBeChanged()));
                disconnect(this->sourceModel(), SIGNAL(layoutChanged()),
                           this, SIGNAL(layoutChanged()));
                disconnect(this->sourceModel(), SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &)),
                           this, SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &)));
            }
 
            QAbstractProxyModel::setSourceModel(sourceModel);
 
            connect(this->sourceModel(), SIGNAL(rowsAboutToBeInserted(const QModelIndex &, int, int)),
                    this, SIGNAL(rowsAboutToBeInserted(const QModelIndex &, int, int)));
            connect(this->sourceModel(), SIGNAL(rowsInserted(const QModelIndex &, int, int)),
                    this, SIGNAL(rowsInserted(const QModelIndex &, int, int)));
            connect(this->sourceModel(), SIGNAL(columnsAboutToBeInserted (const QModelIndex &, int, int)),
                    this, SIGNAL(columnsAboutToBeInserted(const QModelIndex &, int, int)));
            connect(this->sourceModel(), SIGNAL(columnsInserted(const QModelIndex &, int, int)),
                    this, SIGNAL(columnsInserted(const QModelIndex &, int, int)));
            connect(this->sourceModel(), SIGNAL(modelAboutToBeReset()),
                    this, SIGNAL(modelAboutToBeReset()));
            connect(this->sourceModel(), SIGNAL(modelReset()),
                    this, SIGNAL(modelReset()));
            connect(this->sourceModel(), SIGNAL(modelReset()),
                    this, SIGNAL(modelReset()));
            connect(this->sourceModel(), SIGNAL(layoutAboutToBeChanged()),
                    this, SIGNAL(layoutAboutToBeChanged()));
            connect(this->sourceModel(), SIGNAL(layoutChanged()),
                    this, SIGNAL(layoutChanged()));
            connect(this->sourceModel(), SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &)),
                    this, SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &)));
        }
    }
 
    QModelIndex index(int row, int column, const QModelIndex & parent = QModelIndex()) const
    {
        if(!parent.isValid())
        {
            if(column == 0)
                return createIndex(row, column, -1);
            else
                return createIndex(row, column, 0);
        }
        return QModelIndex();
    }
 
    QModelIndex parent(const QModelIndex & /*child*/) const
    {
        return QModelIndex();
    }
 
    int rowCount(const QModelIndex & parent = QModelIndex()) const
    {
        if(!parent.isValid())
            return sourceModel()->rowCount(parent);
        return 0;
    }
 
    int columnCount(const QModelIndex & parent = QModelIndex()) const
    {
        if(!parent.isValid())
            return sourceModel()->columnCount(parent) + 1;
        return 0;
    }
 
    QModelIndex mapToSource(const QModelIndex & proxyIndex) const
    {
        if(proxyIndex.column() <= 0)
            return QModelIndex();
 
        return sourceModel()->index(proxyIndex.row(), proxyIndex.column() - 1);
    }
 
    QModelIndex mapFromSource(const QModelIndex & sourceIndex) const
    {
        return index(sourceIndex.row(), sourceIndex.column() + 1);
    }
 
    QVariant headerData(int section, Qt::Orientation orientation, int role = Qt::DisplayRole) const
    {
        if(orientation == Qt::Horizontal)
        {
            if(section <= 0)
                return QString("Proxy Column");
            return sourceModel()->headerData(section - 1, orientation, role);
        }
        return sourceModel()->headerData(section, orientation, role);
    }
 
    QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const
    {
        if((index.column() <= 0) and (role == Qt::DisplayRole))
            return QString("Proxy Data - Row:%1").arg(index.row());
 
        return sourceModel()->data(mapToSource(index), role);
    }
 
    bool setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole)
    {
        if((index.column() <= 0) and (role == Qt::EditRole))
        {
            // TODO: store data into ProxyModel
            return true;
        }
        return sourceModel()->setData(mapToSource(index), role);
    }
};
 
int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
 
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("Qt.sqlite");
 
    QSqlQueryModel model;
 
    ProxyModel proxy;
    proxy.setSourceModel(&model);
 
    QTableView view;
    view.setWindowTitle("QSqlQueryModel View");
    view.setModel(&model);
    view.show();
 
    QTableView proxyView;
    proxyView.setWindowTitle("ProxyModel View");
    proxyView.setModel(&proxy);
    proxyView.show();
 
    if(db.open())
        model.setQuery("SELECT Country, Location FROM Locations");
 
    view.resizeColumnsToContents();
    proxyView.resizeColumnsToContents();
 
    return app.exec();
}
 
#include "main.moc"

---

Thanks for your replay,

I implemented data and setdata from your example. But the data in the proxy column is not editable.
Even when i reimplemented flags the column stayed gray and did not call setdata.
EDIT sloved graynes by returning Qt::ItemIsEnabled flag EDIT
What I'm doing wrong?

compiling the code with qt 5 i got an error on row 74.

return createIndex(row, column, 0);
call of overloaded 'createIndex(int&, int&, int)' is ambiguous
so i used the default. createIndex(int&, int&)

Code:

---

QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const
    {
        if((index.column() <= 0) and (role == Qt::DisplayRole)){
 
           //return QString("Proxy Data - Row:%1").arg(index.row());
 
            for (int i = 0; i < LastColumn.count();++i){
 
                if (LastColumn[i].first == index.row())
                    return LastColumn[i].second;
            }
 
            return QString("temp");
 
        }
 
        return sourceModel()->data(mapToSource(index), role);
    }
 
    bool setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole)
    {
    qDebug() << "setdata";
 
        if((index.column() <= 0) and (role == Qt::EditRole))
        {
            // TODO: store data into ProxyModel
            for ( int i = 0; i < LastColumn.count();++i){
 
                if (LastColumn[i].first == index.row()){
                    LastColumn.removeAt(i);
                    LastColumn.append(qMakePair(index.row(),value.toString()));
                    return true;
                }
            }
 
            LastColumn.append(qMakePair(index.row(),value.toString()));
            return true;
        }
        return sourceModel()->setData(mapToSource(index), role);
    }
 
    Qt::ItemFlags flags ( const QModelIndex & index ) const{
 
        if (index.column() <= 0){
 
            return (Qt::ItemIsEditable | Qt::ItemIsSelectable | Qt::ItemIsEnabled);
 
        }
 
        return QAbstractProxyModel::flags(index);
 
    }
 
    private:
        QList<QPair<int,QString> > LastColumn;

---

Ok I have ported to Qt 5.0.2, and the new column is not editable but cannot be saved, hope you can figure that out.

Basically the problem is with QAbstractProxyModel, which extensively uses mapToSource(), and mapFromSource()
mapToSource(): All proxy indexes (except the first column) will have a unique source index. All the first column indexes will map to invalid index
mapFromSource(): All source indexes will have a uniue proxy index. This is just fine, but the first column indexes in proxy model cannot be accesable from any source index. Other way of saying it will be that mapFromSource() function will never be able to return a proxy model index from the first column.

As a work around for this I created the invalid dummy index to able to get to the first column index of proxy model

Any why I hope you understand what I did, here is the working code

Code:

---

#include <QtGui>
#include <QtSql>
#include <QtWidgets>
#include <QApplication>
 
// Add one extra column at the begining
class ProxyModel : public QAbstractProxyModel
{
    Q_OBJECT
public:
    explicit ProxyModel(QObject * parent = 0)
        : QAbstractProxyModel(parent)
    {
        ;
    }
 
    void setSourceModel(QAbstractItemModel *sourceModel)
    {
        if(this->sourceModel() != sourceModel)
        {
            if(this->sourceModel() != 0)
            {
                disconnect(this->sourceModel(), SIGNAL(modelAboutToBeReset()),
                           this, SIGNAL(modelAboutToBeReset()));
                disconnect(this->sourceModel(), SIGNAL(modelReset()),
                           this, SIGNAL(modelReset()));
                disconnect(this->sourceModel(), SIGNAL(layoutAboutToBeChanged()),
                           this, SIGNAL(layoutAboutToBeChanged()));
                disconnect(this->sourceModel(), SIGNAL(layoutChanged()),
                           this, SIGNAL(layoutChanged()));
            }
 
            QAbstractProxyModel::setSourceModel(sourceModel);
 
            connect(this->sourceModel(), SIGNAL(modelAboutToBeReset()),
                    this, SIGNAL(modelAboutToBeReset()));
            connect(this->sourceModel(), SIGNAL(modelReset()),
                    this, SIGNAL(modelReset()));
            connect(this->sourceModel(), SIGNAL(modelReset()),
                    this, SIGNAL(modelReset()));
            connect(this->sourceModel(), SIGNAL(layoutAboutToBeChanged()),
                    this, SIGNAL(layoutAboutToBeChanged()));
            connect(this->sourceModel(), SIGNAL(layoutChanged()),
                    this, SIGNAL(layoutChanged()));
        }
    }
 
    QModelIndex index(int row, int column, const QModelIndex & parent = QModelIndex()) const
    {
        if(!parent.isValid())
            return createIndex(row, column);
        return QModelIndex();
    }
 
    QModelIndex parent(const QModelIndex & /*child*/) const
    {
        return QModelIndex();
    }
 
    int rowCount(const QModelIndex & parent = QModelIndex()) const
    {
        if(!parent.isValid())
            return sourceModel()->rowCount(QModelIndex());
        return 0;
    }
 
    int columnCount(const QModelIndex & parent = QModelIndex()) const
    {
        if(!parent.isValid())
            return sourceModel()->columnCount() + 1;
        return 0;
    }
 
    QModelIndex mapToSource(const QModelIndex & proxyIndex) const
    {
        if(!proxyIndex.isValid())
            return QModelIndex();
 
        if(proxyIndex.column() == 0)
            return createIndex(proxyIndex.row(), -1, (quintptr)-1); //<<<<<<<<<<<<<<<<<<<<<< This is a workaround. Note is weird to create a index of proxy model and return as if it were an index of sourceModel(), it is not a common but will work (at-least in this case)
 
        return sourceModel()->index(proxyIndex.row(), proxyIndex.column() - 1);
    }
 
    QModelIndex mapFromSource(const QModelIndex & sourceIndex) const
    {
        if(!sourceIndex.isValid())
        {
            if((sourceIndex.row() > -1)                     and //<<<<<<<<<<<<<<<<<<<<<< This is a workaround.
               (sourceIndex.column() == -1)                 and
               (sourceIndex.internalId() == (quintptr)-1)     )
                return index(sourceIndex.row(), 0);
            else
                return QModelIndex();
        }
 
        return index(sourceIndex.row(), sourceIndex.column() + 1);
    }
 
    QVariant headerData(int section, Qt::Orientation orientation, int role = Qt::DisplayRole) const
    {
        if(orientation == Qt::Horizontal)
        {
            if(section == 0)
                return QString("Proxy Column");
            return sourceModel()->headerData(section - 1, orientation, role);
        }
        return sourceModel()->headerData(section, orientation, role);
    }
 
    QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const
    {
        if(index.column() == 0)
        {
            if((role == Qt::DisplayRole) or (role == Qt::EditRole))
                return QString("Proxy Data - Row:%1").arg(index.row());
            else
                return QVariant();
        }
 
        return sourceModel()->data(mapToSource(index), role);
    }
 
    bool setData(const QModelIndex & index, const QVariant & value, int role = Qt::EditRole)
    {
        if((index.column() == 0) and
           ((role == Qt::DisplayRole) or (role == Qt::EditRole)))
        {
            // TODO: store data into ProxyModel for only column 0
            (void)value;
            emit dataChanged(index, index);
            return true;
        }
 
        return sourceModel()->setData(mapToSource(index), value, role);
    }
 
    Qt::ItemFlags flags(const QModelIndex & index) const
    {
        if(index.column() == 0)
        {
            Qt::ItemFlags flag = Qt::ItemIsEnabled | Qt::ItemIsSelectable | Qt::ItemIsEditable;
            return flag;
        }
 
        return sourceModel()->flags(mapToSource(index));
    }
};
 
#define USE_QSTANDARDMODEL
 
int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
 
#ifdef USE_QSTANDARDMODEL
    QStandardItemModel model;
    model.setRowCount(5);
    model.setColumnCount(2);
#else // USE_QSTANDARDMODEL
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("Qt.sqlite");
 
    QSqlQueryModel model;
#endif // USE_QSTANDARDMODEL
 
    ProxyModel proxy;
    proxy.setSourceModel(&model);
 
    QTableView view;
    view.setWindowTitle("QSqlQueryModel View");
    view.setModel(&model);
    view.show();
 
    QTableView proxyView;
    proxyView.setWindowTitle("ProxyModel View");
    proxyView.setModel(&proxy);
    proxyView.show();
    proxyView.setEditTriggers(QTableView::DoubleClicked);
    proxyView.setItemDelegate(new QStyledItemDelegate(&proxyView));
 
#ifndef USE_QSTANDARDMODEL
    if(db.open())
        model.setQuery("SELECT Country, Location FROM Locations");
#endif // USE_QSTANDARDMODEL
 
    view.resizeColumnsToContents();
    proxyView.resizeColumnsToContents();
 
    return app.exec();
}
#include "main.moc"

---

Thanks again,

it is now clear for me, i thought that mapFromSource was not called by setting data.

There is only 1 thing i do not understand, modelReset() is connected twice( r 37/39), so this is just a copy-past mistake or is it useful?

Quote:

---

There is only 1 thing i do not understand, modelReset() is connected twice( r 37/39), so this is just a copy-past mistake or is it useful?

---

Yes it is copy paste leftovers, just ignore that. Moreove all the signal/slot connections in there are not required, just use the ones required for your functionality, also note that you may need to add more connections if required.