Adding foreign column to a "virtual table" and performing several joins from one table over several

G4bandit

Hi,

does someone knows how to transfer SQL this all SQL commands to c++ using QtSql libs. QtRelationalTableModel and so on.

SELECT a.[Name]
,a.[Surname]
,a.[Name_St]
,s1.Prog
,s2.Kind
FROM [dbo].[AdaList_Main] a
JOIN LinkAdaList_AdaSet s1
ON a.Surname = s1.Surname
JOIN AdaSet s2
ON s1.Prog = s2.Prog
WHERE a.Surname = 'Dummy';

Inside of Microsoft SQL Server managment Studio it works. In future it is tought that the Attribute Surname (now 'Dummy') is feeded by the Content of "LineEdit".

For me the most tricky is to

perform several joins at once
add columns from foreign tables

ATTACHMENT 2017.12.06

My Code look so far like this

RTblModel = new QSqlRelationalTableModel(this);
RTblModel->setTable("AdaList_Main");

RTblModel->setRelation(1,QSqlRelation("AdaSet","Surname","Prog"));     
RTblModel->select();
ui->TV_DBOut->setModel(RTblModel);

It Works so far it is this part
SELECT a.[Name]
,s1.Prog
,a.[Name_St]
FROM [dbo].[AdaList_Main] a
JOIN LinkAdaList_AdaSet s1
ON a.Surname = s1.Surname

Whereas in QT "Surname" is replaced with "Prog"
Now when I want to do the remaining part with

//place directly after the first setRelation
RTblModel->setRelation(1,QSqlRelation("AdaSet","Prog","Kind");

Only what happens is that the urgent column "Surname" is replaced with the column "Kind" (of the 3.thrd table) but no data is showed.
I can not explain to my self why.

SGaist

Hi,

What about QSqlQueryModel ? You can set your current query on it.

JonB

@G4bandit

QSqlQueryModel is the most flexible, and can handle the SELECT you show.

QSqlTableModel is really intended to map one-to-one to a SQL TABLE. You could write your SELECT as a SQL VIEW , I don't know whether QSqlTableModel would be prepared to connect to that instead of a table.

You can pass the value of a line edit into QSqlQueryModel by changing the query, or into QSqlTableModel via the filter.

G4bandit

@SGaist And @JNBarchan
Thks so far for your proposals

I found on this homepage from QT
SQLProgramming QtDoc
the following statement:

And for me it was clear I have a lot of table with foreign key and I also do want to be able to edit data so I decided for QSqlRelationalTableModel(Maybe I am wrong and do not understand the overview table complet :) )

But in fact I stucked on the two explained points.

perform several joins at once
add columns from foreign tables

@JNBarchan
With using QSqlRelationalTableModel and a VIEW generated in SQL (using Microsoft SQL Server Management Studio) I came further, MUCH further it works like I want it to.

So now I can decide either to work continuously with VIEWs from SSMS or try to understand more QSql Classes to be able to make the same stuff like with the VIEWs from SSMS.
Motivation for more understanding is that I will intensivly work with QSql anyway for at least the next 2 months.

JonB

@G4bandit

QSqlQueryModel Read-only: Well, what do you expect from your SELECT query? You certainly don't have a single, actual table you can update from it (you have a JOIN on multiple tables). However, you can use QSqlQuery (not Model) to execute INSERT/UPDATE/DELETE statements which you generate ourself against, say, AdaList_Main table.
QSqlRelationalTableModel with foreign key support: Lets you fetch the value from another table's column corresponding to a value in a column in your table.

RTblModel->setRelation(1,QSqlRelation("AdaSet","Prog","Kind");
Only what happens is that the urgent column "Surname" is replaced with the column "Kind" (of the 3.thrd table) but no data is showed.

What "3.thrd table"?? I see 2 tables in your query? Plus I see no reference to anything named Kind in the SELECT?

Meanwhile, it is interesting to hear that you say QSqlRelationalTableModel does work with a SQL VIEW as source "table". Of course, that will be for SELECT queries only, you won't be able to update anything through it (well, unless you want to look into MS SQL "Updatable VIEWs", but that's a different story).

G4bandit

@JNBarchan

DATE:17.12.08
Regarding Table_Views from SSMS
Interesting concerns from your side. In fact when I open the view inside of SSMS the table generated is grew highlited (like RO) and I am not able to edit data. So actually this should work the same.

But in fact not :) . When I open up and show in the qt tableview I am able to edit and update the fields. The only restriction is I have to push enter. When I just edit and go to other field then the edit will not be saved.

JonB

@G4bandit

I wrote:

What "3.thrd table"?? I see 2 tables in your query? Plus I see no reference to anything named Kind in the SELECT?

No response from you.

The Qt table view will be editing the table associated with the QSqlRelationalTableModel, not something to do with SELECT statement, which you no longer have. This is quite different from any QSqlQueryModel you might have. Since you have never said what it is you might wish to edit, it's impossible to know whether this is what you want or not.

If you need further help on this question, I suggest you clarify what code you are actually using, and precisely what you might want to achieve.

