QSqlRelationalTableModel, QTableView, QDataWidgetMapper and QComboBox with filters

[addebito]

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.

[addebito]

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

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.

What I have to do to achieve this?

As always, thank you for your time and support.

[addebito]

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

[mrjj]

@addebito
Thank you for sharing :)

[addebito]

@mrjj you're welcome.

[addebito]

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)");