Updating QSqlRelationalTableModels



  • Hello,

    Over the past two weeks, I've been diving into Qt's model / view framework, and in particular, the SQL model classes. Right now I'm struggling with QSqlRelationTableModel objects. Particularly, updating the models when the foreign table(s) to which they have foreign keys into are modified.

    Right now I have TableA and TableB. TableB has a column, tableAId, which is a foreign key to tableA's id column. My application has an SqlTableModel on tableA, and an SqlRelationalTableModel on TableB (with a relation established with TableA). Multiple rows from TableB can be open at once for editing - I'm using a QTabWidget, and all edit tabs share the same model...each edit tab searches for the particular row to use with a QDataWidgetMapper. TableA can also be updated, including the field from TableA that is part of the relation in the table B relational model.

    I find that after I submit TableA model changes, the mapped values from the relational model are not updated.
    I've found that I can get updated values in the relational model by calling .select() on it, but then the currentIndex on any combo boxes editing TableB items which have foriegn keys to tableA are reset to 0.

    Here's an example to put some plain english around the issue:
    Table A has a list of cities:
    @id | Name

    1 | New York
    2 | London
    3 | Oslo
    4 | Constantinople@

    Say TableB has travel plans - because you're going to take a vacation after learning the model / view classes. A travel plan record includes a city id. My models would look like this:

    @QSqlTableModel *cityModel = new QSqlTableModel(parent, myDB);
    cityModel->setTable("TableA");
    cityModel->setSubmitPolicy(QSqlTableModel::OnManualSubmit);
    cityModel->select();

    QSqlRelationalTableModel *travelModel = new QSqlRelationalTableModel(parent, myDB);
    travelModel->setTable("TableB");
    travelModel->setSubmitPolicy(QSqlTableModel::OnManualSubmit);
    travelModel->setRelation(1, QSqlRelation("TableA", "id", "name")); // Here the second column in TableB is cityId;
    travelModel->select();@

    Up to this point, everything works fine. Now later on, someone makes a change to a city. For example, they change the name of Constantinople to Istanbul.

    I would like it if all current travel plan edit tabs that were open would update their city combo boxes to now show Istanbul instead of Constantinople. The only way I have found that I can do this is to manually call
    @travelModel->select();@

    Doing so changes all combo box current index values to 0 - they all now show New York, regardless of what values they previously had. Even worse, I might not know what to set the value back to! A user may have changed the city selection in one editor, but not submitted the changes yet.

    Are the SQL models supposed to be able to handle this kind of updating? I'm wondering if it is simply an issue of hooking up some signals/slots. Any thoughts would be greatly appreciated.

    Thanks!



  • I also wanted to add that calling:
    @travelModel->relationalModel(1).select();@

    in place of or after
    @travelModel->select();@

    results in the same behavior - the current index of the combo boxes are set to 0.

    I'm using Qt version 4.8.1



  • Looks like I can't add attachments to posts. Below is an implementation of the example I gave above. I had to split it up over two posts. Maybe this is bad etiquette - but I also really wanted to provide a fully working example.

    As it currently is, a user can edit a city's name, and nothing is explicitly called to update the relational model between the travelPlans table and the cities table.

    In MainWindow::refreshRelationalModel() there are lines you can uncomment to see the combo box behavior when the model is re-selected;

    mainwindow.h
    @#ifndef MAINWINDOW_H
    #define MAINWINDOW_H

    #include <QMainWindow>
    #include <QAction>
    #include <QtSql>

    class MainWindow : public QMainWindow
    {
    Q_OBJECT

    public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();

    public slots:
    void editCity(const QModelIndex &index);
    void editPlans(const QModelIndex &index);
    void tabIndexChanged();
    void refreshRelationalModel();

    private:
    // Private helpers
    void setupUi();
    void setupDb();
    void setupModels();
    void setupConnections();

    // ui members
    QListView citiesView;
    QListView plansView;
    QTabWidget tabWidget;
    QAction *saveAction;
    
    // Sql members
    QSqlDatabase db;
    QSqlTableModel *citiesModel;
    QSqlRelationalTableModel *travelPlansModel;
    

    };

    #endif // MAINWINDOW_H
    @

    mainwindow.cpp
    @#include <QtGui>
    #include "mainwindow.h"
    #include "cityeditor.h"
    #include "planseditor.h"

    MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent)
    {
    setupDb();
    setupModels();
    setupUi();
    setupConnections();
    }

    MainWindow::~MainWindow()
    {

    }

    void MainWindow::setupUi() {
    citiesView.setModel(citiesModel);
    citiesView.setModelColumn(1);

    plansView.setModel(travelPlansModel);
    plansView.setModelColumn(1);
    
    QVBoxLayout *vlayout = new QVBoxLayout();
    vlayout->addWidget(&citiesView);
    vlayout->addWidget(&plansView);
    
    QHBoxLayout *hlayout = new QHBoxLayout();
    hlayout->addItem(vlayout);
    hlayout->addWidget(&tabWidget);
    
    setCentralWidget(new QWidget());
    centralWidget()->setLayout(hlayout);
    
    // I'll sneak menu in here too
    QMenu *menu = menuBar()->addMenu("&File");
    saveAction = menu->addAction("Save");
    saveAction->setShortcut(QKeySequence::Save);
    

    }

    void MainWindow::setupDb() {
    db = QSqlDatabase::addDatabase("QSQLITE");
    //db.setDatabaseName(":memory");
    db.setDatabaseName("travel.db");
    db.open();

    db.exec&#40;"PRAGMA foreign_keys = ON"&#41;; // off by default
    
    db.exec&#40;"drop table travelPlans;"&#41;;
    db.exec&#40;"drop table cities;"&#41;;
    
    db.exec&#40;"create table cities ("
             "id INTEGER PRIMARY KEY,"
             "name TEXT&#41;;");
    db.exec&#40;"create table travelPlans ("
             "id INTEGER PRIMARY KEY,"
             "dayArrive TEXT,"
             "dayDepart TEXT,"
             "cityId,"
             "FOREIGN KEY(cityId&#41; REFERENCES cities(id));");
    
    db.exec&#40;"insert into cities (name&#41; values ('New York');");
    db.exec&#40;"insert into cities (name&#41; values ('London');");
    db.exec&#40;"insert into cities (name&#41; values ('Oslo');");
    db.exec&#40;"insert into cities (name&#41; values ('Constantinople');");
    
    db.exec&#40;"insert into travelPlans (dayArrive, dayDepart, cityId&#41; "
             "values ('monday', 'wednesday', 1);");
    db.exec&#40;"insert into travelPlans (dayArrive, dayDepart, cityId&#41; "
             "values ('thursday', 'saturday', 4);");
    db.lastError();
    

    }

    void MainWindow::setupModels() {
    citiesModel = new QSqlTableModel(this, db);
    citiesModel->setTable("cities");
    citiesModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    citiesModel->select();

    travelPlansModel = new QSqlRelationalTableModel(this, db);
    travelPlansModel->setTable("travelPlans");
    travelPlansModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
    travelPlansModel->setRelation(3, QSqlRelation("cities", "id", "name"));
    travelPlansModel->select();
    

    }

    void MainWindow::setupConnections() {
    connect(&citiesView, SIGNAL(doubleClicked(QModelIndex)), this, SLOT(editCity(QModelIndex)));
    connect(&plansView, SIGNAL(doubleClicked(QModelIndex)), this, SLOT(editPlans(QModelIndex)));
    connect(&tabWidget, SIGNAL(currentChanged(int)), this, SLOT(tabIndexChanged()));
    }

    void MainWindow::editCity(const QModelIndex &index) {
    CityEditor *cityEdit = new CityEditor();

    cityEdit->setModel(citiesModel);
    cityEdit->setIndex(index);
    connect(cityEdit, SIGNAL(cityModelChanged()), this, SLOT(refreshRelationalModel()));
    
    tabWidget.setCurrentIndex(tabWidget.addTab(cityEdit, "FIXME"));
    

    }

    void MainWindow::editPlans(const QModelIndex &index) {
    PlansEditor *planEdit = new PlansEditor();

    planEdit->setModel(travelPlansModel);
    planEdit->setIndex(index);
    tabWidget.setCurrentIndex(tabWidget.addTab(planEdit, "FIXME"));
    

    }

    void MainWindow::tabIndexChanged(/int index/) {
    saveAction->disconnect();
    tabWidget.
    connect(saveAction, SIGNAL(triggered()), tabWidget.currentWidget(), SLOT(save()));
    qDebug() << "saveAction connected";
    }

    void MainWindow::refreshRelationalModel() {
    // As the application is now, if you rename a city,
    // plan editors don't see the change
    // Uncomment either of these lines to get the relational model to update,
    // however, you'll see that all combo boxes lose their index value
    // travelPlansModel->select();
    //travelPlansModel->relationModel(3)->select();

    }
    @



  • cityeditor.h
    @#ifndef CITYEDITOR_H
    #define CITYEDITOR_H

    #include <QtGui>

    class QSqlTableModel;

    class CityEditor : public QWidget
    {
    Q_OBJECT
    public:
    explicit CityEditor(QWidget *parent = 0);

    void setModel(QSqlTableModel *m);
    void setIndex(const QModelIndex &index);
    

    signals:
    void cityModelChanged();

    public slots:
    void save();

    private:
    QSqlTableModel *model;
    QDataWidgetMapper mapper;
    QLineEdit nameEdit;
    };

    #endif // CITYEDITOR_H
    @

    cityeditor.cpp
    @#include <QtSql>
    #include "cityeditor.h"

    CityEditor::CityEditor(QWidget *parent) :
    QWidget(parent)
    {
    QVBoxLayout *vlayout = new QVBoxLayout();

    QLabel *label = new QLabel();
    label->setText("City Name:");
    vlayout->addWidget(label);
    vlayout->addWidget(&nameEdit);
    
    setLayout(vlayout);
    

    }

    void CityEditor::setModel(QSqlTableModel *m) {
    model = m;

    mapper.setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
    mapper.setModel(model);
    mapper.addMapping(&nameEdit, 1);
    

    }

    void CityEditor::setIndex(const QModelIndex &index) {
    mapper.setCurrentIndex(index.row());
    }

    void CityEditor::save() {
    qDebug() << "In save...";
    if (model->database().transaction()) {
    qDebug() << mapper.submit();
    qDebug() << model->submitAll();
    qDebug() << model->database().commit();

        emit cityModelChanged();
    }
    

    }
    @

    planseditor.h
    @#ifndef PLANSEDITOR_H
    #define PLANSEDITOR_H

    #include <QtGui>

    class QSqlRelationalTableModel;

    class PlansEditor : public QWidget
    {
    Q_OBJECT
    public:
    explicit PlansEditor(QWidget *parent = 0);

    void setModel(QSqlRelationalTableModel *m);
    void setIndex(const QModelIndex &index);
    

    public slots:
    void save();

    private:
    QSqlRelationalTableModel *model;
    QDataWidgetMapper mapper;
    QLineEdit arriveEdit;
    QLineEdit departEdit;
    QComboBox cityCombo;
    };

    #endif // PLANSEDITOR_H
    @

    planseditor.cpp
    @#include <QtSql>
    #include "planseditor.h"

    PlansEditor::PlansEditor(QWidget *parent) :
    QWidget(parent)
    {
    QVBoxLayout *vlayout = new QVBoxLayout();

    QLabel *label = new QLabel();
    label->setText("Arrive:");
    vlayout->addWidget(label);
    vlayout->addWidget(&arriveEdit);
    
    label = new QLabel();
    label->setText("Depart:");
    vlayout->addWidget(label);
    vlayout->addWidget(&departEdit);
    
    label = new QLabel();
    label->setText("City:");
    vlayout->addWidget(label);
    vlayout->addWidget(&cityCombo);
    
    setLayout(vlayout);
    

    }

    void PlansEditor::setModel(QSqlRelationalTableModel *m) {
    model = m;

    cityCombo.setModel(model->relationModel(3));
    cityCombo.setModelColumn(1);
    
    mapper.setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
    mapper.setModel(model);
    mapper.setItemDelegate(new QSqlRelationalDelegate(&mapper));
    mapper.addMapping(&arriveEdit, 1);
    mapper.addMapping(&departEdit, 2);
    mapper.addMapping(&cityCombo, 3);
    

    }

    void PlansEditor::setIndex(const QModelIndex &index) {
    mapper.setCurrentIndex(index.row());
    }

    void PlansEditor::save() {
    qDebug() << "In save...";
    if (model->database().transaction()) {
    qDebug() << mapper.submit();
    qDebug() << model->submitAll();
    qDebug() << model->database().commit();
    }
    }
    @

    main.cpp
    @#include <QApplication>
    #include "mainwindow.h"

    int main(int argc, char *argv[])
    {
    QApplication a(argc, argv);
    MainWindow w;
    w.show();

    return a.exec&#40;&#41;;
    

    }
    @

    I'm looking for a way to update the relational model (in this example, the combo boxes on the plans editor pages), without losing their index.


Log in to reply
 

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