Resolving complex foreign keys in QSqlRelationalTable

Question

I'm working with QSqlRelationalModel and I have some problem.

For example if we deal with simple tables like:

Location
+------+--------+
|  id  |  name  |
+------+--------+

Department
+------+-------------+
|  id  | location_id |
+------+-------------+

Then I can write:

departmentModel = new QSqlRelationalTableModel(this);
departmentModel->setTable("Department");
departmentModel->setRelation(Department_LocationId, QSqlRelation("Location", "id", "name"));
departmentView = new QTableView;
departmentView->setModel(departmentModel);
departmentView->setItemDelegate(new QSqlRelationalDelegate(this));

and it'll work fine, and display location names instead of ID's.

But in my case I can't apply this approach. Suppose I have next tables:

Person
+------+-------------+
|  id  |  firstname  |
+------+-------------+

Experience
+------+------------------+
|  id  |  person_id (FK)  |
+------+------------------+

Participant
+------+-----------------+
|  id  |  experience_id  |
+------+-----------------+ 

Assume that I want to use Participant as QSqlRelationalTable:

QSqlRelationalTable participantModel;
participantModel->setTable(Participant);
...
participantView->setModel(participantModel);
participantView->setItemDelegate(new QSqlRelationalDelegate(this));

And I want to display Person.firstname instead of experience_id in view (and also I don't want lose editing funcionality). How can I do this?

I can't use setRelation() as in example above, because:

participantModel->setRelation("experience_id", QSqlRelation("Experience", "id", WHAT_DO_I_HAVE_TO_WRITE_HERE_TO_GET_WHAT_I_WANT);

I can't write "person_id", because I want to have firstname displayed instead of person_id.

Answer 1

I think you should redesign your tables into something like this

Participant
+------+------------------+----------------------+
|  id  |  person_id (FK)  |  experience_id (FK)  |
+------+------------------+----------------------+ 

Person
+------+-------------+
|  id  |  firstname  |
+------+-------------+

Experience
+------+--------------+--------
|  id  |  experience  |  year   . . . (and so on)
+------+--------------+-------------

And now you can:

departmentMode->setTable("Participant");
departmentModel->setRelation(1,QSqlRelation("Person","id","firstName"));
departmentModel->setRelation(2,QSqlRelation("Experience","id","experience"));

I am quite confused on how you really want it to be applied in your project because of how you made your tables. Maybe if you can describe what your plan is so I can help more.