QSqlRelationalTableModel Example to Qml (TableView) with relation

freaksdd · wrote on 2 Mar 2022, 07:49

Hi,
i would like to port the example Code from Qt Widget to Qt Quick (Qml):
Relational Table Model Example

Question:
how tho show a QSqlRelationTableModel with relations of other tables in Qml-Views

I success a TableView with simple tableModel to view all Data.

When i use the code from the Example and write set the model to (Qml) TableView, only the non-relation-Columns will be written with there content normaly.
My idea will be to use a ComboBox (like in the Widget-Example) to handle the input of Relation-Columns. But there is also the problem to mark the current saved one.

main.cpp - Code-Snipped from Example with set the model to qml rootContext:

#include <some includes>

void initializeModelRelation(QSqlRelationalTableModel *model)
{
//! [0]
    model->setTable("employee");
//! [0]

    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
//! [1]
    model->setRelation(2, QSqlRelation("city", "id", "name"));
//! [1] //! [2]
    model->setRelation(3, QSqlRelation("country", "id", "name"));
//! [2]

//! [3]
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("City"));
    model->setHeaderData(3, Qt::Horizontal, QObject::tr("Country"));
//! [3]

    model->select();
}

void createRelationalTables()
{
    QSqlQuery query;
    query.exec("create table employee(id int primary key, name varchar(20), city int, country int)");
    query.exec("insert into employee values(1, 'Espen', 5000, 47)");
    query.exec("insert into employee values(2, 'Harald', 80000, 49)");
    query.exec("insert into employee values(3, 'Sam', 100, 1)");

    query.exec("create table city(id int, name varchar(20))");
    query.exec("insert into city values(100, 'San Jose')");
    query.exec("insert into city values(5000, 'Oslo')");
    query.exec("insert into city values(80000, 'Munich')");

    query.exec("create table country(id int, name varchar(20))");
    query.exec("insert into country values(1, 'USA')");
    query.exec("insert into country values(47, 'Norway')");
    query.exec("insert into country values(49, 'Germany')");
}

int main(int argc, char *argv[])
{
QGuiApplication app(argc, argv);
...
if (!createConnection())
      return EXIT_FAILURE;
createRelationalTables();

QSqlRelationalTableModel *relationModel = new QSqlRelationalTableModel (qApp);
initializeModelRelation(relationModel);

QQmlApplicationEngine engine;
engine.rootContext()->setContextProperty(QString("relaModel"), relationModel);
...
}

main.qml - simple TableView

import QtQuick 2.15
import QtQuick.Window 2.15
import QtQuick.Controls 1.4
import Qt.labs.qmlmodels 1.0

Window {
  id: window
  width: 640
  height: 480
  visible: true
  title: qsTr("Hello World")

    TableView {
      id: rtv
      height: 250
      width: 400
      visible: true

      model: relaModel

      TableViewColumn {
        title: "id"
        width: 50
      }
      TableViewColumn {
        role: "name"
        title: "Name"
        width: 100
      }
      TableViewColumn {
        role: "city"
        title: "City"
        width: 100

        }
      }
      TableViewColumn {
        role: "country"
        title: "Country"
        width: 100
      }
      Component.onCompleted: {
        model.select()
      }
  }
}

the result of this code looks so:

I wood like to use a ComboBox or something else. Importend is to get all the data from the other table with the current value from the first table like from the original example:

Has anybody a solution or a notice/reference to find a solution for this problem?
Thank you very much for any help.

freaksdd · 3 Nov 2022, 10:06

I found a solution by my self.
My solution ... :

show in the relation field only a ComboBox
(in future i will have a Lable to change the label by click to ComoBox)
can read and write the value of the current filed

So...at first i find out, that i used in the example above the Quick.Controls 1 .. so the old one.
Now i update my Code to Quick.Controls 2.

The only misstake i found is, i haven't read the api to the end.
Why was the resulting Column empty?

When you use realtions and one of the columnName of both tables are equal (in this example like 'name'), the filedName will change in the form of (e.g. tablename_columnname_id)
QSqlRelationTableModel - Found under 'Detail Description' under 'Notes'

