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

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

Code:

---

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.

Code:

---

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 but the result is awkward. I have read and implemented a MasterDetail Form but was not quite happy with the result since it splits the display into multiple QTableViews. I have read the documentation on QSqlRelationalTableModel 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

HI,

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

Code:

---

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

---

see QSqlRelationalTableModel detailed description

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.

Quote:

---

Originally Posted by mcosta

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

Code:

---

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

Code:

---

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)

---

Quote:

---

Originally Posted by 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.

---

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

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

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

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