QSqlRelationalTableModel Example to Qml (TableView) with relation
-
Hi,
i would like to port the example Code from Qt Widget to Qt Quick (Qml):
Relational Table Model ExampleQuestion:
how tho show a QSqlRelationTableModel with relations of other tables in Qml-ViewsI 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. -
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_3When 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 forEspenthe city-id:5000-> this is the realtion to city-nameOslo
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 columnnameof the tablecity
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
- show in the relation field only a ComboBox
