Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QSqlRelationalTableModel, QTableView, QDataWidgetMapper and QComboBox with filters



  • Hi at all,
    I have a problem with QSqlRelationalTableModel.
    For example, if I have these 3 tables on my database....

    TABLE COUNTRY

    ID NAME
    1 USA
    2 FRANCE
    3 UK

    TABLE CITY

    ID NAME COUNTRY_ID
    1 NEW YORK 1
    2 CHICAGO 1
    1 PARIS 2
    2 TOULOUSE 2
    1 LONDON 3

    TABLE PEOPLE

    ID NAME COUNTRY_ID CITY_ID
    1 MARK 1 1
    1 MARIE 2 1

    Inside table PEOPLE I'd like to replace/display the COUNTRY_ID and CITY_ID like this:

    TABLE "PEOPLE"

    ID NAME COUNTRY_ID CITY_ID
    1 MARK USA NEW YORK
    2 MARIE FRANCE PARIS

    ...and finally, in the same window, under the QTableView I'll have some widgets mapped with a QDataWidgetMapper:

    • 1 QLineEdit to edit the name of the person
    • 1 QComboBox to select the country of the person
    • 1 QComboBox to select the city of the person

    When I'll click on the city combobox I will have to display according to the country.
    if USA ... NEW YORK and CHICAGO
    if FRANCE ... PARIS and TOULOUSE

    Do you think is it possible with QSqlRelationalTableModel?
    Is the correct approach?

    I never used QSqlRelationalTableModel and I'm started from "relationaltablemodel example" but in this case, the 2 dummy tables (city, country) have only 2 coloumns (id, name).
    As mentioned before, I should filter the city combobox selection according to the country value.

    Do you have any suggestions?
    Thank you.



  • I made an example program.

    #include <QtSql>
    #include <QtWidgets>
    
    QTableView *createView(const QString &title, QSqlTableModel *model) {
      QTableView *view = new QTableView;
      view->setModel(model);
      view->setItemDelegate(new QSqlRelationalDelegate(view));
      view->setWindowTitle(title);
      return view;
    }
    
    bool createConnection() {
      QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
      db.setDatabaseName(":memory:");
      if (!db.open()) {
        QMessageBox::critical(
            nullptr, QObject::tr("Cannot open database"),
            QObject::tr("Unable to establish a database connection."),
            QMessageBox::Cancel);
        return false;
      }
      return true;
    }
    
    void createRelationalTables() {
      QSqlQuery query;
      query.exec("create table people(id int primary key, name varchar(20), "
                 "country_id int, city_id int)");
      query.exec("insert into people values(1, 'Mark', 1, 1)");
      query.exec("insert into people values(2, 'John', 1, 2)");
      query.exec("insert into people values(3, 'Marie', 2, 1)");
    
      query.exec("create table city(id int, name varchar(20), country_id int)");
      query.exec("insert into city values(1, 'New York', 1)");
      query.exec("insert into city values(2, 'Chicago', 1)");
      query.exec("insert into city values(1, 'Paris', 2)");
      query.exec("insert into city values(2, 'Toulouse', 2)");
      query.exec("insert into city values(1, 'London', 3)");
    
      query.exec("create table country(id int, name varchar(20))");
      query.exec("insert into country values(1, 'USA')");
      query.exec("insert into country values(2, 'FRANCE')");
      query.exec("insert into country values(3, 'UK')");
    }
    
    int main(int argc, char *argv[]) {
      QApplication app(argc, argv);
    
      if (!createConnection())
        return EXIT_FAILURE;
    
      createRelationalTables();
    
      QSqlRelationalTableModel model;
      model.setTable("people");
      // model.setFilter("(country_name_3 = 'USA') and (city_name_2 = 'New York')");
      model.setFilter("(country_name_3 = 'USA')");
      model.setEditStrategy(QSqlTableModel::OnManualSubmit);
      model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
      model.setRelation(2, QSqlRelation("country", "id", "name"));
      model.setRelation(3, QSqlRelation("city", "id", "name"));
      model.select();
    
      QTableView *t = createView(QObject::tr("Relational Table Model"), &model);
      QWidget *w = new QWidget();
      QVBoxLayout *l = new QVBoxLayout();
      QComboBox *cmbCountry = new QComboBox();
      QComboBox *cmbCity = new QComboBox();
    
      // setup the combo country
      QSqlTableModel *relModelCountry = model.relationModel(2);
      int fieldIndex = relModelCountry->fieldIndex("name");
      cmbCountry->setModel(relModelCountry);
      cmbCountry->setModelColumn(fieldIndex);
    
      // setup the combo city
      QSqlTableModel *relModelCity = model.relationModel(3);
      fieldIndex = relModelCity->fieldIndex("name");
      cmbCity->setModel(relModelCity);
      cmbCity->setModelColumn(fieldIndex);
    
      QDataWidgetMapper *m_mapper = new QDataWidgetMapper();
      m_mapper->setModel(&model);
      m_mapper->addMapping(cmbCountry, 2);
      m_mapper->addMapping(cmbCity, 3);
      m_mapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
      m_mapper->toFirst();
      QObject::connect(t->selectionModel(), &QItemSelectionModel::currentRowChanged,
                       m_mapper, &QDataWidgetMapper::setCurrentModelIndex);
    
      l->addWidget(t);
      l->addWidget(cmbCountry);
      l->addWidget(cmbCity);
      w->setLayout(l);
      w->setGeometry(0, 0, 600, 400);
      w->show();
    
      return app.exec();
    }
    

    And when you run the application...

    0168ffd0-3549-48f7-bf0c-da8f9bc1f277-immagine.png

    Why I see 5 rows ? One for every city...
    In table peolpe I've inserted only 3 datas (Mark, John and Marie)

    Finally, is it possible to filter the seleciont of the City Combobox based on the country id ??
    Eg: if the country is "USA" I'd like to show only New York and Chicago and nothing else, but now I see all cities.

    bce74d01-a95b-4aa2-a5be-7482c5a8f6e4-immagine.png
    What I have to do to achieve this?

    As always, thank you for your time and support.



  • This is my solution, I hope this help someone.

    mainwindow.cpp

    #include "mainwindow.h"
    
    #include "ui_mainwindow.h"
    
    #include <QDebug>
    #include <QMessageBox>
    #include <QSqlError>
    #include <QSqlField>
    #include <QSqlRecord>
    
    MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow)
    {
        ui->setupUi(this);
    
        createConnection();
        createRelationalTables();
    
        m_model = new QSqlRelationalTableModel();
        m_model->setTable("people");
        m_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        m_model->setJoinMode(QSqlRelationalTableModel::LeftJoin);
        m_model->setRelation(2, QSqlRelation("country", "id", "name"));
        m_model->setRelation(3, QSqlRelation("city", "id", "name"));
        m_model->select();
    
        // setup the table view
        ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
        ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
        ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
        ui->tableView->setModel(m_model);
        // ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));
    
        int fieldName;
    
        // setup the combo city
        m_relModelCity = m_model->relationModel(3);
        m_relModelCity->setEditStrategy(QSqlTableModel::OnManualSubmit);
        fieldName = m_relModelCity->fieldIndex("name");
        qDebug() << "city: fieldName" << fieldName;
        ui->cmbCity->setModel(m_relModelCity);
        ui->cmbCity->setModelColumn(fieldName);
    
        // setup the combo country
        m_relModelCountry = m_model->relationModel(2);
        m_relModelCountry->setEditStrategy(QSqlTableModel::OnManualSubmit);
        fieldName = m_relModelCountry->fieldIndex("name");
        qDebug() << "country: fieldName" << fieldName;
        ui->cmbCountry->setModel(m_relModelCountry);
        ui->cmbCountry->setModelColumn(fieldName);
    
        m_mapper = new QDataWidgetMapper();
        m_mapper->setModel(m_model);
        m_mapper->addMapping(ui->edtName, 1);
        m_mapper->addMapping(ui->cmbCountry, 2);
        m_mapper->addMapping(ui->cmbCity, 3);
        m_mapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);
        m_mapper->toFirst();
    
        connect(ui->tableView->selectionModel(), &QItemSelectionModel::currentRowChanged, this, &MainWindow::slotCurrentRowChanged);
        connect(ui->cmbCountry, QOverload<int>::of(&QComboBox::currentIndexChanged), this, &MainWindow::slotCurrentCountryIndexChanged);
        connect(ui->cmbCity, QOverload<int>::of(&QComboBox::currentIndexChanged), this, &MainWindow::slotCurrentCityIndexChanged);
    
        setWidgetsReadOnly();
    }
    
    MainWindow::~MainWindow()
    {
        delete ui;
    }
    
    void MainWindow::slotCurrentRowChanged(const QModelIndex &current, const QModelIndex &previous)
    {
        Q_UNUSED(previous)
    
        m_mapper->setCurrentIndex(current.row());
    }
    
    bool MainWindow::createConnection()
    {
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(":memory:");
        if (!db.open())
        {
            QMessageBox::critical(nullptr, QObject::tr("Cannot open database"), QObject::tr("Unable to establish a database connection."), QMessageBox::Cancel);
            return false;
        }
        return true;
    }
    
    void MainWindow::createRelationalTables()
    {
        QSqlQuery query;
        query.exec("create table people("
                   "id int primary key, "
                   "name varchar(20), "
                   "country_id int, "
                   "city_id int)");
        query.exec("insert into people values(1, 'Mark', 0, 1)");
        query.exec("insert into people values(2, 'John', 0, 0)");
        query.exec("insert into people values(3, 'Marie', 1, 2)");
        query.exec("insert into people values(4, 'Annie', 1, 3)");
    
        query.exec("create table city("
                   "id int primary key, "
                   "country_id int, "
                   "name varchar(20))");
        query.exec("insert into city values(0, 0, 'New York')");
        query.exec("insert into city values(1, 0, 'Chicago')");
        query.exec("insert into city values(2, 1, 'Paris')");
        query.exec("insert into city values(3, 1, 'Toulouse')");
        query.exec("insert into city values(4, 2, 'London')");
        query.exec("insert into city values(5, 2, 'Liverpool')");
    
        query.exec("create table country("
                   "id int primary key, "
                   "name varchar(20))");
        query.exec("insert into country values(0, 'USA')");
        query.exec("insert into country values(1, 'FRANCE')");
        query.exec("insert into country values(2, 'UK')");
    }
    
    void MainWindow::slotCurrentCountryIndexChanged(int index)
    {
        // Filter the City ComboBox Values
        m_relModelCity->setFilter("country_id = " + QString::number(index));
        m_relModelCity->select();
    
        if ((index > -1) && (m_editing))
        {
            int row            = m_mapper->currentIndex();
            int fieldName      = m_relModelCountry->fieldIndex("name");
            QString newCountry = m_relModelCountry->index(index, fieldName).data().toString();
    
            // Get the new Country ID
            QSqlQuery queryCountry;
            queryCountry.prepare("SELECT * FROM country WHERE name = '" + newCountry + "';");
            bool ok = queryCountry.exec();
            if (!ok)
                qDebug() << queryCountry.lastError().text();
            queryCountry.first();
            int newCountryId = queryCountry.record().field("id").value().toInt();
            qDebug() << "new country id:" << newCountryId << "name:" << newCountry;
    
            // Get the new City ID
            QSqlQuery queryCity;
            queryCity.prepare("SELECT * FROM city WHERE country_id = :country_id;");
            queryCity.bindValue(":country_id", newCountryId);
            ok = queryCity.exec();
            if (!ok)
                qDebug() << queryCity.lastError().text();
            queryCity.first();
            int newCityId = queryCity.record().field("id").value().toInt();
            qDebug() << "new city id:" << newCityId << "name:" << queryCity.record().field("name").value().toString();
    
            // Replace the Country and City values
            QSqlRecord record = m_model->record(row);
            qDebug() << "record" << record;
            record.setValue("country_name_3", newCountryId);
            record.setValue("city_name_2", newCityId);
            record.setGenerated("country_name_3", true);
            record.setGenerated("city_name_2", true);
            qDebug() << "record" << record;
            ok = m_model->setRecord(row, record);
            if (!ok)
                qDebug() << "Country: setRecord error" << m_model->lastError().text();
        }
    }
    
    void MainWindow::slotCurrentCityIndexChanged(int index)
    {
        if ((index > -1) && (m_editing))
        {
            int row         = m_mapper->currentIndex();
            int fieldName   = m_relModelCity->fieldIndex("name");
            QString newCity = m_relModelCity->index(index, fieldName).data().toString();
    
            // Get the new City ID
            QSqlQuery query;
            query.prepare("SELECT * FROM city WHERE name = '" + newCity + "';");
            bool ok = query.exec();
            if (!ok)
                qDebug() << query.lastError().text();
            query.first();
            int newCityId = query.record().field("id").value().toInt();
            qDebug() << "new city id:" << newCityId << "name:" << newCity;
    
            // Replace the City value
            QSqlRecord record = m_model->record(row);
            record.setValue("city_name_2", newCityId);
            record.setGenerated("country_name_3", true);
            record.setGenerated("city_name_2", true);
            qDebug() << "record" << record;
            ok = m_model->setRecord(row, record);
            if (!ok)
                qDebug() << "City: setRecord error" << m_model->lastError().text();
        }
    }
    
    void MainWindow::on_btnOk_clicked()
    {
        bool result = m_model->database().transaction();
        qDebug() << "transaction:" << result;
    
        result = m_model->submitAll();
        qDebug() << "model->submitAll: " << result;
        if (result)
        {
            result = m_model->database().commit();
            qDebug() << "commit:" << result;
        }
        else
        {
            result = m_model->database().rollback();
            qDebug() << "rollback:" << result;
        }
    
        setWidgetsReadOnly();
        m_model->select();
    }
    
    void MainWindow::on_btnCancel_clicked()
    {
        bool result = m_model->database().transaction();
        qDebug() << "transaction:" << result;
    
        m_model->revertAll();
    
        result = m_model->database().rollback();
        qDebug() << "rollback:" << result;
    
        setWidgetsReadOnly();
        m_model->select();
    }
    
    void MainWindow::on_btnEditValues_clicked()
    {
        qDebug() << "*** Edit mode";
        m_editing = true;
    
        ui->edtName->setEnabled(true);
        ui->cmbCountry->setEnabled(true);
        ui->cmbCity->setEnabled(true);
    }
    
    void MainWindow::setWidgetsReadOnly()
    {
        qDebug() << "*** Read Only mode";
        m_editing = false;
    
        ui->edtName->setEnabled(false);
        ui->cmbCountry->setEnabled(false);
        ui->cmbCity->setEnabled(false);
    }
    

    mainwindow.h

    #pragma once
    
    #include <QDataWidgetMapper>
    #include <QMainWindow>
    #include <QSqlQuery>
    #include <QSqlRelationalDelegate>
    #include <QSqlRelationalTableModel>
    
    QT_BEGIN_NAMESPACE
    namespace Ui
    {
    class MainWindow;
    }
    QT_END_NAMESPACE
    
    class MainWindow : public QMainWindow
    {
        Q_OBJECT
    
      public:
        MainWindow(QWidget *parent = nullptr);
        ~MainWindow();
    
      private slots:
        void slotCurrentCountryIndexChanged(int index);
        void slotCurrentCityIndexChanged(int index);
        void slotCurrentRowChanged(const QModelIndex &current, const QModelIndex &previous);
    
        void on_btnOk_clicked();
        void on_btnCancel_clicked();
        void on_btnEditValues_clicked();
        void setWidgetsReadOnly();
    
      private:
        Ui::MainWindow *ui;
        bool m_editing = false;
        QSqlRelationalTableModel *m_model;
        QSqlTableModel *m_relModelCountry;
        QSqlTableModel *m_relModelCity;
        QDataWidgetMapper *m_mapper;
        bool createConnection();
        void createRelationalTables();
    };
    

    mainwindow.ui

    <?xml version="1.0" encoding="UTF-8"?>
    <ui version="4.0">
     <class>MainWindow</class>
     <widget class="QMainWindow" name="MainWindow">
      <property name="geometry">
       <rect>
        <x>0</x>
        <y>0</y>
        <width>658</width>
        <height>505</height>
       </rect>
      </property>
      <property name="windowTitle">
       <string>MainWindow</string>
      </property>
      <widget class="QWidget" name="centralwidget">
       <layout class="QVBoxLayout" name="verticalLayout">
        <item>
         <widget class="QTableView" name="tableView"/>
        </item>
        <item>
         <widget class="QPushButton" name="btnEditValues">
          <property name="text">
           <string>Edit values</string>
          </property>
         </widget>
        </item>
        <item>
         <widget class="QLineEdit" name="edtName"/>
        </item>
        <item>
         <widget class="QComboBox" name="cmbCountry">
          <property name="minimumSize">
           <size>
            <width>200</width>
            <height>0</height>
           </size>
          </property>
         </widget>
        </item>
        <item>
         <widget class="QComboBox" name="cmbCity">
          <property name="minimumSize">
           <size>
            <width>200</width>
            <height>0</height>
           </size>
          </property>
         </widget>
        </item>
        <item>
         <layout class="QHBoxLayout" name="horizontalLayout">
          <item>
           <widget class="QPushButton" name="btnOk">
            <property name="text">
             <string>Commit</string>
            </property>
           </widget>
          </item>
          <item>
           <widget class="QPushButton" name="btnCancel">
            <property name="text">
             <string>Rollback</string>
            </property>
           </widget>
          </item>
         </layout>
        </item>
       </layout>
      </widget>
      <widget class="QMenuBar" name="menubar">
       <property name="geometry">
        <rect>
         <x>0</x>
         <y>0</y>
         <width>658</width>
         <height>21</height>
        </rect>
       </property>
      </widget>
      <widget class="QStatusBar" name="statusbar"/>
     </widget>
     <resources/>
     <connections/>
    </ui>
    

    main.cpp

    #include "mainwindow.h"
    
    #include <QApplication>
    
    int main(int argc, char *argv[])
    {
        QApplication a(argc, argv);
        MainWindow w;
        w.show();
        return a.exec();
    }
    

    Demo_RelationalTableModel.pro

    QT       += core gui sql
    
    greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
    
    CONFIG += c++11
    
    # The following define makes your compiler emit warnings if you use
    # any Qt feature that has been marked deprecated (the exact warnings
    # depend on your compiler). Please consult the documentation of the
    # deprecated API in order to know how to port your code away from it.
    DEFINES += QT_DEPRECATED_WARNINGS
    
    # You can also make your code fail to compile if it uses deprecated APIs.
    # In order to do so, uncomment the following line.
    # You can also select to disable deprecated APIs only up to a certain version of Qt.
    #DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0
    
    SOURCES += \
        main.cpp \
        mainwindow.cpp
    
    HEADERS += \
        mainwindow.h
    
    FORMS += \
        mainwindow.ui
    
    # Default rules for deployment.
    qnx: target.path = /tmp/$${TARGET}/bin
    else: unix:!android: target.path = /opt/$${TARGET}/bin
    !isEmpty(target.path): INSTALLS += target
    

  • Lifetime Qt Champion

    @addebito
    Thank you for sharing :)



  • @mrjj you're welcome.



  • Other small improvement....

    If you have to filter the people table, example for a new column called "mygroup" you must write the full name (table name + "." + field name).
    If you have to filter on column 2 or 3 pay attention at the relationed column because the name is an alias !!

    m_model = new QSqlRelationalTableModel();
    m_model->setTable("people");
    m_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    m_model->setJoinMode(QSqlRelationalTableModel::LeftJoin);
    m_model->setRelation(2, QSqlRelation("country", "id", "name"));
    m_model->setRelation(3, QSqlRelation("city", "id", "name"));
    m_model->setFilter("people.mygroup = 0");  // <--- this is the new line !!!!
    m_model->select();
    

    obviously the table should be... (mygroup column added)

        query.exec("create table people("
                   "id int primary key, "
                   "name varchar(20), "
                   "country_id int, "
                   "city_id int, "
                   "mygroup int)"); // <--- new column !!!
        query.exec("insert into people values(1, 'Mark', 0, 1, 0)");
        query.exec("insert into people values(2, 'John', 0, 0, 0)");
        query.exec("insert into people values(3, 'Marie', 1, 2, 0)");
        query.exec("insert into people values(4, 'Annie', 1, 3, 1)");
    

Log in to reply