QSqlRelationalTableModel and complex queries



  • @VRonin said in QSqlRelationalTableModel and complex queries:

    if you need to edit the model and changes be reflected in the database you'll have to implement your own model

    I 'm thinking of implementing my own model, but I 'll need some help.
    It's the QSqlTableModel I 'ii have to subclass, right?
    EDIT: abandoned, too complicated, I 'll just use QSqlQueryModel and QSqlQuery. (about QStandardItemModel: I read some thing, but didn't understand much).



  • @Panoss said in QSqlRelationalTableModel and complex queries:

    It's the QSqlTableModel I 'ii have to subclass

    I'd start from scratch with a QAbstractItemModel

    about QStandardItemModel: I read some thing, but didn't understand much

    a good starting point is http://doc.qt.io/qt-5/modelview.html



  • Thanks VRonin, but as I mentioned, I abandon the subclassing.



  • 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
    {
        Q_OBJECT
    public:
        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;
    
    private:
        QStringList stringList;
    
    signals:
        
    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());
        else
            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);
        else
            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:
    bbb
    aaa

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


  • Lifetime Qt Champion

    Hi,

    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;
    ui.manufacturers_tbl->setModel(manufacturers_tbl_model);
    

    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)



  • 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) {
             query.seek(index.row());
             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?



  • "get rid of the const with const_cast"

    NOOOOOOOOOOOOOOOOOOO!!!!
    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?



  • 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?



  • @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();
                model->insertRow(newRow);
                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.



  • 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.