How to integrate QSqlTableModel with a TableView defined in QML?

wahynes · wrote on 24 Apr 2014, 02:46

Hi,

I'm trying to use a C++ QSqlTableModel with a TableView object that
is defined in QML.

As a test, just to make sure my QSqlTableModel was working, I tested it
with the following code:

@
QSqlTableModel *myModel = new QSqlTableModel(0,existing_db_connection);
myModel->setTable( "myTable" );
myModel->select();

QTableView *myView = new QTableView;
myView->setModel( myModel );
myView->show();
@

As expected, this popped up a new window with the expected data
neatly laid out under properly named column headers. My problem
is that I don't want a new pop-up window; I want the table's data
integrated with an existing qml TableView definition for my application's
UI.

Here's a snippet of the QML definition for the TableView I want to
use:

@
TableView {
id: items_acquired_list
objectName: "myTableView"
anchors.top: item_aquired_editor.bottom
anchors.bottom: parent.bottom
anchors.left: parent.left
anchors.right: parent.right

                    TableViewColumn {
                        role: "acquisition_id"
                        title: "Acquisition Id"
                        width: 80
                    }
                    TableViewColumn {
                        role: "source_thrift_id"
                        title: "Source Thrift Id"
                        width: 80
                    }
                    TableViewColumn {
                        role: "source_date_id"
                        title: "Source Date Id"
                        width: 80
                    }
                    TableViewColumn {
                        role: "item_type"
                        title: "Item Type"
                        width: 60
                        visible: true
                    }
                    TableViewColumn {
                        role: "item_cost_ea"
                        title: "Item Cost Ea"
                        width: 60
                        visible: true
                    }
                    TableViewColumn {
                        role: "product_code"
                        title: "Product Code"
                        width: 60
                        visible: true
                    }
       }

@

I'm not such a newbie that I can't query a QML object's single property from
within C++, or set up a QML signal/C++ slot connection for when that property
changes. But on the other hand, crossing the barrier from C++ to QML with
respect to models, views, and especially delegates leaves me still in the house
of the terminally befuddled.

I've tried numerous things to get the C++ QSqlTableModel's data into
the QML's TableView; all of them failed, so I haven't enumerated them
here. But, I'll be happy to do so if needed to diagnose my problem and
find a solution.

Near-miss examples I've found try to explain the solution in terms of
a QML ListView. Never having implemented a ListView,
let alone a TableView, unfortunately increases my befuddlement factor,
rather than decreasing it.

Any help appreciated!

An end-to-end QML->C++ example using any generic table would
be especially appreciated!

Regards,

wahynes

Eddy · wrote on 24 Apr 2014, 05:47

Hi and welcome to devnet,

"this example":http://stackoverflow.com/questions/18616497/how-to-use-models-with-qml

might help you with your journey

wahynes · wrote on 24 Apr 2014, 22:01

Journey, indeed, ha ha. Though, I was kinda hoping to avoid a cross-country type of journey. Thanks for the pointer, Eddy. The given example did spark a light-bulb, or two.

You'll notice that in my TableView QML, I haven't defined a model. Every time I did define one I got a not-defined error. I've learned not to argue with compilers, so naturally I assumed I was doing something wrong, and took the model: myModel statement out of the QML; hoping that I could do a late-bind by getting a pointer to my TableView object, casting it to a QTableView, and calling setModel() from the C++ side. This would be ideal, in theory, but that story ends pretty horribly (app crashed).

This QML TableView exists nested within one tab of my GUI. I load the whole QML file early on, but apparently this TableView object isn't even instantiated until that tab is switched to. I'm assuming that's the case, because I can't find a pointer to it until the tab is switched to by a user (that would be me, in this case). I thought that would be fine, because I don't want to run a bunch of SQL queries across all the tabs of my app unless the user actually switched
to the tab anyway. So far so good?

Other examples, like the one you provided, have lead me to try:

@
guiEngine->rootContext()->setContextProperty( "myModel", myModel );
@

This seemingly did nothing; whether or not I had the 'model: myModel' statement defined in my QML. Here's my light-bulb aha! (and the problem,
if I'm reading the example correctly). Your example showed that the setContextProperty() call was done BEFORE loading the QML file.

I'm guessing now, that's not purely coincidence. I'm also guessing that's why
I get a not defined error; because I'm calling setContextProperty() long after I've loaded the QML, and the app is already being navigated through.

Is this right? I.e., setContextProperty() only has any effect if it's done before loading the QML file? If that's true, then do I simply reload the whole QML
file again, every time I want to bind another C++ model to something? Or is
that too simplistic of a way of thinking?

Thanks again, for any pointers.

wahynes

wahynes · wrote on 25 Apr 2014, 21:57

Travel update:

Empirical evidence suggests that setContextProperty() does nothing
if it's not called BEFORE loading the QML file.

Observation: this is unfortunate, because I wanted to start displaying
trace information into another pane of my GUI before my .ini file
was procesed, or the DB was connected.

Part one of my solution was to delay loading the QML until my DB
was connected.
Reloading the qml file was not the answer. Not unless, of course
you want a second copy of your app's window to get created. I did
not, so this didn't work for me.
I surmise that QSqlTableModel, despite its name, doesn't actually
provide all the features needed to work as a model within QML by
itself. In my (very limited) experience, it needs to be sub-classed
to provide a hash of columns in your table that map to the roles in
your TableView's QML.

