Updating a relational column in QSqlRelationalTableModel (PyQt6)

Question

So, I’m stumped by a problem when updating a record on a relational column in a QSqlRelationalTableModel with PyQt6.

Background: I’m developing an app which includes a GUI to keep track of clients. The clients (companies) are stored in a PostgreSQL-table (‚clients‘), where one column (‚representative‘) contains a foreign key to another table (‚humans‘). If a client is associated with a human representative, the index of that human is stored in column ‚clients.representative’.

In my app I have a window where all the fields of the table „clients“ are mapped to a QSqlRelationalTableModel („mappermodel“). This works fine and I can edit all fields in the database.

The problem comes, when I want to add a new entry in „humans“ and assign it as a representative for an existing client. For this I have a button which opens a new window, where I can input the persons detail. The input is added via a separate function to the database-table „humans“, which also works fine. This function returns the index of the new entry via querying lastval(). This index needs to be inserted in the table „clients“, relational column „representative“. For reasons I don’t understand I can’t get this to happen.

My code (minimal example):

from PyQt6.QtWidgets import QWidget, QLineEdit, QDataWidgetMapper, QVBoxLayout, QPushButton
from PyQt6.QtSql import QSqlQuery, QSqlRelationalTableModel, QSqlRelation, QSqlTableModel

client_model = QSqlRelationalTableModel()                                                                                                                                             
client_model.setTable("clients")
client_model.setEditStrategy(QSqlRelationalTableModel.EditStrategy.OnFieldChange) 
client_model.setJoinMode(QSqlRelationalTableModel.JoinMode.LeftJoin)                                                      
client_model.setRelation(3, QSqlRelation("humans", "human_id","name"))                              # humanid = 0, name = 1 in table 'humans'                         
client_model.select()

                                                                                                             
person_model = QSqlTableModel()
person_model.setTable('humans')
person_model.select()

class EditClient(QWidget):
    def __init__(self, clientnr:int):
        super(EditClient, self).__init__()

        self.example_client_name = QLineEdit()
        self.example_representative_display = QLineEdit()
        self.example_representative_display.setReadOnly(True)
        self.add_representative = QPushButton()
        self.add_representative.clicked.connect(self.open_form2)

        # bunch of other input widgets

        self.example_layout = QVBoxLayout()                                                                      
        self.example_layout.addWidget(self.example_client_name)                                                  
        self.example_layout.addWidget(self.example_representative_display)                                       
        self.example_layout.addWidget(self.add_representative)                                                   
        self.setLayout(self.example_layout)

        self.mapper = QDataWidgetMapper()  
        self.mapper.setModel(client_model)
        self.mapper.addMapping(self.example_client_name, 1)
        self.mapper.addMapping(self.example_representative_display, 3)
      
        ## clientnr is selected in another function which should not be relevant here. It is passed into this class. 

        self.mapper.setCurrentIndex(clientnr) 
        self.form2 = New_Representative()
        self.form2.add_person.clicked.connect(self.add_new_person)                                                       
                                                                      
    def open_form2(self):                                                                                        
        self.form2.show()

    def add_person(self):                                                                                        
        new_human = person_model.record()                                                                   
        new_human.setGenerated(0, False) # index of new person is generated by Postgresql                        
        new_human.setValue(1, self.example_person.text())  
                                                  
        if not person_model.insertRecord(-1, new_human):                                                    
            print(person_model.lastError().text())
        self.insert_new_index()
                                                      
                                                                                 
    def get_last_index(self):                                                                                    
        lastval = QSqlQuery()                                                                                    
        lastval.exec("select lastval()")                                                                         
        lastval.next()                                                                        
        val = lastval.value(0)                                                                                   
        return val

    def insert_new_index(self):
        new_index:int = self.get_last_index()
        index_of_rep = client_model.index(self.mapper.currentIndex(), 3)
        
        ## This is where it fails. 
                                                                     
class New_Representative(QWidget):                                                                               
    def __init__(self):                                                                                          
        super(New_Representative, self).__init__()                                                               
                                                                              
        self.example_person = QLineEdit()                                                                        
        # another bunch of input widgets here
                                                                
        self.add_person = QPushButton()                                                             
                                                             
                                                                                                         
        self.person_layout = QVBoxLayout()                                                                       
        self.person_layout.addWidget(self.example_person)                                                        
        self.person_layout.addWidget(self.add_person)                                                            
        self.setLayout(self.person_layout)                                                                       
                                                                                                         
    

What I have tried to insert the index of the new person into table 'clients':

Adding the newly inserted persons index in the relational field ‚clients.representative’ via the client_model should be straight forward. My understanding is that setData() is the appropriate method. The documentation says:

For relational columns, value must be the index, not the display value. The index must also exist in the referenced table, otherwise the function returns false.

The index of my newest entry in the table 'humans' is correctly returned by self.get_last_index. However, client_model.setData(index_of_rep, new_index, Qt.ItemDataRole.EditRole) returns False. Same thing happens with client_model.setItemData(index_of_rep, {0 : new_index, 2 : new_index}) or client_model.setItemData(index_of_rep, {2 : new_index}).

I have checked that the flags for client_model (index_of_rep) contain ItemIsEditable (which should be default for QSqlRelationalTableModel anyway). They do:

ItemFlag.ItemNeverHasChildren|ItemIsEnabled|ItemIsEditable|ItemIsSelectable

I don't get any error message from client-model.lastError().text(), so I guess the failure occurs before it reaches the database.

I really appreciate any help or insight into what is going on here. Please let me know if you need more infos.

Also: I'm aware that using nextval() is not ideal, as it could be nexted in between the methods. This shouldn't be a huge problem for my application, but I still would like to do the whole operation by using a nested SQL-Statement (like in this question). Unfortunately I don't see a way how this can be done using Qt's tablemodels. Am I missing something there?

Thanks again for any help!