What is the correct way to query data in SQLAlchemy (async) in case of multiple levels of relationships?

Question

I'm trying to read data from related tables which are x levels deep and which have relationships specified, i.e.:

table A
table B
table C
table ABC 

Table ABC has relationships ABC.a = A, ABC.b = B and ABC.c = C, i.e. foreign keys ABC.aid = A.id, ABC.bid = B.id and ABC.cid = C.id.

• aid, bid and cid in ABC are set unique using UniqueContstraint
• relationship is using lazy="joined"

When I do select(ABC) I'm able to get all values from ABC and also from related tables, i.e.:

{ABC.a: {A}, ABC.b: {B}, ABC.c: {C}}

I have also table D which has a relationship to ABC (D.abcid = ABC.id) and I struggle to construct a correct select statement which would give me all data also from A, B and C. Actually I'm not sure if this should work or I missed / do not understand something in the documentation as I have tried various loading strategies, specified join_depth for D and ABC tables, etc. No matter what I'm getting:

sqlalchemy.exc.InvalidRequestError: The unique() method must be invoked on this Result, as it contains results that include joined eager loads against collections

I would like to get the data the same way as for 1st level relationship, i.e.:

{D.abc : {ABC.a: {A}, ABC.b: {B}, ABC.c: {C}}}

Is it possible or do I have to change the select query completely and just create multiple joins and manually pick all the values I need? I'm able to get correct records from the database when I just take the generated select statement and use it directly in a DB shell (MariaDB) so I assume that the only issue is my lack of understanding of how SQL handles/presents these records internally.

Answer 1

The issue was using uselist=True in one of the models, all relationships are working perfectly down to the lowest level now.