I have tables like below

import sqlalchemy as sa

class A(Base):
    id = sa.Column(sa.Integer)
    name = sa.Column(sa.String)

class B(Base):
    id = sa.Column(sa.Integer)
    a_id = sa.Column(sa.Integer)

and has query:

# Basic query
query = sa.select(B).join(A, A.id == B.a_id)
result = await session.execute(query)
results = result.scalars().all()

How should I change to get desired result?

query = sa.select(B).join(A, A.id == B.a_id)
result = session.execute(query)
results = result.scalars().all()

# Problem
# SOME_KEY should be indicated in query as loading column
# SOME_KEY's type should be A class
# I want below thing

results[0].SOME_KEY.name # it should give joined `A` entity's property value

I have read documentation, have seen loading techniques, but could not find solution , it is mostly for relations.

CodePudding user response：

Arbitrary query with multiple objects per result

with Session(engine) as session:
    for (b, a) in session.execute(select(B, A).join(A, B.a_id == B.id)).all():
        print (b, a)

Relationship without ForeignKey

from sqlalchemy.orm import Session, declarative_base, aliased, relationship, remote, foreign

class A(Base):
    __tablename__ = 'a_table'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    b_list = relationship('B', primaryjoin="remote(A.id) == foreign(B.a_id)", back_populates='a')

class B(Base):
    __tablename__ = 'b_table'
    id = Column(Integer, primary_key=True)
    a_id = Column(Integer)

    a = relationship('A', primaryjoin="remote(A.id) == foreign(B.a_id)", back_populates='b_list')

with Session(engine) as session:
    for (b,) in session.execute(select(B).join(B.a)).all():
        print (b, b.a_id, b.a, b.a.id, b in b.a.b_list)