How do I display information from three database tables in a QTableView?

[Nele]

Here is the story: The application I am working on needs information stored in three tables of a normalized relational database. The information has to be displayed in one QTableView since that would be the "natural view" onto the data. In sql speak its a simple equi join as in
select * from A, B, C where A.b=B.id AND A.c=C.id
The app needs to be able to create, update and delete rows in/from the three tables and display the results in the QTableView. Coming from web apps, I thought this would be a pretty easy one, but I can't figure out how to do it in qt.

The qt examples use a QSqlRelationalTableModel in combination with a QTableView. Foreign keys in main table A are resolved using e.g.
model->setRelation(2, QSqlRelation("city", "id", "name"));
Is it possible to resolve foreign keys to more than one field/column in the model? I need more information from table B and C than just replacing the id columns in table A by one value from table B or C.

I have searched the web and read threads aiming at basically the same topic. Those threads were either not answered or answered by proposing to implement a custom model - is there no other way to solve it?

The examples I have found so far on how to implement a custom model are - let's say "basic" - do you know a good web resource to start from? I have read and implemented half of a Custom Model (http://www.civilnet.cn/book/embedded/GUI/Qt4/ch10lev1sec3.html#ch10lev1sec3) but the result is awkward. I have read and implemented a MasterDetail Form (http://www.civilnet.cn/book/embedded/GUI/Qt4/ch13lev1sec3.html#ch13lev1sec3) but was not quite happy with the result since it splits the display into multiple QTableViews. I have read the documentation on QSqlRelationalTableModel (http://doc.qt.nokia.com/4.6/qsqlrelationaltablemodel.html) and experimented with it.

Bare in mind I am new to qt and probably miss one or more obvious things. So, all suggestions are welcome! Thank you in advance for your help and time!
Nele

[mcosta]

HI,

have you tried to set the second relation calling setRelation again?


model->setRelation(3, QSqlRelation(C_tableName, "id", fieldname);


see QSqlRelationalTableModel detailed description

[ChrisW67]

You can get the display you want by setting the QTableView's model to a QSqlQueryModel with your three-table SQL in it. There is no way for the view to update through this model as-is though. By deriving from QSqlQueryModel and implementing flags(), setData(), insertRows(), and removeRows() you should be able to make it updateable . You will need to decide how to handle trying to set data in one column from table C when the corresponding rows in tables A and B do not exist, or if you try to update one of the foreign key fields etc. (this is why it doesn't Just Work (TM): there is no single correct answer). The Model Subclassing Reference, Creating New Models, and the Query Model Example covers what you would need to do.

[Nele]

have you tried to set the second relation calling setRelation again?


model->setRelation(3, QSqlRelation(C_tableName, "id", fieldname);



Yes, I have. Each foreign key column in table A can be replaced by one value from the referenced table (B or C) - but that is not what I want. I need the whole record from tables B and C, e.g. something like

table A = (A.id, A.b_id, A.c_id, A.name, A.age) and
table B = (B.id, B.profession, B.company, ...) and
table C = (C.id, C.street, C.zipcode, C.city, ...) resulting in the view
(A.id, B.profession, B.company, C.street, C.zipcode, C.city, A.name, A.age)

[Nele]

You can get the display you want by setting the QTableView's model to a QSqlQueryModel with your three-table SQL in it. There is no way for the view to update through this model as-is though. By deriving from QSqlQueryModel and implementing flags(), setData(), insertRows(), and removeRows() you should be able to make it updateable . You will need to decide how to handle trying to set data in one column from table C when the corresponding rows in tables A and B do not exist, or if you try to update one of the foreign key fields etc. (this is why it doesn't Just Work (TM): there is no single correct answer). The Model Subclassing Reference, Creating New Models, and the Query Model Example covers what you would need to do.

Ok. I will try that and post back. Thank you for your suggestion!

[Nele]

@ChrisW67: It took me ages to implement it, but finally it works using the approach you suggested. Thank you for your explanaition!

[nasit.bhavin@gmail.com]

Nele, Can you post few code OR some idea? How did you do that?

[lukeQt]

This is an old post, but can you post a solution to this problem?