[FIXED] Get values in one or more joined tables using PySide

Issue

I'm trying to understand the advantage of using QSqlRelationalTableModel versus QSqlTableModel when dealing with tables linked through unique IDs. In the following example, the organization field is correctly displayed by name instead of by ID number. However, how would I access the corresponding "size" or "address" fields of the linked record?

from PySide.QtCore import *
from PySide.QtGui import *
from PySide.QtSql import *

db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("relational_test_01.sqlite")
db.open()
q = QSqlQuery()
q.exec_("CREATE TABLE people(id INTEGER PRIMARY KEY, first VARCHAR(50), last VARCHAR(50), organization INTEGER)")
q.exec_("INSERT INTO people VALUES(1,'John', 'Smith', 1)")
q.exec_("INSERT INTO people VALUES(2,'Bob', 'Jones', 2)")
q.exec_("CREATE TABLE organizations(id INTEGER PRIMARY KEY, name VARCHAR(50),  size INTEGER, address VARCHAR(50))")
q.exec_("INSERT INTO organizations VALUES(1,'Central Gym', 30, '400 Central Street')")
q.exec_("INSERT INTO organizations VALUES(2,'Shoe Store', 5, '200 Central Street')")

db.close()


model = QSqlRelationalTableModel()
model.setTable("people")
model.setRelation(3, QSqlRelation("organizations", "id", "name"))
model.setFilter("people.id = 1")
model.select()
count = model.rowCount()
if count == 1:
    record = model.record(0)
    org = record.value(3)
    print(org)

Solution

There is a missing feature here, which has been reported as QTBUG-9320. Specifically, there is no obvious way to get the original foreign key value from the related table.

Fortunately, there is an undocumented work-around that avoids the issue. This works by exploiting the third argument of QSqlRelation, which is actually able to specifiy more than one column in the result.

Here is how to to get this to work in your example:

model = QSqlRelationalTableModel()
model.setTable("people")
model.setRelation(3, QSqlRelation("organizations", "id", "name, relTblAl_3.id"))
model.setFilter("people.id = 1")
model.select()
record = model.record(0)
for i in range(record.count()):
    print((i, record.value(i)))
org_model = model.relationModel(3)
org_model.setFilter('organizations.id = %s' % record.value(4))
org_model.select()
record = org_model.record(0)
print()
for i in range(record.count()):
    print((i, record.value(i)))

output:

(0, 1)
(1, 'John')
(2, 'Smith')
(3, 'Central Gym')
(4, 1)

(0, 1)
(1, 'Central Gym')
(2, 30)
(3, '400 Central Street')

The relTblAl_3.id is needed to avoid name clashes, and is documented in the notes at the end of the Detailed Description for QSqlRelationalTableModel. However, this can be avoided if you always use unique names for every column (e.g. using org_id instead of id would not then require qualification with the table name).

Answered By - ekhumoro