Update foreign key in Qt QSqlRelationalTableModel

Question

I'm coding in python (PySide2), but the overall concept concerns the Qt Framework. Here are two tables of mine:

Table "recordings":

| Column    | Type               |
| --------  | -------------------|
| id        | int (primary key)  |
| param1    | int                |
| param2    | int                |
| ...       | int                |
| paramN    | int                |

Table "analyzed_recs":

| Column    | Type               |
| --------  | -------------------|
| id        | int (primary key)  |
| rec_id    | int (foreign key)  |  <-- Points to recordings.id
| paramN    | int                |

I need in my program to display param1 and param2 from the former. In Qt I used a QSqlRelationalTable to fulfill this objective:

def _init_db_models(self):
    self.analyzed_recs_sql_model = QSqlTableModel(self, self.db)
    self.analyzed_recs_sql_model.setTable("analyzed_recs")
    rec_id = self.analyzed_recs_sql_model.fieldIndex('rec_id')
    self.analyzed_recs_sql_model.setRelation(rec_id, QSqlRelation("recordings", "id", "param1"))
    self.analyzed_recs_sql_model.setRelation(rec_id, QSqlRelation("recordings", "id", "param2"))
    self.analyzed_recs_sql_model.select()
    self.analyzed_data_table.setModel(self.analyzed_recs_sql_model)

This code works fine in displaying the desired fields.

However, when it comes to update a record in analyzed_recs:

record = self.analyzed_recs_sql_model.record()
record.remove(record.indexOf("id"))
record.setValue("rec_id", self.current_rec_id)
record.setValue("param1", self.param1)
record.setValue("param2", param2)
self.analyzed_recs_sql_model.insertRecord(-1, record)
self.analyzed_recs_sql_model.submitAll()

The column rec_id is not set (NULL) into the table (the other params are correctly inserted into the table). On the contrary, if I avoid using QSqlRelationalTableModel and take QSqlTableModel instead, the insertion is performed correctly (as I expected), but I lose the INNER JOIN display feature.

I was thinking as a work around to create two distinct models, a QSqlRelationalTableModel only for displaying and a QSqlTableModel only for editing the data. However I don't like the extra workload of syncing the two.

I'm sure there is a Qt feature to achieve this, but unfortunately I'm missing it. Any suggestion?

score 0 · Answer 1 · answered Jun 04 '22 at 17:46

I've had the same problem using PYQT. The record object returned by calling record() method has no fields named 'rec_id' because the QSqlRelationalTableModel changes it with the referenced field name 'param1'. We can verify the field names using:

fieldcount = record.count()
for i in range(fieldcount):
    logging.info("field %s %s", i, record.fieldName(i))

so we need to add the field before assigning it:

record = self.analyzed_recs_sql_model.record()
record.remove(record.indexOf("id"))

record.append(QSqlField("rec_id"))  

record.setValue("rec_id", self.current_rec_id)

Update foreign key in Qt QSqlRelationalTableModel

1 Answers1