I'm guessing that the C++ version of QTableView must provide this
missing piece, but if you define you're TableView in QML, you
will have to do this for your C++ model before passing it to QML.

Those of you with more than just a few days experience with
QSqlTableModel should feel free to disagree and correct my
assumption here.
I found very helpful snippets of code here:
http://qt-project.org/wiki/QML_and_QSqlTableModel
and here
http://stackoverflow.com/questions/14613824/qsqltablemodel-inheritor-and-qtableview

Neither one of these worked as written for me. I'm on QT 5.2.1. Don't
know if that's why they didn't work. But they did get me close enough
to merge and modify the code and get a version of it working for my
environment.

Since I'll soon be beyond the 6000 character limit for posts, I will post the solution
in a follow-up reply following this one.

wahynes · wrote on 25 Apr 2014, 21:59

Continued . . .

The following solution does display a wrapped C++ QSqlTableModel
into a QML definition of a TableView. It only displays the info. I haven't
figured out how to make it editable--which is my end-goal--but that's a
journey for another day :)

NOTE: If you're using any other version of QT than 5.2.1 then your
mileage might vary. No warranties for any version of QT is implied
or given. :)

5.1 The wrapped QSqlTableModel class:
@
class DbTableModel : public QSqlTableModel
{
Q_OBJECT
private:
QHash<int, QByteArray> roles;

void generateRoleNames();

public:

explicit DbTableModel(const DbTableModel &other, QObject *parent = 0);

explicit DbTableModel(QObject *parent = 0, QSqlDatabase db = QSqlDatabase());

~DbTableModel();

Q_INVOKABLE QVariant data(const QModelIndex &index, int role=Qt::DisplayRole ) const;

virtual void setTable ( const QString &table_name );

virtual QHash<int, QByteArray> roleNames() const;

};
@

5.2 The wrapper implementation:

@
DbTableModel::DbTableModel(const DbTableModel &other, QObject *parent)
: QSqlTableModel(parent,other.database())
{

}

DbTableModel::DbTableModel(QObject *parent, QSqlDatabase db)
: QSqlTableModel(parent,db)
{

}

DbTableModel::~DbTableModel()
{

}

QVariant DbTableModel::data ( const QModelIndex & index, int role ) const
{

if(index.row() >= rowCount())
{
    return QString("");
}
if(role < Qt::UserRole)
{
    return QSqlTableModel::data(index, role);
}

QModelIndex modelIndex = this->index(index.row(), role - Qt::UserRole - 1 );
return QSqlQueryModel::data(modelIndex, Qt::EditRole);

}

// Role names are set to whatever your db table's column names are.
//
void DbTableModel::generateRoleNames()
{
roles.clear();
for (int i = 0; i < columnCount(); i++)
{
roles[Qt::UserRole + i + 1] = QVariant(headerData(i, Qt::Horizontal).toString()).toByteArray();
}
}

QHash<int, QByteArray> DbTableModel::roleNames() const
{
return roles;
}

void DbTableModel::setTable ( const QString &table_name )
{
QSqlTableModel::setTable(table_name);
generateRoleNames();
}
@

5.3 An example of its usage:
@
void example(QQmlApplicationEngine *guiEngine)
{
DbTableModel *itemsAcquiredModel;

    itemsAcquiredModel = new DbTableModel(0,dbt.database());

    itemsAcquiredModel->setTable("items_acquired");
    itemsAcquiredModel->setFilter("acquisition_id < 100");
    itemsAcquiredModel->setSort(1, Qt::AscendingOrder);
    itemsAcquiredModel->select();

    guiEngine->rootContext()->setContextProperty( "itemsAcquiredModel", itemsAcquiredModel );

    guiEngine->load(QUrl("qrc:/main.qml"));

    QObject *topLevel = guiEngine->rootObjects().value(0);

    guiView = qobject_cast<QQuickWindow *>(topLevel);

    guiView->show();

}
@

5.4 Here's the QML this works against:

@
TableView {
id: items_acquired_list
objectName: "itemsAcquiredList"
anchors.top: item_aquired_editor.bottom
anchors.bottom: parent.bottom
anchors.left: parent.left
anchors.right: parent.right

                    TableViewColumn {
                        role: "acquisition_id"
                        title: "Acquisition Id"
                        width: 80
                    }
                    TableViewColumn {
                        role: "source_thrift_id"
                        title: "Source Thrift Id"
                        width: 80
                    }
                    TableViewColumn {
                        role: "source_date_id"
                        title: "Source Date Id"
                        width: 80
                    }
                    TableViewColumn {
                        role: "item_type"
                        title: "Item Type"
                        width: 60
                    }
                    TableViewColumn {
                        role: "item_cost_ea"
                        title: "Item Cost Ea"
                        width: 60
                    }
                    TableViewColumn {
                        role: "scanned_product_code"
                        title: "Product Code"
                        width: 60
                    }
                    model: itemsAcquiredModel

                }

@

fecub · wrote on 22 Feb 2015, 00:46

@wahynes after 12 hours work on this QSqlTableModel and QML problem, your solution helped me and I just want to say THANK YOU VERY MUCH!

fecub · wrote on 22 Feb 2015, 00:46

@wahynes after 12 hours work on this QSqlTableModel and QML problem, your solution helped me and I just want to say THANK YOU VERY MUCH!