When you debug the currentFieldNames u find the right name. In the example above the value use:

city_name_3

When you use a read-only Component like Label, it looks like:

Label {
    text: city_name_3
  }

When you want to use a read-write value in a comboBox, i found only the solution to write my one invokable funtion to get the relation-data: (this code is only for Test to get and write the Data .. it is not optimiesd for now)

QSqlRealtionTableModel:

[HEADER]
...
Q_INVOKABLE QVariant getRelation(const QModelIndex &item, int role = Qt::EditRole);
...
[CPP]
QVariant LQSqlRelationalTableModel::getRelation(const QModelIndex &item, int role)
{
  if (item.row() >= rowCount())
      return QString();
  QSqlRelation rela = relation(role);
 // is any realtion on this role?
  if(!rela.isValid())
    return QString();
  // get the table of the realtion
  QSqlTableModel *rModel = relationModel(role);
  QVariantList list;
  for(int i = 0; i < rModel->columnCount(); i++)
  {
    // look for the write headerName we need
    if(rModel->headerData(i, Qt::Orientation::Horizontal) == rela.displayColumn())
    {
      for(int j = 0; j < rModel->rowCount(); j++)
      {
        // use the data-method for QSqlTableModel to get all values from the RealtionCoumn and save it in a List
        QModelIndex modelIndex = rModel->index(j, i);
        QVariant value = rModel->data(modelIndex);
        list.append(value);
      }
      break;
    }
  }
  return list;
}

When you use now this data for a model, you got a list of all the poibile relation-data:

[QML]
TableView{
model: relaModel
delegate: DelegateChooser {   // Qt.Quick.Controls 2
     DelegateChoice {
         column: 0
         delegate: Label {    // it is also possible to use as spinbox, but by id you do not need realy
            text: id
         }
     }
     DelegateChoice {
         column: 1
         delegate: Label {
            text: name
         }
     }
     DelegateChoice {   
              column: 2
              delegate: ComboBox {           
                  implicitWidth: 140
                  onActivated: {
                    relaModel.setData(relaModel.index(row, column), currentText)
                  }
                  Component.onCompleted: {
                    model = relaModel.getRelation(relaModel.index(row, column))
                    currentIndex = relaModel.hasRelation(relaModel.index(row, column), city_name_3)
                  }
              }
      }
      DelegateChoice {
                column: 3
                delegate: TextField {
                  text: country_name_2
                  selectByMouse: true
                  implicitWidth: 140      
                  onAccepted: { 
                    // i don't know why the get a different row like '-1' - so i have to check it     
                    if (row >= 0) {
                      var sendText = text      
                      relaModel.setData(relaModel.index(row, column), text)      
                    }      
                  }      
                }      
        }
   }
}

so know it comes the importend thing:

in the comboBox the come the hole Data from the relationTable, so there text is the expected one.
in the TextField of Column 3 (Country) can you write the text by hand. But the Text have to be exactly one of the realtionData's-ID. So you do not have the ID -> you have the value, the Data will not write in the Database. -> you will handle this in ::setData(...)

Why does the cpp look so difficult and to much for some little thing?

currently i found this solution cause you got only the realtion-data but you have to set the right id 5000 .
Example: for the first Row you got for Espen the city-id: 5000 -> this is the realtion to city-name Oslo
The Problem is, that you get in the ::data-function the realtion-Data ('Oslo') and not the id ('5000')
When you wnat to set the data, you have to compare, if the data in the City-Table in Column Name. So you only handle the data with the realtion and not with the id.
-> that make it immpossible, that you use unique-values in the column name of the table city
Why?
- When you have two values with same name, the data will fetch only the first one. so it is possible that you got the wrong data

So now my test looks like:

+++++++++++++++++++++++++
The next questions is, how to get the id (5000) instead of the realtion-Data (Oslo ) to handle columns with no unique-values. Any Idea?
+++++++++++++++++++++++++

I know i use in this example a SpinBox for Id, but it is only a test to handle some Components with this QSqlRealtionTableModel.
I'm realy sorry for my english, but i hope you understand all what i want to tell you.

Greetings from Germany