QSqlRelationalTableModel and complex queries

  • I changed my mind again because I found the code of QSqlTableModel.
    So I 'll go subclassing QSqlTableModel.
    I 'll just add a new function that will set a query.
    What do you think?

  • This post is deleted!

  • How about QAbstractListModel instead of QAbstractItemModel?
    Because I have a list (a QTableView), I suppose this is the one I should use.
    My header:

    #ifndef PGSSQLMODEL_H
    #define PGSSQLMODEL_H
    #include <QAbstractListModel>
    #include <QStringList>
    class PGsSqlModel : public QAbstractListModel
        explicit PGsSqlModel(QObject *parent = 0);
        PGsSqlModel(const QStringList &strings, QObject *parent = 0)
                 : QAbstractListModel(parent), stringList(strings) {}
         int rowCount(const QModelIndex &parent = QModelIndex()) const;
         QVariant data(const QModelIndex &index, int role) const;
         QVariant headerData(int section, Qt::Orientation orientation,
                             int role = Qt::DisplayRole) const;
        QStringList stringList;
    public slots:
    #endif // PGSSQLMODEL_H

    My Source:

    #include "pgssqlmodel.h"
    PGsSqlModel::PGsSqlModel(QObject *parent) : QAbstractListModel(parent)
        stringList << "bbb" << "aaa";
    int PGsSqlModel::rowCount(const QModelIndex &parent) const
        return stringList.count();
    QVariant PGsSqlModel::data(const QModelIndex &index, int role) const
        if (!index.isValid())
            return QVariant();
        if (index.row() >= stringList.size())
            return QVariant();
        if (role == Qt::DisplayRole)
            return stringList.at(index.row());
            return QVariant();
    QVariant PGsSqlModel::headerData(int section, Qt::Orientation orientation, int role) const
        if (role != Qt::DisplayRole)
            return QVariant();
        if (orientation == Qt::Horizontal)
            return QString("Column %1").arg(section);
            return QString("Row %1").arg(section);

    I use set model for a QTableView, but I get a table with two empty rows.
    I should get two rows containing:

    What ust I do?
    And how can I access the database?

  • Lifetime Qt Champion


    How are you setting that model ?

  • Hi SGaist.
    It's ok now, I'm getting the expected result. I was just hiding a column (forgotten code) without noticing it (ok, very silly of me :)).

    I use this to set the model(if this is what you mean):

    PGsSqlModel *manufacturers_tbl_model = new PGsSqlModel;

    How can I access the database from within the class?

  • Lifetime Qt Champion

    Through QSqlQuery

  • I ended up using QAbstractTableModel, I think this is the one I need.
    Now, let 's say I make a function getDataFromDB, and result set is stored in a QStringList (?? I don't know, I 'm asking, maybe you can suggest me what to use).

    Then in ::data function, how shall I use this variable to return the data to the "user"? (I mean the QTableView that will use the data to display them)

  • Qt Champions 2018

    I'd suggest a QHash<quint64,QVariant> where the key has the most significant 32 bits as the row index and the least significant 32 as the column

  • I 've made this function: (I call it of course, like this: model->setQuery("SELECT id, name FROM parts"))

     void TableModel::setQuery(const QSqlQuery &qry) {
         query = qry;

    I suppose this is not enough for the model to get the data from db and then be available (I mean the data from db) to the model? (a QAbstractTableModel)
    Must I do something more?

    And this is the data function:

     QVariant TableModel::data(const QModelIndex &index, int role) const
         if (!index.isValid())
             return QVariant();
         if (index.row() >= query.size())
             return QVariant();
         if (role == Qt::DisplayRole) {
             return query.value(index.column()); 
         } else {
             return QVariant();

    Where I get an error:
    ...\tablemodel.cpp:74: error: C2662: 'QSqlQuery::seek' : cannot convert 'this' pointer from 'const QSqlQuery' to 'QSqlQuery &'
    Conversion loses qualifiers

    It's at the line 'query.seek(index.row());'.

  • Lifetime Qt Champion

    How did you declared your query member variable ?

  • private:

     QSqlQuery query;

    I read from this:
    "get rid of the const with const_cast"

    What does this mean?

  • Qt Champions 2018

    "get rid of the const with const_cast"

    mutable QSqlQuery query; solves your compilation issue but I don't think your logic is going anywhere.

    Just a second here. let's take a step back: what do you want to achieve?

    • does the model need to be editable and changes be reflected in the database?
    • will you have more than 1 column?

  • I want my QTableView to display data from two tables.
    This in SQL means my model should have the ability to:

    1. make LEFT JOINS on the second table
    2. Concatenate two fields.

    For these things, QSqlQueryModel works fine.

    But I also need to add and remove records, which QSqlQueryModel cannot do.

    So, my conclusion is I should subclass QSqlQueryModel (this is what I'm doing right now, but I have problems...) and add custom functionality for adding and removing rows.

    What do you think?

  • Qt Champions 2018

    Personally I would not subclass anything, I'd use a QStandardItemModel fill it with a QSqlQuery and react to QStandardItemModel::rowsInserted and QStandardItemModel::rowsRemoved signals to trigger the update in the db via another QSqlQuery

  • The problem is I don't have the slightest idea how to do all this.
    Is there some example I could look at?

    EDIT: QStandardItemModel has no setQuery function.
    How shall I "fill it with a QSqlQuery"?
    You mean wit QStandardItemModel::setItem?

  • Qt Champions 2018

    @Panoss said in QSqlRelationalTableModel and complex queries:

    How shall I "fill it with a QSqlQuery"

    manually, outside the model

        model->removeRows(0, model->rowCount());
        model->removeColumns(0, model->columnCount());
        QSqlQuery testQuery;
        testQuery.prepare("select * from MyTable");
        if (testQuery.exec()) {
            for (bool firstRun = true; testQuery.next();) {
                const QSqlRecord currRecord = testQuery.record();
                if (firstRun) {
                    firstRun = false;
                    model->insertColumns(0, currRecord.count());
                    for (int i = 0; i < currRecord.count(); ++i)
                        model->setHeaderData(i, Qt::Horizontal, currRecord.fieldName(i));
                const int newRow = model->rowCount();
                for (int i = 0; i < currRecord.count(); ++i)
                    model->setData(model->index(newRow, i), currRecord.value(i));

  • Works, the model->removeRow works, it removes a row from the model ,but how do I remove this row from the db too? Something like this?

    QSqlQuery query;
        query.prepare("DELETE FROM MyTable WHERE id=1");
        if (query.exec()) {
    etc. etc.

  • Lifetime Qt Champion

    You'll likely have to make a more precise delete query but otherwise, yes.

  • What do you mean by "more precise"? I thought "WHERE id=1" makes it absolutely precise (the value "1" was chosen for the shake of the example).

  • Lifetime Qt Champion

    Sorry, that was badly written, I meant that you would have to ensure that you are passing the correct parameter(s) to the delete query to ensure you are deleting the same row in the database.

  • Ah, ok, guys, VRonin, SGaist, thank you very much.

  • Qt Champions 2018

    be careful here as if you naively connect the database delete to rowsRemoved the first two lines:
    (model->removeRows(0, model->rowCount()); model->removeColumns(0, model->columnCount());)
    will delete your entire database and you cannot just use a QSignalBlocker on the model either or the view won't update

Log in to reply

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.