G4bandit


    You wrote:

    What "3.thrd table"?? I see 2 tables in your query? Plus I see no reference to anything named Kind in the SELECT?

No response from you.

Please a bit more patience. Following is working for me and also given by examples from Qt itself
Tbl1->Tbl2 (Table refer to...) AND
Tbl1-> Tbl3
with setRelation "I am able" to get some specific column values of the refered tables but in this constellation
Tbl1->Tbl2->Tbl3
"I am not able to" get a specific attribute(column) of Tbl3. This is my challenge.
Tbl1 refer to Tbl3 indirectly trough Tbl2

You wrote
    The Qt table view will be editing the table associated with the QSqlRelationalTableModel, not something to do with SELECT statement, which you no longer have. This is quite different from any QSqlQueryModel you might have. Since you have never said what it is you might wish to edit, it's impossible to know whether this is what you want or not.

If you need further help on this question, I suggest you clarify what code you are actually using, and precisely what you might want to achieve.

Sorry for that I misunderstood you. So I did not meant "your concerns".

What do I precisly want to achieve?
I want to create a gui or widget where the connection between tbl1->tbl2->Tbl3 is resolved to only ONE TableView and the User of the widget has the overview about the mandatory attributes. So does not to fight with background SQL error messages.

My current state regarding

perform several joins at once
Workaround: to create view in SSMS and refer to the views. Reading, Writing to the table view does not make any problems.
Niceto: due to CVS (tortoise svn) or at all better understanding of Qt Data Model to perform this step inside of Qt instead creating view in SSMS.
add columns from foreign tables
I do it currently in this way

model->setTable("Tbl1");
model->setRelation(ColumnIdx, QSqlRelation("Tbl2","RelatedColumnToTbl1FromTbl2","Column1OfTbl2, Column2OfTbl2......."))
It is working, - performance is not that nice but it is working.

JonB

@G4bandit said in Adding foreign column to a "virtual table" and performing several joins from one table over several:

Please a bit more patience. Following is working for me and also given by examples from Qt itself
Tbl1->Tbl2 (Table refer to...) AND
Tbl1-> Tbl3
with setRelation "I am able" to get some specific column values of the refered tables but in this constellation
Tbl1->Tbl2->Tbl3
"I am not able to" get a specific attribute(column) of Tbl3. This is my challenge.
Tbl1 refer to Tbl3 indirectly trough Tbl2

Yes, this is precisely my understanding of the facilities offered by QSqlRelationalTableModel. It supports one "main" table, with as many foreign key relations as you wish between the main table and other FK tables. It does not support any further level of FKs from the FK tables to yet other FK tables. If you wanted that you'd need one QSqlRelationalTableModel for Tbl1->Tbl2, and another QSqlRelationalTableModel for Tbl2->Tbl3. And then you'd have to handle whatever is necessary for Tbl1->Tbl2->Tbl3 yourself.

G4bandit

@JNBarchan said in Adding foreign column to a "virtual table" and performing several joins from one table over several:

It does not support any further level of FKs from the FK tables to yet other FK tables. If you wanted that you'd need one QSqlRelationalTableModel for Tbl1->Tbl2, and another QSqlRelationalTableModel for Tbl2->Tbl3. And then you'd have to handle whatever is necessary for Tbl1->Tbl2->Tbl3 yourself.

This is exactly the info I have missed as a beginner. Otherwise without this info I would still asking myself:
"Is it because I do not understand exactly the Qt Table Model or because the Qt Table Model does not give me the option like I expact"

For me it is new of imagination that I have to handle two different RelationalTables to visualise a connection like Tbl1->Tbl2->Tbl3(-> = refer to...).

JonB

@G4bandit
No problem about being a beginner! I am too (mostly).

But don't forget, in your original question your code only showed 2 tables and a single JOIN, which one QSqlRelationalTableModel can handle. Since then you've edited it at some point, and added the complication of the JOIN from table #2 -> table #3, which one QSqlRelationalTableModel on its own can't do.

For the future of posting here, it's OK to fix "mistakes" in your original code, but it's probably better to show new code in a reply here if you need to change it so as to ask a further question. It makes it easier for us to follow what's changing.

Best of luck!

SGaist

Just in case, QSqlTableModel and derivatives are QSqlQueryModel with writing added so depending on your needs it might still be an option.

JonB

@SGaist
Just how much of the QSqlQueryModel-ness can you actually use with QSqlTableModel, in practice?

The key here is http://doc.qt.io/qt-5/qsqltablemodel.html#setQuery :

This function simply calls QSqlQueryModel::setQuery(query). You should normally not call it on a QSqlTableModel. Instead, use setTable(), setSort(), setFilter(), etc., to set up the query.

From his own QSqlQueryModel, the OP can afford to pass in the arbitrary query he shows, if he wants. But once he moves to QSqlTableModel he really needs to be using the setTable() level instead, doesn't he? Certainly if he wants to do modification statements.

[edit: Fixed class name SGaist]

SGaist

Yes that's right. But he can also add the write layer himself like it has been done for QSqlTableModel and others..